7.2.1 存储过程基本知识
1．什么是存储过程
在开发SQL Server应用程序过程中，T-SQL语句是应用程序与SQL Server数据库之间使用的主要编程接口。应用程序与SQL Server数据库交互执行某些操作有两种方法：一种是存储在本地的应用程序记录操作命令，应用程序向SQL Server发送每一个命令，并对返回的数据进行处理；另一种是在SQL Server中定义某个过程，其中记录了一系列的操作，每次应用程序只需调用该过程就可完成该操作。这种在SQL Server中定义的过程被称为存储过程。
2．存储过程的功能
SQL Server中的存储过程类似于编程语言中的过程和函数，它具有以下功能：
· 接受输入参数并返回多个输出值。
· 包含T-SQL语句用以完成特定的SQL Server操作。
· 返回一个指示成功与否及失败原因的状态代码给调用它的过程。
存储过程是一组预编译的Transact-SQL语句，主体构成是标准SQL命令，同时包括SQL的扩展：语句块、结构控制命令、变量、常量、运算符、表达式、流程控制等，所有这些组合在一起用于构造存储过程。
3．存储过程的优点
存储过程有以下几个优点：
· 允许模块化编程，增强代码的重用性和共享性
· 使用存储过程可以加快运行速度
· 使用存储过程可以减少网络流量。
· 存储过程可以作为安全性机制。
4．存储过程的分类
存储过程有以下几种类型：系统存储过程、用户存储过程、临时存储过程、扩展存储过程、远程存储过程。
系统存储过程是由系统提供的过程，可以作为命令直接执行。系统存储过程还可以作为模板存储过程，指导用户如何编写有效的存储过程。系统存储过程存储在master数据库中，其前缀为sp_。系统存储过程可以在任意一个数据库中执行。本书附录B给出了常用的系统存储过程。
用户存储过程是创建在用户数据库中的存储过程。其名称前面一般不加sp_前缀。主要在应用程序中使用，以完成特定的任务。
临时存储过程属于用户存储过程。如果用户存储过程前面加上符号“#”，则该存储过程称为局部临时存储过程，只能在一个用户会话中使用；如果用户存储过程前面加上符号“##”，则该过程称为全局存储过程，可以在所有用户会话中使用。
扩展存储过程是在SQL Server环境之外执行的动态链接库DLL，其前缀为xp_。尽管这些动态链接库在SQL Server环境之外，但它们可以被加载到SQL Server系统中，并且按照存储过程的方式执行。
远程存储过程是指从远程服务器上调用的存储过程，或者是从连接到另外一个服务器上的客户机上调用的存储过程，是非本地服务器上的存储过程。
下面主要介绍用户存储过程的创建、修改、删除等操作。
7.2.2 创建存储过程
在SQL Server中创建存储过程主要有以下3种方法：使用向导、使用企业管理器、 使用Transact-SQL命令。
1.使用向导创建存储过程
使用向导创建存储过程的步骤为：
(1)启动企业管理器，展开数据库目录。
(2)在企业管理器的【工具】菜单中选择【向导】项。
(3)在【选择向导】对话框中展开【数据库】，单击【创建储存过程向导】项。
(4)单击【确定】按钮，打开创建存储过程的欢迎对话框(见图7-6)，这个对话框向用户介绍了利用创建向导创建存储过程的主要步骤。
程的主要步骤。
图7-6 创建存储过程的欢迎对话框图 7-7 选择储存过程对话框
(5)单击【下一步】按钮，打开数据库选择对话框(存储过程必须创建在一个数据库中)，在数据库下拉列表中选择所需要的数据库MyDb。
(6)单击【下一步】按钮，打开选择储存过程对话框，对话框*4个列表框，第1列为选定的数据库中的所有表，第2列为插入复选框，第3列为删除复选框，第4列为更新复选框。在这个对话框中选择将要包含在存储过程中的表【books】和对该表的操作【插入】，如图7-7所示。
(7)单击【下一步】按钮，打开完成创建储存过程对话框，如图7-8所示，对话框中显示了系统赋予存储过程的名称及其描述信息。单击【编辑．．．】按钮可以进入编辑储存过程对话框，使用这个对话框可以修改存储过程的名称，列表框中设有 4列信息，依次为列名、数据类型、长度和是否选择复选框。默认情况下选择所有列，单击复选框来选择需要的列，如图7-9所示。
在编辑储存过程对话框中单击【编辑SQL…】按钮，打开编辑储存过程SQL对话框，进行Transact-SQL的编辑，如图7-10所示。编辑完成后，单击【确定】按钮，返回上一个对话框。
(8)在图7-8所示对话框中，单击【完成】按钮，则存储过程创建完毕。在选定的数据库中，就会形成一个新的存储过程。
注意利用创建向导的存储过程功能有限，只适用于对表的简单操作，若须创建功能复杂的存储过程，则要用CREATE PROCEDURE命令完成。
图7-8 完成创建储存过程对话框图 7-9 编辑储存过程对话框
图7-10 编辑储存过程SQL 对话框
2．使用企业管理器创建存储过程
利用企业管理器创建存储过程步骤如下：
(1)展开要在其中创建存储过程的数据库。
(2)右击【存储过程】图标，在快捷菜单中选择【新建存储过程．．．】命令，打开新建存储过程对话框，如图7-11所示。
图7-11 新建存储过程对话框
(3)在新建存储过程对话框的文本框中书写存储过程定义。【检查语法】按钮用于检查存储过程语法的正确性。定义完存储过程后，单击【确定】按钮保存存储过程。
这种方法与下面讲的利用Transact-SQL命令创建存储过程几乎完全相同。
3．使用Transact-SQL命令创建存储过程
利用Transact-SQL命令创建存储过程的语法结构如下：
CREATE PROC[EDURE] procedure_name [；number] [{@parameter data_type}
[VARYING] [= default] [OUTPUT]][，．．．n]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE，ENCRYPTION}]
[FOR REPLICATION] AS sql_statement [．．．n]
说明：
(1) procedure_name 存储过程名，其命名规则遵守 SQL Server 标识符的命名规则，最长为 128个字符。
(2) [；number] 可选整数，用于将同名的存储过程分成组，以便用单独的一条DROP PROCEDURE将其语句撤消。
(3) @parameter 创建存储过程时可以声明一个或多个参数，最多为1024个。
(4) VARYING只用于光标参数。
(5) default 参数的默认值，可以为NULL，也可以包含通配符(％或_)。
(6) OUTPUT 表明参数为一个输出参数，当使用EXEC[UTE]执行时作为返回值，不能是TEXT类型。
(7) WITH RECOMPILE 每次执行存储过程时重新编译，产生新的执行计划，不能与 FOR REPLICATION同时使用。
(8) WITH ENCRYPTION 将syscomments表中的存储过程文本进行加密，使用户不能利用sp_helptext查看存储过程内容。
(9) FOR REPLICATION 表示该存储过程不能在订阅器上执行，只能在复制期间执行。
(10) sql_statement 作为存储过程主体部分的Transact-SQL内容。
存储过程只能在当前数据库中创建，且只有具有System Administrators、db_owner或 db_ddladmin角色的成员才可以创建。在CREATE PROCEDURE语句中，可以包括任意数量的Transact-SQL语句，但是不能使用CREATE DEFAULT,CREATE PROCEDURE,CREATE RULE,CREATE TRIGGER和CREATE VIEW这些创建对象的语句。
【例7-18】 创建一个简单的存储过程。
USE MyDB
Go
CREATE PROCEDURE borrowed_num
As
SELECT 姓名,已借数量
FROM readers
WHERE 姓名='刘超'
Go
【例7-19】 通过多表连接查询，创建较复杂的存储过程。
Use MyDb
Go
CREATE PROCEDURE borrowed_books1
As
SELECT r.编号,r.姓名,b.图书编号,k.书名,b.借期
FROM readers r inner join borrowinf b
ON r.编号=b.读者编号 INNER JOIN books k
ON b.图书编号=k.编号
WHERE 姓名='刘超'
7.2.3 存储过程中的参数
SQL Server中存储过程的参数包括输入参数和输出参数。参数扩展了SQL Server的功能，通过储存过程每次执行时不同的参数值，实现其灵活性。
1．输入参数
输入参数用于把值传入存储过程。
【例7-20】 使用输入参数，使得能够显示某人借阅书籍的情况。
Use MyDb
Go
CREATE PROCEDURE borrowed_books2
@name varchar(10)
As
SELECT r.编号,r.姓名,b.图书编号,k.书名,b.借期
FROM readers r inner join borrowinf b
ON r.编号=b.读者编号 INNER JOIN books k
ON b.图书编号=k.编号
WHERE 姓名=@name
此例因为利用了输入参数，使得存储每次执行时都可以指定不同的查询条件。
将值传入存储过程有以下几种方法：
(1)直接将值传入，如EXEC borrowed_books2 '张刚'。
(2)利用与声明时相同类型的变量来传递。如EXEC borrowed_books2 @templ (此处，@templ为已声明的字符类型变量，且已赋值)。
注意：指定的输入参数值必须与其定义时的类型相同，并且输入参数值必须与参数在存储过程中声明的顺序相同。
(3)使用参数名进行传递。这种形式对于有多个输入参数时，可以以任意的顺序进行参数传递。但如果对一个参数使用了名字，则必须对随后的参数都使用名字。
若没有将值传入相应的参数，并且在创建存储过程时也没有给参数赋默认值时，执行存储过程会出错。
【例7-21】 修改例7-20,使用默认参数。
Use MyDb
Go
CREATE PROCEDURE borrowed_books3
@name varchar(10)=NULL
As
IF @name IS NULL
SELECT r.编号,r.姓名,b.图书编号,k.书名,b.借期
FROM readers r inner join borrowinf b
ON r.编号=b.读者编号 INNER JOIN books k
ON b.图书编号=k.编号
ELSE
SELECT r.编号,r.姓名,b.图书编号,k.书名,b.借期
FROM readers r inner join borrowinf b
ON r.编号=b.读者编号 INNER JOIN books k
ON b.图书编号=k.编号
WHERE 姓名=@name
Go
请读者自己分析该例子。
2．输出参数
输出参数用于把返回值赋予变量并传给调用它的存储过程或应用程序。声明输出参数时需在声明参数的后面加上OUTPUT，以表明此参数为输出参数。
【例7-22】 利用输出参数计算阶乘。
USE MyDb
IF EXISTS (SELECT name FROM sysobjects
WHERE name='factorial' AND type='P')
DROP PROCEDURE factorial
GO
CREATE PROCEDURE factorial
@in float,
@out float OUTPUT
AS
DECLARE @i int
DECLARE @s float
SET @i=1
SET @s=1
WHILE @i<=@in
BEGIN
SET @s=@s*@i
SET @i=@i+1
END
SET @out=@s
在查询分析器中，用下面一段代码执行此存储过程：
DECLARE @ou float
EXEC factorial 10,@ou OUT
PRINT '其阶乘为：'+CAST(@ou AS varchar(20))
要执行一个带有输出参数的存储过程，必须声明一个变量来接受返回值（此变量不一定与创建存储过程时声明的输出参数同名），并且在变量后必须使用关键字OUT（OUTPUT）。
7.2.4 修改存储过程
1．使用企业管理器进行修改
修改存储过程只需在相应数据库的存储过程对象项中找到要修改的存储过程，双击存储过程打开存储过程属性窗口，如图7-12，在窗口中直接进行修改，完成后单击【确定】按钮。
图7-12 存储过程属性对话框
2．使用T-SQL命令进行修改
使用ALTER PROCEDURE可以修改存储过程，其语法结构如下：
ALTER PROC[EDURE] procedure_name [;number]
[{@parameter data_type} [VARYING] [=default] [OUTPUT]][,…n]
[WITH {RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
{FOR REPLICATION}
AS
sql-statement […n]
说明：ALTER PROCEDURE与CREATE PROCEDURE很类似。在CREATE PROCEDURE命令中使用的选项也必须在ALTER ROCEDURE中使用。ALTER ROCEDURE只能修改一个存储过程。如果该存储过程调用了其他存储过程，则不影响被调用的存储过程。只有具有db_owner和db_ddladmin权限的成员才被允许执行ALTER ROCEDURE，执行ALTER ROCEDURE语句的权限不能授予其他用户。
3. 使用查询分析器进行修改
打开查询分析器，按F8键（或单击工具栏上的对象浏览器按钮 ），在对象浏览器窗口中，打开存储过程所在的数据库，打开[存储过程]文件夹，右击要修改的存储过程名（如test）,在弹出的菜单中选择【编辑】（如图7-13所示），则存储过程出现在查询分析器的编辑窗口中，修改完毕保存即可。
图7-13 在查询分析器中编辑存储过程
7.2.5 运行存储过程
1．存储过程的编译
首次运行存储过程时，SQL Server编译并检验其中的程序，如果发现错误，系统将拒绝运行此存储过程。
即使只执行一条Transact-SQL语句，也要创建一份执行计划，执行计划包括存储过程所需的表行的索引。执行计划保留在缓存中，用于后续执行时完成存储过程的查询任务，提高执行速度。当出现下列几种情况时，存储过程被重新编译：
（1）当SQL Server重新启动，或存储过程第一次被执行时。
（2）存储过程修改后或其引用的表索引被删除后，执行计划被重新创建。
（3）当一个用户在使用缓冲区中的执行计划，重新编译为第二个用户创建第二个执行计划。
（4)存储过程删除或重建后，缓冲区中所有的执行计划备件都被删除，执行时自动进行重新编译，形成一份新的执行计划。
2．存储过程的执行
具有存储过程执行许可EXECUTE的用户，才可以执行存储过程。在查询分析器中，可以直接输入存储过程名，指定相应的输入参数和输出参数后执行。或者利用EXECUTE命令执行，这种方法同样适用于应用程序中调用存储过程。存储过程与函数不同，它不能直接用过程名返回值，也不能直接在表达式中使用。EXECUTE的语法结构如下：
[[EXEC[UTE]]
{[@return status =]
procedure_name[；number]| @procedure_name_var)
[[ @parameter=] {value| @variable [OUTPUT] | [DEFAULT]} [,…n]
[WITH RECOMPILE]
说明：
(1)利用WITH RECOMPILE可选项可以在执行存储过程时强制重新编译。
(2)@return status：为一整型变量，用于保存存储过程的返回状态。
7.2.6 删除存储过程
1．使用企业管理器删除存储过程
利用SQL Server 企业管理器删除存储过程是一种简单有效的方法。展开数据库后，单击存储过程图标，在右边窗口中出现的存储过程中选择要删除的过程，单击右键，在弹出的快捷菜单中单击【删除】选项，即将存储过程删除。
2．使用T-SQL命令删除存储过程
利用DROP PROCEDURE命令删除存储过程,其语法结构如下：
DROP PROCEDURE{procedure} [，．．．n]
【例7-23】 删除上例中创建的存储过程borrowed_num。
USE MyDb
GO
DROP PROCEDURE borrowed_num
存储过程被删除后，其存储在sysobjects和syscomments中的定义也被删除掉，如果用系统过程sp_helptext来查看存储过程文本，则不会看到定义的内容。
7.2.7 查看存储过程的有关信息
使用sp helptext系统存储过程，可以查看定义存储过程的T-SQL语句。图7-14显示了如何使用查询分析器查看存储过程borrowed_books1的定义。
图7-14 显示存储讨程的定义
使用企业管理器，用与上节介绍的修改存储过程相同的步骤打开存储过程属性对话框，也可以查看存储过程的定义。
如果存储过程的定义是被加密的，即在定义或修改存储过程的语句中使用了WITH ENCRYPTION子句，则存储过程的定义以不可读的形式保存在syscomments表中。这时，将不能查看存储过程的定义。比如，用以下语句修改borrowed_books1的定义：
Use MyDb
Go
ALTER PROCEDURE borrowed_books1
WITH ENCRYPTION
As
SELECT r.编号,r.姓名,b.图书编号,k.书名,b.借期
FROM readers r inner join borrowinf b
ON r.编号=b.读者编号 INNER JOIN books k
ON b.图书编号=k.编号
WHERE 姓名='刘超'
在查询分析器中执行以上语句，结果窗口中显示：
命令已成功完成．
表示存储过程修改成功，此时再执行sp_helptext borrowed_books1,将看不到borrowed_books1定义的文本，系统提示“对象备注已加密”。
使用系统存储过程sp_help可以获得有关存储过程创建者、创建日期以及存储过程使用的参数等信息。
使用系统存储过程sp_depends可以列出被该存储过程使用的对象以及调用该存储过程的其他存储过程的名称。这些信息可以用来了解删除该存储过程时会影响哪些数据库对象。