第 11 章 存储过程与触发器

1. 第11章 存储过程与触发器

2. 本章内容 11.1 存储过程 11.1.1 存储过程的功能及优势 11.1.2 存储过程类型 11.1.3 常用系统存储过程 11.1.4 设计存储过程 11.1.5 实现存储过程 11.2 触发器 11.2.1 DML触发器 11.2.2 DDL触发器

3. 11.1 存储过程 • 存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。

4. 10.1 存储过程概述 11.1.1. 存储过程的功能及优势 • SQL Server的存储过程可实现以下功能： (1)接收输入参数并以输出参数的形式为调用过程或批处理返回多个值。 (2)包含执行数据库操作的编程语句，包括调用其他过程。 (3)为调用过程或批处理返回一个状态值，以表示成功或失败(及失败原因)。

5. 10.1 存储过程概述 存储过程具有以下优点 (1)模块化编程。 (2)快速执行。 (3)减少网络通信量。 (4)提供安全机制。 (5)保证操作一致性。

6. 10.1 存储过程概述 11.1.2. 存储过程的类型 • SQL Server存储过程的类型包括： • 用户定义存储过程 • 扩展存储过程。 • 系统存储过程

7. 10.1 存储过程概述 (1)用户定义存储过程 • 是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。它处于用户创建的数据库中，存储过程名前没有前缀sp_。

8. 10.1 存储过程概述 (2)扩展存储过程 • 扩展存储过程是SQL Server可以动态装载和执行的动态链接库(DLL)。当扩展存储过程加载到SQL Server中，它的使用方法与系统存储过程一样。扩展存储过程只能添加到master数据库中，其前缀是xp_。

9. 10.1 存储过程概述 (3)系统存储过程 • 是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，它从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。 • 通过系统存储过程，SQL Server中的许多管理性或信息性的活动(例如使用sp_depends、sp_helptexts可以了解数据数据库对象、数据库信息)都可以顺利有效地完成。尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。

10. 11.1.3 常用系统存储过程 1. sp_help 2. sp_helpdb 3. sp_helpfile 4. sp_rename 5. sp_renamedb 6. sp_databases 7. sp_tables 8. sp_columns

11. 1. sp_help 报告有关数据库对象(sys.sysobjects兼容视图中列出的所有对象)、用户定义数据类型或SQL Server 2005提供的数据类型的信息。语法格式如下。 sp_help [ [ @objname = ] 'name' ]

12. 【例11.1】返回有关所有对象的信息。以下示例将列出有关teaching数据库中每个对象的信息。【例11.1】返回有关所有对象的信息。以下示例将列出有关teaching数据库中每个对象的信息。 USE teaching GO EXEC sp_help GO

13. 【例11.2】返回数据类型信息。以下示例将列出有关teaching数据库中smallint数据类型的信息。【例11.2】返回数据类型信息。以下示例将列出有关teaching数据库中smallint数据类型的信息。 USE teaching GO EXEC sp_help smallint GO

14. 【例11.3】返回有关单个对象的信息。以下示例将显示有关student表的信息。【例11.3】返回有关单个对象的信息。以下示例将显示有关student表的信息。 USE teaching GO EXEC sp_help 'student' GO

15. 2. sp_helpdb 报告有关指定数据库或所有数据库的信息。语法格式如下。 sp_helpdb [ [ @dbname= ] 'name' ] [ @dbname = ] 'name'：要报告其信息的数据库的名称。name的数据类型为sysname，没有默认值。如果未指定name，则sp_helpdb将报告sys.databases目录视图中所有数据库的信息。 返回代码值为0(成功)或1(失败)。

16. 【例11.4】返回有关单个数据库的信息。以下示例显示有关teaching数据库的信息。【例11.4】返回有关单个数据库的信息。以下示例显示有关teaching数据库的信息。 EXEC sp_helpdb ' teaching ' GO

17. 【例11.5】返回有关所有数据库的信息。以下示例显示运行在SQL Server服务器上所有数据库的信息。 EXEC sp_helpdb GO

18. 3. sp_helpfile 返回与当前数据库关联的文件的物理名称及属性。使用此存储过程可以确定附加到服务器或从服务器分离的文件名。语法格式如下。 sp_helpfile [ [ @filename = ] 'name' ] [ @filename = ] 'name'：是当前数据库中任意文件的逻辑名称。name的数据类型为sysname，默认值为NULL。如果未指定name，则返回当前数据库中所有文件的属性。 返回代码值为0(成功)或1(失败)。

