第 11 章 存储过程与触发器

本章内容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 触发器

11.1 存储过程

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

10.1 存储过程概述

11.1.1. 存储过程的功能及优势

SQL Server 的存储过程可实现以下功能：(1) 接收输入参数并以输出参数的形式为调用过

程或批处理返回多个值。(2) 包含执行数据库操作的编程语句，包括调用

其他过程。(3) 为调用过程或批处理返回一个状态值，以表

示成功或失败 ( 及失败原因 ) 。

10.1 存储过程概述

存储过程具有以下优点

(1) 模块化编程。(2) 快速执行。 (3) 减少网络通信量。(4) 提供安全机制。(5) 保证操作一致性。

10.1 存储过程概述

SQL Server 存储过程的类型包括：用户定义存储过程扩展存储过程。系统存储过程

11.1.2. 存储过程的类型

10.1 存储过程概述

(1) 用户定义存储过程

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

10.1 存储过程概述

(2) 扩展存储过程

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

10.1 存储过程概述

(3) 系统存储过程 是指由系统提供的存储过程，主要存储在 master 数据库

中并以 sp_ 为前缀，它从系统表中获取信息，从而为系统管理员管理 SQL Server 提供支持。

通过系统存储过程， SQL Server 中的许多管理性或信息性的活动 ( 例如使用 sp_depends、 sp_helptexts 可以了解数据数据库对象、数据库信息 ) 都可以顺利有效地完成。尽管系统存储过程被放在 master 数据库中，仍可以在其他数据库中对其进行调用 ( 调用时，不必在存储过程名前加上数据库名 ) 。当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。

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

1. sp_help

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

sp_help [ [ @objname = ] 'name' ]

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

USE teachingGOEXEC sp_helpGO

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

USE teachingGOEXEC sp_help smallintGO

【例 11.3 】返回有关单个对象的信息。以下示例将显示有关 student 表的信息。

USE teachingGOEXEC sp_help 'student'GO

2. sp_helpdb

报告有关指定数据库或所有数据库的信息。语法格式如下。

sp_helpdb [ [ @dbname= ] 'name' ] [ @dbname = ] 'name' ：要报告其信息的数据库的

名称。 name 的数据类型为 sysname ，没有默认值。如果未指定 name ，则 sp_helpdb 将报告sys.databases 目录视图中所有数据库的信息。

返回代码值为 0( 成功 )或 1( 失败 ) 。

【例 11.4 】返回有关单个数据库的信息。以下示例显示有关 teaching 数据库的信息。

EXEC sp_helpdb ' teaching 'GO

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

EXEC sp_helpdbGO

3. sp_helpfile

返回与当前数据库关联的文件的物理名称及属性。使用此存储过程可以确定附加到服务器或从服务器分离的文件名。语法格式如下。

sp_helpfile [ [ @filename = ] 'name' ] [ @filename = ] 'name' ：是当前数据库中任意文

件的逻辑名称。 name 的数据类型为 sysname ，默认值为 NULL 。如果未指定 name ，则返回当前数据库中所有文件的属性。

返回代码值为 0( 成功 )或 1( 失败 ) 。

【例 11.6 】以下示例返回有关 teaching 中的文件的信息

USE teachingGOEXEC sp_helpfileGO

4. sp_rename 在当前数据库中更改用户创建对象的名称。 此对象可以是表、索引、列、别名数据类型或

Microsoft .NET Framework 公共语言运行时(CLR) 用户定义类型。语法格式如下。

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'

[ , [ @objtype = ] 'object_type' ] 只能更改当前数据库中的对象名称或数据类型名

称。大多数系统数据类型和系统对象的名称都不能更改。

【例 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

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( 成功 ) 或非零数字 ( 失败 )。

【例 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

6. sp_databases 列出驻留在 SQL Server 2005 Database Engine

实例中的数据库或可以通过数据库网关访问的数据库。语法格式如下。

sp_databases 所返回的数据库名称可以作为 USE 语句的参数，

用来更改当前数据库上下文。 返回代码值为无。

【例 11.11 】以下示例显示如何执行 sp_databases。

EXEC sp_databasesGO

7. sp_tables 返回可在当前环境中查询的对象列表。这些对象

是可以在 FROM 子句中出现的任何对象。语法格式如下。

