第10章 存储过程

第10章存储过程存储过程是SQL语句和可选控制流语句的预编译集合，它以一个名称存储并作为一个单元处理。

本章介绍存储过程的创建、执行、修改和删除等。

10.1 概述存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。

存储过程可以使对数据库的管理，以及显示关于数据库及其用户信息的工作容易得多。

存储过程可包含程序流、逻辑以及对数据库的查询。

它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点：可以在单个存储过程中执行一系列SQL语句。

可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。

存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，且能减少网络通信的负担。

10.2创建存储过程要使用存储过程，首先要创建一个存储过程。

可以使用Transact-SQL 语言的CREATE PROCEDURE语句，也可以使用企业管理器或者存储过程创建向导来完成。

1．使用CREATE PROCEDURE语句创建存储过程CREATE PROCEDURE语句的语法格式为：CREATE PROC[EDURE ] procedure_name [; number][ {@parameter data_type}[VARYING ][ = default][OUTPUT]][,…n][WITH{RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}][FOR REPLICATION]AS sql_statement […n ]其中各参数含义如下：procedure_name新存储过程的名称。

number 是可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。

例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。

DROP PROCEDURE orderproc语句将除去整个组。

如果名称中包含定界标识符，则数字不应包含在标识符中，只应在“procedure_name”前后使用适当的定界符。

@parameter 过程中的参数。

在CREATE PROCEDURE语句中可以声明一个或多个参数。

用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。

存储过程最多可以有2100个参数。

data_type 参数的数据类型。

所有数据类型(包括text、ntext和image)均可以用作存储过程的参数。

不过，cursor数据类型只能用于OUTPUT参数。

如果指定的数据类型为cursor，也必须同时指定VARYING和OUTPUT关键字。

VARYING 指定作为输出参数支持的结果集(由存储过程动态构造，内容可以变化)。

仅适用于游标参数。

default 参数的默认值。

如果定义了默认值，不必指定该参数的值即可执行过程。

默认值必须是常量或NULL。

OUTPUT 表明参数是返回参数。

该选项的值可以返回给EXE[UTE]。

使用OUTPUT参数可将信息返回给调用过程。

{RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION} RECOMPILE表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。

ENCRYPTION表示SQL Server加密syscomments表中包含CREATE PROCEDURE语句文本的条目。

FOR REPLICATION 指定不能在订阅服务器上执行为复制创建的存储过程。

sql_statement 过程中要包含的任意数目和类型的Transact-SQL语句。

但有一些限制。

【例10.1】下面创建一个简单的存储过程stud_degree ，用于检索所有学生的成绩记录：USE school--判断stud_degree存储过程是否存在，若存在，则删除IF EXISTS (SELECT name FROM sysobjectsWHERE name = 'stud_degree' AND type ='P')DROP PROCEDURE stud_degreeGOUSE schoolGO--创建存储过程stud_degreeCREATE PROCEDURE stud_degreeASSELECT student.sno,student.sname,ame,score.degreeFROM student,course,scoreWHERE student.sno=score.sno AND o=oORDER BY student.snoGO通过下述SQL语句执行该存储过程：USE school--判断stud_degree存储过程是否存在，若存在，则执行它IF EXISTS (SELECT name FROM sysobjectsWHERE name = 'stud_degree' AND type ='P')EXEC stud_degree /*执行存储过程stud_degree*/GO执行结果为：sno sname cname degree----- -------- ------------- --------------------101 李军计算机导论 64101 李军数字电路 85103 陆君计算机导论 92103 陆君操作系统 86105 匡明计算机导论 88105 匡明操作系统 75107 王丽计算机导论 91107 王丽数字电路 79108 曾华计算机导论 78108 曾华数字电路 81109 王芳计算机导论 76109 王芳操作系统 68创建存储过程时应该注意下面几点：存储过程的最大大小为128MB。

用户定义的存储过程只能在当前数据库中创建(临时过程除外，临时过程总是在tempdb中创建)。

在单个批处理中，CREATE PROCEDURE语句不能与其他Transact-SQL语句组合使用。

存储过程可以嵌套使用，在一个存储过程中可以调用其他的存储过程。

嵌套的最大深度不能超过32层。