19. 【例11.6】以下示例返回有关teaching中的文件的信息【例11.6】以下示例返回有关teaching中的文件的信息 USE teaching GO EXEC sp_helpfile GO

20. 4. sp_rename 在当前数据库中更改用户创建对象的名称。 此对象可以是表、索引、列、别名数据类型或Microsoft .NET Framework公共语言运行时(CLR)用户定义类型。语法格式如下。 sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name' [ , [ @objtype = ] 'object_type' ] 只能更改当前数据库中的对象名称或数据类型名称。大多数系统数据类型和系统对象的名称都不能更改。

21. 【例11.7】重命名表。以下示例将student表重命名为stu。【例11.7】重命名表。以下示例将student表重命名为stu。 USE teaching GO EXEC sp_rename 'student', 'stu' GO • 【例11.8】重命名列。以下示例将student表中的SNO重命名为SID。 USE teaching GO EXEC sp_rename 'student.SNO', 'SID', 'COLUMN' GO • 【例11.9】重命名索引。以下示例将PK_student索引重命名为PK_stu。 USE teaching GO EXEC sp_rename 'student.PK_student', 'PK_stu', 'INDEX' GO

22. 5. sp_renamedb 更改数据库的名称。语法格式如下。 sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name' [ @dbname =] 'old_name'：数据库的当前名称。old_name的数据类型为sysname，无默认值。 [ @newname =] 'new_name'：数据库的新名称。new_name必须遵循有关标识符的规则。new_name的数据类型为sysname，无默认值。 返回代码值为0(成功)或非零数字(失败)。

23. 【例11.10】以下示例先创建Accounting数据库，然后将该数据库的名称更改为Financial，再查询sys.databases目录视图以确认数据库的新名称。【例11.10】以下示例先创建Accounting数据库，然后将该数据库的名称更改为Financial，再查询sys.databases目录视图以确认数据库的新名称。 USE master GO CREATE DATABASE Accounting GO EXEC sp_renamedb N'Accounting', N'Financial' GO SELECT name, database_id, modified_date FROM sys.databases WHERE name = N'Financial' GO

24. 6. sp_databases 列出驻留在SQL Server 2005 Database Engine实例中的数据库或可以通过数据库网关访问的数据库。语法格式如下。 sp_databases 所返回的数据库名称可以作为USE语句的参数，用来更改当前数据库上下文。 返回代码值为无。

25. 【例11.11】以下示例显示如何执行sp_databases。 EXEC sp_databases GO

26. 7. sp_tables 返回可在当前环境中查询的对象列表。这些对象是可以在FROM子句中出现的任何对象。语法格式如下。 sp_tables [ [ @table_name = ] 'name' ] [ , [ @table_owner = ] 'owner' ] [ , [ @table_qualifier = ] 'qualifier' ] [ , [ @table_type = ] "'type'" ] [ , [@fUsePattern = ] 'fUsePattern']

27. 【例11.12】返回可在master数据库中查询的对象列表。【例11.12】返回可在master数据库中查询的对象列表。 USE master EXEC sp_tables GO

28. 【例11.13】返回有关teaching中的表的信息。以下示例返回有关teaching数据库中的dbo所拥有的表的信息。【例11.13】返回有关teaching中的表的信息。以下示例返回有关teaching数据库中的dbo所拥有的表的信息。 USE teaching GO EXEC sp_tables @table_name = '%', @table_owner = 'dbo', @table_qualifier = 'teaching' GO

29. 8. sp_columns 返回当前环境中可查询的指定表或视图的列信息。语法格式如下。 sp_columns [ @table_name = ] object [ , [ @table_owner = ] owner ] [ @table_name =] object：用于返回目录信息的表或视图的名称。object的数据类型为 nvarchar(384)，没有默认值。支持通配符模式匹配。 [ @table_owner =] owner：用于返回目录信息的表或视图的对象所有者。owner的数据类型为nvarchar(384)，默认值是NULL。支持通配符模式匹配。如果未指定owner，则应用基础DBMS的默认表或视图可见性规则。 返回代码值为无。

30. 【例11.14】以下示例返回指定表course的列信息。【例11.14】以下示例返回指定表course的列信息。 USE teaching GO EXEC sp_columns @table_name = 'course', @table_owner = 'dbo'

31. 11.1.4 设计存储过程 1.存储过程的设计规则 2.限定存储过程内的名称 3.加密过程定义

32. 11.1.5 实现存储过程 1 创建存储过程 2 执行存储过程 3 修改存储过程 4 重新编译存储过程 5 删除存储过程