sp_tables [ [ @table_name = ] 'name' ] [ , [ @table_owner = ] 'owner' ] [ , [ @table_qualifier = ] 'qualifier' ] [ , [ @table_type = ] "'type'" ] [ , [@fUsePattern = ] 'fUsePattern']

【例 11.12 】返回可在 master 数据库中查询的对象列表。

USE master EXEC sp_tablesGO

【例 11.13 】返回有关 teaching 中的表的信息。以下示例返回有关 teaching 数据库中的 dbo 所拥有的表的信息。

USE teachingGOEXEC sp_tables @table_name = '%', @table_owner = 'dbo', @table_qualifier = 'teaching'GO

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 的默认表或视图可见性规则。

返回代码值为无。

【例 11.14 】以下示例返回指定表 course 的列信息。

USE teachingGOEXEC sp_columns @table_name = 'course', @table_owner = 'dbo'

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

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

11.1.5 实现存储过程

1. 使用 Transact-SQL 命令创建存储过程

10.2.1 创建存储过程

创建存储过程语句的语法格式如下：CREATE PROC[EDURE] procedure_name [; number ]

[{@parameter data_type }

[VARYING] [=default] [OUTPUT]] [, ...n ]

[WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [,...n ]

1 创建用户定义的存储过程

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

10.2 存储过程的创建与使用

2 ．使用图形工具创建存储过程

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

10.2.1 创建存储过程

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

10.2.1 创建存储过程

2 执行存储过程

执行存储过程的语法格式：[[EXEC[UTE]]

{[@return_status=]

procedure_name [;number]|@procedure_name_var} [[@parameter=]{value|@variable

[OUTPUT]|[DEFAULT]]

[ ,...n ]

[WITH RECOMPILE ]

10.2 存储过程的创建与使用

10.2.3 修改存储过程

1. 使用 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 ]

3 修改存储过程修改存储过程可以通过 Transact-SQL 语句和使用图形工具实现。

10.2 存储过程的创建与使用

2．使用 SQL Server 管理平台修改存储过程

10.2.3 修改存储过程

4 重新编译存储过程

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

10.2 存储过程的创建与使用

5 删除存储过程

存储过程可以被快速删除和重建，因为它没有存储数据。

DROP PROCEDURE 的语法如下：DROP PROCEDURE {procedure_name} [ ,...n ]

例如删除例 10-2 创建的存储过程 employee_dep ：DROP PROCEDURE employee_dep

GO

1. 使用 Transact-SQL 命令删除存储过程

10.2.4 删除存储过程

2．使用 SQL Server 管理平台删除存储过程

1) 连接到相应的 Microsoft SQL Server Database Engine 实例之后，在“对象资源管理器”中，单击服务器名称以展开服务器树。

2) 展开“数据库”，然后选择用户数据库，如teaching 。

3) 展开“可编程性”，右键单击其中要删除的存储过程如 getstudent2 ，然后单击 “删除”

触发器是一种特殊类型的存储过程。 触发器主要是通过事件进行触发而被执行的，而

存储过程可以通过过程名字直接调用。当对某一表进行 UPDATE、 INSERT、 DELETE 操作时，SQL Server就会自动执行触发器所定义的 SQL语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。

触发器的主要作用就是能够实现由主键和外键所不能保证的参照完整性和数据的一致性。

11.2 触发器概述

1. DML 触发器类型

AFTER 触发器INSTEAD OF 触发器CLR 触发器

11.2.1 DML 触发器

2. 触发器与约束的比较

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

11.2.1 DML 触发器

3. DML 触发器功能比较

11.2.1 DML 触发器

功能 AFTER 触发器 INSTEAD OF 触发器

适用范围 表 表和视图

每个表或视图包含触发器的数量

每 个 触 发 操 作 (UPDATE 、DELETE 和 INSERT)包含多个触发器

每 个 触 发 操 作 (UPDATE 、DELETE 和 INSERT)包含一个触发器

级联引用 无任何限制条件 不允许在作为级联参照完整性约束目标的表上使用 INSTEAD OF UPDATE和 DELETE触发器。

执行

晚于： 约束处理 声明性引用操作 创建插入的和删除的表 触发操作

早于：约束处理 替代：触发操作 晚于：创建插入的和删除的表

执行顺序 可指定第一个和最后一个执行 不适用

插入的和删除的表中的varchar(max)、nvarchar(max)和 varbinary(max)列引用。

允许 允许

插入的和删除的表中的text、ntext和 image列引用。

