第16章 实现存储过程.docx

1、第16章 实现存储过程第16章 实现存储过程本章概述 本章介绍了非常重要的概念：存储过程。存储过程极大提高了常用SQL语句的执行速度。并且从架构上为数据库程序员提供了安全方便的途径，设计通用的编程接口，为应用程序和终端用户提供需要已经经过逻辑处理的信息。 本章仍然建议以动手练习为主，提高动手能力。教学目标 掌握SQL Server如何处理存储过程的原理 掌握创建、执行、修改和删除存储过程 掌握创建带参数的存储过程 掌握执行扩展存储过程 掌握创建自定义错误消息教学重点 存储过程的概念，处理过程以及优点 创建、执行、修改和删除存储过程 创建带参数的存储过程教学难点 存储过程的处理过程 存储过程同S

2、QL脚本、视图的比较，优缺点教学资源课本知识点16.1 存储过程介绍16.2 创建、执行、修改和删除存储过程16.3 在存储过程中使用参数16.4 执行扩展存储过程16.5 错误信息处理16.6 性能考虑16.7 推荐操作实验实验16A-1：编写和执行存储过程实验16A-2：查找存储过程的信息实验16B-1：使用创建存储过程向导实验16B-2：在存储过程中使用错误处理实验16B-3：定制错误信息习题习题1-对应16.2创建存储过程习题2-对应16.2修改存储过程习题3-对应本章综合知识习题4-对应16.5错误信息处理习题5-对应16.5错误信息处理习题6-对应本章综合知识习题7-对应本章综合知

3、识教师光盘实验文件Labfilesallfiles.exe习题解答tPrepanswer.doc教学指导手册包新版幻灯片Powerpnt PPT_2071_2073_16.ppt先修知识在正式开始学习本章内容以前，学生须具备下列知识基础。知识基础推荐补充熟悉关系数据库基本概念数据库基础建议学时 课堂教学（3课时）+实验教学（3课时）教学过程16.1 存储过程介绍教学提示 ：本节主要达到一个目的。 掌握存储过程的概念、处理过程和优点。了解存储过程的使用场合对数据库程序员是非常重要的，企业数据库程序员需要为企业数据库应用在设计时选择数据接口使用的相关技术，而存储过程就是非常重要的接口技术。（略讲）

4、内容教学方法提示讲授：简单的说存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需简单的调用。讲到这里,可能有人要问：这么说存储过程就是一堆SQL语句而已啊？那么存储过程与一般的SQL语句有什么区别呢? 存储过程的主要优点有： 1.存储过程只在创造时进行编译（编译，指的是将脚本语言转换成可执行的机器语言的过程），以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert

5、,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量 。4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。阅书：16.1幻灯：第3-14页16.2 创建、执行、修改和删除存储过程教学提示 ：本节主要达到一个目的。 掌握创建、执行、修改和删除存储过程的方法。(精讲+演示)内容教学方法提示讲述：在了解了存储过程的概念和特点后，我们学习创建、执行和修改它的方法。讲授课本。演示：书上的例子阅书：16.2幻灯：第16-27页 建议同实验A结合起来讲。 16.3 在存储过程中使用参数教学提示

6、 ：本节主要达到一个目的。 掌握使用带参数的存储过程的方法。（精讲+演示）内容教学方法提示讲述：大家到这个时候已经知道了什么是存储过程，存储过程就象是已经设置好的指令。就好像我们对机器人发出指令“扫地”机器人就会开始扫地。但是我们也许需要在存储过程中使用参数指定一些变量。比如说我们希望能够在“扫地”指令中加入地点变量，使指令变成类似“到XX地 扫地”，其中XX可以是客厅，也可以是卧室。这样就给存储过程加入了极大的灵活性。我们来看一下如何使用。讲授课本。演示：书上的例子阅书：16.3幻灯：第30-35页 建议同实验结合起来讲。16.4 执行扩展存储过程教学提示 ：本节主要达到一个目的。 掌握扩展

