37205-00 - SQL Server 2005课后实训参考答案(10)

2．使用IF UPDATE(column)尽可能优化上题中的触发器以提高系统效率。

CREATE TRIGGER T_UPDATE_ProOut ON ProOut FOR UPDATE AS

IF UPDATE(Quantity) BEGIN

UPDATE Product SET Stocks=Stocks-(SELECT Quantity FROM INSERTED)

WHERE ProNo=(SELECT ProNo FROM INSERTED)

UPDATE Product SET Stocks=Stocks+(SELECT Quantity FROM DELETED)

WHERE ProNo=(SELECT ProNo FROM DELETED) PRINT '已更新产品表的库存数量' END GO

UPDATE ProOut SET Quantity=50

WHERE SaleDate='2006-1-1' and CusNo='001' and ProNo='00001' and Quantity=10

徐人凤

共 46 页 第 46 页

5/19/2015 46

46

GO

CREATE TRIGGER T3 ON ProIn FOR UPDATE AS

IF UPDATE(Quantity) BEGIN

UPDATE Product SET Stocks=Stocks+(SELECTQuantity FROM INSERTED)

WHERE ProNo=(SELECT ProNo FROM INSERTED)

UPDATE Product SET Stocks=Stocks-(SELECTQuantity FROM DELETED)

WHERE ProNo=(SELECT ProNo FROM INSERTED) END GO

CREATE TRIGGER T6 ON ProOut FOR UPDATE AS

徐人凤

共 47 页 第 47 页

5/19/2015 47

47

IF UPDATE(Quantity) BEGIN

UPDATE Product SET Stocks=Stocks-(SELECT Quantity FROM INSERTED)

WHERE ProNo=(SELECT ProNo FROM INSERTED)

UPDATE Product SET Stocks=Stocks+(SELECTQuantity FROM DELETED)

WHERE ProNo=(SELECT ProNo FROM DELETED) END GO

徐人凤 共 48 页 第 48 页 5/19/2015 48 48

单元13（逐行处理查询结果_游标） 本实训使用Sale数据库。

1．创建存储过程P_SelProduct，逐行显示产品销售信息，内容包括产品编号、产品名称、销售日期、销售数量、销售金额，要求显示格式如下：

产品编号 产品名称 销售日期 销售数量 销售

金额

00001 电视 2006-1-1 10 30000

产品编号 产品名称 销售日期 销售数量 销售

金额

00002 空调 2006-1-2 5 10000

CREATE PROCEDURE P_SelProduct AS

DECLARE @ProNo nvarchar(5),@ProName nvarchar(20),@SaleDate DateTime,@Quantity decimal(6,0),@Je decimal(8,2) --定义游标

DECLARE Crs CURSOR FOR SELECT

P.ProNo,ProName,SaleDate,Quantity,Je=Price*Quanti

徐人凤

共 49 页 第 49 页

5/19/2015 49

49

ty

FROM Product P,ProOut PO WHERE P.ProNo=PO.ProNo ORDER BY SaleDate --打开游标 OPEN Crs --取得第一行数据

FETCH NEXT FROM Crs INTO

@ProNo ,@ProName,@SaleDate,@Quantity,@Je --通过判断@@FETCH_STATUS控制循环 WHILE @@FETCH_STATUS=0 BEGIN

PRINT '产品编号 产品名称 销售日期 销售数量 销售金额 '

PRINT @ProNo+' '+@ProName+' '+STR(YEAR(@SaleDate),4)+

'-'+STR(MONTH(@SaleDate),2)+'-'+STR(DAY(@SaleDate),2)+' '

+Str(@Quantity,6)+' ' +STR(@Je,8,2)+' ' --取得下一行数据

FETCH NEXT FROM Crs INTO

徐人凤

共 50 页 第 50 页

5/19/2015 50

50