游标、触发器、存储过程实例

实验八

一、游标部分：

实验名称：综合训练（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