试验三 存储过程和触发器

实验三存储过程和触发器一、实验目的

1．掌握Transact-SQL编程知识；

2．学习、掌握存储过程和触发器的建立和使用

二、存储过程

练习1建立简单存储过程

创建一个简单的存储过程，了解实现存储过程的语法。

1）输入并执行下面语句

USE Northwind

GO

CREATE PROCEDURE FirstProc

AS

SELECT TOP 5 ProductName, UnitPrice FROM Products ORDER BY UnitPrice desc GO

2）输入并执行如下语句：

Use northwind

exec firstproc

这个存储过程的含义是什么？是否可以用视图实现同样的功能？

答：查询在Products中Unit price 在前5的商品名称（Product name）和价格（Unit price）可以视图来实现同样的功能，实验结果如图1所示

图1 练习1结果截图

注意：区别视图和存储过程。

存储过程第一次执行时进行编译并驻留在高速缓存中，以后再执行时，只需从高速缓存中调用已编好的二进制代码，对于经常被执行且功能固定的查询需求，存储过程将大节省SQL server执行时间（2）存储过程可以减少网络流量（3）是一种安全机制（4）屏蔽T-SQL 命令

视图是一个虚拟表。视图不在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用的表，并且在引用视图上自动生成。

练习2进一步使用存储过程，当执行存储过程时，将执行时的信息返回给用户

1）输入并执行下面语句

create proc Error_proc

as

declare @MaxPrice money

declare @Char varchar(20)

select @Maxprice=max(unitprice) from

products --找出价格最大值，并将值赋给

变量

set @char=cast(@Maxprice as varchar(20)) --转换数据类型为字符型raiserror('The max price is %s',10,1,@char) go

2）输入并执行语句调用存储过程:exec error_proc

显示结果是什么？变量值是否传递给显示信息？

答：变量值可以传递到显示信息，实验结果为

练习3使用输出参数返回变量值

通过使用Output选项返回存储过程中的数值

1）输入并执行下面语句

create proc Return_proc

@ReturnMaxPrice money output

as

select @ReturnMaxPrice=max(unitprice) from products

go

2）执行下面语句，调用存储过程

declare @return money

exec Return_proc @return output

select @return

是否显示结果？显示的内容是什么

答：可以显示。结果为：

注意：在存储过程中的返回参数定义Output选项，在调用存储过程时也要定义Output选项，来接收返回值。

练习4按如下要求编写存储过程，并执行

1）在pubs数据库中创建一个存储过程，输入书的ID号（title_id），存储过程检索该书的书名、出版社名。

use pubs

go

create proc return_info @title_id nvarchar(6) as

select titles.title,publishers.pub_name

from titles,publishers

where titles.pub_id=publishers.pub_id and titles.title_id=@title_id

go

执行存储过程：

use pubs

exec return_info 'BU7832'

实验结果为：

2）创建一个存储过程，入口参数为一个时间类型的值，返回如下格式的时间字符串：xxxx 年xx月xx日。(提示：使用DATEPART函数，可在联机丛书中查询使用方法)

create proc dataty

@date datetime

as

