叶磊数据库.doc

实验报告姓名： 叶磊 学号： 1 任务概述（任务说明）1、 存储过程使用相应SQL语句，完成如下操作：1. 新建一存储过程proc1,显示指定用户ID的订单信息列表（订单号、成交时间、订单总金额、订单状态及所有订单的平均金额），并成交时间降序排列。2. 新建一存储过程proc2,通过输入订单号、书目ID及数量，实现给该订单增加商品、更新订单总金额，并返回当前订单包含的商品总数（商品类别数量）。3. 新建一存储过程proc3，通过修改指定订单、指定商品的价格，并返回该订单的新总金额及优惠金额。4. 新建一存储过程proc4，统计各订单总金额的分布情况。订单金额划分情况如下： 0-50元、51-100元、101-150元、151-200元和201元以上。5. 新建一存储过程 proc5，实现查询购买过指定书目ID的用户还同时购买过的购买次数最多的前3名书目名称及购买次数，并按照购买次数排序。6. 新建一存储过程proc6，实现查询与指定用户ID购买过相同商品的用户及购买过相同商品的数量，并按照相同商品数量降序排列。2、 触发器1. 创建一触发器tg1，当往orderbook表中增加记录时，实现商品库存相应减少；当修改orderbook表中相应商品数量时，实现商品数量相应变动。2. 创建一触发器tg2，当往orderbook中增加记录时，如果商品库存量少于10，则不允许购买该商品，并给出相应提示。新建图书订购情况统计表bookstas（包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定），并根据数据库的订单情况将社科类图书的订购情况插入表中。创建一触发器tg3，当往orderbook表中增加记录时，自动更新bookstas表相应图书的统计信息4源码，文档化-新建一存储过程proc1,显示指定用户ID的订单信息列表-（订单号、成交时间、订单总金额、订单状态及所有订单的平均金额），并成交时间降序排列。create procedure proc1user_id varchar(20)asselect oid,ordertime,payment,orderstate,avgpayment,sumpaymentfrom order,orderstate,(select avg(payment) avgpaymentfrom orderwhere user = user_id) a,(select sum(payment) sumpaymentfrom orderwhere user = user_id) bwhere order.state = orderstate.osidand user = user_idorder by ordertime descgoexec proc1 102-新建一存储过程proc2,通过输入订单号、书目ID及数量，-实现给该订单增加商品、更新订单总金额，并返回当前订单包含的商品总数（商品类别数量）。create procedure proc2(order_id varchar(20),book_d varchar(20),book_s varchar(20)asupdate orderset payment = payment + pricefrom book,orderwhere oid in(select orderidfrom orderbookwhere bookid = book_d) and bid = book_dupdate orderbookset quantity = quantity + book_swhere orderid = order_idselect bookidfrom orderbookwhere orderid = order_idgoexec proc2 2014001,1001,4-新建一存储过程proc3，通过修改指定订单、指定商品的价格，并返回该订单的新总金额及优惠金额。create procedure proc3order_id varchar(20),book_id varchar(20),s_price varchar(20)asupdate orderset payment = payment + (s_price - price) * quantity) from book,orderbookwhere bookid = book_id and( bid = book_id and orderid = order_id)select *from orderwhere oid = order_idgoexec proc3 2014001,1001,20-新建一存储过程proc4，统计各订单总金额的分布情况。订单金额划分情况如下：- 0-50元、-100元、-150元、-200元和元以上。create procedure proc4asbegindeclare zerotofifty int,fiftytofundred int,hundredtohunf int,hunftoTwoh int,moretwoh int;select zerotofifty = COUNT(casewhen payment = 0 AND payment = 51 AND payment = 101 AND payment = 151 AND payment = 201 then 5 end)from orderprint zerotofifty;print fiftytofundred;print hundredtohunf;print hunftoTwoh;print moretwohendexec proc4-新建一存储过程proc5，实现查询购买过指定书目ID的用户还同时-购买过的购买次数最多的前名书目名称及购买次数，并按照购买次数排序。create procedure proc5book_id varchar(20)asbegindeclare user_id int;select user_id = uidfrom orderbook,order,userwhere bookid = book_idand orderid = oidand order.user = uidselect uid,name,oid,quantity,a.bookid,a.titlefrom user,order,orderbook,(select top 3 sum(quantity) sumquantity,bookid,titlefrom orderbook,bookwhere bookid = bidgroup by bookid,titleorder by sumquantity desc) awhere uid = user_idand uid = userand oid = orderidand orderbook.bookid = a.bookidorder by a.sumquantity descendexec proc5 1003-新建一存储过程proc6，实现查询与指定用户ID购买过-相同商品的用户及购买过相同商品的数量，并按照相同商品数量降序排列。create procedure proc6user_id varchar(20)asbeginselect a.uid,,a.bookid,a.quantityfrom orderbook,order,user,(select uid,name,bookid,quantityfrom orderbook,order,userwhere orderid = oidand user = uid) awhere user.uid = user_idand order.user = user.uidand order.oid = orderbook.orderidand a.uid != user.uidand a.bookid = orderbook.bookidgroup by a.uid,,a.bookid,a.quantityorder by quantity descendexec proc6 102-创建一触发器tg1，当往orderbook表中增加记录时，-实现商品库存相应减少；当修改orderbook表中相应商品数量时，实现商品数量相应变动。create trigger tg1on orderbook for insert,updateasbegindeclare order_id int,book_id int, quantity int;select book_id = bookid,quantity = quantity from inserted;update bookset book.stock = book.stock - quantitywhere book_id = book.bid;print insert succeed;declare book_idf int, quantityf int,bookido int, quantityo int,lst int;select bookido = bookid,quantityo = quantity from deleted;select book_idf = bookid,quantityf = quantity from inserted;update bookset book.stock = book.stock - quantityf + quantityowhere book_idf = book.bid;print update succeed;enddrop trigger tg1-创建一触发器tg2，当往orderbook中增加记录时，-如果商品库存量少于，则不允许购买该商品，并给出相应提示。create trigger tg2on orderbook after insertas begin declare stock int,bookid int; select bookid = s.bookid from inserted s; select stock = b.stock from book b where b.bid = bookid; if(stock 10) raiserror(库存少于，不允许购买,16,1); rollback tran;enddrop trigger tg2-新建图书订购情况统计表bookstas（包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定），-并根据数据库的订单情况将社科类图书的订购情况插入表中。创建一触发器tg3，-当往orderbook表中增加记录时，自动更新bookstas表相应图书的统计信息create table bookstas(id int primary key,title varchar(20) not null,category varchar(20) not null,price float not null,quantity int);create trigger tg3on orderbook for insertas begindeclare bookid int,quantity int;select bookid = bookid,quantity = quantity from inserted; update bookstas set quantity = quantity + quantitywhere id = bookidenddrop trigger tg35 执行过程截屏6 总结 存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称