第九章 －－ 存储过程与触发器
9.1 存储过程的综述
9.5 系统存储过程和扩展存储过程目录
9.2 创建、执行、修改、删除简单存储过程
9.3 创建和执行含参数的存储过程
9.4 存储过程的重新编译
9.6 案例中的存储过程
9.7 触发器综述
9.8 触发器的创建和执行
9.9 修改和删除触发器
9.10 嵌套触发器
9.11 案例中的触发器存储过程由一组预先编辑好的
SQL语句组成 。 将其放在服务器上,
由用户通过指定存储过程的名字来执行它 。 触发器是一种特殊类型的存储过程,它不是由用户直接调用,而是当用户对数据进行操作 （ 包含数据的
INSERT,UPDATE或 DELETE操作 ） 时自动执行 。
本章主要介绍存储过程和触发器的基本概念及其创建,修改和使用等存储过程是一种数据库对象,
是为了实现某个特定任务,将一组预编译的 SQL语句以一个存储单元的形式存储在服务器上,供用户调用 。 存储过程在第一次执行时进行编译,然后将编译好的代码保存在高速缓存中便于以后调用,这样可以提高代码的执行效率 。
操作方法 。
9.1 存储的过程综述
9.1.1 存储过程的概念
9.1.2 存储过程的类型在 SQL Server中存储过程可以分为五类 。 即系统存储过程,本地存储过程,临时存储过程,远程存储过程和扩展存储过程 。
9.2 创建、执行、修改、删除简单存储过程
9.2.1 创建存储过程
（ 1） 使用企业管理器创建存储过程
【 例 9.1】 在 Student数据库中,创建一个名称为
ST_CHAXUN_01的存储过程,该存储过程返回计算机系学生的姓名,性别,出生日期信息 。
其操作步骤如下：
（ 1） 打开企业管理器,展开控制台目录,
依次展开服务器组,服务器,数据库节点 。
（ 2） 单击相应的数据库 （ 这里我们选择
Student数据库 ） 。 在其右边的详细窗格中右键单击,存储过程,图标,在弹出的快捷菜单中选择,新建存储过程,命令 。
（ 3) 执行,新建存储过程,命令,打开,存储过程属性,对话框,如图 9.1所示 。 在其文本框中首先输入所有者和存储过程名称,例如所有者 DBO 替换 OWNER,用 过 程 名
ST_CHAXUN_01替换 PROCEDURE NAME。
(4) 在文本框的第二行输入存储过程文本，根据题意输入如下语句。
SELECT 姓名,性别,出生日期
FROM 学生
WHERE 系部代码＝ ’ 01?
(5) 输入完成后,单击,检查语法,按钮,
语法检查 是否正确 。
(6) 如果没有任何错误,单击,确定,按钮,
将存储过程保存到数据库中 。
（ 2）使用 SQL语句创建存储过程在查询分析器中,用 SQL语句创建存储过程的语法格式如下：
CREATE PROC [EDURE] procedure_name
[;number]
【 例 9.2】 在 Student数据库中,创建一个查询存储过程 ST_PRO_BJ,该存储过程将返回计算机系的班级名称 。 其程序清单如下：
USE student
GO
CREATE PROCEDURE ST_PRO_BJ
AS
SELECT 班级名称
FROM 班级,系部
WHERE 系部,系部代码 =班级,系部代码 and 系部,系部名称 ='计算机 '
GO
9.2.2 执行存储过程对存储在服务器上的存储过程,可以使用
EXECUTE命令或其名称执行它,其语法格式如下：
[ [ EXEC [ UTE ] ]
{
[@return_status =]
{ procedure_name [;number] | @
procedure_name_var
}
[[@parameter=]{value | @variable [OUTPUT] |
[DEFAULT]}
[,┅ n]
[WITH RECOMPLE]
【 例 9.３ 】 在查询分析器中执行 ST_PRO_BJ,其代码清单如下：
USE Student
EXECUTE ST_PROC_BJ
GO
其执行结果如下图所示 。
9.2.3 查看存储过程对用户建立的存储过程,可以使用企业管理器或有关的系统存储过程查看该存储过程的定义 。
（ 1） 使用企业管理器查看存储过程
（ 2） 使用系统存储过程查看存储过程
9.2.4 修改存储过程当存储过程所依赖的基本表发生变化或者根据需要,用户可以对存储过程的定义或者参数进行修改 。 更改通过执行 CREATE PROCEDURE
语句创建的过程,不会更改权限,也不影响相关的存储过程或触发器 。 修改存储过程可以使用 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 ]
【 例 9.５ 】 修改存储过程 ST_PRO_BJ,使该存储过程返回经济管理系的班级名称 。 其程序清单如下：
USE student
GO
ALTER PROC DBO.ST_PROC_BJ
AS
SELECT 班级名称
FROM 班级,系部
WHERE 系部,系部代码 =班级,系部代码 and 系部,
系部名称 ='经济管理系 '
GO
9.2.5 删除存储过程当存储过程不再需要时,可以使用企业管理器或 DROP PROCEDURE语句将其删除 。
(１ )使用企业管理器删除存储过程在企业管理器中,用鼠标右键单击要删除的存储过程,从弹出的快捷菜单中选择,删除,命令,将弹出,移除对象,对话框,在次对话框中,单击,全部移出,
按钮,删除该存储过程 。
(2)使用 DROP PROCEDURE语句删除存储过程
DROP PROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除,其语法格式如下：
DROP PROCEDURE 存储过程名 [,… n]
【 例 9.６ 】 删除存储过程 ST_CHAXUN_01,其程序清单如下：
USE student
GO
DROP PROCEDURE ST_CHAXUN_01
GO
9.3 创建含参数的存储过程在存储过程中使用参数,可以扩展存储过程的功能 。 使用输入参数,
可以将外部信息传到存储过程；使用输出参数,可以将存储过程内的信息传到外部 。
【 例 9.６ 】 在 Student数据库中,建立一个名为
XIBU_INFOR的存储过程,它带有一个参数,
用于接受系部代码,显示该系部名称和系主任信息 。 其程序清单如下：
USE student
GO
CREATE PROCEDURE XIBU_INFOR
@系部代码 CHAR (2)
AS
SELECT系部名称,系主任
FROM 系部
WHERE 系部代码 =@系部代码
GO
9.4 存储过程重新编译
1.在建立存储过程时设定重新编译其语法格式如下：
CREAT PROCEDURE procedure_name
WITH RECOMPLE
AS sql_statement
2.在执行存储过程时设定重新编译其语法格式如下：
EXECTUE procedure_name WITH RECOMPILE
3.通过使用系统存储过程设定重新编译其语法格式如下：
EXEC sp_recompile OBJECT
【 例 9.８ 】 利用 sp_recompile命令为存储过程
ST_PROC_BJ设定重编译标记 。
在查询分析器中执行代码：
EXEC sp_recompile ST_PROC_BJ
GO
运 行 后 提 示,,已 成 功 地 将 对 象 ’
ST_PROC_BJ?标记为重新编译 。,
在 SQL Server中有两类重要的存储过程：系统存储过程和扩展存储过程 。 这些存储过程为用户管理数据库,
获取系统信息,查看系统对象提供了很大的帮助 。 下面分别对两类存储过程做简单的介绍 。
9.5 存储过程与扩展存储过程
9.5.1 系统存储过程在 SQL Server中存在两百多个系统存储过程,这些系统存储过程的使用,使用户很容易地管理 SQL Server的数据库 。
在安装 SQL Server数据库系统时,系统存储过程被系统安装在 master数据库中,
并且初始化状态只有系统管理员拥有使用权 。 所有的系统存储过程名称都是以
sp_开头 。
【 例 9.9】 利用 sp_addgroup命令在当前数据库中建立一个角色 user_group。 执行如下代码：
USE master
GO
EXEC sp_addgroup user_group
【 例 9.10】 利用 sp_addlogin命令建立一个登录用户名为 user01。 执行如下代码：
USE master
GO
EXEC sp_addlogin user01
【 例 9.11】 利用 sp_addtype命令创建新的用户自定义数据库类型 user_date,该类型为 datetime
数据类型 。
执行如下代码：
EXEC sp_addtype user_date,datetime
运行结果为类型已添加 。
【 例 9.12】 使用 SP_monitor显示 CPU,I/O的使用信息 。
在查询分析器中输入如下代码：
USE master
GO
EXEC SP_monitor
GO
返回如下图所示的结果集，该结果报告了当时有关 SQL Server繁忙程度的信息。
9.5.2 扩展存储过程
【 例 9.13】 使用 sp_addextendproc存储过程将一个编写好的扩展存储过程 xp_userprint.dll注册到
SQL Server中 。
执行代码如下：
EXEC sp_addextendproc xp_userprint,?
xp_userprint.dll?
【 例 9.14】 使用存储过程 xp_dirtree返回本地操作系统的系统目录,C:\winnt”目录树 。
执行代码如下：
EXEC xp_dirtree,C:\winnt”
执行结果返回目录树 。
【 例 9.15】 利用扩展存储过程 xp_cmdshell为一个操作系统外壳执行指定命令串,并作为文本返回任何输出 。
执行代码如下：
EXEC master xp_cmdshell,dir *.exe”
GO
执行结果返回系统目录下的文件内容文本信息 。
9.6 案例中的存储过程
1,创建一个查询存储过程
USE student
GO
┄ 如果存储过程 TEACHER存在,将其删除
IF EXISTS(SELECT NAME FROME
SYSOBJECTS WHERE NANE=?TEACHER? AND
TYPE=?P?)
DROP PROCEDURE TEACHER
GO
┄ 建立一个查询存储过程
CREATE PROCEDURE TEACHER
┄ 查询选项
WITH ENCRYPTION
AS
SELECT 姓名,职称
FROM 教师,系部
WHERE 系部,系部代码 =教师,系部代码 and
系部,系部名称 ='计算机 '
GO
┄ 执行 TEACHER
EXEC TEACHER
GO
2,创建带输入参数的存储过程
USE student
GO
┄ 如果存储过程教师查询存在,将其删除
IF EXISTS(SELECT NAME FROME
SYSOBJECTS WHERE NANE=?教师查询 ’
AND TYPE=?P?)
DROP PROCEDURE教师查询
GO
┄ 创建一个带参数的存储过程 教师查询
CREATE PROCEDURE 教师查询
@XIBIE CHAR(8)
┄ 查询选项
WITH ENCRYPTION
AS
SELECT 教师,姓名,教师,职称,教师,职务
FROM 教师,系部
WHERE 系部,系部代码 =教师,系部代码 and 系部,
系部名称 =@XIBIE
ORDER BY 教师,教师编号
GO
┄ 执行存储过程,并向存储过程传递参数 。
EXEC教师查询 计算机
GO
3,创建带输出参数的存储过程
USE Student
GO
┄ 如果存储过程单科成绩分析存在,将其删除
IF EXISTS(SELECT NAME FROME
SYSOBJECTS WHERE NANE=? 单科成绩分析 ’
AND TYPE=?P?)
DROP PROCEDURE单科成绩分析
GO
┄ 创建存储过程 单科成绩分析
┄ 定义一个输入参数 KECHENGMING
┄ 定义三个输出参数 AVGCHENGJI,MAXCHENGJI
和 MINCHENGJI,用于接受平均成绩,最高成绩和最低成绩
CREATE PROCEDURE 单科成绩分析
@KECHENGMINGVARCHAR(20)
@AVGCHENGJI tinyint OUTPUT,
@MAXCHENGJI tinyint OUTPUT,
@MINCHENGJI tinyint OUTPUT,
AS
SELECT @AVGCHENGJI=AVG( 成绩 ),
@MAXCHENGJI=MAX (成绩 )，
@MINCHENGJI=MIN (成绩 )
FROM 课程注册
WHERE 课程号 (SELECT课程号 FROM 课程
WHERE 课程名 =@KECHENGMING)
GO
USE STUDENT
┄ 声明四个变量,用于保存输入和输出参数
DECLARE @KECHENGMING VARCHAR(20)
DECLARE @AVGCHENGJI tinyint OUTPUT
DECLARE @MAXCHENGJI tinyint OUTPUT
DECLARE @MINCHENGJI tinyint OUTPUT
┄ 为输出参数赋值
SELECT @KECHENGMING=?计算机 ’
┄ 执行存储过程
EXEC成绩查询 1 @KECHENGMING，
@AVGCHENGJI1 OUTPUT,
@MAXCHENGJI1 OUTPUT,
@MINCHENGJI1 OUTPUT
┄ 显示结果
SELECT @KECHENGMING AS 课程名,@
AVGCHENGJI1 AS 平均成绩,@ MAXCHENGJI 1
AS最高成绩,@ MINCHENGJI 1 AS最低成绩
GO
9.7 触发器综述
9.7.1 触发器的概念触发器是一种特殊类型的存储过程,它也是由 T-SQL语句组成,可以完成存储过程能完成的功能,但是它具有自己的显著的特点：它与表紧密相连,可以看做表定义的一部分；它不可能通过名称被直接调用,更不允许参数,而是当用户对表中的数据进行修改时,自动执行；它可以用于
SQL Server约束,默认值和规则的完整性检查,实施更为复杂的数据完整性约束 。
9.7.2 触发器的优点触发器包含复杂的处理逻辑,
能够实现复杂的完整性约束 。 同其他约束相比,它主要有以下优点：
（ 1） 触发器自动执行 。 在对表中的数据做了任何修改 （ 如手工输入或者通过应用程序实现的修改 ） 之后立即被激活 。
（ 2） 触发器能够对数据库中的相关表实现级联更改 。 触发器是基于一个表创建的,但是可以针对多个表进行操作,实现数据库中相关表的级联更改 。 例如,可以在产品表的产品编号字段上建立一个插入触发器,当对产品表增加记录时,在产品销售表的产品编号上自动插入产品编号值 。
(3)触发器可以实现比 CHECK约束更为复杂的数据完整性约束 。 在数据库中为了实现数据完整性约束,可以使用 CHECK约束或触发器 。 CHECK约束不允许引用其他表中的列来完成检查工作,而触发器可以引用其他表中的列 。 例如,在 Student
数据库中,向学生表中插入记录时,当输入系部代码时,必须先检查系部表中是否存在该系 。 这只能通过触发器实现,
而不能通过 CHECK约束完成 。
(4)触发器可以评估数据修改前后的表的状态,并根据其差异采取对策 。
(5)一个表中可以同时存在三个不同操作的触发器 （ INSERT,UPDATE或 DELETE）,
对于同一个修改语句可以有多个不同的对策以响应 。
9.7.3 触发器的种类
SQL Server 2000按触发被激活的时机可以分为两种类型,AFTER触发器和
INSTEAD OF触发器 。
AFTER触发器又称为后触发器,该类触发器是在引起触发器执行的修改语句成功完成之后执行 。 如果修改语句因错误
（ 如违反约束或语法错误 ） 而执行失败,
触发器将不会执行 。 此类触发器只能定义在表上,不能创建在视图上 。 可以为每个触发器操作 （ INSERT,UPDATE或 DELETE）
创建多个 AFTER触发器 。 如果表有多个
AFTER 触发器,可使用
sp_settriggerorder定义哪个 AFTER触发器最先激发,哪个最后激发 。 除第一个和最后一个触发器外,所有其他的 AFTER触发器的激发顺序不确定,并且无法控制 。
INSTEAD OF触发器又称为替代触发器,当引起触发器执行的修改语句停止时,该类触发器替代触发操作执行 。 该类触发器既可在表上定义,也可在视图上定义 。 对于每个该类触发操作 （ INSERT,UPDATE或
DELETE）,只能定义一个 INSTEAD OF触发器 。
9.8 触发器的创建和执行
（ 1）使用企业管理器创建触发器
9.8.1 创建触发器
（ 2）使用 SQL语句创建触发器
【 例 9.18】 在 Student数据库中,为,产品,表中 产 品 编 号 建 立 一 个 名 为 del_xiaoshou 的
DELETE触发器,其作用是当删除,产品,表中的记录时,同时删除,产品销售,表中与
,产品,表相关的记录 。 其程序清单如下：
USE Student
GO
CREATE TRIGGER del_xiaoshou
ON [dbo].[产品 ]
FOR DELETE
AS
DELETE 产品销售 WHETE 产品编号 IN
(SELECT 产品编号 ) FROM DELETED
GO
9.8.2 查看触发器
(1)使用系统存储过程查看触发器信息
【 例 9.19】 使用系统存储过程 sp_helptrigger查看,产品,表上存在的触发器的信息 。 其程序清单如下：
USE student
GO
EXEC SP_helptrigger 产品
GO
(2)使用企业管理器查看触发器信息
（ 1）使用系统存储过程修改触发器名称
9.9 修改和删除触发器
9.9.1 修改触发器对触发器进行重命名,可以使用系统存储过程 sp_rename来完成,其语法格式如下：
[EXECUTE] sp_rename 触发器原名,触发器新名
（ 2）使用企业管理器修改触发器文本使用企业管理器修改触发器的操作步骤与创建触发器相似,只不过在打开,触发器,
对话框后,从名称对话框中选择需要修改的触发器,然后对文本中的 SQL语句进行修改即可 。
修改完后,使用,语法检查,选项来验证语法是否正确 。 最后,单击,确定,按钮,完成触发器的修改 。
（ 3）使用 SQL语句修改触发器
【 例 9.21】 删除,产品,表中产品编号为,0001”
的记录,观察触发器 del_xiaoshou的作用 。 其程序清单如下：
USE student
GO
DELETE FROM 产品 WHERE 产品编号 ='0001'
GO
在查询分析器中运行,其结果如下图所示 。
9.9.3 删除触发器其语法格式为：
ALTER TABLE 表名
{ENABLE|DISABLE} 触发器名称
9.9.2 禁止或启用触发器
9.10 嵌套触发器
1.使用系统存储过程改变嵌套使用 sp_config系统存储过程设置是否允许嵌套的语法格式如下：
EXEC sp_configure?nested trigger?,0|1
2.使用企业管理器设置嵌套
9.11 案例中的触发器
1.创建一个 INSERT触发器
USE Student
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name=N?insert_xibu? AND type=?tr?)
DROP TREIGGER insert_xibu
GO
CREATE TRIGGER insert_xibu ON [dbo].[教师 ]
FOR INSERT
AS
DECLARE @XIBU CHAR(2)
SELECT @XIBU =教师,系部代码
FROM 教师,inserted
WHERE 教师,系部代码 = inserted.系部代码
IF @XIBU<>''
PRINT('记录插入成功 ')
ELSE
BEGIN
PRINT('系部代码不存在教师表中,不能插入记录,插入将终止 !')
ROLLBACK TRANSACTION
END
GO
2.创建一个 DELETE触发器
USE Student
GO
CREATE TRIGGER del_xibu ON 教师
FOR DELETE
AS
IF (SELECT COUNT(*) FROM 系部 INNER
JOIN DELETED
ON 教师,系部代码 =DELETED.系部代码 )>0
BEGIN
PRINT('该系部代码被系部表引用,你不可以删除此条记录,删除将终止 ')
ROLLBACK TRANSACTION
END
ELSE
PRINT('记录已删除 ')
GO
3.创建一个 UPDATE触发器
USE Student
GO
CREATE TRIGGER updatel_xibu ON
[dbo].[教师 ]
FOR UPDATE
AS
IF UPDATE(姓名 )
BEGIN
PRINT('不能修改系部名称 ')
ROLLBACK TRANSACTION
END
GO
练 习 题
1,什么是存储过程？ 存储过程有什么特点？
2,什么是触发器？ 触发器有什么特点？
3,使用触发器有那些优点？
4,触发器有几种类型？
5,创建存储过程有哪些方法？ 执行存储过程的命令是什么？ 用哪个命令可以删除存储过程？
6,查看存储过程和触发器信息的系统存储过程有哪些？
第九章结束！
返回主页
9.1 存储过程的综述
9.5 系统存储过程和扩展存储过程目录
9.2 创建、执行、修改、删除简单存储过程
9.3 创建和执行含参数的存储过程
9.4 存储过程的重新编译
9.6 案例中的存储过程
9.7 触发器综述
9.8 触发器的创建和执行
9.9 修改和删除触发器
9.10 嵌套触发器
9.11 案例中的触发器存储过程由一组预先编辑好的
SQL语句组成 。 将其放在服务器上,
由用户通过指定存储过程的名字来执行它 。 触发器是一种特殊类型的存储过程,它不是由用户直接调用,而是当用户对数据进行操作 （ 包含数据的
INSERT,UPDATE或 DELETE操作 ） 时自动执行 。
本章主要介绍存储过程和触发器的基本概念及其创建,修改和使用等存储过程是一种数据库对象,
是为了实现某个特定任务,将一组预编译的 SQL语句以一个存储单元的形式存储在服务器上,供用户调用 。 存储过程在第一次执行时进行编译,然后将编译好的代码保存在高速缓存中便于以后调用,这样可以提高代码的执行效率 。
操作方法 。
9.1 存储的过程综述
9.1.1 存储过程的概念
9.1.2 存储过程的类型在 SQL Server中存储过程可以分为五类 。 即系统存储过程,本地存储过程,临时存储过程,远程存储过程和扩展存储过程 。
9.2 创建、执行、修改、删除简单存储过程
9.2.1 创建存储过程
（ 1） 使用企业管理器创建存储过程
【 例 9.1】 在 Student数据库中,创建一个名称为
ST_CHAXUN_01的存储过程,该存储过程返回计算机系学生的姓名,性别,出生日期信息 。
其操作步骤如下：
（ 1） 打开企业管理器,展开控制台目录,
依次展开服务器组,服务器,数据库节点 。
（ 2） 单击相应的数据库 （ 这里我们选择
Student数据库 ） 。 在其右边的详细窗格中右键单击,存储过程,图标,在弹出的快捷菜单中选择,新建存储过程,命令 。
（ 3) 执行,新建存储过程,命令,打开,存储过程属性,对话框,如图 9.1所示 。 在其文本框中首先输入所有者和存储过程名称,例如所有者 DBO 替换 OWNER,用 过 程 名
ST_CHAXUN_01替换 PROCEDURE NAME。
(4) 在文本框的第二行输入存储过程文本，根据题意输入如下语句。
SELECT 姓名,性别,出生日期
FROM 学生
WHERE 系部代码＝ ’ 01?
(5) 输入完成后,单击,检查语法,按钮,
语法检查 是否正确 。
(6) 如果没有任何错误,单击,确定,按钮,
将存储过程保存到数据库中 。
（ 2）使用 SQL语句创建存储过程在查询分析器中,用 SQL语句创建存储过程的语法格式如下：
CREATE PROC [EDURE] procedure_name
[;number]
【 例 9.2】 在 Student数据库中,创建一个查询存储过程 ST_PRO_BJ,该存储过程将返回计算机系的班级名称 。 其程序清单如下：
USE student
GO
CREATE PROCEDURE ST_PRO_BJ
AS
SELECT 班级名称
FROM 班级,系部
WHERE 系部,系部代码 =班级,系部代码 and 系部,系部名称 ='计算机 '
GO
9.2.2 执行存储过程对存储在服务器上的存储过程,可以使用
EXECUTE命令或其名称执行它,其语法格式如下：
[ [ EXEC [ UTE ] ]
{
[@return_status =]
{ procedure_name [;number] | @
procedure_name_var
}
[[@parameter=]{value | @variable [OUTPUT] |
[DEFAULT]}
[,┅ n]
[WITH RECOMPLE]
【 例 9.３ 】 在查询分析器中执行 ST_PRO_BJ,其代码清单如下：
USE Student
EXECUTE ST_PROC_BJ
GO
其执行结果如下图所示 。
9.2.3 查看存储过程对用户建立的存储过程,可以使用企业管理器或有关的系统存储过程查看该存储过程的定义 。
（ 1） 使用企业管理器查看存储过程
（ 2） 使用系统存储过程查看存储过程
9.2.4 修改存储过程当存储过程所依赖的基本表发生变化或者根据需要,用户可以对存储过程的定义或者参数进行修改 。 更改通过执行 CREATE PROCEDURE
语句创建的过程,不会更改权限,也不影响相关的存储过程或触发器 。 修改存储过程可以使用 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 ]
【 例 9.５ 】 修改存储过程 ST_PRO_BJ,使该存储过程返回经济管理系的班级名称 。 其程序清单如下：
USE student
GO
ALTER PROC DBO.ST_PROC_BJ
AS
SELECT 班级名称
FROM 班级,系部
WHERE 系部,系部代码 =班级,系部代码 and 系部,
系部名称 ='经济管理系 '
GO
9.2.5 删除存储过程当存储过程不再需要时,可以使用企业管理器或 DROP PROCEDURE语句将其删除 。
(１ )使用企业管理器删除存储过程在企业管理器中,用鼠标右键单击要删除的存储过程,从弹出的快捷菜单中选择,删除,命令,将弹出,移除对象,对话框,在次对话框中,单击,全部移出,
按钮,删除该存储过程 。
(2)使用 DROP PROCEDURE语句删除存储过程
DROP PROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除,其语法格式如下：
DROP PROCEDURE 存储过程名 [,… n]
【 例 9.６ 】 删除存储过程 ST_CHAXUN_01,其程序清单如下：
USE student
GO
DROP PROCEDURE ST_CHAXUN_01
GO
9.3 创建含参数的存储过程在存储过程中使用参数,可以扩展存储过程的功能 。 使用输入参数,
可以将外部信息传到存储过程；使用输出参数,可以将存储过程内的信息传到外部 。
【 例 9.６ 】 在 Student数据库中,建立一个名为
XIBU_INFOR的存储过程,它带有一个参数,
用于接受系部代码,显示该系部名称和系主任信息 。 其程序清单如下：
USE student
GO
CREATE PROCEDURE XIBU_INFOR
@系部代码 CHAR (2)
AS
SELECT系部名称,系主任
FROM 系部
WHERE 系部代码 =@系部代码
GO
9.4 存储过程重新编译
1.在建立存储过程时设定重新编译其语法格式如下：
CREAT PROCEDURE procedure_name
WITH RECOMPLE
AS sql_statement
2.在执行存储过程时设定重新编译其语法格式如下：
EXECTUE procedure_name WITH RECOMPILE
3.通过使用系统存储过程设定重新编译其语法格式如下：
EXEC sp_recompile OBJECT
【 例 9.８ 】 利用 sp_recompile命令为存储过程
ST_PROC_BJ设定重编译标记 。
在查询分析器中执行代码：
EXEC sp_recompile ST_PROC_BJ
GO
运 行 后 提 示,,已 成 功 地 将 对 象 ’
ST_PROC_BJ?标记为重新编译 。,
在 SQL Server中有两类重要的存储过程：系统存储过程和扩展存储过程 。 这些存储过程为用户管理数据库,
获取系统信息,查看系统对象提供了很大的帮助 。 下面分别对两类存储过程做简单的介绍 。
9.5 存储过程与扩展存储过程
9.5.1 系统存储过程在 SQL Server中存在两百多个系统存储过程,这些系统存储过程的使用,使用户很容易地管理 SQL Server的数据库 。
在安装 SQL Server数据库系统时,系统存储过程被系统安装在 master数据库中,
并且初始化状态只有系统管理员拥有使用权 。 所有的系统存储过程名称都是以
sp_开头 。
【 例 9.9】 利用 sp_addgroup命令在当前数据库中建立一个角色 user_group。 执行如下代码：
USE master
GO
EXEC sp_addgroup user_group
【 例 9.10】 利用 sp_addlogin命令建立一个登录用户名为 user01。 执行如下代码：
USE master
GO
EXEC sp_addlogin user01
【 例 9.11】 利用 sp_addtype命令创建新的用户自定义数据库类型 user_date,该类型为 datetime
数据类型 。
执行如下代码：
EXEC sp_addtype user_date,datetime
运行结果为类型已添加 。
【 例 9.12】 使用 SP_monitor显示 CPU,I/O的使用信息 。
在查询分析器中输入如下代码：
USE master
GO
EXEC SP_monitor
GO
返回如下图所示的结果集，该结果报告了当时有关 SQL Server繁忙程度的信息。
9.5.2 扩展存储过程
【 例 9.13】 使用 sp_addextendproc存储过程将一个编写好的扩展存储过程 xp_userprint.dll注册到
SQL Server中 。
执行代码如下：
EXEC sp_addextendproc xp_userprint,?
xp_userprint.dll?
【 例 9.14】 使用存储过程 xp_dirtree返回本地操作系统的系统目录,C:\winnt”目录树 。
执行代码如下：
EXEC xp_dirtree,C:\winnt”
执行结果返回目录树 。
【 例 9.15】 利用扩展存储过程 xp_cmdshell为一个操作系统外壳执行指定命令串,并作为文本返回任何输出 。
执行代码如下：
EXEC master xp_cmdshell,dir *.exe”
GO
执行结果返回系统目录下的文件内容文本信息 。
9.6 案例中的存储过程
1,创建一个查询存储过程
USE student
GO
┄ 如果存储过程 TEACHER存在,将其删除
IF EXISTS(SELECT NAME FROME
SYSOBJECTS WHERE NANE=?TEACHER? AND
TYPE=?P?)
DROP PROCEDURE TEACHER
GO
┄ 建立一个查询存储过程
CREATE PROCEDURE TEACHER
┄ 查询选项
WITH ENCRYPTION
AS
SELECT 姓名,职称
FROM 教师,系部
WHERE 系部,系部代码 =教师,系部代码 and
系部,系部名称 ='计算机 '
GO
┄ 执行 TEACHER
EXEC TEACHER
GO
2,创建带输入参数的存储过程
USE student
GO
┄ 如果存储过程教师查询存在,将其删除
IF EXISTS(SELECT NAME FROME
SYSOBJECTS WHERE NANE=?教师查询 ’
AND TYPE=?P?)
DROP PROCEDURE教师查询
GO
┄ 创建一个带参数的存储过程 教师查询
CREATE PROCEDURE 教师查询
@XIBIE CHAR(8)
┄ 查询选项
WITH ENCRYPTION
AS
SELECT 教师,姓名,教师,职称,教师,职务
FROM 教师,系部
WHERE 系部,系部代码 =教师,系部代码 and 系部,
系部名称 =@XIBIE
ORDER BY 教师,教师编号
GO
┄ 执行存储过程,并向存储过程传递参数 。
EXEC教师查询 计算机
GO
3,创建带输出参数的存储过程
USE Student
GO
┄ 如果存储过程单科成绩分析存在,将其删除
IF EXISTS(SELECT NAME FROME
SYSOBJECTS WHERE NANE=? 单科成绩分析 ’
AND TYPE=?P?)
DROP PROCEDURE单科成绩分析
GO
┄ 创建存储过程 单科成绩分析
┄ 定义一个输入参数 KECHENGMING
┄ 定义三个输出参数 AVGCHENGJI,MAXCHENGJI
和 MINCHENGJI,用于接受平均成绩,最高成绩和最低成绩
CREATE PROCEDURE 单科成绩分析
@KECHENGMINGVARCHAR(20)
@AVGCHENGJI tinyint OUTPUT,
@MAXCHENGJI tinyint OUTPUT,
@MINCHENGJI tinyint OUTPUT,
AS
SELECT @AVGCHENGJI=AVG( 成绩 ),
@MAXCHENGJI=MAX (成绩 )，
@MINCHENGJI=MIN (成绩 )
FROM 课程注册
WHERE 课程号 (SELECT课程号 FROM 课程
WHERE 课程名 =@KECHENGMING)
GO
USE STUDENT
┄ 声明四个变量,用于保存输入和输出参数
DECLARE @KECHENGMING VARCHAR(20)
DECLARE @AVGCHENGJI tinyint OUTPUT
DECLARE @MAXCHENGJI tinyint OUTPUT
DECLARE @MINCHENGJI tinyint OUTPUT
┄ 为输出参数赋值
SELECT @KECHENGMING=?计算机 ’
┄ 执行存储过程
EXEC成绩查询 1 @KECHENGMING，
@AVGCHENGJI1 OUTPUT,
@MAXCHENGJI1 OUTPUT,
@MINCHENGJI1 OUTPUT
┄ 显示结果
SELECT @KECHENGMING AS 课程名,@
AVGCHENGJI1 AS 平均成绩,@ MAXCHENGJI 1
AS最高成绩,@ MINCHENGJI 1 AS最低成绩
GO
9.7 触发器综述
9.7.1 触发器的概念触发器是一种特殊类型的存储过程,它也是由 T-SQL语句组成,可以完成存储过程能完成的功能,但是它具有自己的显著的特点：它与表紧密相连,可以看做表定义的一部分；它不可能通过名称被直接调用,更不允许参数,而是当用户对表中的数据进行修改时,自动执行；它可以用于
SQL Server约束,默认值和规则的完整性检查,实施更为复杂的数据完整性约束 。
9.7.2 触发器的优点触发器包含复杂的处理逻辑,
能够实现复杂的完整性约束 。 同其他约束相比,它主要有以下优点：
（ 1） 触发器自动执行 。 在对表中的数据做了任何修改 （ 如手工输入或者通过应用程序实现的修改 ） 之后立即被激活 。
（ 2） 触发器能够对数据库中的相关表实现级联更改 。 触发器是基于一个表创建的,但是可以针对多个表进行操作,实现数据库中相关表的级联更改 。 例如,可以在产品表的产品编号字段上建立一个插入触发器,当对产品表增加记录时,在产品销售表的产品编号上自动插入产品编号值 。
(3)触发器可以实现比 CHECK约束更为复杂的数据完整性约束 。 在数据库中为了实现数据完整性约束,可以使用 CHECK约束或触发器 。 CHECK约束不允许引用其他表中的列来完成检查工作,而触发器可以引用其他表中的列 。 例如,在 Student
数据库中,向学生表中插入记录时,当输入系部代码时,必须先检查系部表中是否存在该系 。 这只能通过触发器实现,
而不能通过 CHECK约束完成 。
(4)触发器可以评估数据修改前后的表的状态,并根据其差异采取对策 。
(5)一个表中可以同时存在三个不同操作的触发器 （ INSERT,UPDATE或 DELETE）,
对于同一个修改语句可以有多个不同的对策以响应 。
9.7.3 触发器的种类
SQL Server 2000按触发被激活的时机可以分为两种类型,AFTER触发器和
INSTEAD OF触发器 。
AFTER触发器又称为后触发器,该类触发器是在引起触发器执行的修改语句成功完成之后执行 。 如果修改语句因错误
（ 如违反约束或语法错误 ） 而执行失败,
触发器将不会执行 。 此类触发器只能定义在表上,不能创建在视图上 。 可以为每个触发器操作 （ INSERT,UPDATE或 DELETE）
创建多个 AFTER触发器 。 如果表有多个
AFTER 触发器,可使用
sp_settriggerorder定义哪个 AFTER触发器最先激发,哪个最后激发 。 除第一个和最后一个触发器外,所有其他的 AFTER触发器的激发顺序不确定,并且无法控制 。
INSTEAD OF触发器又称为替代触发器,当引起触发器执行的修改语句停止时,该类触发器替代触发操作执行 。 该类触发器既可在表上定义,也可在视图上定义 。 对于每个该类触发操作 （ INSERT,UPDATE或
DELETE）,只能定义一个 INSTEAD OF触发器 。
9.8 触发器的创建和执行
（ 1）使用企业管理器创建触发器
9.8.1 创建触发器
（ 2）使用 SQL语句创建触发器
【 例 9.18】 在 Student数据库中,为,产品,表中 产 品 编 号 建 立 一 个 名 为 del_xiaoshou 的
DELETE触发器,其作用是当删除,产品,表中的记录时,同时删除,产品销售,表中与
,产品,表相关的记录 。 其程序清单如下：
USE Student
GO
CREATE TRIGGER del_xiaoshou
ON [dbo].[产品 ]
FOR DELETE
AS
DELETE 产品销售 WHETE 产品编号 IN
(SELECT 产品编号 ) FROM DELETED
GO
9.8.2 查看触发器
(1)使用系统存储过程查看触发器信息
【 例 9.19】 使用系统存储过程 sp_helptrigger查看,产品,表上存在的触发器的信息 。 其程序清单如下：
USE student
GO
EXEC SP_helptrigger 产品
GO
(2)使用企业管理器查看触发器信息
（ 1）使用系统存储过程修改触发器名称
9.9 修改和删除触发器
9.9.1 修改触发器对触发器进行重命名,可以使用系统存储过程 sp_rename来完成,其语法格式如下：
[EXECUTE] sp_rename 触发器原名,触发器新名
（ 2）使用企业管理器修改触发器文本使用企业管理器修改触发器的操作步骤与创建触发器相似,只不过在打开,触发器,
对话框后,从名称对话框中选择需要修改的触发器,然后对文本中的 SQL语句进行修改即可 。
修改完后,使用,语法检查,选项来验证语法是否正确 。 最后,单击,确定,按钮,完成触发器的修改 。
（ 3）使用 SQL语句修改触发器
【 例 9.21】 删除,产品,表中产品编号为,0001”
的记录,观察触发器 del_xiaoshou的作用 。 其程序清单如下：
USE student
GO
DELETE FROM 产品 WHERE 产品编号 ='0001'
GO
在查询分析器中运行,其结果如下图所示 。
9.9.3 删除触发器其语法格式为：
ALTER TABLE 表名
{ENABLE|DISABLE} 触发器名称
9.9.2 禁止或启用触发器
9.10 嵌套触发器
1.使用系统存储过程改变嵌套使用 sp_config系统存储过程设置是否允许嵌套的语法格式如下：
EXEC sp_configure?nested trigger?,0|1
2.使用企业管理器设置嵌套
9.11 案例中的触发器
1.创建一个 INSERT触发器
USE Student
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name=N?insert_xibu? AND type=?tr?)
DROP TREIGGER insert_xibu
GO
CREATE TRIGGER insert_xibu ON [dbo].[教师 ]
FOR INSERT
AS
DECLARE @XIBU CHAR(2)
SELECT @XIBU =教师,系部代码
FROM 教师,inserted
WHERE 教师,系部代码 = inserted.系部代码
IF @XIBU<>''
PRINT('记录插入成功 ')
ELSE
BEGIN
PRINT('系部代码不存在教师表中,不能插入记录,插入将终止 !')
ROLLBACK TRANSACTION
END
GO
2.创建一个 DELETE触发器
USE Student
GO
CREATE TRIGGER del_xibu ON 教师
FOR DELETE
AS
IF (SELECT COUNT(*) FROM 系部 INNER
JOIN DELETED
ON 教师,系部代码 =DELETED.系部代码 )>0
BEGIN
PRINT('该系部代码被系部表引用,你不可以删除此条记录,删除将终止 ')
ROLLBACK TRANSACTION
END
ELSE
PRINT('记录已删除 ')
GO
3.创建一个 UPDATE触发器
USE Student
GO
CREATE TRIGGER updatel_xibu ON
[dbo].[教师 ]
FOR UPDATE
AS
IF UPDATE(姓名 )
BEGIN
PRINT('不能修改系部名称 ')
ROLLBACK TRANSACTION
END
GO
练 习 题
1,什么是存储过程？ 存储过程有什么特点？
2,什么是触发器？ 触发器有什么特点？
3,使用触发器有那些优点？
4,触发器有几种类型？
5,创建存储过程有哪些方法？ 执行存储过程的命令是什么？ 用哪个命令可以删除存储过程？
6,查看存储过程和触发器信息的系统存储过程有哪些？
第九章结束！
返回主页
