试验三 存储过程和触发器

实验三 存储过程和触发器一、实验目的1 掌握Transact-SQL编程知识；2 学习、掌握存储过程和触发器的建立和使用二、存储过程练习1 建立简单存储过程创建一个简单的存储过程，了解实现存储过程的语法。1）输入并执行下面语句USE NorthwindGOCREATE PROCEDURE FirstProcASSELECT TOP 5 ProductName, UnitPrice FROM Products ORDER BY UnitPrice descGO2）输入并执行如下语句：Use northwindexec firstproc这个存储过程的含义是什么？是否可以用视图实现同样的功能？ 答：查询在Products中Unit price 在前5的商品名称（Product name）和价格（Unit price） 可以视图来实现同样的功能，实验结果如图1所示 图1 练习1结果截图注意： 区别视图和存储过程。 存储过程第一次执行时进行编译并驻留在高速缓存中，以后再执行时，只需从高速缓存中调用已编好的二进制代码，对于经常被执行且功能固定的查询需求，存储过程将大节省SQL server执行时间（2）存储过程可以减少网络流量（3）是一种安全机制（4）屏蔽T-SQL命令视图是一个虚拟表。视图不在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用的表，并且在引用视图上自动生成。练习2 进一步使用存储过程，当执行存储过程时，将执行时的信息返回给用户1）输入并执行下面语句create proc Error_procas declare MaxPrice moneydeclare 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) go2）输入并执行语句调用存储过程:exec error_proc显示结果是什么？变量值是否传递给显示信息？ 答：变量值可以传递到显示信息，实验结果为练习3 使用输出参数返回变量值通过使用Output选项返回存储过程中的数值1）输入并执行下面语句create proc Return_procReturnMaxPrice money outputas select ReturnMaxPrice=max(unitprice) from products go2）执行下面语句，调用存储过程declare return moneyexec Return_proc return outputselect return是否显示结果？显示的内容是什么答：可以显示。结果为：注意： 在存储过程中的返回参数定义Output选项，在调用存储过程时也要定义Output选项，来接收返回值。练习4 按如下要求编写存储过程，并执行1） 在pubs数据库中创建一个存储过程，输入书的ID号（title_id），存储过程检索该书的书名、出版社名。 use pubsgocreate proc return_infotitle_id nvarchar(6)asselect titles.title,publishers.pub_namefrom titles,publisherswhere titles.pub_id=publishers.pub_id and titles.title_id=title_id go执行存储过程：use pubsexec return_info BU7832实验结果为： 2） 创建一个存储过程，入口参数为一个时间类型的值，返回如下格式的时间字符串：xxxx年xx月xx日。(提示：使用DATEPART函数，可在联机丛书中查询使用方法) create proc datatydate datetimeasprint(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 pubsgocreate proc infasselect au_id as 作者编号,au_lname as 作者名,state,所在地=case when state=CA then 加州when state=KS then 堪萨斯else othersendfrom authors go执行存储过程：exec inf.以下结果略去。二、触发器练习1：创建触发器要求：Products表中的UnitIsStock字段存放的是每个产品的库存量，Order Details表中存放的是订单信息。当增加一个新订单时，库存量应该自动减去订单里面的订货数量。1） 先使用sp_helptrigger Order Details命令查看Order Details表中关于触发器信息。2） 在Order Details表上创建触发器，自动计算库存量。输入并执行下面语句USE NorthwindGOIF EXISTS ( SELECT name FROM sysobjectsWHERE type = TR AND name = OrdDet_Insert )DROP TRIGGER OrdDet_InsertGOCREATE TRIGGER OrdDet_InsertON Order DetailsFOR INSERTASUPDATE P SET UnitsInStock = (P.UnitsInStock - I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDGO3） 用sp_helptrigger Order Details命令查看Order Details表中关于触发器信息。4）使用下面的语句测试触发器。输入编号为11077的订单，并且订货数量为50，并用两个Select语句查看结果。（下面语句一起执行结果会很明显）select productid,UnitsInStock from products where productid=22insert into Order Details (orderid,productid,unitprice,quantity,discount)values (11077,22,21.00,50,0.0)select productid ,UnitsInStock from products where productid=224） 两次库存数量的差值是什么？答：两次库存的差额是50实验结果如右图所示：注意：临时表Inserted的使用。练习2：创建删除触发器设有两张表NewCategories和NewProducts。当删除NewCategories表中一条记录时，NewProducts表中的相关数据同时删除。1）创建两张新表NewCategories和NewProducts。USE NorthwindGOSELECT * INTO NewCategories FROM CategoriesSELECT * INTO NewProducts FROM ProductsGO2）输入并执行下面语句，用以在NewCategories表上创建删除触发器CREATE TRIGGER Category_Delete ON NewCategories FOR DELETEAS DELETE NewProducts FROM NewProducts AS P INNER JOIN Deleted AS d ON P.CategoryID = D.CategoryID3）使用下面的语句测试触发器。在NewCategories表中删除分类号为6的记录，并用两个Select语句查看NewProducts表结果。（下面语句一起执行结果会很明显）SELECT ProductID, CategoryID, Discontinued FROM NewProducts WHERE CategoryID = 6DELETE NewCategories WHERE CategoryID = 6SELECT ProductID, CategoryID, Discontinued FROM NewProducts WHERE CategoryID = 64） NewProducts表中分类号为6的记录是否自动删除?答：删除了。如右图所示 练习3：使用触发器验证业务规则newProducts表中存放每个产品的基本信息，Order Details表中存放的是订单信息。如果一个产品存在着订单，那么这个产品不能从newProducts表中被删除。1）在上面实验创建的newProducts表上创建触发器。USE NorthwindGOCREATE TRIGGER Product_Delete ON NewProducts FOR DELETEASIF (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 TRANSACTIONEND2）使用下面语句测试触发器，将产品编号为1的产品信息从NewProducts中删除。DELETE NewProducts WHERE ProductID = 63） 是否能删除？为什么？不能删除，触发器验证业务规则，如运行结果所示运行结果：消息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 NorthwindGOCREATE TRIGGER Product_Delete2 ON Products FOR DELETEASIF (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 TRANSACTIONEN