实验八
一、游标部分:
实验名称:综合训练(2课时)
1、 请使用游标和循环语句为sale_item表建立一个更新触发器updateSaleItem,当修改销售明细表中某个货品的数量或单价时自动修改销售主表中的相应定单的定单金额。
create trigger updateSaleItem on sale_item
for update
as
if update(qty) or update(unit_price)
begin
declare @order_no int,@prod_id char(5)
declare cur_SaleItem cursor for
select order_no,prod_id from deleted
open cur_SaleItem
begin transaction
fetch cur_SaleItem into @order_no,@prod_id
while(@@fetch_status=0)
begin
update sales
set tot_amt=tot_amt-deleted.qty*deleted.unit_price+
inserted.qty*inserted.unit_price
from inserted,deleted
where sales.order_no=inserted.order_no
and inserted.order_no=deleted.order_no
and sales.order_no=@order_no
and inserted.prod_id=deleted.prod_id
and inserted.prod_id=@prod_id
fetch cur_SaleItem into @order_no,@prod_id
end
commit tran
close cur_SaleItem
deallocate cur_SaleItem
end
2、 请使用游标和循环语句编写一个存储过程proSearchCustomer,根据客户编号,查询该客户的名称、地址以及所有与该客户有关的销售记录,销售记录按商品分组输出。
if exists(select name from sysobjects
where name='proSearchCustomer' and type='p')
drop procedure proSearchCustomer
go
create procedure proSearchCustomer @cust_id char(5)
as
begin
declare @cust_name char(20),@cust_addr char(40)
select @cust_name=Cust_name,@cust_addr=addr from Customer
where Cust_id=@cust_id
if len(@cust_name)>0
begin
select @cust_name 'Customer',@cust_addr 'Address'
Declare cust_cursor cursor for
Select c.Prod_name,sum(b.Qty*b.Unit_price)
from Sales a,Sale_item b,Product c
where a.Cust_id=@cust_id and a.Order_no=b.Order_no
and b.Prod_id=c.Prod_id