第九章存储过程与触发器

第九章存储过程与触发器 9.1存储过程. 9.2触发器 9.3常用的系统存储过程

9.1存储过程 一、存储过程的作用将T-SQL查询转化为存储过程是提高SQL Server服务器性能的最佳方法之一，因为存储过程是在服务器端运行，所以执行速度快，而且存储过程方便用户查询，提高数据使用效率。当一个存储过程创

建时即被翻译成可执行的系统代码 保存在系统表内，当作是数据库的对象之一，一般用户只要执行存储过程，并提供存储过程所需的参数，就可以得到需要的查询结果，而不用接触到具体的SQL命令。存储过程的作用有：

（１）执行速度快。存储过程第一 次执行时进行编译并驻留在高速缓存中，以后再执行时，只需从高速缓存中调用已编译好的二进制代码，对于经常被执行且功能固定的查询需求，存储过程将大大节省 SQL Server执行时间。

（２）减少网络流量。存储过程存 储在服务器上并在服务器上执行，网络上只传送存储过程执行的最终数据，大大减少了网络流量。（３）作为一种安全机制。通过设置用户只可能使用存储过程访问数据，限制用户不能直接操作数据库

中的敏感数据，以保障数据的安全 性。（４）屏蔽T-SQL命令，提供交互查询的客户接口，增加数据库应用的方便性。

二、存储过程的类型 （一）内建存储过程　　内建存储过程由系统提供，在安装SQL Server后自动装入，定义在系统数据库master中，其存储过程名前缀是sp_。 SQL Server为了管理上的方

便，提供了大量内建存储过程让系 统管理员直接使用，以便获得一些系统信息，或直接得到某些特定的功能，如sp_help存储过程可以检索系统表的信息，sp_helpdb存储过程可以显示数据库对象信息， sp_adduser存储过程可以显示新

建用户信息。 　　内建存储过程又分目录存储过程、系统存储过程、复制存储过程、事务存储过程和扩充存储过程。（１）目录存储过程：用来显示数据库对象的信息，如数据库内建哪

些表、表内哪些列。常用的有 sp_helpdb、sp_help、 sp_helptext等存储过程。（２）系统存储过程：用来处理 SQL Server的系统管理工作，如新建用户、设置选项、设置密码等。常用的有sp_adduser

sp_dboption sp_password 等存储过程。（３）复制存储过程：用来处理有关数据库复制方面的事务。（４）事务存储过程：用来进行创建系统工作，如安排数据库复制、安排工作执行的时间或系统警告信

息等。 （５）扩充存储过程：用于加载和执行DLL动态链接库，扩充存储过程名称通常以“xp_”为前缀。（二）本地存储过程　　本地存储过程是在用户数据库中创建的存储过程，用以完成特定

的数据库操作任务，也称为用户存 储过程。通常数据库所有者和 ddl_admin角色成员可以创建本地存储过程，数据库所有者可以授权其他用户创建本地存储过程。本地存储过程通常只能应用于创建它的数据库，并且存储过程名不能使

用sp_前缀，以免系统误解。 　　本地存储过程可分为永久性存储过程或临时存储过程。（１）永久存储过程：在当前数据库中创建，存储过程名存储于当前数据库的系统表sysobject中，文本保存在数据库的系统注释表中，

可供以后使用。 （２）临时存储过程：如果在存储过程名称之前加上“#”或“##”前缀，代表此存储过程是一临时保存的存储过程，这些存储过程创建在 tempdb数据库中，与所有临时对象一样在SQL Server结束运行时

会被删除。临时存储过程有两种， “#”表示局部临时存储过程，这种存储过程只能由创建它的用户使用，其他用户无法使用，该存储过程的拥有者也无法将其执行权限下放给其他用户，当拥有者和 SQL Server结束连接时，该存储

过程即自动被删除。“##”表示全 局临时存储过程，这种存储过程可以为其他所有用户使用，拥有者不能限定别人使用它，当最后一个用户结束连接时，该存储过程即自动被删除。

三、创建存储过程 （一）命令方式创建存储过程　　储过程时没有给出该形式参数值，系统使用该默认值。 OUTPUT关键字指定参数是做为输出用，即该参数从存储过程返回数值给原调用者。

VARYING关键字指定参数的内容可 以变化，当参数是游标cursor数据类型，它只能当做输出参数用，并且结果集会动态变化，此时必须同时指定VARYING和OUTPUT关键字，RECOMPILE关键字指定SQL Server每次运行该过程时，将对

其重新编译。ENCRYPTION关键字 指定SQL Server加密系统注释表 syscomments中包含CREATE PROCEDURE语句文本的条目，这样可防止别人使用系统存储过程读取该定义文本。 FOR REPLICATION子句指定该存储

过程不能在订阅服务器上执行复 制。如果创建的存储过程是为了在数据库复制（replication）中筛选被复制的数据（filter stored procedure），可以指定该选项。该选项不能和WITH RECOMPILE子句一起使用。

sql_statement表示过程体，包含 一条T-SQL语句或多条T-SQL语句，但要注意不能有以下对象创建语句： CREATE VIEW CREATE DEFAULT CREATE RULE

CREATE PROCEDURE CREATE TRIGGER 以下语句中必须使用对象所有者名对数据库对象进行限定： CREATE TABLE ALTER TABLE DROP TABLE

