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

课后实训参考答案

单元1（SQL Server概述）

1、使用SQL语句。在Sale数据库中创建名为MyDataType的用户定义数据类型，数据类型为NVARCHAR，长度为20，该列允许为NULL。 USE Sale GO

EXEC sp_addtype MyDataType,'NVARCHAR(20)','NULL' GO

单元2（单表数据）

使用查询窗口或sqlcmd实施查询。本实训使用Xk数据库。 --1．查看系部编号为“03”的系部名称。 USE Xk GO

SELECT DepartName FROM Department WHERE DepartNo='03' GO

--2．查看系部名称中包含有“工程”两个字的系的全名。 USE Xk

徐人凤

共 1 页 第 1 页

5/19/2015 1

1

GO

SELECT DepartName FROM Department

WHERE DepartName LIKE '%工程%' GO

--3．显示共有多少个系部。 USE XK GO

SELECT '系部总数'=COUNT(*) FROM Department GO

--4．显示“01”年级共有多少个班级。 USE XK GO

SELECT '01 级班级数'=COUNT(*) FROM Class

WHERE ClassNo LIKE '2001%' GO

SELECT '01 级班级数'=COUNT(*) FROM Class

徐人凤

共 2 页 第 2 页

5/19/2015 2

2

WHERE ClassName LIKE '01%' GO

SELECT '01 级班级数'=COUNT(*) FROM Student

WHERE ClassNo LIKE '2001%' GO

--5．查看在“周二晚”上课的课程名称和教师。 USE XK GO

SELECT '课程名称'=CouName,'任课教师'=Teacher FROM Course

WHERE SchoolTime='周二晚' GO

--6．查看姓“张”、“陈”、“黄”同学的基本信息，要求按照姓名降序排序查询结果。 USE XK GO SELECT * FROM Student

WHERE StuName LIKE '张%' OR

徐人凤

共 3 页 第 3 页

5/19/2015 3

3

StuName LIKE '陈%' OR StuName LIKE '黄%' ORDER BY StuName DESC GO --方法 USE XK GO SELECT * FROM Student

WHERE StuName like '[张,陈,黄]%' ORDER BY StuName DESC GO

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

单元2（多表查询）

--1.按系部统计课程的平均报名人数，要求显示系部名称、平均报名人数。

SELECT '系部名称'=DepartName,'平均报名人数'=AVG(WillNum)

FROM Course C,Department D WHERE C.DepartNo=D.DepartNo GROUP BY DepartName GO

--如果小数点后只保留位

SELECT '系部名称'=DepartName,'平均报名数'=CONVERT(DECIMAL(5,2),AVG(WillNum)) FROM Course C,Department D WHERE C.DepartNo=D.DepartNo GROUP BY DepartName GO

--2.统计各个系部的班级数，要求显示系部编号、系部名称和班级数量。

SELECT C.DepartNo,DepartName,COUNT(*)

徐人凤

共 5 页 第 5 页

5/19/2015 5

5

FROM Class C,Department D WHERE C.DepartNo=D.DepartNo GROUP BY C.DepartNo,DepartName GO

--3．查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号，按志愿号（升序）排序查询结果。

SELECT CouName,Credit,SchoolTime,WillOrder FROM StuCou SC,Course C,Student S

WHERE SC.CouNo=C.CouNo AND SC.StuNo=S.StuNo AND StuName='甘蕾' ORDER BY WillOrder GO

--4．查看“00电子商务”班的选修报名情况。要求显示学号、姓名、课程编号、课程名称、志愿号，并按学号（升序）、志愿号排序（升序）。

SELECT S.StuNo,StuName,C.CouNo,CouName,WillOrder FROM StuCou SC,Course C,Student S,Class CL WHERE SC.CouNo=C.CouNo AND SC.StuNo=S.StuNo AND CL.ClassNo=S.ClassNo AND

徐人凤

共 6 页 第 6 页

5/19/2015 6

6

ClassName Like '00电子商务' ORDER BY S.StuNo,WillOrder GO