存储过程如果创建了临时表，则该临时表只能用于该存储过程，而且当存储过程执行完毕后，临时表自动被删除。

创建存储过程时，“sq_statement”不能包含下面的Transact-SQL语句：SET SHOWPLAN_TEXT、SETSHOWMAN_ALL、CREATE VIEW、CREATE DEFAULT、CREATE RULE、CREATE PROCEDURE和CREATETRIGGER。

SQL Server允许创建的存储过程引用尚不存在的对象。

在创建时，只进行语法检查。

执行时，如果高速缓存中尚无有效的计划，则编译存储过程以生成执行计划。

只有在编译过程中才解析存储过程中引用的所有对象。

因此，如果语法正确的存储过程引用了不存在的对象，则仍可以成功创建；但在运行时将失败，因为所引用的对象不存在。

2．使用企业管理器创建存储过程使用企业管理器创建存储过程的操作步骤如下：(1) 打开企业管理器，展开服务器组，并展开相应的服务器。

(2) 打开“数据库”文件夹，并打开要创建存储过程的数据库。

(3) 选择“存储过程”选项，右击鼠标，执行“新建存储过程”命令，打开创建存储过程对话框，如图10.1所示。

图10.1创建存储过程(4) 在“文本”列表框中显示了CREATE PROCEDURE语句的框架，可以修改要创建的存储过程的名称，然后加入存储过程所包含的SQL语句。

(5) 单击“检查语法”按钮可以检查创建存储过程的SQL语句的语法是否正确。

(6) 如果要将其设置为下次创建存储过程的模板，可单击“另存为模板”按钮。

(7) 完成后，单击“确定”按钮即可创建一个存储过程。

3．使用向导创建SQL Server 2000还提供了创建存储过程的向导.【例10.2】 使用向导创建一个存储过程insert_table8_1，对应的操作步骤如下：(1) 在企业管理器中，执行“工具”下拉菜单中的“向导”命令，打开“选择向导”对话框，如图10.2所示。

(2) 在“数据库”文件夹选择“创建存储过程”向导，单击“确定”按钮，出现创建存储过程向导欢迎对话框。

(3) 单击“下一步”按钮，出现选择数据库对话框，如图10.3所示。

(4) 选择数据库后，单击“下一步”按钮，出现选择存储过程对话框，如图10.4所示。

图10.2 “选择向导”对话框图10.3选择数据库图10.4 选择存储过程在此对话框中，列出了所有表，以及可以对表进行的插入、删除和更新操作。

可以通过选中每个表对应的复选框来确定要对表进行的操作。

例如，选择table8表后面的“插入”栏中的复选框。

(5) 单击“下一步”按钮，出现完成创建存储过程对话框，如图10.5所示。

若单击“完成”按钮，即可完成存储过程的创建。

(6) 单击“编辑”按钮，可编辑存储过程，如图10.6所示。

图10.5 完成创建存储过程图10.6 编辑存储过程(7) 单击“编辑SQL”按钮，即可打开“编辑存储过程SQL”对话框，其中的列表框显示了创建该存储过程的Transact-SQL语句，如图10.7所示。

可以在已有的Transact-SQL语句的基础上进行编辑，可以单击“分析”按钮来执行语法检查。

(8) 单击“确定”按钮，返回到图10.5所示的对话框。

图10.7 “编辑存储过程SQL”对话框10.3执行存储过程执行存储过程使用EXECUTE语句，其完整语法格式如下：[ [ EXEC[UTE] ][ @return_status = ]{ procedure_name [ ;number ] | @procedure_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ][ ,…n ][ WITH RECOMPILE ]各参数含义如下：@return_status 是一个可选的整型变量，保存存储过程的返回状态。

这个变量在用于EXECUTE 语句前，必须在批处理、存储过程或函数中声明过。

procedure_name 是调用的存储过程的名称。

过程名称必须符合标识符规则。

无论服务器的代码页或排序方式如何，扩展存储过程的名称总是区分大小写。

;number 是可选的整数，用于将相同名称的过程进行组合，使得它们可以用一句 DROP PROCEDURE 语句除去。

该参数不能用于扩展存储过程。

@procedure_name_var 是局部定义变量名，代表存储过程名称。