TRUNCATE TABLE CREATE INDEX DROP INDEX UPDATE STATISTICS 此外，除SET SHOWPLAN_TEXT和 SET SHOWPLAN_ALL外，其他SET 语句均可在存储过程中使用。

　　在存储过程中可以使用 EXECUTE语句调用其他存储过程，即存储过程可以嵌套调用，SQL Server最多可允许32层嵌套调用，详见EXECUTE命令说明。自动执行的存储过程必须由系统管理员在master数据库中创建

成，并由sysadmin固定服务器角 色控制系统在后台执行，这些过程不能有任何输入参数。　　该命令权限默认属于 db_owner数据库所有者、 db_ddladmin固定数据库角色成员和sysadmin固定服务器角色成员

【例9-1】建立一个存储过程，查 询traffic数据库中驾驶员驾照号、姓名和各次出车行程。 USE traffic IF EXISTS(SELECT name FROM sysobjects /*检查是否已存在同名的存储过程*/

WHERE name=‘xclist1’ AND TYPE=‘p’) DROP PROCEDURE xclist1 /*若有则删除*/ GO CREATE PROCEDURE xclist1 AS

SELECT jsy.驾照号, jsy.姓 名 ,xc.出车单号, cd.实际行程 FROM jsy,xc,cd WHERE jsy.驾照号=xc.主驾 AND xc.出车单号=cd.出车单号 GO 　　该过程不使用任何参数，在查

询分析器的查询窗口输入上述语句 后单击工具栏中“执行查询”图标，在消息窗口系统显示“命令已成功完成”。　　成功执行CREATE PROCEDURE 语句后，该过程名称存储在 sysobjects系统表中，而CREATE

PROCEDURE语句的文本存储在 syscomments系统表中，可以在企业管理器中展开traffic数据库，在表对象窗口中选择相应的系统表，返回所有行进行查看，如图 9-2和图9-3。

图9-2 sysobjects系统表中新建的存储过程

图9-3 syscomments系统表中新建存储过程的文本

【例9-2】建立一个加密的存储过 程，查询指定姓名的驾驶员的驾照号和各次出车的行程。 USE traffic IF EXISTS(SELECT name FROM sysobjects WHERE name=’xclist2’ AND

TYPE=‘p’) DROP PROCEDURE xclist2 GO CREATE PROCEDURE xclist2 @name char(10) WITH ENCRYPTION AS

SELECT jsy.姓名 , cd.实际行程 FROM jsy,xc,cd WHERE jsy.姓名=@name AND xc. 主驾= jsy.驾照号 AND xc.出车单号=cd.出车单号 GO 【例9-3】建立一个存储过程，此

过程为xc_pro存储过程组中的一 个成员，根据用户输入的驾照号，查询该驾驶员的姓名和出车次数。 CREATE PROCEDURE xc_pro;1 @jsy_idint ,@cc_tat int OUTPUT AS

SET @cc=(SELECT COUNT(*) FROM xc WHERE xc.主驾=@jsy_id) SELECT 姓名,@cc FROM jsy WHERE 驾照号=@jsy_id GO

　　此过程定义了一个输入变量和 一个输出变量，出车次数通过输出变量@cc_tat返回给过程的调用者。【例9-4】建立一个存储过程，此过程为xc_pro存储过程组的另一成员，此过程返回与指定字符匹配

的驾驶员的姓名和驾照号。 CREATE PROCEDURE xc_pro;2 @jsy_name varchar(10)=’王%’ AS SELECT 姓名, 驾照号 FROM jsy WHERE 姓名LIKE @jsy_name

GO 　　在调用此过程时可以使用通配符输入参数，如输入参数为‘%明 %’，可查询所有姓名中有‘明’ 字的驾驶员姓名和驾照号。如果没有输入参数，则返回以‘王’字开头的驾驶员姓名和驾照号。

（二）界面方式创建存储过程 第1步启动企业管理器，展开控制台根目录层次结构，选择要创建存储过程的数据库。第2步在存储过程对象上单击鼠标右键，在快捷菜单上选择“新建存储过程”，如图9-4。

第3步在打开的创建存储过程编辑 界面的文本框中撰写存储过程代码，如图9-5。可以单击“检查语法”按钮检查你写的存储过程的语法，以确定没有语法错误。单击 “确定”按钮，保存存储过程。

四、执行存储过程 　　存储过程创建后，可以在查询分析器中使用EXECUTE语句来执行它，EXECUTE语句可执行系统存储过程、用户自定存储过程和扩充存储过程，而且不仅可以执行目前数据库上创建的存储过程，也可以执

甚至于执行网络上其他SQLServer 的存储过程，只要用户有足够的权限。EXECUTE语句的语法格式为： [EXEC[UTE]]{[@return_status=] {procedure_name[;number] | @procedure_name_var} [[@parameter=]{value |

@variable[OUTPUT] | [DEFAULT]}][,…n][WITH RECOMPILE]} 参数说明如下： @return_status为整形变量，用于保存存储过程的返回状态，需要在执行存储过程前先定义该变量。

procedure_name为存储过程名， number为一组存储过程中的成员序号。 @procedure_name_var为保存存储过程名称的变量。 @parameter为存储过程中定义的形式参数名，value为实际参数

值，@variable为变量，OUTPUT关 键字指定变量用于保存OUTPUT参数返回的值，DEFAULT关键字指定不提供实际参数，而使用相应的默认值。 WITH RECOMPILE子句指定强制编译。

第九章 存储过程与触发器