第九章 存储过程与触发器

1. 第九章 存储过程与触发器 讲解提纲: 9.1 存储过程综述 9.2 创建、执行、修改、删除简单存储过程 9.3 创建和执行含参数的存储过程 9.4 存储过程的重新编译 9.5 系统存储过程与扩展存储过程 9.7 触发器综述 9.8 触发器的创建执行 9.9 修改和删除触发器

2. 本章要点 • 了解存储过程和触发器的作用、特点。 • 掌握利用企业管理器、查询分析器创建、 修改和删除存储过程和触发器的方法。

3. 9.1 存储过程概述 9.1.1 存储过程的概念 所谓存储过程，是一组预编译的Transact-SQL语句，存储在SQL Server中，被作为一种数据库对象保存起来。存储过程的执行不是在客户端而是在服务器端（执行速度快）。存储过程可以是一条简单的Transact-SQL语句，也可以是复杂的Transact-SQL语句和流程控制语句的集合。

4. 在使用Transact-SQL语言编程的过程中，我们可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程（也称为子程序）。 存储过程在第一次执行时进行语法检查和编译，编译好的版本存储在高速缓存中，用于后续调用，下次调用时可以直接执行。

5. 存储过程的特点： 存储过程可以接受输入参数，并且可以用输出参数的形式返回值。存储过程可以包含复杂的流控制语句。 存储过程建立好以后可以被反复调用。一个存储过程可以调用另一个存储过程，嵌套调用可多达32级。 由于存储过程在建立和第一次执行的时候被解析、优化、编译和缓存（驻留在高速缓冲存储器中），因此其执行速度比直接向SQL Server发送T-SQL语句快。 存储过程的执行是通过一条语句来完成的，避免了网络上服务器和客户机之间大量Transact-SQL语句的传送，降低了网络的通信量。 存储过程可以被看作一种安全机制，即使用户没有对表或视图的访问权限，他们也可以被授予执行对表或视图进行访问的存储过程的权限。

6. 9.1.2 存储过程的类型 SQL Server支持五种类型的存储过程。 （1）系统存储过程系统存储过程是在SQL Server中执行管理活动的一类特殊的存储过程（由系统提供）。系统存储过程创建和存储于系统数据库master中，其前缀是sp_ 。系统存储过程可以在任何的数据库中执行却不需要引用数据库名master 。我们可以通过系统存储过程访问和更新系统表。 （2）用户定义（本地）存储过程用户定义存储过程是用户在自己的数据库中创建的存储过程，完成特定数据库操作任务，其名称不能加sp_前缀。

7. （3）临时存储过程临时存储过程属于本地存储过程，在存储过程名称前带有#或##。其中，前缀为#的表示私有存储过程，只有创建私有存储过程的连接能够执行该存储过程（即只能在一个用户会话中使用），并且存储过程在连接关闭时自动被删除。前缀为##的表示全局存储过程，任何连接都可以执行该存储过程（即可以在所有用户会话中使用），当创建全局存储过程的连接关闭并且所有当前正在执行该全局存储过程的连接都完成之后被自动删除。（3）临时存储过程临时存储过程属于本地存储过程，在存储过程名称前带有#或##。其中，前缀为#的表示私有存储过程，只有创建私有存储过程的连接能够执行该存储过程（即只能在一个用户会话中使用），并且存储过程在连接关闭时自动被删除。前缀为##的表示全局存储过程，任何连接都可以执行该存储过程（即可以在所有用户会话中使用），当创建全局存储过程的连接关闭并且所有当前正在执行该全局存储过程的连接都完成之后被自动删除。

8. （4）扩展存储过程扩展存储过程是SQL Server环境之外可以动态装载和执行的动态链接库（DLL）。扩展存储过程的前缀是xp_。（5）远程存储过程远程存储过程是指从远程服务器（非本地SQL Server服务器）上的存储过程。

9. 9.2 创建、执行、修改、删除存储过程 9.2.1 创建存储过程 在创建存储过程之前有以下规则需要考虑： 建立存储过程的CREATE PROCEDURE语句不能与其它的Transact-SQL语句在一个批处理中同时使用。通过CREATE PROCEDURE语句建立存储过程时不能包含这些Transact-SQL语句：CREATE DEFAULT，CREATE TRIGGER，CREATE PROCEDURE，CREATE VIEW ，CREATE RULE。 存储过程可以引用表、视图，甚至临时表。 存储过程中所能使用的局部变量个数仅和可用内存有关。存储过程可以通过Transact-SQL语句和企业管理器来创建。

