第 8章 存储过程的操作与管理存储过程概述存储过程是为完成特定的功能而汇集在一起的一组 SQL程序语句，经编译后存储在数据库中的 SQL程序。
在 SQL Server 中使用存储过程而不使用存储在客户端计算机本地的 Transact-SQL 程序的优点包括：
（ 1）存储过程已在服务器注册。
（ 2）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。
（ 3）存储过程可以强制应用程序的安全性。
（ 4）存储过程允许模块化程序设计。
（ 5）存储过程是命名代码，允许延迟绑定。
（ 6）存储过程可以减少网络通信流量。
8.1 创建存储过程在 SQL Server中，可以使用两种方法创建存储过程：
（ 1）使用创建存储过程模板创建存储过程；
（ 2）利用 SQL Server 管理平台创建存储过程。
当创建存储过程时，需要确定存储过程的三个组成部分：
（ 1）所有的输入参数以及传给调用者的输出参数。
（ 2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。
（ 3）返回给调用者的状态值，以指明调用是成功还是失败。
8.1 创建存储过程
CREATE PROCEDURE的语法形式如下：
CREATE {PROC|PROCEDURE}
[schema_name.]procedure_name[;number]
[{@parameter[type_schema_name.] data_type}
[VARYING][=default][[OUT[PUT]][,...n]
[WITH <procedure_option> [,...n]
[FOR REPLICATION]
AS
{<sql_statement>[;][...n]|<method_specifier>}[;]
<procedure_option>,:=
[ENCRYPTION][RECOMPILE] EXECUTE_AS_Clause]
<sql_statement>,:=
{[BEGIN] statements [END]}
<method_specifier>,:= EXTERNAL NAME
assembly_name.class_name.method_name
8.1.1 使用模板创建存储过程
（ 1）在 SQL Server 管理平台中，选择“视图（ View）”菜单中的“模板资源资源管理器 (Template Explorer)”，出现“模板资源管理器 (Template
Explorer)”窗口，选择“存储过程”中的“创建存储过程”选项，如图 8-1所示。
（ 2）在文本框中可以输入创建存储过程的 Transact_SQL语句，单击“执行”
按钮，即可创建该存储过程。
图 8-1 创建存储过程模板
8.1.2使用管理平台创建存储过程
（ 1）在 SQL Server管理平台中，展开指定的服务器和数据库，然后展开程序，右击存储过程选项，在弹出的快捷菜单中依次选择“新建 → 存储过程 …” 选项，如图 8-2所示，出现创建存储过程窗口。
（ 2）在文本框中可以输入创建存储过程的 Transact_SQL语句，单击“执行”按钮，即可创建该存储过程。
图 8-2 新建存储过程
8.1.2使用管理平台创建存储过程例 8-1 创建一个带有 SELECT语句的简单过程，该存储过程返回所有员工姓名,Email地址，电话等。该存储过程不使用任何参数程序清单如下。
USE adventureworks
GO
CREATE PROCEDURE au_infor_all
AS
SELECT lastname,firstname,emailaddress,phone
FROM person.contact
GO
8.1.2使用管理平台创建存储过程例 8-2 创建一个存储过程，以简化对 sc表的数据添加工作，使得在执行该存储过程时，其参数值作为数据添加到表中。
程序清单如下：
CREATE PROCEDURE [dbo].[ pr1_sc_ins]
@Param1 char(10),@Param2 char(2),@Param3 real
AS
BEGIN
insert into sc(sno,cno,score)
values(@Param1,@Param2,@Param3)
END
8.1.2使用管理平台创建存储过程例 8-3 创建一个带有参数的简单存储过程，从视图中返回指定的雇员（提供名和姓）及其职务和部门名称，该存储过程接受与传递的参数精确匹配的值程序清单如下。
USE AdventureWorks;
GO
CREATE PROCEDURE GetEmployees
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT LastName,FirstName,JobTitle,Department
FROM HumanResources.vEmployeeDepartment
WHERE FirstName = @firstname AND LastName = @lastname;
GO
8.1.2使用管理平台创建存储过程例 8-4下面的存储过程从表 person.contact中返回指定的一些员工姓名及其电话。该存储过程对传递的参数进行模式匹配。
如果没有提供参数，则使用预设的默认值（姓氏以字母 D开头）
程序清单如下。
USE AdventureWorks;
GO
CREATE PROCEDURE au_infor2
@lastname varchar(40) = 'D%',@firstname varchar(20) = '%'
AS
SELECT firstname,lastname,phone
FROM person.contact
WHERE firstname LIKE @firstname AND lastname LIKE @lastname
GO
8.1.2使用管理平台创建存储过程例 8-5以下示例显示有一个输入参数和一个输出参数的存储过程。
存储过程中的第一个参数 @sname将接收由调用程序指定的输入值 (学生姓名 )，第二个参数 @sscore（成绩）将用于将该值返回调用程序。 SELECT 语句使用 @sname参数获取正确的 @sscore值，
并将该值分配给输出参数。
程序清单如下：
CREATE PROCEDURE s_score
@sname char(8),@sscore real output
AS
SELECT @sscore =score from sc join s on s.sno=sc.sno
where sn=@sname
GO
8.1.3 执行存储过程
可以使用 Transact-SQL EXECUTE 语句来运行存储过程。存储过程与函数不同，因为存储过程不返回取代其名称的值，也不能直接在表达式中使用。
执行存储过程必须具有执行存储过程的权限许可，才可以直接执行存储过程，直接执行存储过程可以使用 EXECUTE命令来执行，
语法形式如下：
[[EXEC[UTE]]
{
[@return_status=]
{procedure_name[;number]|@procedure_name_var}
[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}
[,...n]
[ WITH RECOMPILE ]
8.1.3 执行存储过程例 8-6 执行存储过程 au_infor_all。
au_infor_all 存储过程可以通过以下方法执行：
EXECUTE（ EXEC） au_infor_all
例 8-7 使用 EXECUTE 命令传递参数，执行例 8-2定义的存储过程 pr1_sc_ins。
sc_ins存储过程可以通过以下方法执行：
EXEC pr1_sc_ins ‘3130040101’,’c1’,85
当然，在执行过程中变量可以显式命名：
EXEC sc_ins @Param1=’ 3130040101’,@Param2=’c1’,@Param3=85
例 8-8 执行例 8-3定义的存储过程 GetEmployees 。
GetEmployees存储过程可以通过以下方法执行：
EXECUTE（ EXEC） GetEmployees 'Dull','Ann' 或者
EXECUTE（ EXEC） GetEmployees @lastname = 'Dull',@firstname = 'Ann' 或者
EXECUTE（ EXEC） GetEmployees @firstname = 'Ann',@lastname = 'Dull'
8.2查看、修改和删除存储过程
8.2.1 查看存储过程
8.2.2 修改存储过程
8.2.3 重命名和删除存储过程
8.2.1 查看存储过程
（ 1）使用 SQL Server管理平台查看用户创建的存储过程。
在 SQL Server管理平台中，展开指定的服务器和数据库，选择并依次展开“程序 → 存储过程”，然后右击要查看的存储过程名称，如图 8-3所示，从弹出的快捷菜单中，选择
“创建存储过程脚本为 → CREATE到 → 新查询编辑器窗口”，则可以看到存储过程的源代码。
图 8-3 查看存储过程
8.2.1 查看存储过程
（ 2）使用系统存储过程来查看用户创建的存储过程 。
可供使用的系统存储过程及其语法形式如下：
sp_help，用于显示存储过程的参数及其数据类型，其语法为：
sp_help [[@objname=] name]，参数 name为要查看的存储过程的名称。
sp_helptext，用于显示存储过程的源代码，其语法为：
sp_helptext [[@objname=] name]，参数 name为要查看的存储过程的名称。
sp_depends，用于显示和存储过程相关的数据库对象，其语法为：
sp_depends [@objname=]’object’，参数 object为要查看依赖关系的存储过程的名称。
sp_stored_procedures，用于返回当前数据库中的存储过程列表，其语法为：
sp_stored_procedures[[@sp_name=]'name']
[,[@sp_owner=]'owner']
[,[@sp_qualifier =] 'qualifier']
其中,[@sp_name =] 'name' 用于指定返回目录信息的过程名； [@sp_owner
=] 'owner' 用于指定过程所有者的名称； [@qualifier =] 'qualifier' 用于指定过程限定符的名称。
8.2.2 修改存储过程
存储过程可以根据用户的要求或者基表定义的改变而改变。使用
ALTER PROCEDURE语句可以更改先前通过执行 CREATE
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 ]
8.2.2 修改存储过程例 8-9 创建了一个名为 proc_person 的存储过程，该存储过程包含姓名和 Email地址信息。然后，用 ALTER PROCEDURE重新定义了该存储过程，使之只包含姓名信息，并使用 ENCRYPTION关键字使之无法通过查看 syscomments表来查看存储过程的内容。
程序清单如下。
USE adventureworks
GO
/*创建一个存储过程，该存储过程包含姓名和 Email地址信息 */
CREATE PROCEDURE proc_person
AS
SELECT firstname,lastname,emailaddress
FROM person.contact
ORDER BY lastname,firstname
GO
8.2.2 修改存储过程下面对该存储过程进行重新定义。使之只包含姓名信息，并使用
ENCRYPTION关键字使之无法通过查看 syscomments表来查看存储过程的内容。
程序清单如下：
ALTER PROCEDURE proc_person
WITH ENCRYPTION
AS
SELECT firstname,lastname
FROM person.contact
ORDER BY lastname,firstname
GO
8.2.3 重命名和删除存储过程
1,重命名存储过程修改存储过程的名称可以使用系统存储过程 sp_rename，其语法形式如下：
sp_rename 原存储过程名称，新存储过程名称另外，通过 SQL Server管理平台也可以修改存储过程的名称。在 SQL Server管理平台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”
选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。
2,删除存储过程删除存储过程可以使用 DROP命令,DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：
drop procedure {procedure}[,…n]
当然，利用 SQL Server管理平台也可以很方便地删除存储过程。在 SQL Server
管理平台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，
则会弹出除去对象对话框，在该对话框中，单击“确定”按钮，即可完成删除操作。单击“显示相关性”按钮，则可以在删除前查看与该存储过程有依赖关系的其他数据库对象名称。
在 SQL Server 中使用存储过程而不使用存储在客户端计算机本地的 Transact-SQL 程序的优点包括：
（ 1）存储过程已在服务器注册。
（ 2）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。
（ 3）存储过程可以强制应用程序的安全性。
（ 4）存储过程允许模块化程序设计。
（ 5）存储过程是命名代码，允许延迟绑定。
（ 6）存储过程可以减少网络通信流量。
8.1 创建存储过程在 SQL Server中，可以使用两种方法创建存储过程：
（ 1）使用创建存储过程模板创建存储过程；
（ 2）利用 SQL Server 管理平台创建存储过程。
当创建存储过程时，需要确定存储过程的三个组成部分：
（ 1）所有的输入参数以及传给调用者的输出参数。
（ 2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。
（ 3）返回给调用者的状态值，以指明调用是成功还是失败。
8.1 创建存储过程
CREATE PROCEDURE的语法形式如下：
CREATE {PROC|PROCEDURE}
[schema_name.]procedure_name[;number]
[{@parameter[type_schema_name.] data_type}
[VARYING][=default][[OUT[PUT]][,...n]
[WITH <procedure_option> [,...n]
[FOR REPLICATION]
AS
{<sql_statement>[;][...n]|<method_specifier>}[;]
<procedure_option>,:=
[ENCRYPTION][RECOMPILE] EXECUTE_AS_Clause]
<sql_statement>,:=
{[BEGIN] statements [END]}
<method_specifier>,:= EXTERNAL NAME
assembly_name.class_name.method_name
8.1.1 使用模板创建存储过程
（ 1）在 SQL Server 管理平台中，选择“视图（ View）”菜单中的“模板资源资源管理器 (Template Explorer)”，出现“模板资源管理器 (Template
Explorer)”窗口，选择“存储过程”中的“创建存储过程”选项，如图 8-1所示。
（ 2）在文本框中可以输入创建存储过程的 Transact_SQL语句，单击“执行”
按钮，即可创建该存储过程。
图 8-1 创建存储过程模板
8.1.2使用管理平台创建存储过程
（ 1）在 SQL Server管理平台中，展开指定的服务器和数据库，然后展开程序，右击存储过程选项，在弹出的快捷菜单中依次选择“新建 → 存储过程 …” 选项，如图 8-2所示，出现创建存储过程窗口。
（ 2）在文本框中可以输入创建存储过程的 Transact_SQL语句，单击“执行”按钮，即可创建该存储过程。
图 8-2 新建存储过程
8.1.2使用管理平台创建存储过程例 8-1 创建一个带有 SELECT语句的简单过程，该存储过程返回所有员工姓名,Email地址，电话等。该存储过程不使用任何参数程序清单如下。
USE adventureworks
GO
CREATE PROCEDURE au_infor_all
AS
SELECT lastname,firstname,emailaddress,phone
FROM person.contact
GO
8.1.2使用管理平台创建存储过程例 8-2 创建一个存储过程，以简化对 sc表的数据添加工作，使得在执行该存储过程时，其参数值作为数据添加到表中。
程序清单如下：
CREATE PROCEDURE [dbo].[ pr1_sc_ins]
@Param1 char(10),@Param2 char(2),@Param3 real
AS
BEGIN
insert into sc(sno,cno,score)
values(@Param1,@Param2,@Param3)
END
8.1.2使用管理平台创建存储过程例 8-3 创建一个带有参数的简单存储过程，从视图中返回指定的雇员（提供名和姓）及其职务和部门名称，该存储过程接受与传递的参数精确匹配的值程序清单如下。
USE AdventureWorks;
GO
CREATE PROCEDURE GetEmployees
@lastname varchar(40),
@firstname varchar(20)
AS
SELECT LastName,FirstName,JobTitle,Department
FROM HumanResources.vEmployeeDepartment
WHERE FirstName = @firstname AND LastName = @lastname;
GO
8.1.2使用管理平台创建存储过程例 8-4下面的存储过程从表 person.contact中返回指定的一些员工姓名及其电话。该存储过程对传递的参数进行模式匹配。
如果没有提供参数，则使用预设的默认值（姓氏以字母 D开头）
程序清单如下。
USE AdventureWorks;
GO
CREATE PROCEDURE au_infor2
@lastname varchar(40) = 'D%',@firstname varchar(20) = '%'
AS
SELECT firstname,lastname,phone
FROM person.contact
WHERE firstname LIKE @firstname AND lastname LIKE @lastname
GO
8.1.2使用管理平台创建存储过程例 8-5以下示例显示有一个输入参数和一个输出参数的存储过程。
存储过程中的第一个参数 @sname将接收由调用程序指定的输入值 (学生姓名 )，第二个参数 @sscore（成绩）将用于将该值返回调用程序。 SELECT 语句使用 @sname参数获取正确的 @sscore值，
并将该值分配给输出参数。
程序清单如下：
CREATE PROCEDURE s_score
@sname char(8),@sscore real output
AS
SELECT @sscore =score from sc join s on s.sno=sc.sno
where sn=@sname
GO
8.1.3 执行存储过程
可以使用 Transact-SQL EXECUTE 语句来运行存储过程。存储过程与函数不同，因为存储过程不返回取代其名称的值，也不能直接在表达式中使用。
执行存储过程必须具有执行存储过程的权限许可，才可以直接执行存储过程，直接执行存储过程可以使用 EXECUTE命令来执行，
语法形式如下：
[[EXEC[UTE]]
{
[@return_status=]
{procedure_name[;number]|@procedure_name_var}
[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}
[,...n]
[ WITH RECOMPILE ]
8.1.3 执行存储过程例 8-6 执行存储过程 au_infor_all。
au_infor_all 存储过程可以通过以下方法执行：
EXECUTE（ EXEC） au_infor_all
例 8-7 使用 EXECUTE 命令传递参数，执行例 8-2定义的存储过程 pr1_sc_ins。
sc_ins存储过程可以通过以下方法执行：
EXEC pr1_sc_ins ‘3130040101’,’c1’,85
当然，在执行过程中变量可以显式命名：
EXEC sc_ins @Param1=’ 3130040101’,@Param2=’c1’,@Param3=85
例 8-8 执行例 8-3定义的存储过程 GetEmployees 。
GetEmployees存储过程可以通过以下方法执行：
EXECUTE（ EXEC） GetEmployees 'Dull','Ann' 或者
EXECUTE（ EXEC） GetEmployees @lastname = 'Dull',@firstname = 'Ann' 或者
EXECUTE（ EXEC） GetEmployees @firstname = 'Ann',@lastname = 'Dull'
8.2查看、修改和删除存储过程
8.2.1 查看存储过程
8.2.2 修改存储过程
8.2.3 重命名和删除存储过程
8.2.1 查看存储过程
（ 1）使用 SQL Server管理平台查看用户创建的存储过程。
在 SQL Server管理平台中，展开指定的服务器和数据库，选择并依次展开“程序 → 存储过程”，然后右击要查看的存储过程名称，如图 8-3所示，从弹出的快捷菜单中，选择
“创建存储过程脚本为 → CREATE到 → 新查询编辑器窗口”，则可以看到存储过程的源代码。
图 8-3 查看存储过程
8.2.1 查看存储过程
（ 2）使用系统存储过程来查看用户创建的存储过程 。
可供使用的系统存储过程及其语法形式如下：
sp_help，用于显示存储过程的参数及其数据类型，其语法为：
sp_help [[@objname=] name]，参数 name为要查看的存储过程的名称。
sp_helptext，用于显示存储过程的源代码，其语法为：
sp_helptext [[@objname=] name]，参数 name为要查看的存储过程的名称。
sp_depends，用于显示和存储过程相关的数据库对象，其语法为：
sp_depends [@objname=]’object’，参数 object为要查看依赖关系的存储过程的名称。
sp_stored_procedures，用于返回当前数据库中的存储过程列表，其语法为：
sp_stored_procedures[[@sp_name=]'name']
[,[@sp_owner=]'owner']
[,[@sp_qualifier =] 'qualifier']
其中,[@sp_name =] 'name' 用于指定返回目录信息的过程名； [@sp_owner
=] 'owner' 用于指定过程所有者的名称； [@qualifier =] 'qualifier' 用于指定过程限定符的名称。
8.2.2 修改存储过程
存储过程可以根据用户的要求或者基表定义的改变而改变。使用
ALTER PROCEDURE语句可以更改先前通过执行 CREATE
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 ]
8.2.2 修改存储过程例 8-9 创建了一个名为 proc_person 的存储过程，该存储过程包含姓名和 Email地址信息。然后，用 ALTER PROCEDURE重新定义了该存储过程，使之只包含姓名信息，并使用 ENCRYPTION关键字使之无法通过查看 syscomments表来查看存储过程的内容。
程序清单如下。
USE adventureworks
GO
/*创建一个存储过程，该存储过程包含姓名和 Email地址信息 */
CREATE PROCEDURE proc_person
AS
SELECT firstname,lastname,emailaddress
FROM person.contact
ORDER BY lastname,firstname
GO
8.2.2 修改存储过程下面对该存储过程进行重新定义。使之只包含姓名信息，并使用
ENCRYPTION关键字使之无法通过查看 syscomments表来查看存储过程的内容。
程序清单如下：
ALTER PROCEDURE proc_person
WITH ENCRYPTION
AS
SELECT firstname,lastname
FROM person.contact
ORDER BY lastname,firstname
GO
8.2.3 重命名和删除存储过程
1,重命名存储过程修改存储过程的名称可以使用系统存储过程 sp_rename，其语法形式如下：
sp_rename 原存储过程名称，新存储过程名称另外，通过 SQL Server管理平台也可以修改存储过程的名称。在 SQL Server管理平台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”
选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。
2,删除存储过程删除存储过程可以使用 DROP命令,DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：
drop procedure {procedure}[,…n]
当然，利用 SQL Server管理平台也可以很方便地删除存储过程。在 SQL Server
管理平台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，
则会弹出除去对象对话框，在该对话框中，单击“确定”按钮，即可完成删除操作。单击“显示相关性”按钮，则可以在删除前查看与该存储过程有依赖关系的其他数据库对象名称。
