叶磊数据库精品管理资料Word文件下载.docx
《叶磊数据库精品管理资料Word文件下载.docx》由会员分享,可在线阅读,更多相关《叶磊数据库精品管理资料Word文件下载.docx(15页珍藏版)》请在冰豆网上搜索。
@user_idvarchar(20)
as
selectoid,ordertime,payment,orderstate,avgpayment,sumpayment
from"
order"
orderstate,
(selectavg(payment)avgpayment
from"
where"
user”=@user_id)a,
(selectsum(payment)sumpayment
order”
where”user”=@user_id)b
where"
order”。
state=orderstate。
osid
and"
user”=@user_id
orderbyordertimedesc
go
execproc1102
-——新建一存储过程proc2,通过输入订单号、书目ID及数量,
——-实现给该订单增加商品、更新订单总金额,并返回当前订单包含的商品总数(商品类别数量)。
createprocedureproc2(
@order_idvarchar(20),
@book_dvarchar(20),
@book_svarchar(20)
)
update"
setpayment=payment+price
frombook,"
whereoidin(
selectorderid
fromorderbook
wherebookid=@book_d)
andbid=@book_d
updateorderbook
setquantity=quantity+@book_s
whereorderid=@order_id
selectbookid
fromorderbook
execproc22014001,1001,4
——-新建一存储过程proc3,通过修改指定订单、指定商品的价格,并返回该订单的新总金额及优惠金额.
createprocedureproc3
@order_idvarchar(20),
@book_idvarchar(20),
@s_pricevarchar(20)
update”order"
setpayment=payment+((@s_price—price)*quantity)
frombook,orderbook
wherebookid=@book_idand(bid=@book_idandorderid=@order_id)
select*
from”order”
whereoid=@order_id
execproc32014001,1001,20
-——新建一存储过程proc4,统计各订单总金额的分布情况。
订单金额划分情况如下:
——-0-50元、—100元、—150元、-200元和元以上。
createprocedureproc4
begin
declare@zerotofiftyint,
@fiftytofundredint,
@hundredtohunfint,
@hunftoTwohint,
@moretwohint;
select@zerotofifty=COUNT(case
whenpayment〉=0ANDpayment〈=50then’1'
end),
@fiftytofundred=COUNT(case
whenpayment〉=51ANDpayment〈=100then'
2’end),
@hundredtohunf=COUNT(case
whenpayment〉=101ANDpayment<
=150then'
3'
@hunftoTwoh=COUNT(case
whenpayment〉=151ANDpayment〈=200then'
4’end),
@moretwoh=COUNT(case
whenpayment〉=201then’5’end)
from”order”
print@zerotofifty;
print@fiftytofundred;
print@hundredtohunf;
print@hunftoTwoh;
print@moretwoh
end
execproc4
———新建一存储过程proc5,实现查询购买过指定书目ID的用户还同时
———购买过的购买次数最多的前名书目名称及购买次数,并按照购买次数排序.
createprocedureproc5
@book_idvarchar(20)
declare@user_idint;
select@user_id=uid
fromorderbook,"
,”user”
wherebookid=@book_id
andorderid=oid
and”order”.”user"
=uid
selectuid,name,oid,quantity,a.bookid,a。
title
from"
user”,”order”,orderbook,(selecttop3sum(quantity)sumquantity,bookid,title
fromorderbook,book
wherebookid=bid
groupbybookid,title
orderbysumquantitydesc)a
whereuid=@user_id
anduid="
user”
andoid=orderid
andorderbook.bookid=a。
bookid
orderbya。
sumquantitydesc
execproc51003
——-新建一存储过程proc6,实现查询与指定用户ID购买过
———相同商品的用户及购买过相同商品的数量,并按照相同商品数量降序排列。
createprocedureproc6
selecta。
uid,a.name,a.bookid,a。
quantity
fromorderbook,”order”,”user"
,(selectuid,name,bookid,quantity
fromorderbook,”order”,"
whereorderid=oid
and"
user”=uid)a
where”user"
.uid=@user_id
and”order”。
”user”=”user”.uid
and"
。
oid=orderbook.orderid
anda.uid!
="
user”。
uid
anda。
bookid=orderbook。
groupbya。
uid,a.name,a。
bookid,a。
orderbyquantitydesc
execproc6102
---创建一触发器tg1,当往orderbook表中增加记录时,
—-—实现商品库存相应减少;
当修改orderbook表中相应商品数量时,实现商品数量相应变动.
createtriggertg1
onorderbook
forinsert,update
declare@order_idint,
@book_idint,
@quantityint;
select@book_id=bookid,@quantity=quantity
frominserted;
updatebook
setbook.stock=book。
stock-@quantity
where@book_id=book。
bid;
print’insertsucceed’;
declare@book_idfint,
@quantityfint,
@bookidoint,
@quantityoint,
@lstint;
select@bookido=bookid,@quantityo=quantity
fromdeleted;
select@book_idf=bookid,@quantityf=quantity
frominserted;
setbook。
stock=book.stock—@quantityf+@quantityo
where@book_idf=book.bid;
print’updatesucceed’;
droptriggertg1
-——创建一触发器tg2,当往orderbook中增加记录时,
—-—如果商品库存量少于,则不允许购买该商品,并给出相应提示.
createtriggertg2
onorderbookafterinsert
as
declare@stockint,@bookidint;
select@bookid=s。
bookid
frominserteds;
select@stock=b。
stock
frombookb
whereb。
bid=@bookid;
if(@stock<
10)
raiserror('
库存少于,不允许购买’,16,1);
rollbacktran;
droptriggertg2
-——新建图书订购情况统计表bookstas(包含图书编号、图书名称、图书类别、图书价格和订购册数,数据类型自定),
—--并根据数据库的订单情况将社科类图书的订购情况插入表中。
创建一触发器tg3,
—--当往orderbook表中增加记录时,自动更新bookstas表相应图书的统计信息
createtablebookstas
(
idintprimarykey,
titlevarchar(20)notnull,
categoryvarchar(20)notnull,
pricefloatnotnull,
quantityint
);
createtriggertg3
forinsert
declare@bookidint,
@quantityint;
select@bookid=bookid,@quantity=quantity
updatebookstas
setquantity=quantity+@quantity
whereid=@bookid
droptriggertg3
5执行过程截屏
6总结
存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。
存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值.
1:
触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。
所以触发器可以用来实现对表实施复杂的完整性约`束.
2:
SQLServer为每个触发器都创建了两个专用表﹕Inserted表和Deleted表.这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。
这两个表的结构总是与被该触发器作用的表的结构相同.触发器执行完成后﹐与该触发器相关的这两个表也被删除。