--5．按系部统计各系的最少报名人数、最多报名人数、平均报名人数和报名总数，并汇总显示所有系部的报名总数。要求平均报名人数保留两位小数位。

SELECT '系部名称'=DepartName,'最少报名人数

'=MIN(WillNum),'最多报名人数'=MAX(WillNum),'平均报名人数'=CONVERT(DECIMAL(5,2),AVG(WillNum)),'报名总数'=SUM(WillNum)

FROM Course C,Department D WHERE C.DepartNo=D.DepartNo GROUP BY DepartName WITH CUBE GO

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

单元3（维护数据）

--12．学号为“00000025”的同学第一志愿报名选修“001”课程，请在数据库中进行处理。

INSERT StuCou(StuNo,CouNo,WillOrder,State) VALUES('00000025','001',1,'报名') GO

/*课程报名人数增加人*/ UPDATE Course

SET WillNum=WillNum+1 WHERE CouNo='001' GO

--2．删除学号为“00000025”的学生的选课报名信息。

DELETE StuCou

WHERE StuNo='00000025' GO

/*课程表'00000025'同学报名的那些课程报名人数都要减少1*/ --方法： UPDATE Course

SET WillNum=WillNum-1

徐人凤

共 8 页 第 8 页

5/19/2015 8

8

FROM Course,StuCou

WHERE StuNo='00000025' and Course.CouNo=StuCou.CouNo GO

--方法2：（游标）

DECLARE @CouNo char(3) DECLARE MYCursor cursor for

SELECT CouNo from StuCou WHERE StuNo='00000025' OPEN MYCursor

FETCH NEXT FROM MYCursor INTO @CouNo WHILE @@FETCH_STATUS=0 BEGIN

UPDATE Course SET WillNum=WillNum-1 WHERE CouNo=@CouNo

FETCH NEXT FROM MYCursor INTO @CouNo END

CLOSE MYCursor DEALLOCATE MYCursor GO

徐人凤

共 9 页 第 9 页

5/19/2015 9

9

--3．需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。

UPDATE Student SET StuName='杜小静' FROM Student S,Class C

WHERE S.ClassNo=C.ClassNo AND

StuName='杜晓静' AND ClassName='00多媒体' GO

--“00电子商务”班的“林斌”同学申请将已选修的“网络信息检索原理与技术”课程修改为“Linux操作系统”。 UPDATE StuCou

SET CouNo=(SELECT CouNo FROM Course WHERE CouName='Linux操作系统')

FROM StuCou SC,Student S,Course C,Class Cl WHERE SC.StuNo=S.StuNo AND SC.CouNo=C.CouNo AND S.ClassNo=Cl.ClassNo AND S.StuName='林斌' AND

Cl.ClassName='00电子商务' AND CouName='网络信息检索原理与技术'

徐人凤

共 10 页 第 10 页

5/19/2015 10

10

2．在Sale数据库下新增名字为UserGroup的文件组。 USE Sale Go

ALTER DATABASE Sale ADD FILEGROUP UserGroup Go

3．以增加次数据文件的方式扩充Sale数据的容量。次数据文件容量为5MB，最大容量为10MB，文件增量为1MB。要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。 USE MASTER GO

ALTER DATABASE Sale ADD FILE (NAME=Sale2,

FILENAME='C:\\Sale2.ndf', SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=1MB)

TO FILEGROUP UserGroup GO

徐人凤

共 16 页 第 16 页

5/19/2015 16

16

4．使用存储过程显示Sale数据库的信息。 SP_helpdb Sale GO

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

单元6（创建与管理数据表）

1．实训项目都是围绕Sale数据库展开，进销存系统通常包括客户资料、产品信息、进货记录、销售记录等。所以针对Sale数据库，设计表了表1-4，数据请见表5-8，并将在后续章节逐步完善。使用Transcact-SQL语句，在Sale数据库下创建客户表、产品表、入库表和销售表并输入数据。请将脚本保存为.sql文件。

表1 Customer（客户表）结构