10. 1. 使用企业管理器创建存储过程 第1步：启动企业管理器，在选定的服务器上打开连接。 第2步：单击数据库（Databases）旁的加号（+），打 开数据库文件夹。 第3步：单击要建立存储过程的数据库名旁的加号（+） ，打开该数据库文件夹。 第4步：在Stored Procedures上右击鼠标，弹出快捷菜 单，如图1-1所示，单击New Stored Procedure....菜单选项。 第5步：在随后出现的窗口的编辑框中输入存储过程内容。 第6步：单击窗口左下角的“检查”按钮检查存储过程中是否 存在语法错误。 第7步：单击OK按钮保存存储过程的定义。

12. 在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限…”按钮。在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限…”按钮。

13. 2. 使用Transact-SQL语句创建存储过程 部分语法格式如下： CREATE PROC[EDURE] procedure_name [{@parameter data_type} [= default] [OUTPUT]] [,...n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }] AS sql_statement [...n]

14. 主要参数含义： procedure_name：表示存储过程名。存储过程名必须遵循标识符命名规则。 @parameter ：表示存储过程中使用的参数。参数名必须遵循标识符命名规则。参数仅具有局部作用，因此，不同的存储过程可以使用相同的参数名。一个存储过程中可以声名一个或多个参数，最多可达1024个。定义时没有OUTPUT关键字的参数为输入参数，在存储过程执行时，除了有缺省值的输入参数以外，所有声明的输入参数都必须提供值。

15. data_type：表示参数的数据类型。存储过程 中声明的参数可以使用所有的数据类型。 default：表示参数的缺省值。如果一个参数具有缺省值，则存储过程执行时可以不为其指定值，如果指定值，则该值就取代缺省值。缺省值必须是常量或者空值，如果存储过程中使用LIKE关键字，缺省值中可以包含通配符（%， _， []，and [^]）。

16. OUTPUT ：表示声明的参数是输出参数。输出参数能够在存储过程执行时返回值。Text或者Image数据类型的参数不能作为输出参数。 {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} RECOMPILE:表示SQL Server 不缓存存储过程的计划，因此存储过程每次执行都会被重新编译。ENCRYPTION表示对存储过程的文本进行加密。 sql_statement ：表示存储过程中可以包含任意数量和类型的Transact-SQL语句。

17. 3. 举例[例9-1]创建一个基于数据库student的存储过程，该存储过程能够查询每个同学各门功课的成绩，然后调用该过程。 • Use student go create procdeure student_cj as select xs.学号, xs.姓名, kc.课程名,xs_kc.成绩 from xs, kc, xs_kc where xs.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号

18. 调用存储过程 • Exec student_cj go 通过上例了解存储过程的使用。

19. [例9-2] 在student中建立一个名为st_proc_bj的存储过程.该存储过程将返回计算机系的班级名称。 所用脚本内容如下： Use student Go Create proc dbo.st_proc_bj as Select 班级名称 from 班级,系部 where 系部.系部代码 =班级.系部代码 and 系部.系部名称=‘计算机’ go Execute st_proc_bj go

20. [例9-3] 在student中建立一个名为st_scr的存储过程，用 于检索电子商务概论课程成绩，并按降序排序。 所用脚本内容如下： Use student If exists (select name from sysobjects where name=‘st_scr’ and type=‘p’) Drop procedure st_scr go Create procedure st_scr As Select 姓名,课程名称,成绩 from 成绩表 as sc inner join 学生表 as st on sc.学号=st.学号 inner join 课程表 as co 0n sc.课程编号=co.课程编号 where 课程名称=‘电子商务概论’ order by 成绩 desc go Execute st_scr

21. [例9-4] 创建一个利用流程控制语句的存储过 程，该存储过程能够显示26个字母。 CREATE PROCEDURE letters_print AS DECLARE @mycount int SET @mycount=0 WHILE @mycount<26 BEGIN PRINT CHAR(ASCII(‘a’)+ @mycount) SET @mycount=@mycount+1 END GO