print(str(DATEPART(yy,@date))+'年'+str(DATEPART(mm,@date))+'月

'+str(DATEPART(dd,@date))+'日')

go

执行存储过程：exec dataty '2010-12-25'

实验结果为：

3）在pubs数据库中创建一个存储过程。如果作者所在的State为‘CA’，则显示为“加州”；

如果是‘KS’，显示为“堪萨斯”；若是其它州，显示为“Others”。

use pubs

go

create proc inf

as

select au_id as作者编号,au_lname as作者名,state,

所在地=case when state='CA'then'加州' when state='KS'then'堪萨斯' else'others'

end

from authors

go

执行存储过程：

exec inf

........以下结果略去。

二、触发器

练习1：创建触发器

要求：Products表中的UnitIsStock字段存放的是每个产品的库存量，[Order Details]表中存放的是订单信息。当增加一个新订单时，库存量应该自动减去订单里面的订货数量。

1）先使用sp_helptrigger [Order Details]命令查看[Order Details]表中关于触发器信息。

2）在[Order Details]表上创建触发器，自动计算库存量。输入并执行下面语句

USE Northwind

GO

IF EXISTS ( SELECT name FROM sysobjects

WHERE type = 'TR' AND name = 'OrdDet_Insert' )

DROP TRIGGER OrdDet_Insert

GO

CREATE TRIGGER OrdDet_Insert

ON [Order Details]FOR INSERT

AS

UPDATE P SET

UnitsInStock = (P.UnitsInStock - I.Quantity)

FROM Products AS P INNER JOIN Inserted AS I

ON P.ProductID = I.ProductID

GO

3）用sp_helptrigger [Order Details]命令查看[Order Details]表中关于触发器信息。

4）使用下面的语句测试触发器。输入编号为11077的订单，并且订货数量为50，并用两个Select语句查看结果。（下面语句一起执行结果会很明显）

select productid,UnitsInStock from products where productid=22

insert into [Order Details] (orderid,productid,unitprice,quantity,discount)

values (11077,22,21.00,50,0.0)

select productid ,UnitsInStock from products where productid=22

4）两次库存数量的差值是什么？

答：两次库存的差额是50

实验结果如右图所示：

注意：临时表Inserted的使用。

练习2：创建删除触发器

设有两张表NewCategories和NewProducts。当删除NewCategories表中一条记录时，NewProducts表中的相关数据同时删除。

1）创建两张新表NewCategories和NewProducts。

USE Northwind

GO

SELECT * INTO NewCategories FROM Categories

SELECT * INTO NewProducts FROM Products

GO

2）输入并执行下面语句，用以在NewCategories表上创建删除触发器

CREATE TRIGGER Category_Delete ON NewCategories

FOR DELETE

AS

DELETE NewProducts

FROM NewProducts AS P INNER JOIN Deleted AS d

ON P.CategoryID = D.CategoryID

3）使用下面的语句测试触发器。在NewCategories表中删除分类号为6的记录，并用两个Select语句查看NewProducts表结果。（下面语句一起执行结果会很明显）

SELECT ProductID, CategoryID, Discontinued

FROM NewProducts WHERE CategoryID = 6

DELETE NewCategories WHERE CategoryID = 6

SELECT ProductID, CategoryID, Discontinued

FROM NewProducts WHERE CategoryID = 6

4）NewProducts表中分类号为6的记录是否自

动删除?

答：删除了。如右图所示

练习3：使用触发器验证业务规则

newProducts表中存放每个产品的基本信

息，[Order Details]表中存放的是订单信息。如果一个产品存在着订单，那么这个产品不能从newProducts表中被删除。

1）在上面实验创建的newProducts表上创建触发器。

USE Northwind

GO

CREATE TRIGGER Product_Delete

ON NewProducts FOR DELETE

AS

IF (Select Count (*)

FROM [Order Details] INNER JOIN deleted

ON [Order Details].ProductID = Deleted.ProductID

) > 0

BEGIN

RAISERROR('Transaction cannot be processed. This Product still has a history of orders.', 16, 1)

ROLLBACK TRANSACTION

END

2）使用下面语句测试触发器，将产品编号为1的产品信息从NewProducts中删除。

DELETE NewProducts WHERE ProductID = 6

3）是否能删除？为什么？

不能删除，触发器验证业务规则，如运行结果所示

运行结果：

消息50000，级别16，状态1，过程Product_Delete，第10 行

Transaction cannot be processed. This Product still has a history of orders.

消息3609，级别16，状态1，第1 行

事务在触发器中结束。批处理已中止。

练习4：约束与触发器的激活顺序

Products表中存放每个产品的基本信息，[Order Details]表中存放的是订单信息。如果一个产品存在着订单，那么这个产品不能从Products表中被删除。

1）在Products表上创建触发器。

USE Northwind

GO

CREATE TRIGGER Product_Delete2

ON Products FOR DELETE

AS

IF (Select Count (*)

FROM [Order Details] INNER JOIN deleted

ON [Order Details].ProductID = Deleted.ProductID

) > 0

BEGIN

RAISERROR('Transaction cannot be processed. This Product still has a history of orders.', 16, 1)

ROLLBACK TRANSACTION

END

2）使用下面语句测试触发器，将产品编号为1的产品信息从Products中删除。

DELETE Products WHERE ProductID =1

5）是否能删除？为什么？

不能，因为约束与触发器的激活顺序，表与表中的引用关系发生冲突。

运行结果：

消息547，级别16，状态0，第1 行

DELETE 语句与REFERENCE 约束"FK_Order_Details_Products"冲突。该冲突发生于数据库"Northwind"，表"dbo.Order Details", column 'ProductID'。

语句已终止。

如有侵权请联系告知删除，感谢你们的配合！