列名 数据类是否允备注 型及长度 许为空 CusNo nvarcharNOT （3） NULL 客户编号 CusNamnvarcharNOT e （10） NULL 客户姓名 Address nvarchar（20） NULL 地址 Tel nvarchar（20） NULL 联系电话

表2 Product（产品表）结构

列名 数据类是否允型及长度 许为空 备注 ProNo nvarcharNOT （5） NULL 产品编号 ProNamnvarcharNOT e （20） NULL 产品名 Price DecimalNOT （8,2） NULL 单价 Stocks DecimalNOT （8,0） NULL 库存数量

表3 ProIn（入库表）结构

徐人凤

共 18 页 第 18 页

5/19/2015 18

18

列名 数据类是否允备注 型及长度 许为空 InputDatNOT e DateTime NULL 入库日期 ProNo nvarcharNOT （5） NULL 产品编号 Quantity DecimalNOT （6,0） NULL 入库数量

表4 ProOut（销售表）结构

列名 数据类是否允备注 型及长度 许为空 SaleDate DateTime NOT NULL 销售日期 CusNo nvarcharNOT （3） NULL 客户编号 ProNo nvarchar NOT r（5） NULL 产品编号 Quantity DecimalNOT （6,0） NULL 销售数量

表5 Customer（客户表）数据

CusNo CusName Address Tel 001 杨婷 深圳 0755-22221111 002 陈萍 深圳 0755-22223333 003 李东 深圳 0755-22225555 004 叶合 广州 020-22227777 005 谭新 广州 020-22229999

表6 Product（产品表）数据

徐人凤

共 19 页 第 19 页

5/19/2015 19

19

ProNo ProName Price Stocks 00001 电视 3000.00 800 00002 空调 2000.00 500 00003 床 1000.00 300 00004 餐桌 1500.00 200 00005 音响 5000.00 600 00006 沙发 6000.00 100

表7 ProIn（入库表）数据

InputDate（入库日期） ProNo（产品编号） Quantity（入库数DateTime nvarchar（5） 量） not null not null Decimal（6,0） not null 2006-1-1 00001 10 2006-1-1 00002 5 2006-1-2 00001 5 2006-1-2 00003 10 2006-1-3 00001 10 2006-2-1 00003 20 2006-2-2 00001 10 2006-2-3 00004 30 2006-3-3 00003 20

表8 ProOut（销售表）数据

SaleDate CusNo ProNo Quantity 2006-1-1 001 00001 10 2006-1-2 001 00002 5 2006-1-3 002 00001 5 2006-2-1 002 00003 10 2006-2-2 001 00001 10 2006-2-3 001 00003 20 2006-3-2 003 00001 10 2006-3-2 003 00004 30 2006-3-3 002 00003 20 徐人凤

共 20 页 第 20 页

5/19/2015 20

20

--该题创建表时同时创建主键、外键。先创建表，再创建主键、外键的方法参见照实训5_3 Use Sale Go

CREATE TABLE Customer

(CusNo nvarchar(3) Primary key, CusName nvarchar(10)not null, Address nvarchar(20), Tel Char(20)) Go

/*创建产品表*/

CREATE TABLE Product

(ProNo nvarchar(5)Primary key, ProName nvarchar(20)not null, Price Decimal(8,2)not null, Stocks Decimal(8,0)not null) Go

/*创建入库表*/

徐人凤

共 21 页 第 21 页

5/19/2015 21

21

CREATE TABLE ProIn (InputDate DateTime not null,

ProNo nvarchar(5)References Product(ProNo), Quantity Decimal(6,0)not null) Go

/*创建销售表*/

CREATE TABLE ProOut (SaleDate DateTime not null,

CusNo nvarchar(3)not null References Customer(CusNo),

ProNo nvarchar(5)not null References Product(ProNo),

Quantity Decimal(6,0)not null) Go

--向Customer表输入数据-- INSERT INTO

Customer VALUES('001','杨婷','深圳','0755-22221111') INSERT INTO