7、存储过程的概念和执行方法。 (精讲+演示)内容教学方法提示讲授：扩展存储过程使您得以使用象 C 这样的编程语言创建自己的外部例程。对用户来说，扩展存储过程与普通存储过程一样，执行方法也相同。可将参数传递给扩展存储过程，扩展存储过程可返回结果，也可返回状态。扩展存储过程可用于扩展SQL Server的功能。讲授课本。演示：书上的例子阅书：16.4幻灯：第37-38页 建议同本章习题/实验结合起来讲。16.5 错误信息处理教学提示 ：本节主要达到一个目的。 掌握存储过程中的错误信息处理。 (精讲+演示)内容教学方法提示讲述：既然存储过程是一系列代码的组合，那么在执行过程中就可能发生异常。比如该过程

8、操作的表被管理员误删除了（当然这属于严重错误）。在这种情况下，我们需要赋予存储过程对错误的处理能力。讲授课本。演示：书上的例子阅书：16.5幻灯：第40-42页16.6 性能考虑教学提示 ：本节主要达到一个目的。 了解存储过程对性能的影响。 (略讲)内容教学方法提示讲授课本阅书：16.6幻灯：第44-45页 有关系统监视器和SQL事件查看器的具体用法请通过在联机帮助中查询这两个主题16.7 推荐操作教学提示 ：本节主要达到一个目的。 了解一些经验方法。 (略讲)内容教学方法提示讲授课本阅书：16.7幻灯：第47页总结 经过本章的学习，我们了解了下列的知识和内容。 SQL Server如何处理存

9、储过程的原理 创建、执行、修改和删除存储过程 创建带参数的存储过程 执行扩展存储过程 创建自定义错误消息本章我们学习了提高性能和作为数据架构接口的重要手段存储过程。下一章，我们将学习另外一种编程接口用户定义函数。随堂练习1 在某个使用SQL Server 2000创建数据库中使用了存储过程处理机密数据，应当如何做能够使存储过程中的内容不被查看到？A. 对存储过程的内容加密B. 从syscomments系统表中去除存储过程的内容C. 对所有公共角色拒绝syscomments系统表的SELCET权限D. 对所有公共角色拒绝sysobjects系统表的SELCET权限正确答案：A分析：在存储过程的定

10、义中可以使用WITH ENCRYPTION选项保证存储过程的定义不会被其他用户看到。存储过程的定义以一种不可读的方式存储。在加密后，存储过程的定义不能被解密，存储过程的拥有者和系统管理员都不能查看。2 在一个使用SQL Server 2000创建的数据表Products中，有一列CatalogID。CatalogID列只包含10个不同的数据，同一类的产品数据量在10到5000之内变化。表中的数据经常会发生变化。请问应当采用何种方法使得查询同一类产品数据的性能提高，同时对表结构不产生变化？A. 创建一个以CatalogID为参数的存储过程，在创建时使用WITH RECOMPILE选项B. 为每个

11、CatalogID创建一个存储过程C. 为每个CatalogID穿件一个视图D. 将表根据CatalogID不同分割成多个数据表正确答案：A3 在一个使用SQL Server 2000创建的数据表Products中，使用SQL语句和存储过程查询表中的数据。当表内的数据大量增长后，使用存储过程查询的速度大为下降，而SQL语句的性能并没有很大的变化。请问应当采用何种方法使得存储过程的性能提高？A. 使用WITH RECOMPILE选项提高执行速度慢的存储过程的性能B. 使用执行速度慢的存储过程对每个数据表执行UPDATE STATISTICS语句C. 对执行速度慢的存储过程执行系统存储过程sp_r

12、ecompileD. 使用执行速度慢的存储过程对每个数据表执行DBCC REINEX语句正确答案：C分析：使用WITH RECOMPILE选项使SQL Server不缓存存储过程的计划，每次执行都要重新编译。使用WITH RECOMPILE选项会使存储过程执行速度下降。该选项只在存储过程调用的参数每次都发生很大变化时使用。4 在一个企业内部管理系统的数据库中，员工数据表中有一列PositonType记录员工的职位。为了实现不同级别的员工可以看到不同保密级别的公司信息，在消息数据表中有字段PositonType表示，只有该职位的员工方可查看该消息，当不能修改消息。请问应当使用哪种方法实现该功能？

