第39章使用存储过程.docx
《第39章使用存储过程.docx》由会员分享，可在线阅读，更多相关《第39章使用存储过程.docx（15页珍藏版）》请在冰豆网上搜索。
第39章使用存储过程
第39章使用存储过程
在使用存储过程之前，首先需要创建一个存储过程，这可以通过T-SQL语句CREATEPROCEDURE来完成。
在使用的过程中，包括对存储过程的执行、查看和修改以及删除操作。
39.1创建存储过程
在SQLServer2008中,可以使用T-SQL语句CREATEPROCEDURE来创建存储过程。
在创建存储过程时，应该指定所有的输入参数、执行数据库操作的编程语句、返回至调用过程或批处理时以示成功或失败的状态值、捕获和处理潜在错误时的错误处理语句等。
需要强调的是，必须具有CREATEPROCEDURE权限才能创建存储过程，存储过程是架构作用域中的对象，只能在本地数据库中创建存储过程。
1．创建存储过程的规则
在设计和创建存储过程时，应该满足一定的约束和规则。
只有满足了这些约束和规则才能创建有效的存储过程。
●CREATEPROCEDURE定义自身可以包括任意数量和类型的SQL语句，但表8-2中的语句除外。
因为不能在存储过程的任何位置使用这些语句。
表8-2CREATEPROCEDURE定义中不能出现的语句
CREATEAGGREGATE
CREATERULE
CREATEDEFAULT
CREATESCHEMA
CREATE或ALTERFUNCTION
CREATE或ALTERTRIGGER
CREATE或ALTERPROCEDURE
CREATE.或ALTERVIEW
SETPARSEONLY
SETSHOWPLAN_ALL
SETSHOWPLAN_TEXT
SETSHOWPLAN_XML
USEDatabase_name
●可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。
●可以在存储过程内引用临时表。
●如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失。
●如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。
●如果执行对远程SQLServer2008实例进行更改的远程存储过程，则不能回滚这些更改，而且远程存储过程不参与事务处理。
●存储过程中的参数的最大数目为2100。
●存储过程中的局部变量的最大数目仅受可用内存的限制。
●根据可用内存的不同，存储过程最大可达128MB。
2．存储过程的语法
使用CREATEPROCEDURE语句创建存储过程的语法如下。
CREATEPROCDUREprocedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]][,…n]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
ASsql_statement[…n]
其主要参数含义含义如下：
●Procedure_name新存储过程的名称。
过程名称在架构中必须唯一，可在procedure_name前面使用一个数字符号“#”来创建局部临时过程，使用两个数字符号“#”来创建全局临时过程。
对于CLR存储过程，不能指定临时名称。
●;number是可选的整数，用来对同名的过程分组。
使用一个DROPPROCEDURE语句可将这些分组过程一起删除。
如果名称中包含分隔标识符，则数字不应该包含在标识符中;只应在procedure_name前使用分隔符。
●@parameter过程中的参数。
在CREATEPROCEDURE语句中可以声明一个或多个参数。
除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值，如果指定了FORREPLICATION，则无法声明参数。
●Data_type参数的数据类型。
所有数据类型均可以用作存储过程的参数。
不过cursor数据类型只能用于OUTPUT参数。
如果指定的数据类型为cursor，则还必须指定VARYING和OUTPUT关键字。
对于CLR存储过程，不能指定char,varchar,text,next,image,cursor和table作为参数。
如果参数的数据类型为CLR用户定义类型，则必须对此类型有EXECUTE权限。
●Default参数的默认值。
如果定义了dafault值，则无须指定此参数的值即可执行过程。
默认值必须是常量或NULL。
如果过程使用带like关键字的参数，则可包含下列通配符：
%、_、[]、[^]。
●Output指示参数是输出参数。
此选项的值可以返回给调用EXECUTE的语句。
使用OUTPUT参数将值返回给过程的调用方。
除非是CLR过程，否则text,ntext和image参数不能用作OUTPUT参数。
OUTPUT关键字的输出参数可以为游标占位符，CLR过程除外，要包含在过程中的一个或多个T-SQL语句中。
3．使用图形工具创建
除了直接编写T-SQL创建外，SQLServer2008还提供了一种简便的方法，使用SQLServerManagementStudio工具。
操作步骤如下：
（1）打开SQLServerManagementStudio窗口，连接到【BookDateBase】数据库。
（2）依次展开【服务器】｜【数据库】｜【BookDateBase】|【可编程性】节点。
（3）从列表中右击【存储过程】节点选择【新建存储过程】命令，然后将出现如图8-1所示的显示CREATEPROCEDURE语句的模板，可以修改要创建的存储过程的名称，然后加入存储过程所包含的SQL语句。
图8-1创建存储过程
（4）修改完后，单击【执行】按钮即可创建一个存储过程。
4．创建存储过程的示例
例如，在SQLServer2008的示例数据库【BookDatebase】中创建一个名为Reader_proc的存储过程，它将从表中返回所有读者的姓名、姓别、电话、等级。
使用CREATEPROCEDURE语句如下：
UseBookDatebase
Go
CREATEPROCEDUREReader_proc
As
SELECTRname,Rsex,Rphone,rleve
FROMReader
下面的存储过程proc_GetCountsBook获取了【BookDatebase】数据库中图书的总数量，具体语句如下所示：
UseBookDatebase
Go
CREATEPROCEDUREproc_GetCountsBook
As
SELECTcount（ID）AS总数FROMBooks
以上两个存储过程示例都是从单个表中提取数据，在第二个示例中使用了简单的表达式。
下面使用SELECT语句链接多个表，最终返回了借书人的简明信息。
存储过程名称是proc_BorRreader,创建语句如下：
UseBookDatebase
Go
CREATEPROCEDUREproc_BorR_reader
As
SELECTB.Bnum,B.Bname,B.writer,R.Rcert,R.Rname,BR.botime
FromBooksB,ReaderR,BorrowORreturnBR
WHEREB.Bnum=BR.BnumandR.Rcert=BR.RcertandBR.botime<>''
39.2执行存储过程
在需要执行存储过程时，可以使用T-SQL语句EXECUTE。
如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程，EXECUTE语法格式如下：
[{EXEC|EXECUTE}]
{
[@return_status=]
{procedure_name[;number]|@procedure_name_var}
@parameter=[{value|@variable[OUTPUT]|[DEFAULT]}]
[,…n]
[WITHRECOMPILE]
其中主要参数的含义如下：
●@return_status是一个可选的整型变量，保存存储过程的返回状态。
这个变量在用于EXECUTE语句前，必须在批处理、存储过程或函数中声明过。
●Procedure_name要调用的存储过程名称。
●;number是可选的整数，用于将相同名称的过程进行组合，使得它们可以用一句DROPPROCEDURE语句删除。
在【BookDatebase】中使用的过程可以Reader_proc;1、proc_GetCountsBook；２等来命名。
DROPPROCEDUREReader_proc语句将除去整个组。
在对过程分组后，不能删除组中的单个过程。
例如，DROPPROCEDUREproc_GetCountsBook;２是不允许的。
●@procedure_name_var是局部定义变量名，代表存储过程名称。
●@parameter是过程参数，在CREATEPROCEDURE语句中定义。
参数名称前必须加上符号“@”。
●Value是过程中参数的值。
如果参数名称没有指定，参数值必须以CREATEPROCEDURE语句中定义的顺序给出。
如果参数值是一个对象名称、字符串或通过数据库名称或所有者名称进行限制，则整个名称必须用单引号括起来。
如果参数值是一个关键字，则该关键字必须用双引号括起来。
●@variable是用来保存参数或者返回参数的变量。
●OUTPUT指定存储过程必须返回一个参数。
该存储过程的匹配参数也必须由关键字OUTPUT创建。
使用游标变量作参数时使用该关键字。
●DEFAULT根据过程的定义，提供参数的默认值。
当过程需要的参数值是没有事先定义好的默认值，或缺少参数，或指定了DEFAULT关键字，就会出错。
下面，我们通过EXECUTE语句来依次执行8.2.1节创建的3个存储过程。
首先是Reader_proc存储过程，它位于【BookDatebase】数据库中，使用语句如下：
UseBookDatebase
Go
EXECUTEReader_proc
执行上述语句后，结果如图8-2所示。
图8-2执行存储过程Reader_proc
然后再使用同样的方法，执行【BookDatebase】数据库中的两个存储过程，结果分别如图8-3和图8-4所示。
图8-3执行存储过程proc_GetCountsBook
图8-4执行存储过程proc_BorR_reader
运行EXECUTE语句无须权限，但是需要对EXECUTE字符串内引用的对象的权限。
例如，如果字符串包含INSERT语句，则EXECUTE语句的调用方对目标表必须具有INSERT权限。
除使用EXECUTE直接执行外，还可以将存储过程嵌入到INSERT语句中执行。
这样操作时，INSERT语句将把本地或远程存储过程返回的结果集加入到一个本地表中。
SQLServer2008会将存储过程中的SELECT语句返回的数据载入表中，前提是表必须存在并且数据类型必须匹配。
39.3存储过程参数
存储过程的优势不仅在于存储在服务器端、运行速度快、还有重要的一点就是存储过程可完成的功能非常强大，特别是在SQLServer2008中。
本节将学习如何在存储过程使用参数，包括输入参数和输出参数，以及参数的默认值等。
1．参数的定义
ＳQLServer2008的存储过程可以使用两种类型的参数：
输入参数和输出参数。
参数用于在存储过程以及应用程序之间交换数据，其中：
●输入参数允许用户将数据值传递到存储过程或函数。
●输出参数允许存储过程将数据值或游标变量传递给用户。
●每个存储过程向用户返回一个整数代码，如果存储过程没有显式设置返回代码的值，则返回代码为0。
存储过程的参数在创建时应在CREATEPROCEDURE和AS关键字之间定义，每个参数都要指定参数名和数据类型，参数名必须以@符号为前缀，可以为参数指定默认值；如果是输出参数，则应用OUTPUT关键描述。
各个参数定义之间用逗号隔开，具体语法如下：
@parameter_namedata_type[=default][OUTPUT]
2．输入参数
输入参数，即指在存储过程中有一个条件，在执行存储过程时为这个条件指定值，通过存储过程返回相应的信息。
使用输入参数可以向同一存储过程多次查找数据库。
例如，可以创建一个存储过程用于返回【BookDatebase】数据库上某条借阅信息中包括的图书名称。
通过为同一存储过程指定不同的借阅者，来返回不同的图书名称。
在8.2.1节最后创建的存储过程proc_BorR_reader只能对表进行特定的查询。
若要使这个存储过程更加通用化、灵活且能够查询某个类别中相应的图书信息，那么读者信息中的读者卡号就应该是可变的，这样的存储过程才能返回某个类别的图书信息。
在这个存储过程上将一个读者的卡号作为参数来实现，名称为proc_GetReaderBooks,其代码如下：
USE[BookDateBase]
GO
CREATEPROCEDURE[dbo].[proc_GetReaderBooks]
@Rcertint
As
SELECTB.Bnum,B.Bname,B.writer,R.Rname,BR.botime,R.Rcert
FromBooksB,ReaderR,BorrowORreturnBR
WHEREB.Bnum=BR.BnumANDR.Rcert=BR.RcertANDBR.botime<>''ANDBR.Rcert=@Rcert
以上代码，创建一个名为proc_GetReaderBooks的存储过程，使用一个字符串型的参数@Rcert来执行。
执行带有输入参数的存储过程时，SQLServer2008提供了如下两种传递参数的方式。
●按位置传递这种方式是在执行存储过程的语句中，直接给出参数的值。
当有多个参数时，给出的参数的顺序与创建存储过程的语句中的参数的顺序一致，即参数传递的顺序就是参数定义的顺序。
使用这种方式执行proc_GetReaderBooks存储过程的代码为：
EXECproc_GetReaderBooks‘10010’
这种方式是在执行存储过程的语句中，使用“参数名=参数值”的形式给出参数值。
通过参数名传递参数的好处是，参数可以以任意顺序给出。
用这种方式执行proc_GetReaderBooks存储过程的代码如下，执行结果如图8-5所示。
EXECproc_GetReaderBooks@Rcert=’10010’
图8-5执行结果
3．使用默认参数值
执行存储过程proc_GetReaderBooks时，如果没有指定参数，则系统运行就会出错；如果希望不给出参数时也能够正确运行，则可以给参数设置默认值来实现。
因此，如果要将proc_GetReaderBooks存储过程修改为默认值使用类别编号为10010的proc_GetReaderBooks，则可以运行下列代码：
USE[BookDateBase]
GO
CREATEPROCEDURE[dbo].[proc_GetReaderBooks]
@Rcertint=10010
As
SELECTB.Bnum,B.Bname,B.writer,R.Rname,BR.botime,R.Rcert
FromBooksB,ReaderR,BorrowORreturnBR
WHEREB.Bnum=BR.BnumANDR.Rcert=BR.RcertANDBR.botime<>''ANDBR.Rcert=@Rcert
4．输出参数
通过定义输出参数，可以从存储过程中返回一个或多个值。
为了使用输出参数，必须在CREATEPROCEDURE语句和EXECUTE语句中指定关键字OUTPUT。
在执行存储过程时，如果忽略OUTPUT关键字，存储过程仍会执行但不返回值。
USE[BookDateBase]
GO
CREATEPROCEDURE[dbo].[proc_GetReaderBookscount]
@Rcertint=10010
@bookcountsintOUTPUT
As
SELECT@bookcount=COUNT（B.Bnum）
FromBooksB,ReaderR,BorrowORreturnBR
WHEREB.Bnum=BR.BnumANDR.Rcert=BR.RcertANDBR.botime<>''ANDBR.Rcert=@Rcert
以上代码创建一个名为proc_GetReaderBooks1的存储过程，它使用两个参数：
@Rcert为输出参数，用于指定要查询的读者编号，默认参数值为10010；@bookcounts为输出参数，用来返回读者借阅的图书数量。
为了接收某一存储过程的返回值，需要一个变量来存放返回参数的值，在该存储过程的调用语句中，必须为这个变量加上OUTPUT关键字来声明。
下面的代码显示了如何调用proc_GetReaderBooks1，并将得到的结果返回到@bookcounts中，其运行结果如图8-6所示。
USE[BookDateBase]
GO
DECLARE@bookcountint
EXECproc_GetReaderBookscount10001,@bookcountOUTPUT
SELECT'读者共借阅图书：
'+STR（@bookcount）+'本'
GO
图8-6带输出参数的存储过程
5．存储过程的返回值
存储过程在执行后都会返回一个整形值。
如果执行成功，则返回0；否则返回-1到-99之间的随机数，也可以使用RETURN语句来指定一个存储过程的返回值。
例如，下面创建一个名为aAndb的存储过程，用以计算出两个参数的和。
本例使用SET语句，但是也可以使用SELECT语句来组织一个字符串，语句如下：
CREATEPROCaANDb
@aint=0,@bint=0,@cint=0OUTPUT
AS
Set@c=@a+@b
Return@c
@c参数由OUTPUT关键字指定。
在执行这个存储过程时，需要指定一个变量存放返回值，然后再显示出来。
如下所示为一个调用这个存储过程的示例：
DECLARE@intcint
EXECaANDb6,2,@intcOUTPUT
SELECT‘两个之和为：
’+STR（@INTC）
执行如果如图8-7所示。
图8-7执行aANDb结果
39.4删除存储过程
使用DROPPROCEDURE语句来从当前的数据库中删除用户定义的存储过程。
删除存储过程的基本语法如下所示。
DROPPROCEDURE{procedure}[,…n]
下面的语句将删除aANDb存储过程：
DROPPROCaANDb
如果另一个存储过程调用某个已被删除的存储过程，SQLServer2008将在执行调用进程时显示一条错误消息。
但是，如果定义了具有相同名称和参数的新存储过程来替换已被删除的存储过程，那么引用该过程的其他过程仍能成功执行。
39.5管理存储过程
在SQLServer2008系统中，可以使用OBJECT_DEFINITION系统函数查看存储过程的内容：
使用ALTERPROCEDURE语句修改已经存储过程。
1．查看存储过程信息
在SQLServer2008系统中，可以使用系统存储过程和目录视图查看有关存储过程的信息。
如果希望查看存储过程的定义信息，可以使用sys.sql_modules目录视图、OBJECT_DEFINITION系统函数、sp_helptext系统存储过程等。
例如，下面代码使用OBJECT_DEFINITION系统函数查看proc_GetReaderBookscount存储过程的定义内容。
SELECTOBJECT_DEFINITION（OBJECT_ID（N’proc_GetReaderBookscount’））
在创建存储过程时使用了WITHENCRYPTION子句，则将隐藏存储过程定义文本的信息，上面将不能查看到具体的文本信息。
还可以使用sys.sql_dependencies对象目录视图、sp_depends系统存储过程等可以查看存储过程的依赖信息。
使用sys.objects、sys.procedure、sys.parameters、sys.numbered_procedures等目录视图可以查看有关存储过程的名称、参数等信息。
2．修改存储过程
使用ALTERPROCEDURE语句来修改现有的存储过程与删除和重建存储过程不同，因为它仍保持存储过程的权限不发生变化。
在使用ALTERPROCEDURE语句修改存储过程时，SQLServer2008会覆盖以前定义的存储过程。
修改存储过程的基本语句如下：
ALTERPROCEDUREprocedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]]
[,…n]
[WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
AS
sql_statement[…n]
修改存储过程的语法中的各参数与创建存储过程语法中的各参数相同，这里就不在重复介绍。
在使用ALTERPROCEDURE语句时，考虑以下方面的事项：
●如果要修改具有任何选项的存储过程，例如WITHENCRYPTION选项，必须在ALTERPROCEDURE语句中包括该选项以保留该选项提供的功能。
●ALTERPROCEDURE语句只能修改一个单一的过程，如果过程调用了其他存储过程，嵌套的存储过程不受影响。
●在默认状态下，允许该语句的执行者是存储过程最初的创建者、sysadmin服务器角色成员和db_owner与db_ddladmin固定的数据库角色成员，用户不能授权执行ALTERPROCEDURE语句。
建议不要直接修改系统存储过程，相反，可以通过从现有的存储过程中复制语句来创建用户定义的系统存储过程，然后修改它以满足要求。
升级会员 