第 8章 存储过程
8.1 存储过程的概念
8.2 存储过程的类型
8.3 创建存储过程
8.4 执行存储过程
8.5 查看、修改存储过程
8.6 删除存储过程
8.1 存储过程的概念
SQL Server的存储过程类似于其它编程语言中的函数。在使用 T-SQL语言编程的过程中，
可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由 SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。
存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。
第
8
章存储过程
< >
8.1 存储过程的概念存储过程相对于本地 SQL语句,具有以下优点：
l 单个存储过程中可以执行一组相关的 SQL语句；
l 存储过程能够实现较快的执行速度 。 只需创建过程一次并将其存储在数据库中,以后即可在程序中调用该过程任意次 。 因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析,优化,并给出最终被存在系统表中的执行计划；
l 存储过程 独立于程序源代码,便于单独修改；
l 存储过程能够减少网络流量 。 存储过程 由一条执行过程代码的单独语句就可实现调用,网络中传送的只是该调用语句,而不需要在网络中发送所有相关的源代码,所以其执行时要比直接使用 SQL语句快 。
第
8
章存储过程
< >
8.2 存储过程的类型
8.2.1 系统存储过程系统存储过程是指由 SQL Server提供的存储过程,用以管理 SQLServer和显示有关数据库和用户的信息 。
8.2.2 临时存储过程
SQL Server支持两种临时过程：局部临时过程和全局临时过程 。 其中在过程名的前面带有 #符号的表示是局部临时过程；而带有 ##符号的表示是全局临时过程 。
8.2.3 远程存储过程仅限于在远程 SQL Server上执行存储过程 。
8.2.4 扩展存储过程扩展存储过程是 SQL Server可以动态装载并执行的动态链接库 （ DLL） 。
第
8
章存储过程
< >
8.3 创建存储过程创建存储过程既可以使用 T-SQL语句实现,也可以在企业管理器中完成 。 存储过程创建后,它的名称存储在系统表 sysobjects中；它的源代码存放在系统表 syscomments中 。
创建存储过程时,需要确定存储过程的三个组成部分：
l 所有的输入参数以及传给调用者的输出参数；
l 被执行的针对数据库的操作语句,包括调用其它存储过程的语句；
l 返回给调用者的状态值,以指明调用是成功还是失败 。
第
8
章存储过程
< >
8.3.1 使用 T-SQL语句创建存储过程创建存储过程的 T-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 ]
第
8
章存储过程
< >
8.3.1 使用 T-SQL语句创建存储过程
【 例 8-1】 在 Educational数据库中创建一个存储过程
spStuGrade_Class_term,要求返回某班某－学期所有学生的课程成绩 。
CREATE PROC spStuGrade_Class_term /* 存储过程名 */
@Para_ClassID char(8),/* 班级名,输入参数 */
@Para_Term tinyint=1 /* 学期,输入参数,默认值为 1 */
WITH ENCRYPTION /* 使用加密存放 */
AS
SELECT S.StudentID,S.StudentName,C.CourseName,G.Grade
FROM Grade G INNER JOIN Student S /* 按 StudentID联接 Student */
ON G.StudentID = S.StudentID INNER JOIN Course C /* 按 CourseID联接 Course */
ON G.CourseID = C.CourseID INNER JOIN Speciality_Course SC
ON G.CourseID = SC.CourseID /* 按 CourseID 联接
Speciality_Course */
WHERE S.ClassID=@Para_ClassID And SC.Term=@Para_Term /* 查询的班级及学期 */
ORDER BY S.StudentID
第
8
章存储过程
< >
8.3.2 使用企业管理器创建存储过程通过企业管理器,可以方便地创建存储过程,具体步骤如下：
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中创建存储过程的数据库
（ 这里为 Educational） 节点 。
3） 右击,存储过程,项,在弹出的快捷菜单中执行,新建存储过程,
菜单项,出现如图 8-2所示的,新建存储过程,对话框 。
4） 在,文本,文本框中显示了 CREATE PROCEDURE语句的框架 。 在此修改存储过程的名称,参数以及对应的 SQL语句,将例 8-1中的存储过程输入,创建一个名为 spStuGrade_Class_term的存储过程 。
5） 单击,检查语法,按钮,检查存储过程中语句的合法性,直到
,语法检查成功,。
6） 若要更改权限,则单击,权限,按钮,在出现的,对象属性,对话框中设置该存储过程的权限 。
7） 单击,确定,按钮,就可完成存储过程的创建工作 。 此时,检查
,存储过程,项,会发现系统中多了一个名为 spStuGrade_Class_term的存储过程 。
第
8
章存储过程
< >
8.4 执行存储过程使用 T-SQL的 EXECUTE语句可以执行存储过程,如果存储过程是批处理中的第一条语句,那么省略 EXECUTE关键字也可以执行存储过程 。 如果需要在每次 SQL Server启动时,由系统自动执行相关的存储过程 。
8.4.1 使用 EXECUTE语句执行存储过程使用 EXECUTE语句可以执行存储过程 。 EXECUTE语句的语法格式如下：
[ EXEC[UTE] ]
{ [@return_status= ]{ procedure_name[;number] |
@procedure_name_var}
[ [@parameterName= ] { value | @variable [OUTPUT]
| [DEFAULT] }[,...n ] ]
[ WITH RECOMPILE ]
第
8
章存储过程
< >
8.4 执行存储过程
1,顺序格式顺序格式是传递参数的最简单方式,它以存储过程定义时参数说明的先后次序,以 value或 @variable的方式传递 。
【 例 8-3】 调 用 例 8-1 中 创 建 的 存 储 过 程
spStuGrade_Class_term,返回 030501班第 5学期所有学生的课程成绩 。
USE Educational
GO
EXECUTE spStuGrade_Class_term '030501',5
/* 030501班第 5学期 */
第
8
章存储过程
< >
8.4 执行存储过程
2,指定参数名格式此种格式就是以 @parameterName= value或 @variable的方式传递,因在参数传递时已指明参数名,故调用时对参数的次序没有要求,这点与顺序格式是完全不同的 。
【 例 8-4】 调 用 例 8-2 中 创 建 的 存 储 过 程
spAvgGrade_Class_Course,求 030501班操作系统课程的平均成绩 。
USE Educational
GO
DECLARE @avg FLOAT /* 声明变量 */
/*调用存储过程 */
EXEC spAvgGrade_Class_Course @Para_Courseavg = @avg OUTPUT,
@Para_ClassID = '030501',@Para_CourseName = '操作系统 '
PRINT @avg /* 输出返回的平均值 */
第
8
章存储过程
< >
8.4 执行存储过程
8.4.2 自动执行存储过程
SQL Server在启动时可以自动运行一个或多个存储过程 。
如果需要 SQL Server在每次启动时自动运行某个存储过程,
可以使用系统存储过程 sp_procoption进行设置 。
使用系统存储过程 sp_procoption可以将 master数据库中的现有存储过程设置为自动执行存储过程或解除自动执行 。
sp_procoption的语法格式为：
sp_procoption [ @ProcName = ] 'procedure_name'
,[ @OptionName = ] 'option'
,[ @OptionValue = ] 'value'
第
8
章存储过程
< >
8.5 查看、修改存储过程
8.5.1 查看存储过程
1,查看存储过程的定义文本
sp_helptext [ @objname = ] 'name'
2,查看存储过程的定义参数
sp_help [ [ @objname = ] name ]
3,查看存储过程的相关性
sp_depends [ @objname = ] 'object'
第
8
章存储过程
< >
8.5 查看、修改存储过程
8.5.2 修改存储过程
1,使用 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 ]
第
8
章存储过程
< >
8.5 查看、修改存储过程
8.5.2 修改存储过程
2,使用企业管理器修改存储过程在企业管理器中修改存储过程就变得简单与方便了,其步骤如下：
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中修改存储过程的数据库 （ 这里为 Educational） 节点 。
3） 单击,存储过程,项,右侧的对象列表中显示出该数据库中所有已创建的存储过程 。 在该列表中找到并双击要修改的存储过程 （ 以 spStuGrade_term为例 ）,出现如图 8-4所示的,存储过程属性,对话框 。
第
8
章存储过程
< >
8.6 删除存储过程
1,使用 T-SQL语句删除存储过程使用 DROP PROCEDURE命令可以在当前数据库中删除一个或多个存储过程 。 DROP PROCEDURE命令的语法格式如下：
DROP PROC[EDURE] { procedure_name }
[,...n ]
例如,要删除 spStuGrade_term存储过程,可以使用以下命令实现：
DROP PROC spStuGrade_term
第
8
章存储过程
< >
8.6 删除存储过程
2,使用企业管理器删除存储过程在企业管理器中可以方便地删除存储过程,其步骤如下：
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中修改存储过程的数据库节点 。
3） 单击,存储过程,项,右侧的对象列表中显示出该数据库中所有已存在的存储过程 。 在该列表中找到并右击要删除的存储过程,在出现的快捷菜单中选择,删除,命令,出现,除去对象,对话框 。
4） 单击,显示相关性,按钮,可以察看该存储过程的依附对象和被依附对象 。
5） 单击,全部除去,按钮,完成删除工作 。
第
8
章存储过程
< >
8.1 存储过程的概念
8.2 存储过程的类型
8.3 创建存储过程
8.4 执行存储过程
8.5 查看、修改存储过程
8.6 删除存储过程
8.1 存储过程的概念
SQL Server的存储过程类似于其它编程语言中的函数。在使用 T-SQL语言编程的过程中，
可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由 SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。
存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。
第
8
章存储过程
< >
8.1 存储过程的概念存储过程相对于本地 SQL语句,具有以下优点：
l 单个存储过程中可以执行一组相关的 SQL语句；
l 存储过程能够实现较快的执行速度 。 只需创建过程一次并将其存储在数据库中,以后即可在程序中调用该过程任意次 。 因为存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析,优化,并给出最终被存在系统表中的执行计划；
l 存储过程 独立于程序源代码,便于单独修改；
l 存储过程能够减少网络流量 。 存储过程 由一条执行过程代码的单独语句就可实现调用,网络中传送的只是该调用语句,而不需要在网络中发送所有相关的源代码,所以其执行时要比直接使用 SQL语句快 。
第
8
章存储过程
< >
8.2 存储过程的类型
8.2.1 系统存储过程系统存储过程是指由 SQL Server提供的存储过程,用以管理 SQLServer和显示有关数据库和用户的信息 。
8.2.2 临时存储过程
SQL Server支持两种临时过程：局部临时过程和全局临时过程 。 其中在过程名的前面带有 #符号的表示是局部临时过程；而带有 ##符号的表示是全局临时过程 。
8.2.3 远程存储过程仅限于在远程 SQL Server上执行存储过程 。
8.2.4 扩展存储过程扩展存储过程是 SQL Server可以动态装载并执行的动态链接库 （ DLL） 。
第
8
章存储过程
< >
8.3 创建存储过程创建存储过程既可以使用 T-SQL语句实现,也可以在企业管理器中完成 。 存储过程创建后,它的名称存储在系统表 sysobjects中；它的源代码存放在系统表 syscomments中 。
创建存储过程时,需要确定存储过程的三个组成部分：
l 所有的输入参数以及传给调用者的输出参数；
l 被执行的针对数据库的操作语句,包括调用其它存储过程的语句；
l 返回给调用者的状态值,以指明调用是成功还是失败 。
第
8
章存储过程
< >
8.3.1 使用 T-SQL语句创建存储过程创建存储过程的 T-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 ]
第
8
章存储过程
< >
8.3.1 使用 T-SQL语句创建存储过程
【 例 8-1】 在 Educational数据库中创建一个存储过程
spStuGrade_Class_term,要求返回某班某－学期所有学生的课程成绩 。
CREATE PROC spStuGrade_Class_term /* 存储过程名 */
@Para_ClassID char(8),/* 班级名,输入参数 */
@Para_Term tinyint=1 /* 学期,输入参数,默认值为 1 */
WITH ENCRYPTION /* 使用加密存放 */
AS
SELECT S.StudentID,S.StudentName,C.CourseName,G.Grade
FROM Grade G INNER JOIN Student S /* 按 StudentID联接 Student */
ON G.StudentID = S.StudentID INNER JOIN Course C /* 按 CourseID联接 Course */
ON G.CourseID = C.CourseID INNER JOIN Speciality_Course SC
ON G.CourseID = SC.CourseID /* 按 CourseID 联接
Speciality_Course */
WHERE S.ClassID=@Para_ClassID And SC.Term=@Para_Term /* 查询的班级及学期 */
ORDER BY S.StudentID
第
8
章存储过程
< >
8.3.2 使用企业管理器创建存储过程通过企业管理器,可以方便地创建存储过程,具体步骤如下：
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中创建存储过程的数据库
（ 这里为 Educational） 节点 。
3） 右击,存储过程,项,在弹出的快捷菜单中执行,新建存储过程,
菜单项,出现如图 8-2所示的,新建存储过程,对话框 。
4） 在,文本,文本框中显示了 CREATE PROCEDURE语句的框架 。 在此修改存储过程的名称,参数以及对应的 SQL语句,将例 8-1中的存储过程输入,创建一个名为 spStuGrade_Class_term的存储过程 。
5） 单击,检查语法,按钮,检查存储过程中语句的合法性,直到
,语法检查成功,。
6） 若要更改权限,则单击,权限,按钮,在出现的,对象属性,对话框中设置该存储过程的权限 。
7） 单击,确定,按钮,就可完成存储过程的创建工作 。 此时,检查
,存储过程,项,会发现系统中多了一个名为 spStuGrade_Class_term的存储过程 。
第
8
章存储过程
< >
8.4 执行存储过程使用 T-SQL的 EXECUTE语句可以执行存储过程,如果存储过程是批处理中的第一条语句,那么省略 EXECUTE关键字也可以执行存储过程 。 如果需要在每次 SQL Server启动时,由系统自动执行相关的存储过程 。
8.4.1 使用 EXECUTE语句执行存储过程使用 EXECUTE语句可以执行存储过程 。 EXECUTE语句的语法格式如下：
[ EXEC[UTE] ]
{ [@return_status= ]{ procedure_name[;number] |
@procedure_name_var}
[ [@parameterName= ] { value | @variable [OUTPUT]
| [DEFAULT] }[,...n ] ]
[ WITH RECOMPILE ]
第
8
章存储过程
< >
8.4 执行存储过程
1,顺序格式顺序格式是传递参数的最简单方式,它以存储过程定义时参数说明的先后次序,以 value或 @variable的方式传递 。
【 例 8-3】 调 用 例 8-1 中 创 建 的 存 储 过 程
spStuGrade_Class_term,返回 030501班第 5学期所有学生的课程成绩 。
USE Educational
GO
EXECUTE spStuGrade_Class_term '030501',5
/* 030501班第 5学期 */
第
8
章存储过程
< >
8.4 执行存储过程
2,指定参数名格式此种格式就是以 @parameterName= value或 @variable的方式传递,因在参数传递时已指明参数名,故调用时对参数的次序没有要求,这点与顺序格式是完全不同的 。
【 例 8-4】 调 用 例 8-2 中 创 建 的 存 储 过 程
spAvgGrade_Class_Course,求 030501班操作系统课程的平均成绩 。
USE Educational
GO
DECLARE @avg FLOAT /* 声明变量 */
/*调用存储过程 */
EXEC spAvgGrade_Class_Course @Para_Courseavg = @avg OUTPUT,
@Para_ClassID = '030501',@Para_CourseName = '操作系统 '
PRINT @avg /* 输出返回的平均值 */
第
8
章存储过程
< >
8.4 执行存储过程
8.4.2 自动执行存储过程
SQL Server在启动时可以自动运行一个或多个存储过程 。
如果需要 SQL Server在每次启动时自动运行某个存储过程,
可以使用系统存储过程 sp_procoption进行设置 。
使用系统存储过程 sp_procoption可以将 master数据库中的现有存储过程设置为自动执行存储过程或解除自动执行 。
sp_procoption的语法格式为：
sp_procoption [ @ProcName = ] 'procedure_name'
,[ @OptionName = ] 'option'
,[ @OptionValue = ] 'value'
第
8
章存储过程
< >
8.5 查看、修改存储过程
8.5.1 查看存储过程
1,查看存储过程的定义文本
sp_helptext [ @objname = ] 'name'
2,查看存储过程的定义参数
sp_help [ [ @objname = ] name ]
3,查看存储过程的相关性
sp_depends [ @objname = ] 'object'
第
8
章存储过程
< >
8.5 查看、修改存储过程
8.5.2 修改存储过程
1,使用 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 ]
第
8
章存储过程
< >
8.5 查看、修改存储过程
8.5.2 修改存储过程
2,使用企业管理器修改存储过程在企业管理器中修改存储过程就变得简单与方便了,其步骤如下：
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中修改存储过程的数据库 （ 这里为 Educational） 节点 。
3） 单击,存储过程,项,右侧的对象列表中显示出该数据库中所有已创建的存储过程 。 在该列表中找到并双击要修改的存储过程 （ 以 spStuGrade_term为例 ）,出现如图 8-4所示的,存储过程属性,对话框 。
第
8
章存储过程
< >
8.6 删除存储过程
1,使用 T-SQL语句删除存储过程使用 DROP PROCEDURE命令可以在当前数据库中删除一个或多个存储过程 。 DROP PROCEDURE命令的语法格式如下：
DROP PROC[EDURE] { procedure_name }
[,...n ]
例如,要删除 spStuGrade_term存储过程,可以使用以下命令实现：
DROP PROC spStuGrade_term
第
8
章存储过程
< >
8.6 删除存储过程
2,使用企业管理器删除存储过程在企业管理器中可以方便地删除存储过程,其步骤如下：
1） 启动企业管理器,展开相应的服务器组和所要操作的服务器节点 。
2） 展开,数据库,文件夹,再展开要在其中修改存储过程的数据库节点 。
3） 单击,存储过程,项,右侧的对象列表中显示出该数据库中所有已存在的存储过程 。 在该列表中找到并右击要删除的存储过程,在出现的快捷菜单中选择,删除,命令,出现,除去对象,对话框 。
4） 单击,显示相关性,按钮,可以察看该存储过程的依附对象和被依附对象 。
5） 单击,全部除去,按钮,完成删除工作 。
第
8
章存储过程
< >
