第9章 用户定义函数、存储过程与触发器知识技能目标: 1．理解用户定义函数、存储过程、触发器的概念 和用途2．掌握自定义函数
的创建、使用、修改和删除3．掌握存储过程的创建、使用、修改和删除4．掌握触发器的创建、使用、修改和删除 引导案例：
在教务管理系统中，有些数据操作经常发生。例如，经常需要根据姓名查询一个学生的所有选修课的信息；学生表中的学生信息一有增减，就需要
自动修改班级表中的班级人数。在高级语言中，需要重复执行的代码可以设计为函数或者过程。同样，在数据库系统中，可以通过用户定义函数、存
储过程、触发器等技术来实现上述功能。本章介绍用户定义函数、存储过程、触发器等技术。 第9章 用户定义函数、存储过程与触发器 9.1
用户定义函数 9.2 存储过程 9.3 触发器 小结 第9章 用户定义函数、存储过程与触发器9.1 用户定义函数9.1.
1 用户定义函数的概念及分类 在SQL Server 中，除了系统内置的函数外，用户可以在数据库中自己定义函数。
用户自定义函数是由一个或多个T_SQL语句组成的子程序，它可以接受参数、执行操作，并将操作结果以值的形式返回。返回目录
SQL Server的用户定义函数分为两类：标量值函数、表值函数。表值函数又分为内嵌表值函数和多语句表值函数。9.1 用户定
义函数 在SQL Server中，除了系统内置的函数外，用户可以在数据库中自己定义函数。 用户定义函数是由一个或多个T_SQL语句
组成的子程序，它可以接受参数、执行操作，并将操作结果以值的形式返回。 SQL Server的用户定义函数分为两类：标量值函数和表值
函数。 表值函数又分为内嵌表值函数和多语句表值函数。 这里只介绍标量值函数和内嵌表值函数。 9.1.1 用户定义函数的概念及分
类 返回目录9.1 用户定义函数 在SQL Server 中使用用户定义函数有以下优点：（1）允许模块化程序设计
函数存储在数据库中，只需创建一次，以后便可以在程序中调用任意次。用户定义函数可以独立于程序源代码进行修改。 （2）执行速度更快
T-SQL 用户定义函数通过缓存计划并在重复执行时重用它来降低 T-SQL 代码的编译开销。因此每次使用用户定义函数时均无须重
新解析和重新优化，从而缩短了执行时间。（3）减少网络流量 某些约束比较复杂，无法用单一标量的表达式表示，此时可以表示为函数，在
WHERE 子句中调用，以减少发送至客户端的数据量。 9.1.1 用户定义函数的概念及分类 返回目录9.1 用户定
义函数9.1.2 创建和使用标量值用户定义函数 标量值用户定义函数返回单个数据值，即标量值。返回类型可以是除
text、ntext、image、cursor 和 timestamp 外的任何数据类型。 返回目录9.1 用户定义函数9.1.
2 创建和使用标量值用户定义函数 1．创建标量值用户定义函数（1）用T_SQL语句创建语法格式：CREATE FUNCTION
函数名( [ [@参数名 AS ]参数类型 [，…n ] ] ) RETURNS
返回值类型 BEGIN 函数
体??????? ?RETURN 标量值表达式 E
ND【例9.1】统计某专业男女生同学的比例。返回目录9.1 用户定义函数9.1.2 创建和使用标量值用户定义函数 1．创建标量
值用户定义函数（2）用SQL Server Management Studio创建1）打开SQL Server Managemen
t Studio；2）在对象资源管理器中展开要建创建用户定义函数的数据库；3）依次展开数据库下“可编程性”、“函数”、“标量值函数
”；4）在“标量值函数”结点上单击右键，选择“新建标量值函数”；5) 在随后打开的通用模板中已经给出了创建标量值函数所需的语句的基
本格式。修改其中的语句为需要的语句；6)单击“分析”按钮，检查语法是否正确；7)单击 “执行”按钮，执行代码。返回目录9.1 用户
定义函数9.1.2 创建和使用标量值用户定义函数 2．标量值用户自定义函数的使用 可以在使用标量表达式的位置调用标量
值函数，包括计算列和 CHECK 约束定义中。 【例9.2】查询“计算机应用技术”专业男女生的比例。返回目录9.1 用户定义函数
9.1.3 创建和使用表值用户定义函数 内嵌表值用户定义函数返回一个SELECT 语句查询的结果集，是table数据类型。
1．创建内嵌表值函数（1）用T_SQL语句创建 语法格式： CREATE FUNCTION 函数名( [ [@参数名 AS
]参数类型 [，…n ] ] ) RETURNS TABLE?????? RETURN 查
询语句 【例9.3】创建用户定义函数，其功能是可用于查看某班某课程的成绩单。返回目录9.1 用户定义函数9.1.3 创建和使用表值
用户定义函数 1．创建内嵌表值函数（2）用SQL Server Management Studio创建 1）打开SQL Ser
ver Management Studio。 2）在对象资源管理器中展开要创建用户定义函数的数据库，如 “教务管理数据库”。
3）依次展开数据库下“可编程性”、“函数”、“表值函数” 节点。 4）在“表值函数”结点上单击右键，选择“新建
内联表值函 数”，将打开一个通用模板。 5）模板已经给出了创建标量值函数所需的语句的基本格式。修 改其中的
语句为需要的语句。 6）单击“分析”按钮，检查语法是否正确。 7）单击 “执行”按钮，完成。返回目录9.1 用户定义函数9
.1.3 创建和使用表值用户定义函数 2．调用内嵌表值函数 内嵌表值函数在 SELECT、INSERT、UPDATE 或 DEL
ETE 语句的FROM 子句中调用。在调用函数的时候要指明函数的拥有者。【例9.4】 查 “计算机应用班071班”的“高等数学”课
程的成绩单。返回目录9.1 用户定义函数9.1.4 查看用户定义函数1．用T-SQL语句查看用户定义函数 可以通过使用一些
系统函数和系统存储过程来查看用户定义函数。（1）查看函数的定义 查看函数的定义可使用OBJECT_DEFINITION 和sp_h
elptext。 【例9.5】查看用户定义函数“某班某课成绩单”的定义 【例9.6】查看用户定义函数“某班某课成绩单”的定义
（2）获得有关函数的信息 获得有关函数的信息（例如函数的创建时间、参数等）可使用sp_help。 【例9.7】查看用户定义函数
“某班某课成绩单”的信息返回目录9.1 用户定义函数9.1.4 查看用户定义函数1．用T-SQL语句查看用户定义函数（3）查看用户
定义函数的依赖关系 查看用户定义函数的依赖关系可使用sp_depends 【例9.8】查看用户定义函数“某班某课成绩单”的依赖
关系返回目录9.1 用户定义函数9.1.4 查看用户定义函数2．用SQL Server Management Studio查看用户
定义函数 1）打开SQL Server Management Studio。 2）在对象资源管理器中展开要查看的数据库，如“教
务管理数据库”。 3）依次展开数据库下“可编程性”、“函数”节点。 4）在“函数”节点下，展开“表值函数”或“标量值函数”，找
到需要查看信息的函数，并在函数名上单击右键，选择 “查看依赖关系”，查看与函数有依赖关系的对象的信息；选择“编写函数脚本为”-“c
reate到”-“新建查询编辑窗口”查看函数源代码。返回目录9.1 用户定义函数9.1.5 修改用户定义函数 1．用T_SQL语句
修改用户定义函数 使用ALTER FUNCTION语句,与CREATE FUNCTION语句类似。 【例9.9
】修改“某专业男女比例”函数，将比值的小数位设置为1位。返回目录9.1 用户定义函数9.1.5 修改用户定义函数 2. 用SQL
Server Management Studio修改自定义函数 用SQL Server Management Studio修改自
定义函数的方法和查看函数信息的方法类似，只需在第4步中函数名上单击右键时，在快捷菜单中选择“修改”即可打开函数脚本进行修改，最后选
择“执行”按钮。返回目录9.1 用户定义函数9.1.6 删除用户定义函数 1．用T_SQL语句删除用户定义函数 语法格式：DRO
P FUNCTION 函数名[ ,...n ] 【例9.10】将自定义函数“某专业男女比例”删除。2．用SQL Server Ma
nagement Studio删除自定义函数 用SQL Server Management Studio删除自定义函数的方法和查
看函数信息的方法类似，只需在第4步中函数名上单击右键时，在快捷菜单中选择“删除”命令,并在弹出的“删除对象”窗口中选择“确定”按钮
。返回目录9.2 存储过程 9.2.1 存储过程的概念 存储过程是存储在SQL Server服务器中的一组预编译过的T-SQL语
句组成的模块，用来完成某项特定的功能。 存储过程可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。 存储过程分
为用户定义的存储过程和系统存储过程。系统存储过程由系统自带并存储在master数据库中，带有 sp_ 前缀，在任何数据库中都可以调
用。系统存储过程功能强大，种类繁多，几乎可以满足各类数据库管理需求。用户定义的存储过程是由用户创建的，必须在所属的数据库中才能被执
行。 返回目录9.2 存储过程 9.2.2 存储过程的优点 （1）执行速度快存储过程是预先编译好放在数据库内的，减少编译语句所花费
的时间。（2）提高系统安全性通过为用户授权，使用户必须通过执行存储过程来访问数据，而不能直接访问存储过程中引用的对象，从而提高了数
据安全性。（3）程序设计模块化存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性。 XI（4）减少网络通
信流量一个需要数百行 T-SQL 代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。返回目录9.2
存储过程 9.2.3 创建存储过程 1．用T_SQL语句创建存储过程 语法格式： CREATE PROC[EDURE] 存储过
程名 ??? [@参数 数据类型 [ OUTPUT] [ ,...n ] ]
AS BEGIN SQL语句 [...n
] END 说明： 存储过程可以带参数，也可以不带参数，参数可以是输入参数，也可以是输出参数
，输出参数用OUTPUT指明。返回目录9.2 存储过程 9.2.3 创建存储过程 1．用T_SQL语句创建存储过程【例9.11】创
建“学生名单”存储过程，其功能是输出所有学生的名单【例9.12】创建“某班学生名单”存储过程，其功能是查看某个班级的学生名单，结果
按学号排序。 【例9.13】创建“查询学生”存储过程，其功能是根据学号查询学生的姓名和年龄。返回目录9.2 存储过程 9.2.3
创建存储过程 2．用SQL Server Management Studio创建存储过程 1）打开SQL Server Mana
gement Studio。 2）在对象资源管理器中展开要创建存储过程的数据库，如“教务管理数据库”。 3）依次展开数据库下“
可编程性”-“存储过程”。 4）在“存储过程”节点单击右键，选择“新建存储过程”，将会自动打开一个模板。 5）模板已经给出了创
建存储过程所需的语句的基本格式。修改其中的语句为需要的语句。 6）单击“分析”按钮，检查语法是否正确。 7）单击 “执行”按钮
，完成。返回目录9.2 存储过程 9.2.4 执行存储过程 语法格式： [ EXECUTE ]??存储过程名 [ [ @参数 =
] 值| @变量名 [ OUTPUT ] ]
???[ ,...n ]说明：如果对存储过程的调用是批处理的第一条语句，则可以直接使用存储过程的名字调用这个存储过程，但如果对存储
过程的调用不是批处理的第一条语句，则不能直接使用存储过程的名字调用这个存储过程，要用EXEC(UTE)关键字。存储过程如果有参数，
调用的时候需要传递参数，如果是输出参数，还需要加OUTPUT关键字。返回目录9.2 存储过程 9.2.4 执行存储过程 【例9.1
4】查看学校学生名单。 【例9.15】查看计算机应用071班学生名单。 【例9.16】查看学号为“20070101101”的学生的
姓名和年龄。返回目录9.2 存储过程 9.2.5 查看存储过程 1．用T-SQL语句查看存储过程 通过使用系统存储过程来
查看。 （1）使用sp_helptext查看存储过程的定义 例如：EXEC sp_helptext ''dbo.
某班学生名单‘ （2）使用sp_help查看有关存储过程的信息 例如：EXEC sp_help ''dbo.某班
学生名单‘ （3）使用sp_depends查看存储过程的依赖关系 例如：EXEC sp_depends ''dbo
.某班学生名单''返回目录9.2 存储过程 9.2.5 查看存储过程 2. 用SQL Server Management Stud
io查看存储过程 1）打开SQL Server Management Studio。 2）在对象资源管理器中展开要查看
的数据库，如“教务管理数 据库”。 3）依次展开数据库下“可编程性”-“存储过程”。 4）在要查看信息的存
储过程的名字上单击右键，选择 “查看依 赖关系”，查看与存储过程有依赖关系的对象的信息；选择 “编写存储过程
脚本为”-“create到”-“新建查询编辑窗口” 查看存储过程源代码。返回目录9.2 存储过程 9.2.6 修改存储
过程 1．用T_SQL语句修改存储过程 语法格式： ALTER PROC[EDURE] 存储过程名 ??
??[@参数 数据类型 [ OUTPUT] [ ,...n ] ] AS
BEGIN SQL语句 [...n ]
END【例9.17】修改“学生名单”存储过程，结果按学号排序。返回目录9.2 存储过程 9.2.6 修改存
储过程 2. 用SQL Server Management Studio修改存储过程 用SQL Server Managemen
t Studio修改存储过程的方法和查看存储过程信息的方法类似，只需在第4步中存储过程名上单击右键时，在快捷菜单中选择“修改”即可
打开存储过程脚本进行修改，最后选择“执行”按钮。返回目录9.2 存储过程 9.2.7 删除存储过程 1．用T_SQL语句删除存储过
程 语法格式： DROP PROCEDURE 存储过程名[ ,...n ] 【例9.18】将存储过程“学生名单”删除。2．用S
QL Server Management Studio删除存储过程 用SQL Server Management Studio
删除存储过程的方法和查看存储过程信息的方法类似，只需在第4步中存储过程名上单击右键时，在快捷菜单中选择“删除”命令,并在弹出的“删
除对象”窗口中选择“确定”按钮。返回目录9.3 触发器 9.3.1 触发器的概念及分类 触发器是一种特殊的存储过程，它不带任何
的输入输出参数，不能被显式调用。触发器与特定的SQL Server对象（如表）相关联，在这些对象的指定操作发生时，服务器自动执行该
触发器。 SQL Server2005 包括两大类触发器：1) DDL 触发器。当服务器或数据库中发生数据定义语言 (DDL) 事
件（CREATE、ALTER、DROP）时将调用DDL触发器。利用DDL触发器可以限制数据库中未经许可的更新和变化。2) DML
触发器。当数据库中发生数据操作语言 (DML) 事件，即在指定表或视图中发生INSERT、UPDATE或 DELETE操作时，将调
用 DML 触发器。DML 触发器经常用于强制执行业务规则和数据完整性。这里只介绍DML触发器。 返回目录9.3 触发器 9.3
.1 触发器的概念及分类DML触发器建立在表或者视图上根据触发器被执行的时机，分为AFTER触发器和INSTEAD OF触发器。
A. AFTER 指定触发器仅在触发操作（INSETRT、UPDATE或DELETE操作）及检查约束都已成功执行后才被激发；一
个表可以定义多个AFTER类型的触发器，但不能对视图定义AFTER触发器。 B. INSTEAD OF 指定触发器是“代替”引
起触发器执行的语句（INSETRT、UPDATE或DELETE操作）的。对于表或视图，每个 INSERT、UPDATE 或 DEL
ETE 语句最多可定义一个 INSTEAD OF 触发器。根据DML触发器所指定的数据修改语句，可以分为INSETRT触发器、UP
DATE触发器、DELETE触发器，它们分别由INSETRT语句、UPDATE语句、DELETE语句触发。返回目录9.3 触发器
9.3.1 触发器的概念及分类DML 触发器在以下方面非常有用： 级联操作：DML 触发器可通过数据库中的相关表实现级联更改。强
化约束：DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK约束更强
的限制，因为DML 触发器可以引用其他表中的列。 审核变化：DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。多响
应：一个表中的多个同类 DML 触发器（INSERT、UPDATE 或 DELETE）允许采取不同的操作来响应同一个修改语句。返回
目录9.3 触发器 9.3.2 创建触发器 1．用T_SQL语句创建触发器 语法格式： CREATE TRIG
GER 触发器名 ON 表名 | 视图名 { FOR | AF
TER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ]
[ , ] [ DELETE ] } AS SQL
_语句 [ ...n ] 返回目录9.3 触发器 9.3.2 创建触发器 1．用T_SQL语句创建触发器说明：1）可在触发器的主
体中使用 UPDATE( ) 测试对某个列的修改。2）在触发器中有两个临时表inserted、deleted可以访问。delete
d表用于存储被DELETE 或UPDATE 语句删除或更新前的行的副本；inserted表用于存储被INSERT或UPDATE语句
插入或更新后的行的副本。即旧行被复制到deleted表中，新行被复制到inserted表中。返回目录9.3 触发器 9.3.2
创建触发器 1．用T_SQL语句创建触发器【例9.19】对学生表创建插入触发器，当有一条新的记录插入表中，就更新班级表中相应班级的
人数。 触发器建立后，执行以下语句，并观察学生表、班级表的变化： INSERT INTO 学生表(学号,姓名,班级) VA
LUES (''20070101150'',''张三'',''jy071'')【例9.20】对学生表创建删除触发器，当有一条记录被删除，就减少
班级表中相应班级的人数。触发器建立后，执行以下语句，并观察学生表、班级表的变化： DELETE 学生表 WHERE 学号=''200
70101150''返回目录9.3 触发器 9.3.2 创建触发器 1．用T_SQL语句创建触发器【例9.21】对学生表创建更新触
发器，当有一条记录被更新，就修改班级表中相关班级的人数。 触发器建立后，执行以下语句，并观察学生表、班级表的变化： UP
DATE 学生表 SET 班级=''jy081'' WHERE 学号=''20070101112'' 返回目录9.3 触发器 9.3
.2 创建触发器 2. 用SQL Server Management Studio创建触发器1）打开SQL Server Mana
gement Studio。2）在对象资源管理器中展开要创建触发器的数据库，如“教务管理 数据库”。3）展开数据库下“表”节点
。4）展开要创建触发器的表。5) 在“触发器”项上，单击右键，选则“新建触发器”， 将会自 动打开一个模板。6）模板已经给出了
创建触发器所需的语句的基本格式。修改其中的 语句为需要的语句。7）单击“分析”按钮，检查语法是否正确。8）单击 “执行”按钮，
完成。返回目录9.3 触发器 9.3.3 查看触发器 1．用T-SQL语句查看触发器 （1）使用sp_helptext查看触
发器的定义 例如：EXEC sp_helptext ''更新学生‘ （2）使用sp_help查看有关触发器的信息
例如：EXEC sp_help ''更新学生‘ （3）使用sp_depends查看触发器的依赖关系 例如：EXE
C sp_depends ''更新学生''返回目录9.3 触发器 9.3.3 查看触发器 2．用SQL Server Managem
ent Studio查看触发器 用SQL Server Management Studio查看触发器的方法和建立触发器类似，只
需在第５步，展开“触发器”后，在要查看的触发器名字上单击右键，选择 “查看依赖关系”，查看与触发器有依赖关系的对象的信息；选择“编
写触发器脚本为”-“create到”-“新建查询编辑窗口”查看函数源代码。返回目录9.3 触发器 9.3.4 修改触发器 1．用
T-SQL语句修改触发器 语法格式： ALTER TRIGGER 触发器名 ON 表名 | 视图名
{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [
, ] [ UPDATE ] [ , ] [ DELETE ] }
AS SQL_语句 [ ...n ] 2．用SQL Server Ma
nagement Studio修改触发器 用SQL Server Management Studio修改触发器的方法和查看触发器
信息的方法类似，只需在第5步中触发器名上单击右键时，在快捷菜单中选择“修改”即可打开触发器脚本进行修改，最后选择“执行”按钮。返回
目录9.3 触发器 9.3.5 删除触发器 1．T_SQL语句删除触发器 语法格式： DROP TRIGGER
触发器名 [ ,...n ] 【例9.22】 将触发器“更新学生”删除。2．用SQL Server Management S
tudio删除触发器 用SQL Server Management Studio删除触发器的方法和查看触发器信息的方法类似，只
需在第5步中触发器名上单击右键时，在快捷菜单中选择“删除”命令,并在弹出的“删除对象”窗口中选择“确定”按钮。返回目录9.3 触发器 9.3.5 使用触发器的注意事项 1）触发器只能在当前的数据库中创建。2）CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用于 一个表。3）如果一个表的外键在定义时包含 DELETE/UPDATE 操作的级联，则 不能为表定义 INSTEAD OF DELETE/UPDATE 触发器。4）DELETE 触发器不能捕获 TRUNCATE TABLE 语句。5）在 DML 触发器中不允许使用下列 Transact-SQL 语句：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、LOAD DATABASE、 LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。6）如果一个表上同时有约束和触发器，约束优先检查，如果触发器 的操作与约束冲突，则触发器将不执行。7）SQL Server 不支持针对系统表的用户定义触发器，因此不要为系 统表创建用户定义触发器。返回目录小结 本章主要介绍了用户定义函数、存储过程与触发器。它们都是一组T_SQL语句的集合，可以完成一个独立的功能，可以被多次调用。用户定义函数是用来扩充内置函数的，具有返回值；存储过程可以由用户直接调用，减少网络流量，加快执行速度；触发器是一种特殊的存储过程，由某些活动触发而自动执行。 其中存储过程和触发器功能强大，使用灵活，是设计和开发数据库应用程序必不可少的工具。返回目录 |
|