22. [例9-5] 创建一个对文本进行加密的存储过程 USE pubs GO CREATE PROCEDURE encrypt_proc WITH ENCRYPTION AS SELECT * FROM employee SELECT COUNT(*) FROM employee GO

23. 总结存储过程的优点 • 实现了模块化编程，作为一个单元来处理 • 存储过程具有对数据库立即访问的功能。 • 使用存储过程可以加快程序的运行速度。 • 使用存储过程可以减少网络流量。 • 使用存储过程可以提高数据库的安全性。

24. 创建存储过程时，需要确定存储过程的三个组成部分：创建存储过程时，需要确定存储过程的三个组成部分： ①所有的输入参数以及传给调用者的输出参 数。 ②被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 ③返回给调用者的状态值，以指明调用是成功还是失败。

25. [例9-6] 在student中建立一个名为st_scr_sp的存储过程.该过程带有两个参数，用于接受指定的学生姓名和课程名称，然后从表中检索相应成绩。

26. 所用脚本内容如下： Use student If exists (select name from sysobjects where name=‘st_scr_sp’ and type=‘p’) Drop procedure st_scr_sp Go Create procedure st_scr_sp @st_name varchar(6),@co_name varchar(6) as Select 姓名,课程名称,成绩 from 成绩表 as sc inner join 学生表 as st on sc.学号=st.学号 inner join 课程表 as co 0n sc.课程编号=co.课程编号 where 姓名=@st_name and 课程名称=@co_name order by 成绩 desc go Execute st_scr_sp ‘李向明’,‘计算机网络基础’

27. [例9-7]从xscj数据库的三个表中查询某人指定课程的成绩和学分。[例9-7]从xscj数据库的三个表中查询某人指定课程的成绩和学分。 • Use xscj go create procedure student_info1 @name char(8),@cname char(16) as select a.学号,姓名,课程名,成绩,学分 from xs a inner join xs_kc b on a.学号=b.学号 inner join kc t on b.课程号=t.课程号 Where a.姓名=@name and t.课程名=@cname Go

28. student_info1存储过程有多种执行方式 • Execute student_info1 ’李利军’,’计算机基础’ 或 Execute student_info1 @name=’李利军’, @cname=’计算机基础’ 或 Execute student_info1,@cname=’计算机基础’, @name=’李利军’

29. 9.2.2 执行存储过程 存储过程创建成功后，保存在数据库中。在SQL Server中可以使用EXECUTE命令来直接执行存储过程。

30. 我们可以使用Transact-SQL的EXECUTE语句来执行存储过程。如果存储过程的执行是批处理中的第一条语句，EXECUTE关键字可以省略。我们可以使用Transact-SQL的EXECUTE语句来执行存储过程。如果存储过程的执行是批处理中的第一条语句，EXECUTE关键字可以省略。 部分语法格式如下： [[EXEC[UTE]] { [@return_status =] procedure_name } [[@parameter =] value | @variable [OUTPUT] | [DEFAULT]] [,...n] [WITH RECOMPILE]

31. [例9_8] 执行存储过程 Use student Execute st_scr

32. 9.2.3 查看和修改存储过程 • 查看、修改存储过程 • 使用企业管理器查看、修改用户创建的存储过程 • 使用系统存储过程查看用户创建的存储过程

33. 1．使用企业管理器查看、修改存储过程 在SQL Server企业管理器中，选择指定的服务器和数据库，选择存储过程的的节点数据库，在右边的列表中显示出当前数据库中所有的存储过程。右击需要查看的存储过程,在弹出的快捷菜单中依次选择“属性|打开存储过程属性”对话框，既可以查看过程定义信息，又可以在文本框中对存储过程的定义进行修改。

34. 2．使用Transact-SQL语句查看存储过程 根据不同需要，可以使用以下系统存储过程。 其语法格式如下： 1）查看存储过程的文本信息： Sp-helptext 存储过程名 2）查看存储过程的相关性 Sp-depends 存储过程名 3）查看存储过程的一般信息 Sp-help 存储过程名 • 注： 同学们自主练习以上的例子。

35. 9.2.4 修改存储过程 • 1．使用企业管理器修改存储过程 • 2．使用Transact-SQL语句修改存储过程

36. 例： • Use xscj go alter procedure student_info1 @name char(8),@cname char(16) as select a.学号,姓名,课程名,成绩,学分 from xs a inner join xs_kc b on a.学号=b.学号 inner join kc t on b.课程号=t.课程号 Where a.姓名=@name and t.课程名=@cname Go

37. 9.2.5 重命名和删除存储过程 1. 重命名存储过程 • 1)使用企业管理器重命名存储 名称(略) • 2)使用系统存储过程重命名存储 过程名称