13、A. 创建视图返回符合职位要求的信息B. 创建存储过程返回符合职位要求的信息C. 使用SELECT权限管理D. 在消息数据表中配置合理的权限正确答案：B分析： 存储过程可以用这样设计： CREATE PROCEDURE SP_1($EmpTyep char(15) AS SELECT NewsText FROM News WHERE PositonType= EmpType 存储过程使用特定的职位类型，返回可以查阅的信息5 某销售公司数据管理系统中使用不同的DepartmentID记录标识不同部门的员工，公司大部分的员工都属于DepartmentID为101的销售部门。由于大多数的查询都是针对

14、销售部门员工，所以希望不需要每次查询附加DepartmentID为101的查询条件，只有需要查询其他部门时才提供DepartmentID参数。请问应当使用何种方法？A. 使用两个存储过程，一个要求提供DepartmentID参数，一个不需要B. 使用将101作为自定义函数的默认参数值C. 使用将101作为存储过程的默认参数值D. 创建一个视图仅包含DepartmentID为101的记录正确答案：C6 在一个SQL Server 2000的股票数据库系统中，为了能查阅历史信息，所有的股票价格更新会被记录。当记录错误信息时，该错误信息必须被返回给客户端程序。下列选项中哪个实现该功能？A. CRAT

15、E PROCEDURE UpdateStockPrice StockID int, Price decimalAS BEGINDECLARE Msg varchar(50),UPDATE Stock SET CurrentPrice = PriceWHERE StockID = StockIDAND CurrentPricePriceSELECT Err=ERROR,RCount =ROWCOUTIF ERROR0 RAISEERROR(Error %d occurred updating Stock %d,10,1,Err,StockID)WITH LOGIF ROWCOUT0BEGIN S

16、ELECT Msg = Stock+STR(StockID)+update to+STR(Price)+. EXEC master.xp_logevent 50001,MsgENDENDB. CRATE PROCEDURE UpdateStockPrice StockID int, Price decimalAS BEGINDECLARE Msg varchar(50),Err int,RCount intUPDATE Stock SET CurrentPrice = PriceWHERE StockID = StockIDAND CurrentPricePriceSELECT Err=ERR

17、OR,RCount =ROWCOUTIF Err0 PRINT Error +STR(Err)+occurred updating Stock+STR( StockID)IF RCount0 PRINT Stock+STR(StockID)+update to+STR(Price)+. ENDC. CRATE PROCEDURE UpdateStockPrice StockID int, Price decimalAS BEGINDECLARE Msg varchar(50),Err int,RCount intUPDATE Stock SET CurrentPrice = PriceWHER

18、E StockID = StockIDAND CurrentPricePriceSELECT Err=ERROR,RCount =ROWCOUTIF Err0 RAISEERROR(Error %d occurred updating Stock %d,10,1,Err,StockID)WITH LOGIF RCount0BEGIN SELECT Msg = Stock+STR(StockID)+update to+STR(Price)+. EXEC.xp_logevent 50001,MsgENDENDD. CRATE PROCEDURE UpdateStockPrice StockID i

19、nt, Price decimalAS BEGINDECLARE Msg varchar(50),Err int,RCount intUPDATE Stock SET CurrentPrice = PriceWHERE StockID = StockIDAND CurrentPricePriceSELECT Err=ERROR,RCount =ROWCOUTIF Err0 RAISEERROR(Error %d occurred updating Stock %d,10,1,Err,StockID)WITH LOGIF RCount0BEGIN SELECT Msg = Stock+STR(S

20、tockID)+update to+STR(Price)+. EXEC master.xp_logevent 50001,MsgENDEND 正确答案：D分析：RAISERROR系统命令可以显示系统信息，使用WITH LOG选项可以将错误记录在服务器错误日志和应用程序日志。xp_logevent存储过程在Microsoft SQL Server日志文件和Windows 2000/NT事件查看器中。xp_logevent可以不发信息给客户端并且发送警告。在Transact-SQL存储过程，触发器，批处理中使用RAISEERROR在Microsoft SQL Server日志文件和Windows 2000/NT事件查看器中写消息，而不使用xp_logevent。使用本地变量存储ROWCOUNT是有必要的。SELECT Err=ERROR,RCount =ROWCOUNT，否则在实行任何语句后ROWCOUNT会被重设为0。布置作业1. 完成书后习题1-72. 预习书中所有实验，并填写操作步骤。在实验课上准备操作案例教学 在完成了本章对存储过程的学习后，学生可以检查案例设计，以及案例中的功能需求，找出可以使用存储过程提高性能或者提高应用设计模块化和灵活性的地方，设计并实现这些存储过程。注意错误处理以及同其他接口的衔接。