33. 1 创建用户定义的存储过程 10.2.1 创建存储过程 1.使用Transact-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 ]

34. 10.2 存储过程的创建与使用 2．使用图形工具创建存储过程 （1）打开SQL Server管理平台，展开节点“对象资源管理器”→“数据库服务器”→“可编程性”→“存储过程”，在窗口的右侧显示出当前数据库的所有存储过程。单击鼠标右键，在弹出的快捷菜单中选择“新建存储过程”命令 。

35. 10.2.1 创建存储过程 （2）在打开的SQL命令窗口中，系统给出了创建存储过程命令的模板，如图10-2所示。在模板中可以输入创建存储过程的Transact-SQL语句后，单击“执行”按钮即可创建存储过程。

36. 10.2.1 创建存储过程 （3）建立存储过程的命令被成功执行后，在“对象资源管理器”→“数据库服务器”→“可编程性”→“存储过程”中可以看到新建立的存储过程

37. 10.2 存储过程的创建与使用 2 执行存储过程 • 执行存储过程的语法格式： [[EXEC[UTE]] {[@return_status=] procedure_name [;number]|@procedure_name_var} [[@parameter=]{value|@variable [OUTPUT]|[DEFAULT]] [ ,...n ] [WITH RECOMPILE ]

38. 3 修改存储过程 10.2.3 修改存储过程 1.使用Transact-SQL命令修改存储过程 修改存储过程可以通过Transact-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 ]

39. 10.2 存储过程的创建与使用 2．使用SQL Server管理平台修改存储过程

40. 4重新编译存储过程 10.2.3 修改存储过程 在执行诸如添加索引或更改索引列中的数据等操作更改了数据库时，应重新编译访问数据库表的原始查询计划以对其重新优化。 在Microsoft SQL Server 2005重新启动后第一次运行存储过程时自动执行此优化。当存储过程使用的基础表发生变化时，也会执行此优化。但如果添加了存储过程可能从中受益的新索引，将不自动执行优化，直到下一次Microsoft SQL Server重新启动后再运行该存储过程时执行优化。在这种情况下，强制在下次执行存储过程时对其重新编译会很有用。

41. 10.2 存储过程的创建与使用 5 删除存储过程 • 存储过程可以被快速删除和重建，因为它没有存储数据。 1.使用Transact-SQL命令删除存储过程 • DROP PROCEDURE的语法如下： DROP PROCEDURE {procedure_name} [ ,...n ] • 例如删除例10-2创建的存储过程employee_dep： DROP PROCEDURE employee_dep GO

42. 10.2.4 删除存储过程 2．使用SQL Server管理平台删除存储过程 1)连接到相应的Microsoft SQL Server Database Engine实例之后，在“对象资源管理器”中，单击服务器名称以展开服务器树。 2)展开“数据库”，然后选择用户数据库，如teaching。 3)展开“可编程性”，右键单击其中要删除的存储过程如getstudent2，然后单击 “删除”

43. 11.2 触发器概述 • 触发器是一种特殊类型的存储过程。 • 触发器主要是通过事件进行触发而被执行的，而存储过程可以通过过程名字直接调用。当对某一表进行UPDATE、INSERT、DELETE操作时，SQL Server就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。 • 触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和数据的一致性。

44. 11.2.1 DML触发器 1. DML触发器类型 • AFTER触发器 • INSTEAD OF触发器 • CLR触发器

45. 11.2.1 DML触发器 2. 触发器与约束的比较 • 约束和DML触发器在特殊情况下各有优点。DML触发器的主要优点在于它们可以包含使用Transact-SQL代码的复杂处理逻辑。因此，DML触发器可以支持约束的所有功能；但DML触发器对于给定的功能并不总是最好的方法。

46. 11.2.1 DML触发器 3. DML触发器功能比较

47. 11.2.1 DML触发器 4. 实现DML触发器 • (1)创建DML触发器前应考虑的问题 • (2)多个DML触发器 • (3)触发器权限和所有权 • (4)创建DML触发器

48. 10.4.1 创建触发器 1. 使用Tranasct-SQL命令创建DML触发器 • CREATE TRIGGER语句的语法格式如下： CREATE TRIGGER trigger_name ON {table_name | view } [ WITH ENCRYPTION ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]} AS sql_statement [ ... n ]

49. 10.4 触发器的创建与使用 (4)创建触发器 2．使用图形工具创建DML触发器

50. 10.4.3 修改触发器 • (5).使用插入的表和删除的表 • (6)修改DML触发器 • (7)重命名DML触发器 • (8)禁用和启用DML触发器 • (9)删除DML触发器