SQL实验报告.doc

实验四触发器实验（一） after 触发器（1） 在lineitem表上定义一个after触发器，当修改列项目extendedprice discount tax时，要把orders表的totalprice一起修改，以保证数据一致性CREATE TRIGGER trig_lineitem_price_update on lineitemfor update as beginif (UPDATE(extendedprice) or UPDATE(tax) or UPDATE(discount)begin-声明游标变量指向inserted表declare cursor_inserted 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,taxwhile FETCH_STATUS=0begin -声明一个变量保存重新计算的新价格declare new_totalprice realselect new_totalprice=extendedprice*(1-discount)*(1+tax)-用新的总价格变量更新orders表的totalpriceupdate orders set totalprice=new_totalprice where orderkey=orderkeyfetch next from cursor_inserted into orderkey,linenumber,extendedprice,discount,taxenddeallocate cursor_insertedendend（2）在lineitem表上定义一个after触发器，当增加一项订单明细时，自动修改orders表的totalprice，以保证数据一致性CREATE TRIGGER trig_lineitem_price_insert on lineitemfor insert asbegin-声明游标变量指向inserted表declare cursor_inserted 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,taxwhile FETCH_STATUS=0begin -声明一个变量保存重新计算的新价格declare new_totalprice realselect new_totalprice=extendedprice*(1-discount)*(1+tax)-用新的总价格变量更新orders表的totalpriceupdate orders set totalprice=totalprice+new_totalprice where orderkey=orderkeyfetch next from cursor_inserted into orderkey,linenumber,extendedprice,discount,taxenddeallocate cursor_insertedend (3) 在lineitem表上定义一个after触发器，当删除一项订单明细记录时，自动修改orders表的totalprice，以保证数据一致性CREATE TRIGGER trig_lineitem_price_delete on lineitemfor delete ASbegin-声明游标变量指向deleted表declare cursor_deleted cursor read_only for select orderkey,linenumber,extendedprice,discount,tax from deleted- 声明变量获取查找信息declare orderkey int,linenumber int,extendedprice real,discount real,tax real- 打开游标open cursor_deleted- 读取游标fetch next from cursor_deleted into orderkey,linenumber,extendedprice,discount,taxwhile FETCH_STATUS=0begin -声明一个变量保存重新计算的新价格declare new_totalprice realselect new_totalprice=extendedprice*(1-discount)*(1+tax)-用新的总价格变量更新orders表的totalpriceupdate orders set totalprice=totalprice-new_totalprice where orderkey=orderkeyfetch next from cursor_inserted into orderkey,linenumber,extendedprice,discount,taxenddeallocate cursor_insertedend(4)验证update触发器-查看号订单的totalpriceselect * from orders where orderkey=1830;-查看明细表的相关信息select * from lineitem where orderkey=1830 and linenumber=1;-验证update触发器update lineitem set tax=tax+0.05 where orderkey=1830;（二） instead of 触发器（1） 在lineitem表上定义一个instead of update触发器，当修改明细表中的数量quantity时，应先检查供应表partsupp的availqty是否足够，不足够则拒绝执行，否则执行并修改相应数值以保证数据一致性由于instead of触发器更新某个表会使得该表上其他不满足更新列不能更新，因此逆向思维使用after触发器实现相同效果 即先更新quantity，再比较availqty，如果满足更新数量，就修改partsupp表的availqty，如果不满足，则把lineitem的quantity更新之后的数据重新修改回来create trigger trig_lineitem_quantity_update on lineitemfor update asbeginif UPDATE(quantity)begin-声明游标变量分别指向inserted表和deleted表declare cursor_inserted cursor read_only for select orderkey,partkey,suppkey,linenumber,quantity from inserteddeclare cursor_deleted cursor read_only for select quantity from deleted- 声明变量获取查找信息declare quantity_diff_lineitem int,quantity_partsupp intdeclare suppkey int,partkey int,orderkey int,linenumber int ,qty_inserted int ,qty_deleted int- 打开游标open cursor_insertedopen cursor_deleted- 读取游标数值赋给变量fetch next from cursor_inserted into orderkey,partkey,suppkey,linenumber,qty_insertedfetch next from cursor_deleted into qty_deletedwhile fetch_status=0begin -计算订单明细修改时，订购数量的变化值inserted表项-deleted表项select quantity_diff_lineitem=qty_inserted-qty_deleted-从partsupp表获取availqty值，注意partsupp表的主键为（partkey，suppkey）select quantity_partsupp=availqty from partsuppwhere suppkey=suppkey and partkey=partkey - 开始判断beginif quantity_diff_lineitem=0print 更新的数量和原表中的值相同，不需要更新else if quantity_diff_lineitem=quantity_partsuppbeginupdate partsupp set availqty=availqty-quantity_diff_lineitemwhere suppkey=suppkey and partkey=partkey print 两个表都更新成功endelsebeginupdate lineitem set quantity=quantity+quantity_diff_lineitemwhere orderkey=orderkey and linenumber=linenumberprint 更新失败endendfetch next from cursor_inserted into orderkey,partkey,suppkey,linenumber,qty_insertedfetch next from cursor_deleted into qty_deletedenddeallocate cursor_inserted deallocate cursor_deletedendend（2）在lineitem表上定义一个instead of insert触发器，当插入明细表中一条记录时，应先检查供应表partsupp的availqty是否足够quantity的数量create trigger trig_lineitem_quantity_insert on lineiteminstead of insertasbegin-声明游标变量指向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,linenumber int- 打开游标open cursor_inserted- 读取游标fetch next from cursor_inserted into orderkey,partkey,suppkey,linenumber,quantitywhile FETCH_STATUS=0begin -为变量赋值select availqty =availqty from partsuppwhere suppkey=suppkey and partkey=partkey if quantity=availqty -如果可以更新begin/*将inserted表中的记录插入到明细表*/insert into lineitem select * from inserted where orderkey=orderkey and linenumber =linenumber/*同时更新partsupp表的数量*/update partsupp set availqty=availqty-quantitywhere suppkey=suppkey and partkey=partkey print partsupp表有足够的货物可以满足lineitem的quantity，插入成功endelsebeginprint partsupp表没有足够的货物可以满足lineitem的quantity，插入失败endfetch next from cursor_inserted into orderkey,partkey,suppkey,linenumber,quantityenddeallocate cursor_insertedend（3）在lineitem表上定义一个instead of delete触发器，当删除明细表中记录时，同时改变供应表partsupp的availqty数值create trigger trig_lineitem_quantity_delete on lineiteminstead of deleteasbegin-声明游标变量指向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 ,partkey,suppkey,linenumber,quantitywhile FETCH_STATUS=0begin /*删除*/delete from lineitem where linenumber=linenumber and orderkey =orderkey /*同时更新partsupp表的数量*/update partsupp set availqty=availqty+quantitywhere suppkey=suppkey and partkey=partkey print 删除成功，并且已经把货物数量归还到partsupp里fetch next from cursor_deleted into orderkey ,partkey,suppkey,linenumber,quantityenddeallocate cursor_deletedend（4）验证update触发器-查看lineitem的quantityselect * from lineitem where orderkey=1830 and linenumber=1;-查询partsupp表的availqtyselect * from partsupp where suppkey =(select suppkey from lineitem where orderkey=1830) 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_lineitem_delete;实验六存储过程实验（1） 定义无参数的存储过程并执行更新所有订单总价goCREATE PROCEDURE Proc_CalTotalPrice ASBEGINupdate orders set totalprice=(select sum(extendedprice*(1-discount)*(1+tax)from lineitemwhere orders.orderkey=lineitem.orderkey)ENDgo执行此存储过程exec Proc_CalTotalPrice;（2） 定义有参数的存储过程并执行更新给定订单号的总价gocreate procedure Pro_CalTotalPrice4Order id_order integerasbeginupdate orders set totalprice=(select SUM(extendedprice*(1-discount)*(1+tax)from lineitemwhere orders.orderkey=lineitem.orderkey)endgo执行此存储过程exec Pro_CalTotalPrice4Order id_order=2;（3） 定义有局部变量的存储过程更新某一个顾客所有订单的总价gocreate procedure Proc_CalTotalPrice4Customer name_customer varchar(50)asdeclare custkey_customer intbeginselect custkey_customer=custkeyfrom customerwhere name_customer=RTRIM(name)update orders set totalprice=(select SUM(extendedprice*(1-discount)*(1+tax)from lineitemwhere orders.orderkey=lineitem.orderkey and orders.custkey=custkey_customer)endgo执行此存储过程exec Proc_CalTotalPrice4Customer name_customer=艾锦亮;查看结果update lineitem set extendedprice=100 ,tax=0.25 where orderkey=1830;select * from lineitem where orderkey=1830;select * from orders where custkey=(select custkey from customer whe