38. 使用系统存储过程重命名存储过程名称 Sp_rename 存储过程原名 存储过程新名

39. 2.删除存储过程 • 1)使用企业管理器删除存储过程(略) • 2)使用Transact-SQL语句删除存储过程

40. 使用Transact-SQL语句删除存储过程 DROP PROCEDURE 存储过程名[ ，…n]

41. 9.7 触发器综述 9.7.1 概述 触发器的作用 • 在指定的表中数据发生变化时被调用以响应INSERT、UPDATE或DELETE事件。 • 用于保护表中的数据，当有操作影响到触发器保护的数据时，触发器自动强制执行业务规则。 • 利用触发器可以方便地实现数据库中的数据完整性。

43. 触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。 一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行(对表或视图发出update、insert、delete语句)。 触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。

44. 9.7.2 触发器的优点 • 触发器是自动的执行的(往表里插入、修改、删除时被自动激活，从而防止对数据的不正确修改)。 • 触发器是基于一个表创建的，但可以针对多个表进行操作，实现数据库中的相关表进行层叠更改。 • 触发器可以实现比 check约束更为复杂的数据完整性约束。 • 一个表中可以同时存在三个不同操作的触发器，对于同一个修改语句可以有多个不同的对策以响应。

45. 9.7.3 触发器的类型 • AFTER触发器(又称为后触发器) 是在引起触发器执行的修改语句完成之后执行。 • INSTEAD OF触发器（又称为替代触发器） 当引起触发器执行的修改语句停止执行时，该 类触发器代替触发操作执行。

46. 触发器中使用的特殊表（逻辑表）INSERTED表和DELETED表触发器中使用的特殊表（逻辑表）INSERTED表和DELETED表 • 执行触发器时，系统为每个触发器都自动创建了INSERTED表和DELETED表。 • INSERTED表：当向表中插入记录时， INSERTED触发器触发执行，新的记录插入到触发器表和INSERTED表中，用于存放插入操作时的行。DELETED表用于存放已从表中执行操作删除的记录。 • 修改一条记录等于插入一条新记录，同时删除旧记录。原记录移到DELETED表中，修改过的记录 插入到INSERTED表中。 • 这两个表的结构和被触发器作用的表的结构相同。 • 用户不能对这两个表进行修改，但可以读取。 INSERTED表和DELETED表的查询方法与表的查询方法相同。

47. 9.8 触发器的创建执行 9.8.1 创建触发器 1、使用企业管理器创建触发器 在企业管理器中，展开指定的服务器 和数据库，右击某个表，从弹出的快捷菜单中依次选择“所有任务|管理触发器”选项，会出现触发器属性对话框 。

48. 2、使用Transact-SQL语句创建触发器 使用Transact-SQL语言中的CREATE TRIGGER命令可以创建触发器，其中需要指定定义触发器的基表、触发器执行的事件和触发器的所有指令。 • SQL格式： CREATE TRIGGER trigger_name On table|view [WITH ENCRYPTION] {FOR|AFTER| INSTEAD OF} {[INSERT] [UPDATE] [DELETE]} AS sql_statement

49. 其中： ON子句：用于指定在其上执行的触发器的表，有时也 称为触发器表。 {FOR|AFTER| INSTEAD OF} ：指定了触发器激活的时 机，即执行哪些操作时激活触发器。 {[INSERT] [UPDATE] [DELETE]}：指定了激活触发器的 语句。 sql_statement：指定了触发器所执行的T_SQL语句。 注意：通常不要在触发器中返回任何结果，因此就不要在触发器定义中使用select语句或变量赋值语句。

50. 练习： 1.创建一个触发器，要求每当在StuCou表中插入数据时， 向客户端显示一条“记录已添加！”的消息。 2.创建一个触发器，要求每当用户插入student表的记录 时，自动显示表中所有内容。 例： use student go create trigger inup on xs for insert,update as raiserror(4008,16,10) Go 消息4008是sysmessage中的用户定义信息。