第16章 实现存储过程.docx
《第16章 实现存储过程.docx》由会员分享，可在线阅读，更多相关《第16章 实现存储过程.docx（14页珍藏版）》请在冰豆网上搜索。
第16章实现存储过程
第16章实现存储过程
本章概述
本章介绍了非常重要的概念：
存储过程。
存储过程极大提高了常用SQL语句的执行速度。
并且从架构上为数据库程序员提供了安全方便的途径，设计通用的编程接口，为应用程序和终端用户提供需要已经经过逻辑处理的信息。
本章仍然建议以动手练习为主，提高动手能力。
教学目标
●掌握SQLServer如何处理存储过程的原理
●掌握创建、执行、修改和删除存储过程
●掌握创建带参数的存储过程
●掌握执行扩展存储过程
●掌握创建自定义错误消息
教学重点
●存储过程的概念，处理过程以及优点
●创建、执行、修改和删除存储过程
●创建带参数的存储过程
教学难点
●存储过程的处理过程
●存储过程同SQL脚本、视图的比较，优缺点
教学资源
课本
知识点
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-对应本章综合知识
教师光盘
实验文件
Labfiles\allfiles.exe
习题解答
tPrep\answer.doc
教学指导手册包
新版幻灯片
Powerpnt\PPT_2071_2073_16.ppt
先修知识
在正式开始学习本章内容以前，学生须具备下列知识基础。
知识基础
推荐补充
熟悉关系数据库基本概念
《数据库基础》
建议学时
课堂教学（3课时）+实验教学（3课时）
教学过程
16.1存储过程介绍
教学提示：
本节主要达到一个目的。
●掌握存储过程的概念、处理过程和优点。
了解存储过程的使用场合对数据库程序员是非常重要的，企业数据库程序员需要为企业数据库应用在设计时选择数据接口使用的相关技术，而存储过程就是非常重要的接口技术。
（略讲）
内容
教学方法
提示
讲授：
简单的说存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需简单的调用。
讲到这里,可能有人要问：
这么说存储过程就是一堆SQL语句而已啊？
那么存储过程与一般的SQL语句有什么区别呢?
存储过程的主要优点有：
1.存储过程只在创造时进行编译（编译，指的是将脚本语言转换成可执行的机器语言的过程），以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时（如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用,可减少数据库开发人员的工作量。
4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
阅书：
16.1
幻灯：
第3-14页
16.2创建、执行、修改和删除存储过程
教学提示：
本节主要达到一个目的。
●掌握创建、执行、修改和删除存储过程的方法。
（精讲+演示）
内容
教学方法
提示
讲述：
在了解了存储过程的概念和特点后，我们学习创建、执行和修改它的方法。
讲授课本。
演示：
书上的例子
阅书：
16.2
幻灯：
第16-27页
●建议同实验A结合起来讲。
16.3在存储过程中使用参数
教学提示：
本节主要达到一个目的。
●掌握使用带参数的存储过程的方法。
（精讲+演示）
内容
教学方法
提示
讲述：
大家到这个时候已经知道了什么是存储过程，存储过程就象是已经设置好的指令。
就好像我们对机器人发出指令“扫地”机器人就会开始扫地。
但是我们也许需要在存储过程中使用参数指定一些变量。
比如说我们希望能够在“扫地”指令中加入地点变量，使指令变成类似“到XX地扫地”，其中XX可以是客厅，也可以是卧室。
这样就给存储过程加入了极大的灵活性。
我们来看一下如何使用。
讲授课本。
演示：
书上的例子
阅书：
16.3
幻灯：
第30-35页
●建议同实验结合起来讲。
16.4执行扩展存储过程
教学提示：
本节主要达到一个目的。
●掌握扩展存储过程的概念和执行方法。
（精讲+演示）
内容
教学方法
提示
讲授：
扩展存储过程使您得以使用象C这样的编程语言创建自己的外部例程。
对用户来说，扩展存储过程与普通存储过程一样，执行方法也相同。
可将参数传递给扩展存储过程，扩展存储过程可返回结果，也可返回状态。
扩展存储过程可用于扩展SQLServer的功能。
讲授课本。
演示：
书上的例子
阅书：
16.4
幻灯：
第37-38页
●建议同本章习题/实验结合起来讲。
16.5错误信息处理
教学提示：
本节主要达到一个目的。
●掌握存储过程中的错误信息处理。
（精讲+演示）
内容
教学方法
提示
讲述：
既然存储过程是一系列代码的组合，那么在执行过程中就可能发生异常。
比如该过程操作的表被管理员误删除了（当然这属于严重错误）。
在这种情况下，我们需要赋予存储过程对错误的处理能力。
讲授课本。
演示：
书上的例子
阅书：
16.5
幻灯：
第40-42页
16.6性能考虑
教学提示：
本节主要达到一个目的。
●了解存储过程对性能的影响。
（略讲）
内容
教学方法
提示
讲授课本
阅书：
16.6
幻灯：
第44-45页
●有关系统监视器和SQL事件查看器的具体用法请通过在联机帮助中查询这两个主题
16.7推荐操作
教学提示：
本节主要达到一个目的。
●了解一些经验方法。
（略讲）
内容
教学方法
提示
讲授课本
阅书：
16.7
幻灯：
第47页
总结
经过本章的学习，我们了解了下列的知识和内容。
●SQLServer如何处理存储过程的原理
●创建、执行、修改和删除存储过程
●创建带参数的存储过程
●执行扩展存储过程
●创建自定义错误消息
本章我们学习了提高性能和作为数据架构接口的重要手段——存储过程。
下一章，我们将学习另外一种编程接口——用户定义函数。
随堂练习
1．在某个使用SQLServer2000创建数据库中使用了存储过程处理机密数据，应当如何做能够使存储过程中的内容不被查看到？
A.对存储过程的内容加密
B.从syscomments系统表中去除存储过程的内容
C.对所有公共角色拒绝syscomments系统表的SELCET权限
D.对所有公共角色拒绝sysobjects系统表的SELCET权限
正确答案：
A
分析：
在存储过程的定义中可以使用WITHENCRYPTION选项保证存储过程的定义不会被其他用户看到。
存储过程的定义以一种不可读的方式存储。
在加密后，存储过程的定义不能被解密，存储过程的拥有者和系统管理员都不能查看。
2．在一个使用SQLServer2000创建的数据表Products中，有一列CatalogID。
CatalogID列只包含10个不同的数据，同一类的产品数据量在10到5000之内变化。
表中的数据经常会发生变化。
请问应当采用何种方法使得查询同一类产品数据的性能提高，同时对表结构不产生变化？
A.创建一个以CatalogID为参数的存储过程，在创建时使用WITHRECOMPILE选项
B.为每个CatalogID创建一个存储过程
C.为每个CatalogID穿件一个视图
D.将表根据CatalogID不同分割成多个数据表
正确答案：
A
3．在一个使用SQLServer2000创建的数据表Products中，使用SQL语句和存储过程查询表中的数据。
当表内的数据大量增长后，使用存储过程查询的速度大为下降，而SQL语句的性能并没有很大的变化。
请问应当采用何种方法使得存储过程的性能提高？
A.使用WITHRECOMPILE选项提高执行速度慢的存储过程的性能
B.使用执行速度慢的存储过程对每个数据表执行UPDATESTATISTICS语句
C.对执行速度慢的存储过程执行系统存储过程sp_recompile
D.使用执行速度慢的存储过程对每个数据表执行DBCCREINEX语句
正确答案：
C
分析：
使用WITHRECOMPILE选项使SQLServer不缓存存储过程的计划，每次执行都要重新编译。
使用WITHRECOMPILE选项会使存储过程执行速度下降。
该选项只在存储过程调用的参数每次都发生很大变化时使用。
4．在一个企业内部管理系统的数据库中，员工数据表中有一列PositonType记录员工的职位。
为了实现不同级别的员工可以看到不同保密级别的公司信息，在消息数据表中有字段PositonType表示，只有该职位的员工方可查看该消息，当不能修改消息。
请问应当使用哪种方法实现该功能？
A.创建视图返回符合职位要求的信息
B.创建存储过程返回符合职位要求的信息
C.使用SELECT权限管理
D.在消息数据表中配置合理的权限
正确答案：
B
分析：
存储过程可以用这样设计：
CREATEPROCEDURESP_1（$EmpTyepchar（15））
AS
SELECTNewsText
FROMNews
WHEREPositonType=@EmpType
存储过程使用特定的职位类型，返回可以查阅的信息
5．某销售公司数据管理系统中使用不同的DepartmentID记录标识不同部门的员工，公司大部分的员工都属于DepartmentID为101的销售部门。
由于大多数的查询都是针对销售部门员工，所以希望不需要每次查询附加DepartmentID为101的查询条件，只有需要查询其他部门时才提供DepartmentID参数。
请问应当使用何种方法？
A.使用两个存储过程，一个要求提供DepartmentID参数，一个不需要
B.使用将101作为自定义函数的默认参数值
C.使用将101作为存储过程的默认参数值
D.创建一个视图仅包含DepartmentID为101的记录
正确答案：
C
6．在一个SQLServer2000的股票数据库系统中，为了能查阅历史信息，所有的股票价格更新会被记录。
当记录错误信息时，该错误信息必须被返回给客户端程序。
下列选项中哪个实现该功能？
A.CRATEPROCEDUREUpdateStockPrice@StockIDint,@Pricedecimal
ASBEGIN
DECLARE@Msgvarchar（50）,
UPDATEStockSETCurrentPrice=@Price
WHEREStockID=@StockID
ANDCurrentPrice<>@Price
SELECT@Err=@@ERROR,@RCount=@@ROWCOUT
IF@@ERROR<>0
RAISEERROR（‘Error%doccurredupdatingStock%d’,10,1,@Err,@StockID）WITHLOG
IF@@ROWCOUT>0
BEGIN
SELECT@Msg=‘Stock’+STR（@StockID）+’updateto’+STR（@Price）+’.’
EXECmaster..xp_logevent50001,@Msg
END
END
B.CRATEPROCEDUREUpdateStockPrice@StockIDint,@Pricedecimal
ASBEGIN
DECLARE@Msgvarchar（50）,@Errint,@RCountint
UPDATEStockSETCurrentPrice=@Price
WHEREStockID=@StockID
ANDCurrentPrice<>@Price
SELECT@Err=@@ERROR,@RCount=@@ROWCOUT
IF@Err<>0
PRINT‘Error‘+STR（@Err）+‘occurredupdating
Stock’+STR（@StockID）
IF@RCount>0
PRINT‘Stock’+STR（@StockID）+’updateto’+STR（@Price）+’.’
END
C.CRATEPROCEDUREUpdateStockPrice@StockIDint,@Pricedecimal
ASBEGIN
DECLARE@Msgvarchar（50）,@Errint,@RCountint
UPDATEStockSETCurrentPrice=@Price
WHEREStockID=@StockID
ANDCurrentPrice<>@Price
SELECT@Err=@@ERROR,@RCount=@@ROWCOUT
IF@Err<>0
RAISEERROR（‘Error%doccurredupdatingStock%d’,10,1,@Err,@StockID）WITHLOG
IF@RCount>0
BEGIN
SELECT@Msg=‘Stock’+STR（@StockID）+’updateto’+STR（@Price）+’.’
EXEC..xp_logevent50001,@Msg
END
END
D.CRATEPROCEDUREUpdateStockPrice@StockIDint,@Pricedecimal
ASBEGIN
DECLARE@Msgvarchar（50）,@Errint,@RCountint
UPDATEStockSETCurrentPrice=@Price
WHEREStockID=@StockID
ANDCurrentPrice<>@Price
SELECT@Err=@@ERROR,@RCount=@@ROWCOUT
IF@Err<>0
RAISEERROR（‘Error%doccurredupdatingStock%d’,10,1,@Err,@StockID）WITHLOG
IF@RCount>0
BEGIN
SELECT@Msg=‘Stock’+STR（@StockID）+’updateto’+STR（@Price）+’.’
EXECmaster..xp_logevent50001,@Msg
END
END
正确答案：
D
分析：
RAISERROR系统命令可以显示系统信息，使用WITHLOG选项可以将错误记录在服务器错误日志和应用程序日志。
xp_logevent存储过程在MicrosoftSQLServer日志文件和Windows2000/NT事件查看器中。
xp_logevent可以不发信息给客户端并且发送警告。
在Transact-SQL存储过程，触发器，批处理中使用RAISEERROR在MicrosoftSQLServer日志文件和Windows2000/NT事件查看器中写消息，而不使用xp_logevent。
使用本地变量存储@@ROWCOUNT是有必要的。
SELECT@Err=@@ERROR,@RCount=@@ROWCOUNT，否则在实行任何语句后@@ROWCOUNT会被重设为0。
布置作业
1.完成书后习题1-7
2.预习书中所有实验，并填写操作步骤。
在实验课上准备操作
案例教学
在完成了本章对存储过程的学习后，学生可以检查案例设计，以及案例中的功能需求，找出可以使用存储过程提高性能或者提高应用设计模块化和灵活性的地方，设计并实现这些存储过程。
注意错误处理以及同其他接口的衔接。
升级会员 