Customer VALUES('002','陈萍','深圳

徐人凤

共 22 页 第 22 页

5/19/2015 22

22

','0755-22223333') INSERT INTO

Customer VALUES('003','李东','深圳','0755-22225555') INSERT INTO

Customer VALUES('004','叶合','广州','020-22227777') INSERT INTO

Customer VALUES('005','谭新','广州','020-22229999') Go

--向Product表中插入数据-- INSERT INTO

Product VALUES('00001','电视',3000.00,800) INSERT INTO

Product VALUES('00002','空调',2000.00,500) INSERT INTO

Product VALUES('00003','床',1000.00,300) INSERT INTO

Product VALUES('00004','餐桌',1500.00,200) INSERT INTO

徐人凤

共 23 页 第 23 页

5/19/2015 23

23

Product VALUES('00005','音响',5000.00,600) INSERT INTO

Product VALUES('00006','沙发',6000.00,100) Go

--向ProIn表中插入数据-- INSERT INTO

ProIn VALUES('2006-1-1','00001',10) INSERT INTO

ProIn VALUES('2006-1-1','00002',5) INSERT INTO

ProIn VALUES('2006-1-2','00001',5) INSERT INTO

ProIn VALUES('2006-1-2','00003',10) INSERT INTO

ProIn VALUES('2006-1-3','00001',10) INSERT INTO

ProIn VALUES('2006-2-1','00003',20) INSERT INTO

ProIn VALUES('2006-2-2','00001',10) INSERT INTO

ProIn VALUES('2006-2-3','00004',30)

徐人凤

共 24 页 第 24 页

5/19/2015 24

24

INSERT INTO

ProIn VALUES('2006-3-3','00003',20) Go

--向ProOut表中插入数据-- INSERT INTO

ProOut VALUES('2006-1-1','001','00001',10) INSERT INTO

ProOut VALUES('2006-1-2','001','00002',5) INSERT INTO

ProOut VALUES('2006-1-3','002','00001',5) INSERT INTO

ProOut VALUES('2006-2-1','002','00003',10) INSERT INTO

ProOut VALUES('2006-2-2','001','00001',10) INSERT INTO

ProOut VALUES('2006-2-3','001','00003',20) INSERT INTO

ProOut VALUES('2006-3-2','003','00001',10) INSERT INTO

ProOut VALUES('2006-3-2','003','00004',30) INSERT INTO

徐人凤

共 25 页 第 25 页

5/19/2015 25

25

ProOut VALUES('2006-3-3','002','00003',20) Go

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

单元7（实施数据完整性）

1．根据你的理解，请在实训5_2的表1-4的备注栏中标出主键、外键。

表1 Customer（客户表）结构

列名 数据类是否允备注 型及长度 许为空 CusNo nvarcharNOT （3） NULL 客户编号 主键 CusNamnvarcharNOT e （10） NULL 客户姓名 Address nvarchar（20） NULL 地址 Tel nvarchar（20） NULL 联系电话

表2 Product（产品表）结构

列名 数据类是否允型及长度 许为空 备注 ProNo nvarcharNOT （5） NULL 产品编号 主键 ProNamnvarcharNOT e （20） NULL 产品名 Price DecimalNOT （8,2） NULL 单价 Stocks DecimalNOT （8,0） NULL 库存数量

表3 ProIn（入库表）结构

列名 数据类是否允备注 型及长度 许为空 InputDate DateTime NOT NULL 入库日期 ProNo NvarcharNOT 产品编号 外键 徐人凤

共 27 页 第 27 页

5/19/2015 27

27

（5） NULL Quantity DecimalNOT （6,0） NULL 入库数量

表4 ProOut（销售表）结构

列名 数据类是否允备注 型及长度 许为空 SaleDate DateTime NOT NULL 销售日期 CusNo nvarcharNOT （3） NULL 客户编号 外键 ProNo nvarchar NOT r（5） NULL 产品编号 外键 Quantity DecimalNOT （6,0） NULL 销售数量

--该题适用于创建表时没有创建主键、外键的情况 /* Customer表的主键为CusNo*/ ALTER TABLE Customer

ADD CONSTRAINT PK_Customer PRIMARY KEY (CusNo) GO

/* Product表的主键为ProNo*/ ALTER TABLE Product

ADD CONSTRAINT PK_Product PRIMARY KEY (ProNo) GO

/*外键：ProOut 表的ProNo*/

徐人凤

共 28 页 第 28 页

5/19/2015 28

28

ALTER TABLE ProOut

ADD CONSTRAINT FK_ProOut_Product FOREIGN KEY (ProNo)

REFERENCES Product (ProNo)

/*外键：ProOut 表的CusNo*/ ALTER TABLE ProOut

ADD CONSTRAINT FK_ProOut_Customer FOREIGN KEY (CusNo)

REFERENCES Customer(CusNo)

/*外键：ProIn 表的ProNo*/ ALTER TABLE ProIn

ADD CONSTRAINT FK_ProIn_Product FOREIGN KEY (ProNo)

REFERENCES Product(ProNo)

--3．约束客户表Customer的CusNo列值长度为3；产品表Product的ProNo列值长度为5。 ALTER TABLE Customer

ADD CONSTRAINT CK_Customer_CusNo CHECK (LEN(CusNo)=3)

徐人凤

共 29 页 第 29 页

5/19/2015 29

29

GO

ALTER TABLE Product

ADD CONSTRAINT CK_Product_ProNo CHECK (LEN(ProNo)=5) GO

--测试： INSERT INTO

Product VALUES('000071','平板电脑',3000.00,800) GO

--系统会自动截断为长度5位。 --测试： INSERT INTO

Product VALUES('009','DVD',3000.00,800) GO

4．对产品表Product的Stocks列、Price列、入库表ProIn的Quantity列、销售表ProOut的Quantity列值进行约束，使其值必须大于0。 ALTER TABLE Product

ADD CONSTRAINT CK_Product_Stocks CHECK (Stocks>0) GO

徐人凤

共 30 页 第 30 页

5/19/2015 30

30

ALTER TABLE Product

ADD CONSTRAINT CK_Product_Price CHECK (Price>0) GO

ALTER TABLE ProIn

ADD CONSTRAINT CK_ProIn_Quantity CHECK (Quantity>0) GO

ALTER TABLE ProOut

ADD CONSTRAINT CK_ProOut_Quantity CHECK (Quantity>0) GO

5．对销售表ProOut的SaleDate列进行约束，当不输入值时，系统默认其值为系统当前日期。

CREATE DEFAULT CurrentDate AS GETDATE() GO

EXEC sp_bindefault CurrentDate,'ProOut.SaleDate' GO

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

单元8（提高查询速度） 本单元实训使用Sale数据库。

1．用户按照CusName（客户姓名）查询客户信息，希望提高查询速度。 USE Sale GO

CREATE INDEX IX_Customer ON Customer (CusName) GO

2．用户按照ProName（产品名称）查询产品信息，希望提高查询速度。 USE Sale GO

CREATE INDEX IX_Product ON Product (ProName) GO

3．用户按照SaleDate（销售日期）查询销售信息，希望提高查询速度。

徐人凤

共 32 页 第 32 页

5/19/2015 32

32

USE Sale GO

CREATE INDEX IX_ProOut ON ProOut (SaleDate) GO

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

单元9（Transcact-SQL语言编程基础） 本单元实训使用Sale数据库。

1．计算有多少种产品（假设为@x），然后显示一条信息：共有x种产品。

USE Sale GO

DECLARE @x int

SET @x=(SELECT COUNT(*) FROM Product) PRINT '共有'+convert(char(2),@x) +'种产品。' GO

2．编写计算n!（n=20）的程序，并显示计算结果。

DECLARE @i int,@PRODUCT FLOAT --定义整型变量@i用来计数；@Product为存放结果的单元

SELECT @i=1,@PRODUCT=1 --给整型变量@i和@sum赋值 WHILE @i<=20 --当@i小于等于时，执行循环体

BEGIN --定义语句块 SELECT @PRODUCT=@PRODUCT*@i --求和 SELECT @i=@i+1 --计数单元加 END --语句块定义结束

PRINT '20！='+convert(char(11),@PRODUCT) --显示

徐人凤

共 34 页 第 34 页

5/19/2015 34

34

结果 GO

3、创建一自定义函数，能够利用该函数计算出销售总金额（数量Quantity*单价Price）。

CREATE FUNCTION GETProduct (@Quantity decimal(6,0), @Price decimal(8,2)) RETURNS decimal(10,2) AS BEGIN

RETURN (@Quantity*@Price) END --测试 SELECT

Price,Quantity,dbo.GETProduct(Price,Quantity) 'ProductQu'

FROM Product,ProOut

WHERE Product.ProNo=ProOut.ProNo GO

徐人凤

共 35 页 第 35 页

5/19/2015 35

35

单元10（保证数据安全性_视图） 本实训使用Sale数据库。

1．创建视图V_Sale1，显示销售日期、客户编号、客户姓名、产品编号、产品名称、单价、销售数量和销售金额。 CREATE VIEW V_Sale1 AS SELECT

SaleDate,CusName,ProName,Price,Quantity,SaleMoney=(Price*Quantity)

FROM ProOut PO,Customer C,Product P

WHERE P.ProNo=PO.ProNo AND C.CusNo=PO.CusNo GO

2．创建视图V_Sale2，统计每种产品的销售数量和销售金额。统计结果包括：产品编号、产品名称、单价、销售数量和销售金额。 CREATE VIEW V_Sale2 AS SELECT

P.ProNo,P.ProName,SaleQuantity=SUM(Quantity),SaleMoney=SUM(Price*Quantity) FROM ProOut PO,Product P

徐人凤

共 36 页 第 36 页

5/19/2015 36

36

WHERE P.ProNo=PO.ProNo GROUP BY P.ProNo,P.ProName GO

3．创建视图V_Sale3，统计销售金额在10万以下的产品信息。

CREATE VIEW V_Sale3 AS

SELECT P.ProNo,P.ProName FROM ProOut PO,Product P WHERE P.ProNo=PO.ProNo GROUP BY P.ProNo,P.ProName

HAVING SUM(Price*Quantity)<100000 GO

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

单元11（保证数据安全性_存储过程） 本实训使用Sale数据库。

1．创建存储过P_Sale1，统计每种产品的销售数量和销售金额。 CREATE PROCEDURE P_Sale1 AS SELECT

Product.ProNo,ProName,Price,Sl=SUM(Quantity),Je=SUM(Price*Quantity) FROM ProOut,Product

WHERE ProOut.ProNo=Product.ProNo GROUP BY Product.ProNo,ProName,Price GO --调用 EXEC P_Sale1

2．创建存储过程P_Sale2，能够根据指定的客户统计汇总该客户购买每种产品的数量和花费金额。 CREATE PROCEDURE P_Sale2 @CusNo nvarchar(3)

徐人凤

共 38 页 第 38 页

5/19/2015 38

38

AS SELECT

Product.ProNo,ProName,Price,Sl=SUM(Quantity),Je=SUM(Price*Quantity) FROM ProOut,Product

WHERE ProOut.ProNo=Product.ProNo AND CusNo=@CusNo

GROUP BY Product.ProNo,ProName,Price GO --调用

EXEC P_Sale2 '001' EXEC P_Sale2 '002' GO

3．创建存储过程P_Sale3，能够根据指定的产品编号和日期，以输出参数的形式得到该产品的销售金额。 USE SALE GO

CREATE PROCEDURE P_Sale3

@ProNo nvarchar(5),@SaleDate DateTime,@Je Decimal(8,2) OUTPUT

徐人凤

共 39 页 第 39 页

5/19/2015 39

39

AS SET @Je=

(SELECT SUM(Price*Quantity) FROM ProOut,Product

WHERE ProOut.ProNo=Product.ProNo AND Product.ProNo=@ProNo AND SaleDate=@SaleDate GROUP BY Product.ProNo,ProName,Price )

print @je GO --调用

DECLARE @Je Decimal(8,2)

EXEC P_Sale3 '00001','2006-1-1',@Je GO

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

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

本文来源：https://www.bwwdw.com/article/wrlw.html