1
第十二章 管理存储过程
? 了解存储过程的基本概念
? 掌握使用企业管理器创建和管理存储过
程
? 掌握使用 T-SQL语句创建和管理存储过
程
2
存储过程的概念
? 将一些固定的操作集中起来由 SQL服务器来完
成，实现某个特定任务，这就是存储过程。类
似于 DOS下的批处理。存储过程是 SQL语句和
可选控制流程语句的预编译集合。是一种封装
重复任务操作的方法，以一个名称存储，作为
一个单元处理。
? 存储过程属于服务器方软件，可立即访问数据
库
3
存储过程的概念
? 存储过程存储在数据库内，可由应用程
序通过一个调用来执行，而且充许用户
声明变量 。同时,存储过程可以接收和
输出参数、返回执行存储过程的状态值，
也可以嵌套调用。
? 注意：存储过程与函数不同，因为存储
过程并不返回取代其名称的值，也不能
直接在表达式中使用。
4
存储过程的分类
? 在 SQL Server 中存储过程分为两类, 系统提供的存储
过程和用户自定义存储过程 。 系统过程主要存储在
master 数据库中, 并以 sp_为前缀, 并且系统存储过程
主要是从系统表中获取信息, 从而为系统管理员管理
SQL Server 提供支持 。 通过系统存储过程, SQL
Server 中的许多管理性或信息性的活动, 如了解数据
库对象, 数据库信息都可以被顺利有效地完成 。 尽管这
些系统存储过程被放在 master 数据库中, 但是仍可以
在其它数据库中对其进行调用, 在调用时不必在存储过
程名前加上数据库名, 而且当创建一个新数据库时, 一
些系统存储过程会在新数据库中被自动创建 。 用户自定
义存储过程是由用户创建并能完成某一特定功能 （ 如查
询用户所需数据信息 ） 的存储过程 。
5
存储过程的运行过程
? 存储过程时存放在 SQL Server中的特别快的数据库对象，
当首次运行存储过程时，它按以下方式进行,
? 1、该过程被划分成部件片断。
? 2、检查引用数据库中其它对象（表、视图等）的部件，确
保引用的对象是存在的，这也被称为分解。
? 3、一旦分解完成，该过程的名字将存放倒 sysobjects表中，
而创建存储过程的代码存放在 syscomments表中
? 4、然后编译，并且，编译过程中将创建如何运行查询的蓝
本。该蓝本通称称为常规计划或查询树，查询树存放在
sysProcedures表中。
? 5、存储过程首次运行时，读出查询计划并完全编译成过程
计划，然后运行。这样，节约了每次运行存成过程的语法
检查、分解和编译查询树的时间。
6
存储过程的优点
? 当利用 SQL Server 创建一个应用程序时, T-SQL 是一种主
要的编程语言 。 若运用 T-SQL 来进行编程有两种方法, 其
一是在本地存储 T-SQL程序并创建应用程序, 向 SQL
Server 发送命令来对结果进行处理 。 其二是可以把部分用
T-SQL 编写的程序作为存储过程存储在 SQL Server 中, 并
创建应用程序来调用存储过程, 对数据结果进行处理 。 存储
过程能够通过接收参数向调用者返回结果集, 结果集的格式
由调用者确定 ； 返回状态值给调用者, 指明调用是成功或是
失败 ； 包括针对数据库的操作语句, 并且可以在一个存储过
程中调用另一存储过程 。
? 我们通常更偏爱于使用第二种方法, 即在 SQL Server 中使
用存储过程, 而不是在客户计算机上调用 T-SQL 编写的一
段程序原因在于存储过程具有以下优点,
7
存储过程的优点
? 可用存储过程封装事务规则。一旦封装
完成，这些规则就可用于多个应用，从
而有一个一致的数据接口，因此，若需
改变过程的功能，只需在一个地方对其
进行修改，而不必对每个应用都进行修
改。
8
存储过程的优点
? 存储过程允许标准组件式编程, 存储过
程在被创建以后, 可以在程序中被多次
调用而不必重新编写该存储过程的 SQL
语句 ； 而且数据库专业人员可随时对存
储过程进行修改, 但对应用程序源代码
毫无影响, 因为应用程序源代码只包含
存储过程的调用语句, 从而极大地提高
了程序的可移植性 。
9
存储过程的优点
? 存储过程能够实现较快的执行速度, 如
果某一操作包含大量的 T-SQL 代码或分
别被多次执行, 那么存储过程要比批处
理的执行速度快很多 。 因为存储过程是
预编译的, 在首次运行一个存储过程时,
查询优化器对其进行分析优化, 并给出
最终被存在系统表中的执行计划 ； 而批
处理的 T-SQL语句在每次运行时都要进行
编译和优化, 因此速度相对要慢一些。
10
存储过程的优点
? 存储过程能够减少网络流量, 对于同一
个针对数据数据库对象的操作 （ 如查询
修改 ）,如果这一操作所涉及到的 T-
SQL 语句被组织成一存储过程, 那么当
在客户计算机上调用该存储过程时, 网
络中传送的只是该调用语句 ； 否则将是
多条 SQL 语句, 从而大大增加了网络流
量, 降低网络负载 。
11
存储过程的优点
? 存储过程可被作为一种安全机制来充分
利用, 系统管理员通过对执行某一存储
过程的权限进行限制, 从而能够实现对
相应的数据访问权限的限制, 避免非授
权用户对数据的访问, 保证数据的安全 。
12
创建存储过程的规则
? 几乎任何可以写成批处理的 T-SQL代码
都可用于创建存储过程，但是在设计存
储过程时，需要遵循下列规则,
? 名字必须符合 SQL Server命名规则。
? 引用的对象必须在创建存储过程前就存
在
? 不能在单个存储过程中创建后去掉或再
创建同名的对象。
? 存储过程最后能有 255各参数。
13
创建存储过程的规则
? 再自己的存储过程中可以引用临时表，局部临
时表再过程结束时将会消失。
? 再存储过程中不能有如下的 SQL创建语句：
Create Default,Create Procedure,Create
Rule,Create Trigger, Create View。
? 可在过程中嵌套过程。
? 创建存储过程的文本不能超过 64K字节，以为
SQL存放再 syscomments表中。
? 若再存储过程中使用了 Select *,而底层表中加
入了新的列，新的列再过程运行时无法显示。
14
使用企业管理器管理存储过程
? 创建存储过程
? 查看和修改存储过程
? 删除存储过程
15
使用创建存储过程向导创建
? 使用创建存储过程向导创建存储过程的步骤如
下,
? 1、打开企业管理器，在树状目录中展开服务器
节点。
? 2、在工具栏中选择命令“工具－向导”，打开
“选择向导”对话框。
? 3、在“选择向导”对话框中选择节点“数据库
－创建存储过程向导”，按“确定”按钮。
? 4、在对话框“欢迎使用创建存储过程向导”中
单击“下一步”
16
使用创建存储过程向导创建
? 5,在对话框“选择数据库”中选择存放存储过程的数
据库名，按“下一步”按钮。
? 6、在对话框“选择存储过程”中选择存储过程将针对
哪些数据表做哪些操作。单击“下一步”按钮。
? 7、在对话框“正在完成创建存储过程向导”中，可以
看到正在创建的存储过程的名称和描述。
? 8、若需要对某个存储过程进行设置，可以在该对话框
内选定该存储过程，然后按“编辑”按钮，打开“编辑
存储过程属性”对话框，在该对话框内可以完成对该存
储过程的设置。
? 9、逐一完成对每个存储过程的设置以后，返回到对话
框“正在完成创建存储过程向导”，按“完成按钮”。
即可结束。
17
图 1 欢迎使用创建存储过程向导对话框
18
图 2 选择数据库对话框
19
图 3 选择数据库对象对话框
20
图 4 完成创建存储过程向导对话框
21
图 5 编辑存储过程属性对话框
22
图 6 编辑存储过程 SQL对话框
23
使用企业管理器创建存储过程
? 在企业管理器中创建一个存储过程步骤如下,
? 1 启动企业管理器, 登录到要使用的服务器
? 2 选择要创建存储过程的数据库, 在左窗格中单
击存储过程 文件夹，此时在右窗格中显示该数
据库的所有存储过程 。
? 3 右击存储过程文件夹，在弹出菜单中选择, 新
建存储过程, 命令
? 4 在, 新建存储过程属性, 对话框中指定存储过
程的名字。在文本框中输入创建存储过程的语句。
? 5 单击, 检查语法, 按钮,检查语法是否正确
? 6 单击, 确定, 保存存储过程，并关闭属性对话
框。
24
图 7 选择新建存储过程对话框（ 1）
25
图 8 选择新建存储过程对话框（ 2）
26
图 9 新建存储过程对话框
27
图 10 设置权限对话框
28
查看和修改存储过程
? 查看存储过程,存储过程被创建之后，
它的名字就存储在系统表 sysobjects中，
它的源代码存放在系统表 syscomments中。
可以使用使用企业管理器或系统存储过
程来查看用户创建的存储过程。
29
查看和修改存储过程
? 在企业管理器中查看和修改存储过程及其定义
文本，步骤如下,
? 1 打开企业管理器, 在树状目录中展开存储过
程所在的数据库节点。
? 2 单击,存储过程,节点，在右窗格中显示该数
据库的所有存储过程 。 选择希望查看或修改的存
储过程，并单击鼠标右键，在弹出菜单中选择命
令, 属性,
? 3 在, 属性, 对话框中可以查看存储过程的名
称、所有者、创建日期和存储过程的定义文本。
30
查看和修改存储过程
? 在, 属性, 对话框中还可以对该存储过
程做如下的修改,
? 修改存储过程定义文本。
? 修改存储过程的权限：单击, 权限, 按
钮，对存储过程的权限进行修改。
? 4,单击, 确定, 按钮，并关闭属性对话
框。
31
查看存储过程的相关性
? 如果希望查看存储过程的相关性，可以
按如下步骤,
? 1、打开企业管理器，在树状目录中展开
存储过程所在的数据库节点。
? 2、单击“存储过程”节点，在右边的内
容窗口中选择希望查看相关性的存储过
程，并单击鼠标右键，在弹出菜单中选
择命令“所有任务－显示相关性”
32
重命名存储过程
? 可以按如下步骤,
? 1、打开企业管理器，在树状目录中展开
存储过程所在的数据库节点。
? 2、单击“存储过程”节点，在右边的内
容窗口中选择希望重命名的存储过程，
并单击鼠标右键，在弹出菜单中选择命
令“重命名”
? 3、输入存储过程的新名称，并按回车键。
? 4、确认新名称。
33
删除存储过程
? 对于不再需要的存储过程，可以使用企业
管理器将其删除，步骤如下,
? 1、打开企业管理器，在树状目录中展开
存储过程所在的数据库节点。
? 2、单击“存储过程”节点，在右边的内
容窗口中选择希望删除的存储过程，并单
击鼠标右键，在弹出菜单中选择命令“删
除”
? 3、单击“全部除去”按钮，删除选定的
存储过程。
34
使用 T-SQL管理存储过程
? 创建存储过程
? 执行存储过程
? 查看存储过程
? 修改存储过程
? 删除存储过程
? 创建一组存储过程
? 在存储过程中使用参数
? With Recompile选项
35
使用 T-SQL创建存储过程
? 创建存储过程时, 需要确定存储过程的三个组成
部分,
? 1,所有的输入参数以及传给调用者的输出参数 。
? 2,被执行的针对数据库的操作语句, 包括调用
其它存储过程的语句 。
? 3,返回给调用者的状态值, 以指明调用是成功
还是失败 。
36
使用 T-SQL创建存储过程
?使用 CREATE PROCEDURE创建存储过程语法如下,
?CREATE PROC[EDURE] procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]
][,...n]
[WITH
RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
AS sql_statement [,..n ]
?其中, 各参数的含义如下所示,
37
使用 T-SQL创建存储过程
? procedure_name,用于指定要创建的存
储过程的名称。
? number,该参数是可选的整数，它用来
对同名的存储过程分组，以便用一条
DROP PROCEDURE 语句即可将同组的
过程一起除去。
? @parameter,过程中的参数。在
CREATE PROCEDURE 语句中可以声
明一个或多个参数。
38
使用 T-SQL创建存储过程
? data_type,用于指定参数的数据类型。
? VARYING,用于指定作为输出 OUTPUT
参数支持的结果集。
? Default,用于指定参数的默认值。
? OUTPUT,表明该参数是一个返回参数
39
使用 T-SQL创建存储过程
? RECOMPILE,表明 SQL Server 不会保存该
存储过程的执行计划 。
? ENCRYPTION, 表示 SQL Server 加密了
syscomments 表，该表的 text字段是包含
CREATE PROCEDURE 语句的存储过程文本。
? FOR REPLICATION,用于指定不能在订阅服
务器上执行为复制创建的存储过程。
? AS,用于指定该存储过程要执行的操作。
? sql_statement,是存储过程中要包含的任意数
目和类型的 Transact-SQL 语句。
40
? 例 1：在数据库 company中创建一个存储过程，用于返回
项目标的不小于 5000的项目情况，并按项目标的的降序
进行排列。
? Use company
? --如果存在同名的存储过程，先删除
? If exists(select name from sysobjects where
name='pinfo50000' and type = 'P')
? Drop procedure pinfo50000
? Go
? Create procedure pinfo50000
? As select * from project where 项目标的 >=50000
? Order by 项目标的 DESC
? Go
? Exec pinfo50000
? go
41
执行存储过程
?直接执行存储过程可以使用 EXECUTE命令
来执行, 其语法形式如下,
?[[EXEC[UTE]] {[@return_status=]
?procedure_name[;number]}
?[[@parameter=]{value|@variable[OUTPU
T]|[DEFAULT]}] [,...n]
[ WITH RECOMPILE ]
?其中，各参数的意义如下,
42
? 如果执行存储过程的语句是批中的第一个
语句，可以省略 execute关键字。
? @return_status为整型局部变量，用于保
存存储过程的返回值； procedure_name
指定执行的存储过程的名称； [;number]
用来指定该存储过程与其它同名存储过程
同组时的标识号。
? @parameter,在创建过程时定义的过程
参数。调用者向存储过程所传递的参数值
由 value参数或 @variable变量提供，或者
使用 default关键字指定使用该参数的默认
值。 output参数说明指定参数为返回参数。
43
? 注意：如果按
,@parameter=value|variable”的形式为
存储过程提供参数，可以不考虑创建存
储过程时的参数顺序，但是必须以这种
方式提供该存储过程的全部参数。如果
不以这种方式提供参数，则必须按照创
建存储过程时参数的顺序提供参数。
? With Recompile指定在实行存储过程时
重新编译执行计划。
44
? 例：在数据库 company中创建一个存储过程，
用于查询某员工所负责项目的平均项目标的。
该存储过程包含一个输入参数，一个输出参数
和一个返回值。
? Use company
? --如果在数据库中已经存在存储过程
GetAvgPbiaodi,先将其删除
? If exists (select name from sysobjects
? Where name = 'GetAvgPbiaodi' and type='P')
? Drop procedure GetAvgPbiaodi
? go
45
? Create procedure GetAvgPbiaodi
? @name varchar(10),@avgpbiaodi int output
? As Declare @ErrorSave int
? Set @ErrorSave = 0
? Select @avgpbiaodi=AVG(项目标的 )
? From project as p INNER JOIN pmanager as pm
on p.负责人 ID = pm.负责人 ID
? Where pm.姓名 = @name
? If (@@Error <>0)
? Set @ErrorSave = @@Error
? Return @ErrorSave
? go
46
? --声明变量，用于保存返回值和输出参数
? Declare @returnvalue int,@avg int
? EXEC @returnvalue=GetAvgPbiaodi '李
中新 ',@avg OUTPUT
? Print '执行的结果,'
? --函数用转换数据类型
? Print '返回值＝ '+cast(@returnvalue as
char(2))
? Print '李中新复杂项目的平均标的为：
'+cast(@avg as char(10))
? go
47
? 例：本例将例 1中的存储过程返回的结果集存入
一个新建的表 importProject中。
? Create table ImportmantProject
? ( 项目编号 int,
? 项目名称 char (10),
? 项目标的 int,
? 客户编号 int,
? 负责人编号 [int] NULL,
? 负责人 ID int )
? Insert into importmantProject
? Exec Pinfo50000
? Go
48
查看存储过程
? 查看存储过程的定义
? 查看存储过程的相关性
? 查看存储过程的所有者、类型、创建日
期和参数
49
? 查看存储过程 的定义：用系统存储过程
sp_helptext可以用于显示存储过程的源代码,语
法为,
? sp_helptext [[@objname=] name]
? 参数 name为要查看的存储过程的名称 。
? 例如,exec sp_helptext GetAvgPbiaodi
? 注意：如果存储过程用带有 ENCRYPTION
选项的方式创建，则无法用 sp_helptext（ 或企业
管理器）查看用于创建存储过程的文本。
查看存储过程的定义
50
查看存储过程的相关性
? 系统存储过程 sp_depends是用于显示和
存储过程相关的数据库对象, 其语法为,
? sp_depends [@objname=]?object?
? 其中, 参数 object为要查看依赖关系的存
储过程的名称 。
? 例如,exec sp_depends GetAvgPbiaodi
51
查看存储过程的所有者等信息
? 系统存储过程 sp_help是用于显示存储过
程的参数及其数据类型
? sp_help [[@objname=] name]
? 其中, 参数 name为要查看的存储过程的
名称 。
? 例,exec sp_help GetAvgPbiaodi
52
? 修改存储过程的名称可以使用系统存储
过程 sp_rename,其语法形式如下,
? sp_rename 原存储过程名称, 新存储过
程名称
? 例,exec sp_rename GetAvgPbiaodi,
?项目平均标的过程 ’
重命名存储过程
53
? 存储过程可以根据用户的要求或者基表
定义的改变而改变 。 使用 ALTER
PROCEDURE语句可以更改先前通过执行
CREATE PROCEDURE 语句创建的过程, 但
不会更改权限, 也不影响相关的存储过程
或触发器 。
修改存储过程
54
修改存储过程
? 其语法形式如下,
? ALTER PROC[EDURE] procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]
][,...n]
[WITH
RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIO
N}]
[FORREPLICATION]
AS sql_statement [,..n ]
? 其中, 格式与创建存储过程的语法完全相同,
只是 Create变为 Alter
55
修改存储过程
? 注意：只有存储过程的创建者、
db_owner和 db_ddladmin的成员才可以
修改存储过程。在 Create Procedure语句
中使用的选项也必须在 Alter Procedure
语句中使用。
56
? 例：修改例 1中创建的存储过程 Pinfo50000,使这
个存储过程值输出字段“项目名称”和“项目
标的”。
? Alter Procedure Pinfo50000
? As select 项目名称，项目标的
? From Project
? Where 项目标的 >=50000
? Order by 项目标的 DESC
? Go
? Exec Pinfo50000
? go
57
删除存储过程
? 删除存储过程可以使用 DROP命令, DROP命
令可以将一个或者多个存储过程或者存储过程
组从当前数据库中删除, 其语法形式如下,
? drop procedure {procedure} [,…n]
? 其中, procedure指定要删除的存储过程或存
储过程组的名称 。
? 例如,drop Procedure Pinfo50000
? 注意：可以选择是否指定过程所有者名称, 但
不能指定服务器名称和数据库名称 。
? 不能除去组内的个别过程, 必须除去整个过程
组；如果该过程组在其它的应用程序或存储过
程中被调用, 系统会显示出错信息 。
58
创建一组过程
? 在,;number”选项中，通过指定分号和
数字，可创建一组存储过程。一组存储
过程通常是为同一应用程序使用的，维
护工作变得向对简单，因为一个特定应
用程序使用的所有过程引用的是同一个
组。
59
? 例,Create Proc group_sp;1
? As select * from authors
? Go
? Create Proc group_sp;2
? As select au_lname from authors
? Go
? Create Proc group_sp;3
? As select distinct city from authors
? Go
? 这个批处理语句将创建一个称为 group_sp的单一
过程，它包括 3个不同过程作为它的一部分。要引
用单个过程，只要执行它们并带有,;number”作
为名字的一部分。如,exec group_sp;3
60
? 在删除过程组时，只要删除过程的名字，
所用组的部件也将被删除。比如,
? Drop procedure dbo.group_sp
? 注意：无法删除组中的某一个过程。
61
在存储过程中使用参数
? 例：下面的存储过程有 5个传入参数，求其平均
值，而后将平均值赋给 output。
? Create Procedure scores
? @score1 smallint,@score2 smallint,
? @score3 smallint,@score4 smallint,
? @score5 smallint,@myAvg smallint Output
? As select
@myAvg=(@score1+@score2+@score3+@score4
+@score5)/5
62
? 要扩展 myAvg的值，首先要定义一个变
量，然后才能运行该程序。
? Declare @AvgScore smallint
? Exec scores 10,9,8,8,10,@AvgScore Output
? Select 'The Average Score is:',@AvgScore
? go
63
在存储过程中使用参数
? 在将值传递给存储过程时，可以按位置顺序传入 (称为按
位置传入 )，也可按引用转参数，即用参数名称＝值的方
式传入参数。当按引用传入参数时，可按任何次序传入
参数。
? 例,declare @Avgscore smallint
? Exec scores
? @score1=10,@score3=9,@score2=8,
? @score4=8,@score5=10,@myAvg=@AvgScore Output
? Select 'The Average score is',@AvgScore
? Go 注意：如果开始时已经按引用传入，则整个过程调用
必须按引用传入，不能在存储过程调用中在按位置传入
和按参数传入二者之间掉换。
64
? 也可用 return关键字来将信息返回给调
用过程。这将会直接将一个变量返回给
调用过程，而无需要求有存储过程定义
及调用过程都要有的 output声明。
在存储过程中使用参数
65
在存储过程中使用参数
? 例,create proc MyReturn
? @t1 smallint,@t2 smallint,@retval smallint
? As select @retval=@t1+@t2
? Return @retval
? 创建该过程后，可输入以下内容对其调用,
? Declare @myReturnValue smallint
? Exec @myReturnValue=myReturn 9,9,0
? Select 'The return value is ',@myReturnValue
66
With Recompile选项
? 可以在 Create Procedure语句或 Exec procedure
语句后增加 With Recompile语句，其位置将影
响存储过程的处理和运行方式。
? 1、用 With Recompile创建过程：在 Create
Procedure中使用 With Recompile后，执行计划
将不被存入 Catch。 每次运行时都要重新编译
整个过程，这与标准查询的处理方式很相似，
这种方式在存储过程带有使常规执行效率较低
的参数时是很有帮助的，通过每次重新编译，
过程可针对新参数进行优化执行。
67
With Recompile选项
? 2,在 Exec procedure中使用的 With
Recompile选项：也可以在 Exec
Procedure中使用 With Recompile子句，
这将为单词执行编译存储过程，并将新
计划放入 Cache中，以供随后的 Exec
Procedure命令使用。
? 3、强制重新编译所有存储过程：可用
sp_recompile存储过程来强制所有引用特
定表的存储过程和触发器在再次运行时
被重新编译。
68
With Recompile选项
? 将存储过程设置为 SQL 启动时自动执行：
可以让存储过程在 SQL Server启动时自
动执行。
第十二章 管理存储过程
? 了解存储过程的基本概念
? 掌握使用企业管理器创建和管理存储过
程
? 掌握使用 T-SQL语句创建和管理存储过
程
2
存储过程的概念
? 将一些固定的操作集中起来由 SQL服务器来完
成，实现某个特定任务，这就是存储过程。类
似于 DOS下的批处理。存储过程是 SQL语句和
可选控制流程语句的预编译集合。是一种封装
重复任务操作的方法，以一个名称存储，作为
一个单元处理。
? 存储过程属于服务器方软件，可立即访问数据
库
3
存储过程的概念
? 存储过程存储在数据库内，可由应用程
序通过一个调用来执行，而且充许用户
声明变量 。同时,存储过程可以接收和
输出参数、返回执行存储过程的状态值，
也可以嵌套调用。
? 注意：存储过程与函数不同，因为存储
过程并不返回取代其名称的值，也不能
直接在表达式中使用。
4
存储过程的分类
? 在 SQL Server 中存储过程分为两类, 系统提供的存储
过程和用户自定义存储过程 。 系统过程主要存储在
master 数据库中, 并以 sp_为前缀, 并且系统存储过程
主要是从系统表中获取信息, 从而为系统管理员管理
SQL Server 提供支持 。 通过系统存储过程, SQL
Server 中的许多管理性或信息性的活动, 如了解数据
库对象, 数据库信息都可以被顺利有效地完成 。 尽管这
些系统存储过程被放在 master 数据库中, 但是仍可以
在其它数据库中对其进行调用, 在调用时不必在存储过
程名前加上数据库名, 而且当创建一个新数据库时, 一
些系统存储过程会在新数据库中被自动创建 。 用户自定
义存储过程是由用户创建并能完成某一特定功能 （ 如查
询用户所需数据信息 ） 的存储过程 。
5
存储过程的运行过程
? 存储过程时存放在 SQL Server中的特别快的数据库对象，
当首次运行存储过程时，它按以下方式进行,
? 1、该过程被划分成部件片断。
? 2、检查引用数据库中其它对象（表、视图等）的部件，确
保引用的对象是存在的，这也被称为分解。
? 3、一旦分解完成，该过程的名字将存放倒 sysobjects表中，
而创建存储过程的代码存放在 syscomments表中
? 4、然后编译，并且，编译过程中将创建如何运行查询的蓝
本。该蓝本通称称为常规计划或查询树，查询树存放在
sysProcedures表中。
? 5、存储过程首次运行时，读出查询计划并完全编译成过程
计划，然后运行。这样，节约了每次运行存成过程的语法
检查、分解和编译查询树的时间。
6
存储过程的优点
? 当利用 SQL Server 创建一个应用程序时, T-SQL 是一种主
要的编程语言 。 若运用 T-SQL 来进行编程有两种方法, 其
一是在本地存储 T-SQL程序并创建应用程序, 向 SQL
Server 发送命令来对结果进行处理 。 其二是可以把部分用
T-SQL 编写的程序作为存储过程存储在 SQL Server 中, 并
创建应用程序来调用存储过程, 对数据结果进行处理 。 存储
过程能够通过接收参数向调用者返回结果集, 结果集的格式
由调用者确定 ； 返回状态值给调用者, 指明调用是成功或是
失败 ； 包括针对数据库的操作语句, 并且可以在一个存储过
程中调用另一存储过程 。
? 我们通常更偏爱于使用第二种方法, 即在 SQL Server 中使
用存储过程, 而不是在客户计算机上调用 T-SQL 编写的一
段程序原因在于存储过程具有以下优点,
7
存储过程的优点
? 可用存储过程封装事务规则。一旦封装
完成，这些规则就可用于多个应用，从
而有一个一致的数据接口，因此，若需
改变过程的功能，只需在一个地方对其
进行修改，而不必对每个应用都进行修
改。
8
存储过程的优点
? 存储过程允许标准组件式编程, 存储过
程在被创建以后, 可以在程序中被多次
调用而不必重新编写该存储过程的 SQL
语句 ； 而且数据库专业人员可随时对存
储过程进行修改, 但对应用程序源代码
毫无影响, 因为应用程序源代码只包含
存储过程的调用语句, 从而极大地提高
了程序的可移植性 。
9
存储过程的优点
? 存储过程能够实现较快的执行速度, 如
果某一操作包含大量的 T-SQL 代码或分
别被多次执行, 那么存储过程要比批处
理的执行速度快很多 。 因为存储过程是
预编译的, 在首次运行一个存储过程时,
查询优化器对其进行分析优化, 并给出
最终被存在系统表中的执行计划 ； 而批
处理的 T-SQL语句在每次运行时都要进行
编译和优化, 因此速度相对要慢一些。
10
存储过程的优点
? 存储过程能够减少网络流量, 对于同一
个针对数据数据库对象的操作 （ 如查询
修改 ）,如果这一操作所涉及到的 T-
SQL 语句被组织成一存储过程, 那么当
在客户计算机上调用该存储过程时, 网
络中传送的只是该调用语句 ； 否则将是
多条 SQL 语句, 从而大大增加了网络流
量, 降低网络负载 。
11
存储过程的优点
? 存储过程可被作为一种安全机制来充分
利用, 系统管理员通过对执行某一存储
过程的权限进行限制, 从而能够实现对
相应的数据访问权限的限制, 避免非授
权用户对数据的访问, 保证数据的安全 。
12
创建存储过程的规则
? 几乎任何可以写成批处理的 T-SQL代码
都可用于创建存储过程，但是在设计存
储过程时，需要遵循下列规则,
? 名字必须符合 SQL Server命名规则。
? 引用的对象必须在创建存储过程前就存
在
? 不能在单个存储过程中创建后去掉或再
创建同名的对象。
? 存储过程最后能有 255各参数。
13
创建存储过程的规则
? 再自己的存储过程中可以引用临时表，局部临
时表再过程结束时将会消失。
? 再存储过程中不能有如下的 SQL创建语句：
Create Default,Create Procedure,Create
Rule,Create Trigger, Create View。
? 可在过程中嵌套过程。
? 创建存储过程的文本不能超过 64K字节，以为
SQL存放再 syscomments表中。
? 若再存储过程中使用了 Select *,而底层表中加
入了新的列，新的列再过程运行时无法显示。
14
使用企业管理器管理存储过程
? 创建存储过程
? 查看和修改存储过程
? 删除存储过程
15
使用创建存储过程向导创建
? 使用创建存储过程向导创建存储过程的步骤如
下,
? 1、打开企业管理器，在树状目录中展开服务器
节点。
? 2、在工具栏中选择命令“工具－向导”，打开
“选择向导”对话框。
? 3、在“选择向导”对话框中选择节点“数据库
－创建存储过程向导”，按“确定”按钮。
? 4、在对话框“欢迎使用创建存储过程向导”中
单击“下一步”
16
使用创建存储过程向导创建
? 5,在对话框“选择数据库”中选择存放存储过程的数
据库名，按“下一步”按钮。
? 6、在对话框“选择存储过程”中选择存储过程将针对
哪些数据表做哪些操作。单击“下一步”按钮。
? 7、在对话框“正在完成创建存储过程向导”中，可以
看到正在创建的存储过程的名称和描述。
? 8、若需要对某个存储过程进行设置，可以在该对话框
内选定该存储过程，然后按“编辑”按钮，打开“编辑
存储过程属性”对话框，在该对话框内可以完成对该存
储过程的设置。
? 9、逐一完成对每个存储过程的设置以后，返回到对话
框“正在完成创建存储过程向导”，按“完成按钮”。
即可结束。
17
图 1 欢迎使用创建存储过程向导对话框
18
图 2 选择数据库对话框
19
图 3 选择数据库对象对话框
20
图 4 完成创建存储过程向导对话框
21
图 5 编辑存储过程属性对话框
22
图 6 编辑存储过程 SQL对话框
23
使用企业管理器创建存储过程
? 在企业管理器中创建一个存储过程步骤如下,
? 1 启动企业管理器, 登录到要使用的服务器
? 2 选择要创建存储过程的数据库, 在左窗格中单
击存储过程 文件夹，此时在右窗格中显示该数
据库的所有存储过程 。
? 3 右击存储过程文件夹，在弹出菜单中选择, 新
建存储过程, 命令
? 4 在, 新建存储过程属性, 对话框中指定存储过
程的名字。在文本框中输入创建存储过程的语句。
? 5 单击, 检查语法, 按钮,检查语法是否正确
? 6 单击, 确定, 保存存储过程，并关闭属性对话
框。
24
图 7 选择新建存储过程对话框（ 1）
25
图 8 选择新建存储过程对话框（ 2）
26
图 9 新建存储过程对话框
27
图 10 设置权限对话框
28
查看和修改存储过程
? 查看存储过程,存储过程被创建之后，
它的名字就存储在系统表 sysobjects中，
它的源代码存放在系统表 syscomments中。
可以使用使用企业管理器或系统存储过
程来查看用户创建的存储过程。
29
查看和修改存储过程
? 在企业管理器中查看和修改存储过程及其定义
文本，步骤如下,
? 1 打开企业管理器, 在树状目录中展开存储过
程所在的数据库节点。
? 2 单击,存储过程,节点，在右窗格中显示该数
据库的所有存储过程 。 选择希望查看或修改的存
储过程，并单击鼠标右键，在弹出菜单中选择命
令, 属性,
? 3 在, 属性, 对话框中可以查看存储过程的名
称、所有者、创建日期和存储过程的定义文本。
30
查看和修改存储过程
? 在, 属性, 对话框中还可以对该存储过
程做如下的修改,
? 修改存储过程定义文本。
? 修改存储过程的权限：单击, 权限, 按
钮，对存储过程的权限进行修改。
? 4,单击, 确定, 按钮，并关闭属性对话
框。
31
查看存储过程的相关性
? 如果希望查看存储过程的相关性，可以
按如下步骤,
? 1、打开企业管理器，在树状目录中展开
存储过程所在的数据库节点。
? 2、单击“存储过程”节点，在右边的内
容窗口中选择希望查看相关性的存储过
程，并单击鼠标右键，在弹出菜单中选
择命令“所有任务－显示相关性”
32
重命名存储过程
? 可以按如下步骤,
? 1、打开企业管理器，在树状目录中展开
存储过程所在的数据库节点。
? 2、单击“存储过程”节点，在右边的内
容窗口中选择希望重命名的存储过程，
并单击鼠标右键，在弹出菜单中选择命
令“重命名”
? 3、输入存储过程的新名称，并按回车键。
? 4、确认新名称。
33
删除存储过程
? 对于不再需要的存储过程，可以使用企业
管理器将其删除，步骤如下,
? 1、打开企业管理器，在树状目录中展开
存储过程所在的数据库节点。
? 2、单击“存储过程”节点，在右边的内
容窗口中选择希望删除的存储过程，并单
击鼠标右键，在弹出菜单中选择命令“删
除”
? 3、单击“全部除去”按钮，删除选定的
存储过程。
34
使用 T-SQL管理存储过程
? 创建存储过程
? 执行存储过程
? 查看存储过程
? 修改存储过程
? 删除存储过程
? 创建一组存储过程
? 在存储过程中使用参数
? With Recompile选项
35
使用 T-SQL创建存储过程
? 创建存储过程时, 需要确定存储过程的三个组成
部分,
? 1,所有的输入参数以及传给调用者的输出参数 。
? 2,被执行的针对数据库的操作语句, 包括调用
其它存储过程的语句 。
? 3,返回给调用者的状态值, 以指明调用是成功
还是失败 。
36
使用 T-SQL创建存储过程
?使用 CREATE PROCEDURE创建存储过程语法如下,
?CREATE PROC[EDURE] procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]
][,...n]
[WITH
RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FORREPLICATION]
AS sql_statement [,..n ]
?其中, 各参数的含义如下所示,
37
使用 T-SQL创建存储过程
? procedure_name,用于指定要创建的存
储过程的名称。
? number,该参数是可选的整数，它用来
对同名的存储过程分组，以便用一条
DROP PROCEDURE 语句即可将同组的
过程一起除去。
? @parameter,过程中的参数。在
CREATE PROCEDURE 语句中可以声
明一个或多个参数。
38
使用 T-SQL创建存储过程
? data_type,用于指定参数的数据类型。
? VARYING,用于指定作为输出 OUTPUT
参数支持的结果集。
? Default,用于指定参数的默认值。
? OUTPUT,表明该参数是一个返回参数
39
使用 T-SQL创建存储过程
? RECOMPILE,表明 SQL Server 不会保存该
存储过程的执行计划 。
? ENCRYPTION, 表示 SQL Server 加密了
syscomments 表，该表的 text字段是包含
CREATE PROCEDURE 语句的存储过程文本。
? FOR REPLICATION,用于指定不能在订阅服
务器上执行为复制创建的存储过程。
? AS,用于指定该存储过程要执行的操作。
? sql_statement,是存储过程中要包含的任意数
目和类型的 Transact-SQL 语句。
40
? 例 1：在数据库 company中创建一个存储过程，用于返回
项目标的不小于 5000的项目情况，并按项目标的的降序
进行排列。
? Use company
? --如果存在同名的存储过程，先删除
? If exists(select name from sysobjects where
name='pinfo50000' and type = 'P')
? Drop procedure pinfo50000
? Go
? Create procedure pinfo50000
? As select * from project where 项目标的 >=50000
? Order by 项目标的 DESC
? Go
? Exec pinfo50000
? go
41
执行存储过程
?直接执行存储过程可以使用 EXECUTE命令
来执行, 其语法形式如下,
?[[EXEC[UTE]] {[@return_status=]
?procedure_name[;number]}
?[[@parameter=]{value|@variable[OUTPU
T]|[DEFAULT]}] [,...n]
[ WITH RECOMPILE ]
?其中，各参数的意义如下,
42
? 如果执行存储过程的语句是批中的第一个
语句，可以省略 execute关键字。
? @return_status为整型局部变量，用于保
存存储过程的返回值； procedure_name
指定执行的存储过程的名称； [;number]
用来指定该存储过程与其它同名存储过程
同组时的标识号。
? @parameter,在创建过程时定义的过程
参数。调用者向存储过程所传递的参数值
由 value参数或 @variable变量提供，或者
使用 default关键字指定使用该参数的默认
值。 output参数说明指定参数为返回参数。
43
? 注意：如果按
,@parameter=value|variable”的形式为
存储过程提供参数，可以不考虑创建存
储过程时的参数顺序，但是必须以这种
方式提供该存储过程的全部参数。如果
不以这种方式提供参数，则必须按照创
建存储过程时参数的顺序提供参数。
? With Recompile指定在实行存储过程时
重新编译执行计划。
44
? 例：在数据库 company中创建一个存储过程，
用于查询某员工所负责项目的平均项目标的。
该存储过程包含一个输入参数，一个输出参数
和一个返回值。
? Use company
? --如果在数据库中已经存在存储过程
GetAvgPbiaodi,先将其删除
? If exists (select name from sysobjects
? Where name = 'GetAvgPbiaodi' and type='P')
? Drop procedure GetAvgPbiaodi
? go
45
? Create procedure GetAvgPbiaodi
? @name varchar(10),@avgpbiaodi int output
? As Declare @ErrorSave int
? Set @ErrorSave = 0
? Select @avgpbiaodi=AVG(项目标的 )
? From project as p INNER JOIN pmanager as pm
on p.负责人 ID = pm.负责人 ID
? Where pm.姓名 = @name
? If (@@Error <>0)
? Set @ErrorSave = @@Error
? Return @ErrorSave
? go
46
? --声明变量，用于保存返回值和输出参数
? Declare @returnvalue int,@avg int
? EXEC @returnvalue=GetAvgPbiaodi '李
中新 ',@avg OUTPUT
? Print '执行的结果,'
? --函数用转换数据类型
? Print '返回值＝ '+cast(@returnvalue as
char(2))
? Print '李中新复杂项目的平均标的为：
'+cast(@avg as char(10))
? go
47
? 例：本例将例 1中的存储过程返回的结果集存入
一个新建的表 importProject中。
? Create table ImportmantProject
? ( 项目编号 int,
? 项目名称 char (10),
? 项目标的 int,
? 客户编号 int,
? 负责人编号 [int] NULL,
? 负责人 ID int )
? Insert into importmantProject
? Exec Pinfo50000
? Go
48
查看存储过程
? 查看存储过程的定义
? 查看存储过程的相关性
? 查看存储过程的所有者、类型、创建日
期和参数
49
? 查看存储过程 的定义：用系统存储过程
sp_helptext可以用于显示存储过程的源代码,语
法为,
? sp_helptext [[@objname=] name]
? 参数 name为要查看的存储过程的名称 。
? 例如,exec sp_helptext GetAvgPbiaodi
? 注意：如果存储过程用带有 ENCRYPTION
选项的方式创建，则无法用 sp_helptext（ 或企业
管理器）查看用于创建存储过程的文本。
查看存储过程的定义
50
查看存储过程的相关性
? 系统存储过程 sp_depends是用于显示和
存储过程相关的数据库对象, 其语法为,
? sp_depends [@objname=]?object?
? 其中, 参数 object为要查看依赖关系的存
储过程的名称 。
? 例如,exec sp_depends GetAvgPbiaodi
51
查看存储过程的所有者等信息
? 系统存储过程 sp_help是用于显示存储过
程的参数及其数据类型
? sp_help [[@objname=] name]
? 其中, 参数 name为要查看的存储过程的
名称 。
? 例,exec sp_help GetAvgPbiaodi
52
? 修改存储过程的名称可以使用系统存储
过程 sp_rename,其语法形式如下,
? sp_rename 原存储过程名称, 新存储过
程名称
? 例,exec sp_rename GetAvgPbiaodi,
?项目平均标的过程 ’
重命名存储过程
53
? 存储过程可以根据用户的要求或者基表
定义的改变而改变 。 使用 ALTER
PROCEDURE语句可以更改先前通过执行
CREATE PROCEDURE 语句创建的过程, 但
不会更改权限, 也不影响相关的存储过程
或触发器 。
修改存储过程
54
修改存储过程
? 其语法形式如下,
? ALTER PROC[EDURE] procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]
][,...n]
[WITH
RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIO
N}]
[FORREPLICATION]
AS sql_statement [,..n ]
? 其中, 格式与创建存储过程的语法完全相同,
只是 Create变为 Alter
55
修改存储过程
? 注意：只有存储过程的创建者、
db_owner和 db_ddladmin的成员才可以
修改存储过程。在 Create Procedure语句
中使用的选项也必须在 Alter Procedure
语句中使用。
56
? 例：修改例 1中创建的存储过程 Pinfo50000,使这
个存储过程值输出字段“项目名称”和“项目
标的”。
? Alter Procedure Pinfo50000
? As select 项目名称，项目标的
? From Project
? Where 项目标的 >=50000
? Order by 项目标的 DESC
? Go
? Exec Pinfo50000
? go
57
删除存储过程
? 删除存储过程可以使用 DROP命令, DROP命
令可以将一个或者多个存储过程或者存储过程
组从当前数据库中删除, 其语法形式如下,
? drop procedure {procedure} [,…n]
? 其中, procedure指定要删除的存储过程或存
储过程组的名称 。
? 例如,drop Procedure Pinfo50000
? 注意：可以选择是否指定过程所有者名称, 但
不能指定服务器名称和数据库名称 。
? 不能除去组内的个别过程, 必须除去整个过程
组；如果该过程组在其它的应用程序或存储过
程中被调用, 系统会显示出错信息 。
58
创建一组过程
? 在,;number”选项中，通过指定分号和
数字，可创建一组存储过程。一组存储
过程通常是为同一应用程序使用的，维
护工作变得向对简单，因为一个特定应
用程序使用的所有过程引用的是同一个
组。
59
? 例,Create Proc group_sp;1
? As select * from authors
? Go
? Create Proc group_sp;2
? As select au_lname from authors
? Go
? Create Proc group_sp;3
? As select distinct city from authors
? Go
? 这个批处理语句将创建一个称为 group_sp的单一
过程，它包括 3个不同过程作为它的一部分。要引
用单个过程，只要执行它们并带有,;number”作
为名字的一部分。如,exec group_sp;3
60
? 在删除过程组时，只要删除过程的名字，
所用组的部件也将被删除。比如,
? Drop procedure dbo.group_sp
? 注意：无法删除组中的某一个过程。
61
在存储过程中使用参数
? 例：下面的存储过程有 5个传入参数，求其平均
值，而后将平均值赋给 output。
? Create Procedure scores
? @score1 smallint,@score2 smallint,
? @score3 smallint,@score4 smallint,
? @score5 smallint,@myAvg smallint Output
? As select
@myAvg=(@score1+@score2+@score3+@score4
+@score5)/5
62
? 要扩展 myAvg的值，首先要定义一个变
量，然后才能运行该程序。
? Declare @AvgScore smallint
? Exec scores 10,9,8,8,10,@AvgScore Output
? Select 'The Average Score is:',@AvgScore
? go
63
在存储过程中使用参数
? 在将值传递给存储过程时，可以按位置顺序传入 (称为按
位置传入 )，也可按引用转参数，即用参数名称＝值的方
式传入参数。当按引用传入参数时，可按任何次序传入
参数。
? 例,declare @Avgscore smallint
? Exec scores
? @score1=10,@score3=9,@score2=8,
? @score4=8,@score5=10,@myAvg=@AvgScore Output
? Select 'The Average score is',@AvgScore
? Go 注意：如果开始时已经按引用传入，则整个过程调用
必须按引用传入，不能在存储过程调用中在按位置传入
和按参数传入二者之间掉换。
64
? 也可用 return关键字来将信息返回给调
用过程。这将会直接将一个变量返回给
调用过程，而无需要求有存储过程定义
及调用过程都要有的 output声明。
在存储过程中使用参数
65
在存储过程中使用参数
? 例,create proc MyReturn
? @t1 smallint,@t2 smallint,@retval smallint
? As select @retval=@t1+@t2
? Return @retval
? 创建该过程后，可输入以下内容对其调用,
? Declare @myReturnValue smallint
? Exec @myReturnValue=myReturn 9,9,0
? Select 'The return value is ',@myReturnValue
66
With Recompile选项
? 可以在 Create Procedure语句或 Exec procedure
语句后增加 With Recompile语句，其位置将影
响存储过程的处理和运行方式。
? 1、用 With Recompile创建过程：在 Create
Procedure中使用 With Recompile后，执行计划
将不被存入 Catch。 每次运行时都要重新编译
整个过程，这与标准查询的处理方式很相似，
这种方式在存储过程带有使常规执行效率较低
的参数时是很有帮助的，通过每次重新编译，
过程可针对新参数进行优化执行。
67
With Recompile选项
? 2,在 Exec procedure中使用的 With
Recompile选项：也可以在 Exec
Procedure中使用 With Recompile子句，
这将为单词执行编译存储过程，并将新
计划放入 Cache中，以供随后的 Exec
Procedure命令使用。
? 3、强制重新编译所有存储过程：可用
sp_recompile存储过程来强制所有引用特
定表的存储过程和触发器在再次运行时
被重新编译。
68
With Recompile选项
? 将存储过程设置为 SQL 启动时自动执行：
可以让存储过程在 SQL Server启动时自
动执行。
