SQL实验报告9362.pdf

《SQL实验报告9362.pdf》由会员分享，可在线阅读，更多相关《SQL实验报告9362.pdf（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验四触发器实验（一）after 触发器（1）在 lineitem 表上定义一个 after 触发器，当修改列项目 extendedprice discount tax 时，要把 orders 表的 totalprice 一起修改，以保证数据一致性 CREATE TRIGGER trig_lineitem_price_update on lineitem for update as begin if(UPDATE(extendedprice)or UPDATE(tax)or UPDATE(discount)begin -声明游标变量指向 inserted 表 declare cursor_in

2、serted cursor read_only for select orderkey,linenumber,extendedprice,discount,tax from inserted -声明变量获取查找信息 declare orderkey int,linenumber int,extendedprice real,discount real,tax real -打开游标 open cursor_inserted -读取游标 fetch next from cursor_inserted into orderkey,linenumber,extendedprice,discount,t

3、ax while FETCH_STATUS=0 begin -声明一个变量保存重新计算的新价格 declare new_totalprice real select new_totalprice=extendedprice*(1-discount)*(1+tax)-用新的总价格变量更新 orders 表的 totalprice update orders set totalprice=new_totalprice where orderkey=orderkey fetch next from cursor_inserted into orderkey,linenumber,extendedpr

4、ice,discount,tax end deallocate cursor_inserted end end （2）在 lineitem 表上定义一个 after 触发器，当增加一项订单明细时，自动修改 orders 表的 totalprice，以保证数据一致性 CREATE TRIGGER trig_lineitem_price_insert on lineitem for insert as begin -声明游标变量指向inserted表 declare cursor_inserted cursor read_only for select orderkey,linenumber,ex

5、tendedprice,discount,tax from inserted -声明变量获取查找信息 declare orderkey int,linenumber int,extendedprice real,discount real,tax real -打开游标 open cursor_inserted -读取游标 fetch next from cursor_inserted into orderkey,linenumber,extendedprice,discount,tax while FETCH_STATUS=0 begin -声明一个变量保存重新计算的新价格 declare n

6、ew_totalprice real select new_totalprice=extendedprice*(1-discount)*(1+tax)-用新的总价格变量更新orders表的totalprice update orders set totalprice=totalprice+new_totalprice where orderkey=orderkey fetch next from cursor_inserted into orderkey,linenumber,extendedprice,discount,tax end deallocate cursor_inserted e

7、nd (3)在lineitem表上定义一个after触发器，当删除一项订单明细记录时，自动修改orders表的totalprice，以保证数据一致性 CREATE TRIGGER trig_lineitem_price_delete on lineitem for delete AS begin -声明游标变量指向deleted表 declare cursor_deleted cursor read_only for select orderkey,linenumber,extendedprice,discount,tax from deleted -声明变量获取查找信息 declare or

8、derkey int,linenumber int,extendedprice real,discount real,tax real -打开游标 open cursor_deleted -读取游标 fetch next from cursor_deleted into orderkey,linenumber,extendedprice,discount,tax while FETCH_STATUS=0 begin -声明一个变量保存重新计算的新价格 declare new_totalprice real select new_totalprice=extendedprice*(1-disco

9、unt)*(1+tax)-用新的总价格变量更新orders表的totalprice update orders set totalprice=totalprice-new_totalprice where orderkey=orderkey fetch next from cursor_inserted into orderkey,linenumber,extendedprice,discount,tax end deallocate cursor_inserted end (4)验证 update 触发器-查看号订单的 totalprice select*from orders where

10、orderkey=1830;-查看明细表的相关信息 select*from lineitem where orderkey=1830 and linenumber=1;-验证 update 触发器 update lineitem set tax=tax+where orderkey=1830;（二）instead of 触发器（1）在 lineitem 表上定义一个 instead of update 触发器，当修改明细表中的数量quantity 时，应先检查供应表 partsupp 的 availqty 是否足够，不足够则拒绝执行，否则执行并修改相应数值以保证数据一致性 由于 instead

11、 of 触发器更新某个表会使得该表上其他不满足更新列不能更新，因此逆向思维使用 after 触发器实现相同效果 即先更新 quantity，再比较 availqty，如果满足更新数量，就修改 partsupp 表的 availqty，如果不满足，则把 lineitem的 quantity 更新之后的数据重新修改回来 create trigger trig_lineitem_quantity_update on lineitem for update as begin if UPDATE(quantity)begin -声明游标变量分别指向 inserted 表和 deleted 表 decla

12、re cursor_inserted cursor read_only for select orderkey,partkey,suppkey,linenumber,quantity from inserted declare cursor_deleted cursor read_only for select quantity from deleted -声明变量获取查找信息 declare quantity_diff_lineitem int,quantity_partsupp int declare suppkey int,partkey int,orderkey int,linenum

13、ber int,qty_inserted int,qty_deleted int -打开游标 open cursor_inserted open cursor_deleted -读取游标数值赋给变量 fetch next from cursor_inserted into orderkey,partkey,suppkey,linenumber,qty_inserted fetch next from cursor_deleted into qty_deleted while fetch_status=0 begin -计算订单明细修改时，订购数量的变化值 inserted 表项-deleted

14、 表项 select quantity_diff_lineitem=qty_inserted-qty_deleted -从 partsupp 表获取 availqty 值，注意 partsupp 表的主键为（partkey，suppkey）select quantity_partsupp=availqty from partsupp where suppkey=suppkey and partkey=partkey -开始判断 begin if quantity_diff_lineitem=0 print 更新的数量和原表中的值相同，不需要更新 else if quantity_diff_li

15、neitem=quantity_partsupp begin update partsupp set availqty=availqty-quantity_diff_lineitem where suppkey=suppkey and partkey=partkey print 两个表都更新成功 end else begin update lineitem set quantity=quantity+quantity_diff_lineitem where orderkey=orderkey and linenumber=linenumber print 更新失败 end end fetch

16、next from cursor_inserted into orderkey,partkey,suppkey,linenumber,qty_inserted fetch next from cursor_deleted into qty_deleted end deallocate cursor_inserted deallocate cursor_deleted end end （2）在 lineitem 表上定义一个 instead of insert 触发器，当插入明细表中一条记录 时，应先检查供应表 partsupp 的 availqty 是否足够 quantity 的数量 crea

17、te trigger trig_lineitem_quantity_insert on lineitem instead of insert as begin -声明游标变量指向 inserted 表 declare cursor_inserted cursor read_only for select orderkey,partkey,suppkey,linenumber,quantity from inserted -声明变量获取查找信息 declare quantity int,availqty int,suppkey int,partkey int,orderkey int,linen

18、umber int -打开游标 open cursor_inserted -读取游标 fetch next from cursor_inserted into orderkey,partkey,suppkey,linenumber,quantity while FETCH_STATUS=0 begin -为变量赋值 select availqty=availqty from partsupp where suppkey=suppkey and partkey=partkey if quantity=availqty -如果可以更新 begin /*将 inserted 表中的记录插入到明细表*

19、/insert into lineitem select*from inserted where orderkey=orderkey and linenumber=linenumber /*同时更新 partsupp 表的数量*/update partsupp set availqty=availqty-quantity where suppkey=suppkey and partkey=partkey print partsupp 表有足够的货物可以满足 lineitem 的 quantity，插入成功 end else begin print partsupp 表没有足够的货物可以满足 l

20、ineitem 的 quantity，插入失败 end fetch next from cursor_inserted into orderkey,partkey,suppkey,linenumber,quantity end deallocate cursor_inserted end （3）在 lineitem 表上定义一个 instead of delete 触发器，当删除明细表中记录 时，同时改变供应表 partsupp 的 availqty 数值 create trigger trig_lineitem_quantity_delete on lineitem instead of d

21、elete as begin -声明游标变量指向 deleted 表 declare cursor_deleted cursor read_only for select orderkey,partkey,suppkey,linenumber,quantity from deleted -声明变量 declare suppkey int,partkey int,orderkey int,linenumber int,quantity int -打开游标 open cursor_deleted -读取游标 fetch next from cursor_deleted into orderkey,

22、partkey,suppkey,linenumber,quantity while FETCH_STATUS=0 begin /*删除*/delete from lineitem where linenumber=linenumber and orderkey=orderkey /*同时更新 partsupp 表的数量*/update partsupp set availqty=availqty+quantity where suppkey=suppkey and partkey=partkey print 删除成功，并且已经把货物数量归还到 partsupp 里 fetch next fro

23、m cursor_deleted into orderkey,partkey,suppkey,linenumber,quantity end deallocate cursor_deleted end （4）验证 update 触发器-查看lineitem的quantity select*from lineitem where orderkey=1830 and linenumber=1;-查询partsupp表的availqty select*from partsupp where suppkey=(select suppkey from lineitem where orderkey=18

24、30)and partkey=(select partkey from lineitem where orderkey=1830 and linenumber=1)-更新数量过大 -更新的值和原值相同 -更新到+200数量，成功 update lineitem set quantity=quantity+200 where orderkey=1830 and linenumber=1;-更新+200成功后lineitem的quantity变化-更新+200成功后partsupp表的availqty变化 实验到此。所有创建的触发器结果 删除触发器 drop trigger trig_lineit

25、em_delete;实验六存储过程实验（1）定义无参数的存储过程并执行 更新所有订单总价 go CREATE PROCEDURE Proc_CalTotalPrice AS BEGIN update orders set totalprice=(select sum(extendedprice*(1-discount)*(1+tax)from lineitem where=END go 执行此存储过程 exec Proc_CalTotalPrice;（2）定义有参数的存储过程并执行 更新给定订单号的总价 go create procedure Pro_CalTotalPrice4Order i

26、d_order integer as begin update orders set totalprice=(select SUM(extendedprice*(1-discount)*(1+tax)from lineitem where=end go 执行此存储过程 exec Pro_CalTotalPrice4Order id_order=2;（3）定义有局部变量的存储过程 更新某一个顾客所有订单的总价 go create procedure Proc_CalTotalPrice4Customer name_customer varchar(50)as declare custkey_cu

27、stomer int begin select custkey_customer=custkey from customer where name_customer=RTRIM(name)update orders set totalprice=(select SUM(extendedprice*(1-discount)*(1+tax)from lineitem where=and=custkey_customer)end go 执行此存储过程 exec Proc_CalTotalPrice4Customer name_customer=艾锦亮;查看结果 update lineitem set

28、 extendedprice=100,tax=where orderkey=1830;select*from lineitem where orderkey=1830;select*from orders where custkey=(select custkey from customer where name=艾锦亮);（4）定义一个带输出参数的存储过程 更新某个顾客的所有的订单总价，并输出总价 go create procedure Proc_CalTotalPrice4Customer2 name_customer varchar(50),totalprice_order real o

29、utput as-声明一个变量存储 name 对应的顾客编号 declare custkey_customer integer -为该变量赋值 select custkey_customer=custkey from customer where name_customer=RTRIM(name)begin -更改订单总价 update orders set totalprice=(select SUM(extendedprice*(1-discount)*(1+tax)from lineitem where=and=custkey_customer)-为返回变量赋值 select total

30、price_order=totalprice from orders where custkey=custkey_customer end go -执行带有输出参数的存储过程要声明输出参数变量 declare totalprice real;exec Proc_CalTotalPrice4Customer2 艾锦亮,totalprice output;-在屏幕上输出返回值结果 select totalprice;实验到此，所有结果显示 （5）修改存储过程名 exec sp_rename Pro_CalTotalPrice4Order,CalTotalPrice4Order;（6）编译存储过程 exec sp_recompile CalTotalPrice4Order;（7）删除存储过程 drop procedure CalTotalPrice4Order;exec sp_helptext CalTotalPrice4Order;