SQL Server 2005数据库技术与应用 教学课件 作者 郭江峰 1_ 第13章 存 储 过 程.ppt

《SQL Server 2005数据库技术与应用 教学课件 作者 郭江峰 1_ 第13章 存 储 过 程.ppt》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、第13章存储过程存储过程的基本概念13.1创建存储过程13.2操作存储过程13.313.1存储过程的基本概念13.1.1存储过程的定义存储过程（StoredProcedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。13.1.2存储过程的优点在SQLServer中使用存储过程有以下几个优点。（1）存储过程已在服务器注册。（2）存储过程允许模块化程序设计。（3）存储过程可以加快运行速度。（4）存储过程可以减少网络通信流量。（5）存储过程可以作为安全性机制。13.1.3系统存储过程在SQ

2、LServer2005中，许多管理活动和信息活动都是通过一种特殊的存储过程执行的，这种存储过程称为系统存储过程。系统存储过程主要存储在master数据库中并以sp_为前缀。系统存储过程主要是从系统表中获取信息，从而为数据库系统管理员管理SQLServer提供支持。13.2创建存储过程13.2.1创建存储过程创建存储过程的完整语法如下：CREATE{PROC

3、PROCEDURE}[schema_name.]procedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][OUTPUT]][,...

4、n][WITH[,...n]][FORREPLICATION]AS{[;][...n]}[;]::=[ENCRYPTION][RECOMPILE]其中各参数的含义如下。（1）schema_name存储过程所属架构的名称。（2）procedure_name新存储过程的名称。（3）@parameter过程中的参数。参数名必须以“at”符号（@）为前缀，在CREATEPROCEDURE语句中可以声明一个或多个参数。（4）[type_schema_name.]data_type参数以及

5、所属架构的数据类型。（5）VARYING指定结果集作为输出参数。仅适用于cursor参数。（6）default参数的默认值。（7）OUTPUT指示参数是输出参数。（8）RECOMPILE指示数据库引擎不缓存该存储过程的计划，该过程在运行时编译。（9）ENCRYPTION指定将存储过程的定义进行加密。（10）FORREPLICATION指定不能在订阅服务器上执行为复制创建的存储过程。（11）要包含在存储过程中的一个或多个Transact-SQL语句。13.2.2使用参数存储过程通过参数来与调用它的程序通信。在程序调用存储过程时，可以通过输入参数将数据

6、传给存储过程，存储过程可以通过输出参数和返回值将数据返回给调用它的程序。1．使用输入参数执行带输入参数的存储过程时，SQLServer提供了以下两种传递参数的方式。（1）按位置传递。（2）通过参数名传递。2．使用默认参数如果存储过程中有输入参数，在执行存储过程时没有给出参数，则系统会显示错误提示。3．使用输出参数通过定义输出参数，可以从存储过程中返回一个或多个值。定义输出参数需要在参数定义的数据类型后使用关键字OUTPUT，或简写为OUT。13.3操作存储过程13.3.1修改存储过程修改存储过程的语法如下：ALTER{PROC

7、PROCEDURE}[schema_name.]p

8、rocedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][OUTPUT]][,...n][WITH[,...n]][FORREPLICATION]AS {[...n]}::=[ENCRYPTION][RECOMPILE]其中，各参数的含义与CREATEPROCEDURE语句中的参数相同。13.3.2重新编译存储过程在执行诸如添加索引或更改索引列中的数据等操作而更改了数据库时，应重新编译访问

9、数据库表的原始查询计划以对其重新优化。SQLServer2005中，强制重新编译存储过程的方式有以下3种。（1）sp_recompile系统存储过程强制在下次执行存储过程时对其重新编译。（2）创建存储过程时在其定义中指定WITHRECOMPILE选项，指明SQLServer将不为该存储过程缓存计划，在每次执行该存储过程时对其重新编译。（3）可以通过指定WITHRECOMPILE选项，强制在执行存储过程时对其重新编译。13.3.3删除存储过程删除存储过程的语法如下：DROPPROCEDUREp