不允许 允许

4. 实现 DML 触发器

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

11.2.1 DML 触发器

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 ]

10.4 触发器的创建与使用

2．使用图形工具创建 DML 触发器

(4) 创建触发器

10.4.3 修改触发器

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

10.4 触发器的创建与使用

1. DML 触发器与 DDL 触发器的比较 DDL 触发器和 DML 触发器的用处不同。 DML 触发器在 INSERT、 UPDATE和 DELETE 语句上操作，

并且有助于在表或视图中修改数据时强制业务规则，扩展数据完整性。

DDL 触发器在 CREATE、 ALTER、 DROP 和其他 DDL 语句上操作。它们用于执行管理任务，并强制影响数据库的业务规则。它们应用于数据库或服务器中某一类型的所有命令。

可以使用相似的 Transact-SQL 语法创建、修改和删除 DML触发器和 DDL 触发器，它们还具有其他相似的行为。

11.2.2 DDL 触发器

10.4 触发器的创建与使用

1. DML 触发器与 DDL 触发器的比较 与 DML 触发器相同， DDL 触发器可以运行在

Microsoft .NET Framework 中创建的以及在 SQL Server中上载的程序集中打包的托管代码。

与 DML 触发器相同，可以为同一个 Transact-SQL 命令创建多个 DDL 触发器。同时， DDL 触发器和激发它的命令运行在相同的事务中。可从触发器中回滚此事务。严重错误可能会导致整个事务自动回滚。从批处理中运行并显式包含 ROLLBACK TRANSACTION 语句的 DDL 触发器将取消整个批处理。

11.2.2 DDL 触发器

10.4 触发器的创建与使用

2. 设计 DDL 触发器

(1) 确定触发器的作用域(2) 指定 Transact-SQL 语句或语句组

1)选择触发 DDL 触发器的特定 DDL 语句 2)选择触发 DDL 触发器的一组预定义的 DDL 语句

11.2.2 DDL 触发器

10.4 触发器的创建与使用

3. 实现 DDL 触发器

(1) 创建 DDL 触发器(2)修改 DDL 触发器(3)禁用和启用 DDL 触发器(4)删除 DDL 触发器

11.2.2 DDL 触发器

10.6 SQL Server的锁机制

本章小结（ 1）存储过程是一组 SQL 语句和流程控制语句的集合，以一个名字存储并作为一个单元处

理。存储过程用于完成某项任务，它可以接受参数、返回状态值和参数值，并且实现嵌套调用。（ 2）触发器就其本质而言是一种特殊的存储过程，有 3种类型：插入触发器、更新触发器

和删除触发器。当执行插入、删除、更新操作时，触发器将自动触发，以确保对数据的处理符合由触发器所定义的规则。使用触发器可以有效地检查数据的有效性和数据的完整性、一致性。（ 3）创建、删除、查看、修改存储过程和触发器可以使用 SQL Server 管理平台或

Transact-SQL 语句。存储过程可以由 CREATE PROCEDURE和 ALTER PROCEDURE 语句创建和修改。它必须先保存在服务器中，然后才能被 EXECUTE 语句执行，同时必须提供输入和输出参数。 Transact-SQL 支持按位置和按名称两种方法传递参数。触发器可以由 CREATE TRIGGER和 ALTER TRIGGER 语句创建和修改，因事件触发而被执行。（ 4）存储过程和触发器的各种信息的查看、修改还可以使用系统存储过程

sp_helptext、 sp_rename、 sp_helptrigger、 sp_depends 实现。（ 5）事务是一个操作序列，它包含了一组数据库操作命令，所有的命令作为一个整体一起

向系统提交或撤销操作请求，即要么都执行，要么都不执行。通常在程序中用 BEGIN TRANSACTION 语句来标识一个事务的开始，用 COMMIT TRANSACTION 语句标识事务结束。事务回滚是指当事务中的某一语句执行失败时，将对数据库的操作恢复到事务执行前或某个指定位置，事务回滚使用 ROLLBACK TRANSACTION 语句。（ 6）锁是在多用户环境下对资源访问的一种限制。当对一个数据源加锁后，此数据源就有

了一定的访问限制。（ 7）事务与锁也是保证数据完整性和正确性的机制，可以确保数据能够正确地被存储、修

改，而不会造成数据在存储或修改过程中因事故或其他用户的中断而导致的数据不完整。