第九章 存储过程与触发器

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

本章要点本章要点

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

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

9.1 9.1 存储过程概述存储过程概述

在使用 Transact-SQL 语言编程的过程中，我们可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由 SQL Server 服务器通过过程名来调用它们，这些过程就叫做存储过程（也称为子程序）。

存储过程在第一次执行时进行语法检查和编译，编译好的版本存储在高速缓存中，用于后续调用，下次调用时可以直接执行。

存储过程的特点：存储过程的特点： 存储过程可以接受输入参数，并且可以用输出参数的形式返回值。存储过程可以包含复杂的流控制语句。

存储过程建立好以后可以被反复调用。一个存储过程可以调用另一个存储过程，嵌套调用可多达 32 级。

由于存储过程在建立和第一次执行的时候被解析、优化、编译和缓存（驻留在高速缓冲存储器中），因此其执行速度比直接向 SQL Server 发送 T-SQL 语句快。

存储过程的执行是通过一条语句来完成的，避免了网络上服务器和客户机之间大量 Transact-SQL 语句的传送，降低了网络的通信量。

存储过程可以被看作一种安全机制，即使用户没有对表或视图的访问权限，他们也可以被授予执行对表或视图进行访问的存储过程的权限。

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

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

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

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

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

1. 使用企业管理器创建存储过程

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

部分语法格式如下：

CREATE PROC[EDURE] procedure_name

[{@parameter data_type} [= default] [OUTPUT]]

[,...n] [WITH {

RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION

}]

AS sql_statement [...n]

2. 使用 Transact-SQL 语句创建存储过程

procedure_name ：表示存储过程名。存储过程名必须遵循标识符命名规则。

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

主要参数含义：

data_type ：表示参数的数据类型。存储过程

中声明的参数可以使用所有的数据类型。

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

OUTPUT ：表示声明的参数是输出参数。输出参数能够在存储过程执行时返回值。 Text 或者 Image 数据类型的参数不能作为输出参数。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE: 表示 SQL Server 不缓存存储过程的计划，因此存储过程每次执行都会被重新编译。 ENCRYPTION 表示对存储过程的文本进行加密。

sql_statement ：表示存储过程中可以包含任意数量和类型的 Transact-SQL 语句。

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. 课程号

调用存储过程

Exec student_cj go

通过上例了解存储过程的使用。

[[ 例例 9-2] 9-2] 在在 studentstudent 中建立一个名为中建立一个名为 st_proc_bjst_proc_bj 的存的存储过程储过程 .. 该存储过程将返回计算机系的班级名称。该存储过程将返回计算机系的班级名称。所用脚本内容如下：所用脚本内容如下：Use studentUse studentGoGoCreate proc dbo.st_proc_bjCreate proc dbo.st_proc_bjas as Select Select 班级名称班级名称 from from 班级班级 ,, 系部 系部 where where 系部系部 .. 系部代码 系部代码 == 班级班级 .. 系部代码 系部代码 and and 系部系部 .. 系部名称系部名称 =‘=‘ 计算机’计算机’gogoExecute st_proc_bjExecute st_proc_bjgogo

[ 例 9-3] 在 student 中建立一个名为 st_scr 的存储过程，用

于检索电子商务概论课程成绩，并按降序排序。所用脚本内容如下：

Use studentIf exists (select name from sysobjects where name=‘st_scr’ and type=‘p’)Drop procedure st_scrgoCreate procedure st_scrAsSelect 姓名 , 课程名称 , 成绩 from 成绩表 as sc inner join 学生表 as st on sc. 学号 =st. 学号 inner join 课程表 as co 0n sc. 课程编号 =co. 课程编号 where 课程名称 =‘电子商务概论’ order by 成绩 descgoExecute st_scr

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

[[ 例例 9-5] 9-5] 创建一个对文本进行加密的存储过程创建一个对文本进行加密的存储过程USE pubsUSE pubsGOGOCREATE PROCEDURE encrypt_procCREATE PROCEDURE encrypt_proc WITH ENCRYPTIONWITH ENCRYPTION ASAS SELECT * FROM employeeSELECT * FROM employee SELECT COUNT(*) FROM employeeSELECT COUNT(*) FROM employee GOGO

总结存储过程的优点

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

创建存储过程时，需要确定存储过程的三个组成部分：

①所有的输入参数以及传给调用者的输出参 数。

② 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。

③ 返回给调用者的状态值，以指明调用是成功还是失败。

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

所用脚本内容如下：Use studentIf exists (select name from sysobjects where name=‘st_scr_sp’ and type=‘p’)Drop procedure st_scr_spGoCreate 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_nameorder by 成绩 descgoExecute st_scr_sp ‘李向明’ ,‘计算机网络基础’

[ 例 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. 课程名 =@cna

meGo

student_info1 存储过程有多种执行方式

Execute student_info1 ’李利军’ ,’计算机基础’

或 Execute student_info1 @name=’李利军’ , @cname=’计算机基础’

或 Execute student_info1,@cname=’计算机基础’ ,

@name=’李利军’

9.2.2 执行存储过程

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

我们可以使用我们可以使用 Transact-SQLTransact-SQL 的的 EXECUTEEXECUTE语句来执行存储过程。如果存储过程的执行是批处语句来执行存储过程。如果存储过程的执行是批处理中的第一条语句，理中的第一条语句， EXECUTEEXECUTE 关键字可以省略。关键字可以省略。

部分语法格式如下：部分语法格式如下：

[[EXEC[UTE]] [[EXEC[UTE]]

{{

[@return_status =] procedure_name [@return_status =] procedure_name

}}

[[@parameter =] value | @variable [OUTPUT[[@parameter =] value | @variable [OUTPUT] | [DEFAULT]] ] | [DEFAULT]]

[,...n] [WITH RECOMPILE][,...n] [WITH RECOMPILE]

[ 例 9_8] 执行存储过程

Use student Execute st_scr

9.2.3 查看和修改存储过程 查看、修改存储过程 使用企业管理器查看、修改用户创建

的存储过程 使用系统存储过程查看用户创建的存

储过程

1 ．使用企业管理器查看、修改存储过程

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

2 ．使用 Transact-SQL 语句查看存储过程

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

9.2.4 修改存储过程

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

程

例： 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. 课程名 =@cn

ameGo

9.2.5 重命名和删除存储过程

1. 重命名存储过程 1) 使用企业管理器重命名存储 名称 ( 略 ) 2) 使用系统存储过程重命名存储 过程名称

使用系统存储过程重命名存储过程名称 使用系统存储过程重命名存储过程名称

Sp_rename Sp_rename 存储过程原名 存储过程新名存储过程原名 存储过程新名

2. 删除存储过程

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

过程

使用使用 Transact-SQLTransact-SQL 语句删除存储过程语句删除存储过程

DROP PROCEDURE DROP PROCEDURE 存储过程名存储过程名 [ [ ，，…… n]n]

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

9.7 9.7 触发器综述触发器综述

触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。 一般的存储过程通过存储过程名称被直接

调用，而触发器主要是通过事件进行触发而被执行 ( 对表或视图发出 update 、 insert 、 delete语句 ) 。

触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于 SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。

9.7.2 触发器的优点

触发器是自动的执行的 ( 往表里插入、修改、删除时被自动激活，从而防止对数据的不正确修改 ) 。

触发器是基于一个表创建的，但可以针对多个表进行操作，实现数据库中的相关表进行层叠更改。

触发器可以实现比 check 约束更为复杂的数据完整性约束。

一个表中可以同时存在三个不同操作的触发器，对于同一个修改语句可以有多个不同的对策以响应。

9.7.3 触发器的类型

AFTER 触发器 (又称为后触发器 )是在引起触发器执行的修改语句完成之后执行。

INSTEAD OF 触发器（又称为替代触发器）当引起触发器执行的修改语句停止执行时，该类触发器代替触发操作执行。

触发器中使用的特殊表（逻辑表） INSERTED 表和 DELETED 表

执行触发器时，系统为每个触发器都自动创建了 INSERTED表和 DELETED 表。

INSERTED 表：当向表中插入记录时， INSERTED 触发器触发执行，新的记录插入到触发器表和 INSERTED 表中，用于存放插入操作时的行。 DELETED 表用于存放已从表中执行操作删除的记录。

修改一条记录等于插入一条新记录，同时删除旧记录。原记录移到 DELETED 表中，修改过的记录

插入到 INSERTED 表中。 这两个表的结构和被触发器作用的表的结构相同。 用户不能对这两个表进行修改，但可以读取。INSERTED 表和 DELETED 表的查询方法与表的查询方法相同。

9.8 触发器的创建执行9.8.1 创建触发器1 、使用企业管理器创建触发器 在企业管理器中，展开指定的服务器

和数据库，右击某个表，从弹出的快捷菜单中依次选择“所有任务 | 管理触发器”选项，会出现触发器属性对话框 。

2 、使用 Transact-SQL 语句创建触发器

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

其中：

ON 子句：用于指定在其上执行的触发器的表，有时也

称为触发器表。

{FOR|AFTER| INSTEAD OF} ：指定了触发器激活的时

机，即执行哪些操作时激活触发器。 {[INSERT] [UPDATE] [DELETE]} ：指定了激活触发器的 语句。

sql_statement ：指定了触发器所执行的 T_SQL 语句。

注意：通常不要在触发器中返回任何结果，因此就不要在触发器定义中使用 select 语句或变量赋值语句。

练习：

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

在 student 数据库中建立一个名为 delete_zhye 的DELETE 触发器，存储在“专业”表中。当用户删除“专业”表中的记录时，如果“班级”表引用了此记录的专业代码，则提示用户不能删除记录，否则提示记录已删除。程序清单如下：

Use Student Go Create trigger delete_zhye on 专业 for delete As If (select count(*) from 班级 inner join delet

ed On 班级 .专业代码 =deleted.专业代码 )>0 begin Print (‘该专业已被班级表所引用，你不可以删除

此记录，删除将终止’ ) Rollback transaction end else print’记录已删除’ go

3. 创建一个 UPDATE 触发器

在 Student 数据库中建立一个名为 update_zymc 的UPDATE 出发器，存储在“专业”表中。当用户更新“专业”表中的专业名称时，提示用户不能修改专业名称。程序清单如下：

Use student Go Create trigger update_zymc On 专业 For update As If update(专业名称 ) Begin Print ‘ 不能修改系部名称’ rollback transaction End go

Use student 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

范例

在 student 数据库中建立一个 insert 触发器，其名称为‘插入成绩触发器 ’，存储在‘学生’表中。在向‘学生’表中插入一条记录时，将引发该触发器，从而实现向‘成绩’表中插入两条记录的操作。

第一步：建立触发器

Use student go create trigger 插入成绩触发器 ON 学生 for insert as -- 不显示由 t-sql 语句影响的记录行数 set nocount off declare @stin char(6) If @@rowcount=1 begin select @stin= 学号 from inserted -- 从临时表 inserted 中获取插入的记录 Insert into 成绩 （学号 , 课程编号） values(@stin,’001’) Insert into 成绩 （学号 , 课程编号） values(@stin,’001’) end

第二步：测试触发器

Insert into 学生 values(‘046’,’王大力’ ,’ 网络技术’ ,’2004’,‘j03’ ）

Select 成绩 . 学号 ,姓名 , 成绩From 成绩 inner join 学生 on 成绩 . 学号 =学生 . 学号

Inner join 课程 on 成绩 . 课程编号 =课程 . 课程编号

Wehre 姓名 =‘王大力’ Print ‘相关记录已插入！’第三步：执行并查看结果

9.8.2 9.8.2 查看触发器查看触发器

11 ．使用企业管理器查看触发器信息．使用企业管理器查看触发器信息22 ．使用系统存储过程查看触发器．使用系统存储过程查看触发器语法格式为：语法格式为： sp_helptrigger sp_helptrigger 表名表名 ,[INSERT],[INSERT] [UPDATE],[DELETE][UPDATE],[DELETE]

9.9 修改和删除触发器9.9.1 修改触发器 1. 使用企业管理器修改触发器正文 2. 使用 Transact-SQL 语句修改触发器 正文

SQL 格式：

ALTER TRIGGER trigger_name

On table|view

[WITH ENCRYPTION]

{FOR|AFTER| INSTEAD OF} {[INSERT] [UPDATE] [DELETE]}

AS

sql_statement

3. 使用系统存储过程修改触发器的名称 重命名触发器 SQL 格式：

sp_rename oldname, newname

9.9.2 禁止或启用触发器

禁止或启用已创建的触发器的执行，只能在查询分析器中进行。语法格式为： alter table 表名 {[Enable|Disable]} 触发器名称其中：Enable 选项为启用触发器 Disable 选项为禁用触发器

9.9.3 删除触发器

1 ．使用企业管理器删除触发器 2 ．使用系统命令删除触发器 3 ．直接删除触发器所在的表

删除触发器删除触发器 SQLSQL 格式：格式：

DROP TRIGGER trigger_nameDROP TRIGGER trigger_name–说明：删除触发器所在的表时，说明：删除触发器所在的表时， SQSQL ServerL Server 将会自动删除与该表相关的将会自动删除与该表相关的触发器触发器

思考

【问题】创建一个触发器 Test1 ，要求每当在 Student 表中修改数据时，向客户端显示一条“记录已修改！”的消息

【问题】将上例中触发器中的 FOR UPDATE 改为 INSTEAD OF UPDATE ，查看执行结果有何不同。

总结：

使用 FOR/AFTER 时，执行触发 SQL语句（ INSERT/UPDATE/DELETE) ：在触发 SQL 语句成功执行后，再执行触发器。

使用 INSTEAD OF 时：执行触发器而不执行触发 SQL 语句。

使用注意事项总结

CREATE TRIGGER 要在单个批处理中执行

ON table 中的表格只能是一个 在同一条 CREATE TRIGGER 语句中，可

以为多个事件（ INSERT/DELETE/UPDATE 定义相同的触发器操作）

可以为每个事件（ INSERT/DELETE/UPDATE ）创建多个触发器

思考

级联更新和删除： 【练习】创建触发器，当修改 student 表

中的学号字段后，自动修改 stucou 表中相应的学号。（以前采用外键方式）

【练习】在 student 表上创建触发器 del_trg ，当删除表中某一个学生的记录时，能自动删除掉该学生的所有成绩记录

本章小结：

触发器是在数据更新后执行的后置过滤器，当有操作影响到触发器保护的数据时，触发器就会自动触发执行，而规则、约束、默认值是在数据更新前进行的检查。如果触发器的操作与规则、约束、默认值检查发生冲突，则不执行触发器的操作。

使用游标

教学内容 : 声明游标、打开游标、提取数据、关闭

游标、释放游标。 教学目标 : 了解游标的几种类型。 掌握游标的作用以及基本操作方法。 教学重、难点 : 游标的使用。

通常，数据库执行的大多数通常，数据库执行的大多数 SQLSQL 命令返回命令返回的记录集包括所有记录或的记录集包括所有记录或 WHEREWHERE 子句中满子句中满足条件的记录，都是同时处理集合内部的所足条件的记录，都是同时处理集合内部的所有数据。但是，有时候用户也需要对结果集有数据。但是，有时候用户也需要对结果集中的一条或一部分记录逐个进行操作。这种中的一条或一部分记录逐个进行操作。这种工作如果放在数据库的前端，用高级语言来工作如果放在数据库的前端，用高级语言来实现，将导致不必要的数据传输，从而延长实现，将导致不必要的数据传输，从而延长执行的时间。执行的时间。

因而必须借助使用 SQL Server 所提供的游标，不但允许定位在结果集的特定记录上，还可以从结果集的当前位置检索一条或多条记录，并支持在服务器端对单条记录进行数据处理。游标提供了一种在服务器内部处理结果集的方法，它可以识别一个数据集合内部指定的工作，从而有选择地按行采取操作。

事实上， sql 语句可以替代游标进行从后台数据库查询多条记录的复杂操作。但是同 sql 语句相比，游标也有其自身的优点，比如系统资源占用少，操作灵活，可根据需要定义变量类型如全局、实例或局部类型和访问类型（私有或公共）等。

SQL Server 支持 4 种 API 服务器游标类型，即静态游标、动态游标、只进游标和键集驱动游标类型。

游标的基本操作

游标的功能比较复杂，要灵活应用游标需要花费较长的时间联系和积累经验。本书只介绍使用游标的基本和常用的方法。如果需要进一步学习，可以参考数据库的相关书籍。

使用游标有 5 个基本的步骤：

声明游标、打开游标、提取数据、关闭游标和释放游标。

[ 例 1] 建立一个游标，用于访问 student 数据库的学生表。

Use student -- 声明游标Declare 学生 _cursor cursor For select * from 学生 -- 打开游标Open 学生 _cursor /* 从游标中提取一行记录，未指定 scroll 选项，fetch next 是惟一可用的提取选项 */

Fetch next from 学生 _cursor

-- 关闭游标Close 学生 _cursor -- 删除游标Deallocate 学生 _cursor 注意： 由于声明游标时在 select 语句中未用where 子句，故返回的结果集是表中的所有记录。但是使用 Fetch 语句每次只能提取一行记录。

使用游标访问数据的一般步骤：

（ 1 ）用 Declare cursor 语句声明游标；

（ 2 ）用 open 语句打开游标； （ 3 ）用 Fetch 语句从游标中提取记录； （ 4 ）用 close 语句关闭游标； （ 5 ）用 Deallocate 语句删除游标。

1 、声明游标 像使用其他类型的变量一样，在使用一个游标之

前，应当先声明它。游标的声明包括两个部分：游标的名称和游标所用到的 SQL 语句。

声明游标的语法如下：DECLARE 游标名 [INSENSITIVE][SCROLL][STATIC│KEYSET│DYNAMIC│FAST_FORWORD] CURSOR

FOR <SELECT 语句 >[FOR {READ ONLY│UPDATE [OF 字段名 1,…

n]}]

其中： 游标名：为声明的游标所取的名字 , 生命游标

必须遵守 -Transact-SQL 对标识符的命名规则 . INSENSITIVE: 使用 INSENSITIVE 定义的游标 ,

把提取出来的数据存入一个在 tempdb 数据库创建的临时表里 . 任何通过这个游标进行的操作 , 都在这个临时表里进行。所有对基本表的改动都不会在游标进行的操作中体现出来，并且该游标不允许修改。若省略 INSENSITIVE 关键字，则用户对基本表所进行的任何操作 , 都将反映在后面的提取（ fetch ）中。

SCROLL ： 使用 SCROLL 关键字定义的游标 , 则以下所有的提

取选项均可使用。如果未指定该关键字，则 fetch next 是惟一支持的提取选项。

具有取数功能的所有选项如下： FIRST: 取第一行数据 ; LAST: 取最后一行数据 ; PRIOR: 取前一行数据 ; NEXT: 取后一行数据 ; PELATIVE: 按相对位置取数据 ; ABSOLUTE: 按绝对位置取数据 .

注意：

1 、若没有在声明时使用 SCROLL 关键字 , 则所声明

的游标只具有默认的 NEXT功能。2 、游标只能一次从数据库中提取一条记录。若提 取所有记录则要与循环紧密结合在一起。

STATIC: 静态游标类型 . KEYSET: 键集游标类型 . DYNAMIC: 动态游标类型 . FAST_FORWORD:只进游标类型 . SELECT 语句 : 一些标准的 SELECT 语句 ,主要

用来定义游标所要进行处理的结果集。在声明游标的 SELECT 语句中 , 不允许使用如COMPUTE,COMPUTE BY 和 INTO等关键字。

READ ONLY: 声明只读 , 类似与视图。不允许通过只读游标进行数据的更新。在 update 和 delete 语句的 where current of子句中不能引用游标。

UPDATE [OF 字段名，… n]: 定义在这个游标里可以更新的字段。若定义了 OF[字段名，… n],则只允许修改所列出的字段 ;如果没有定义 [OF 字段名 ], 则游标里的所有列都可以被更新。

[ 例 2]声明一个只读游标 computer_cursor, 用以查询计算机系的学生信息。

Use student go Declare computer_cursor cursor For Select 学号 , 姓名 , 系别 From 学生 Where 系别 =‘计算机’

For read only

2 、打开游标 声明了游标后，在正式操作之前，必须打开它。打开游标的语法如下： OPEN [GLOBAL] 游标名称 其中 : GLOBAL参数表示打开的是全局游标。 当执行打开游标的语句时，服务器执行声明

游标时使用的 select 语句，如果使用了 INSENSITIVE 关键字， 则服务器会在 tempdb 中建立一张临时表，以存放游标将要操作的数据集的副本。

由于打开游标是对数据库进行一些 SELECT 的操作，它将耗费一段时间，时间长短主要取决于使用的系统性能和这条语句的复杂程度。

当游标打开成功之后 , 可以使用全局变量@@cursor_rows 来获取这个游标中所接受的记录行数。全局变量@@cursor_rows 有四种可能的取值：

0：表示没有被打开的游标。 N ：游标已经完全填充。返回值（ n ）是在游标

中的总行数。 -1 ：表明游标是动态的。 -m: 表明游标被异步填充。

3 ．提取数据 当用 OPEN 语句打开了游标并在数据库中执行了查询后，并不能立即利用查询结果集中的数据，必须用 FETCH语句来提取数据。一条 FETCH语句一次可以将一条记录放入指定的变量中。事实上， FECHT 语句是游标使用核心。使用游标提取某一行的数据使用以下的语法：

游标提取数据的语法

FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE{n |@nvar}| RELATIVE { |@nvar}] FROM] 游标名称 [INTO @变量 [，… n]]

在这个语句中， n 和 nvar 表示游标相对于作为基准的数据行所偏离的位置。

在使用 into子句对变量赋值时，变量的数量和相应的数据类型必须和声明游标时使用的 select语句中引用到的数据列的数目、排列顺序和数据类型完全保持一致，否则服务器会提示出错。在默认情况下（ fetch from 游标名）是表示取下一个数，即

fetch next from 游标名称

从语法上讲，上面所述的就是一条合法的提取数据的语句，但是一般在使用游标时还应当包括其它部分。游标只能一次从后台数据库中提取一条记录，在多数情况下，所要做的是在数据库中从第一条记录开始提取，一直到结束。所以一般要将游标提取数据的语句放在一个循环体内，直到将结果集中的全部数据提取完后，跳出循环圈。通过检测全局变量@@fetch_status 的值，可以得知 fetch 语句是否取到最后一条。当@@fetch_status值为 0时表明提取正常， -1 表示已经取到了结果集的末尾，而其他值均表明操作出了问题。事实上，使用游标提取数据的操作要与 while循环紧密结合在一起。

[ 例 3]声明一个可更新的滚动游标 score_cursor,用以查询学生成绩，并且可更新分数列，将所有记录的成绩增加 0.5 分。

（ 1 ）声明游标 Declare score_cursor scroll cursor For Select 学号，成绩 From 学期成绩 For update of 成绩 /*定义可更新的字段 */

（ 2 ）打开游标 open score_cursor (3)提取数据 -- 执行第一次提取 ,得到结果集中的首记录 fetch absolute 1 from score_cursor update 学期成绩 set 成绩 = 成绩 +0.5 where current of score_cursor /* 如果希望在用游标提取某条记录之后修改或删除

记录，请在游标的声明语句中指定 for update子句，然后在 update或 delete 语句中使用‘where current of 游标名称’子句，以便直接修改或删除当前所指的数据。 */

注意：一次只能修改一条记录，增加循环才可以修改所有记录。--检测全局变量@@ fetch_status,如果仍有记录 , 则继续循环 While @@ fetch_status=0 Begin --只要上次提取成功 ,就会执行下面的提取 Fetch next from score_cursor update 学期成绩 set 成绩 = 成绩 +0.5 where current of score_cursor End(4)关闭游标 close score_cursor

[ 例 4]对已声明游标 score_cursor, 一条条地取出其中的数据。 Open score_cursor /*执行第一次取数操作 */ fetch next from score_cursor /* 检查上一次游标操作所返回的状态值（若成功，

该变量值为 0） */ while (@@score_cursor=0) begin fetch next from score_cursor end 当游标移动到最后一行数据时，继续执行取下一行数据的操

作，将返回错误信息，但这个信息只在@@fetch_status 中体现，同时返回空白的数据。根据判断条件，程序现在就终止循环。

4、关闭游标

在打开游标后， sql server 服务器会专门为游标开辟一定的存储空间存放游标操作的数据结果集，同时使用游标时也会根据具体情况对某些数据进行封锁。所以，在停下使用游标的时候，一定要关闭游标，以通知服务器释放游标所占的资源。关闭游标的语法如下：

close cursor_name 关闭游标以后，可以再次打开游标。在一个批处理中，也可以多次打开和关闭游标。

5 ．释放游标 游标结构本身也会占用一定的计算机资源，所以

在使用完游标后，为了回收被游标占用的资源，应该将游标释放。释放游标的语法如下：

DEALLOCATE cursor_name 当释放完游标后，如要重新使用游标必须重新执

行声明游标的语句。

定位、更新和删除游标数据

要使用游标进行数据的修改，前提条件是该游标必须被声明为更新的游标。只要在进行游标声明，没有带 READ ONLY 关键字的游标都是可更新的游标。

在游标声明过程中可以使用 SLEECT 语句对多表中的数据进行访问。因此若声明的是可更新游标，则可使用该游标对多个表中的数据进行修改，但这种不规范的更新数据的方法很容易导致数据的不一致，所以使用游标更新、删除多表数据时一定要谨慎小心。

进行定位修改游标数据的语法如下： UPDATE 基表名 SET 列名 1= 表达式 1 ， […] WHERE Current Of 游标名

• [ 例 5]已声明好的游标 Score _Cursor ，对其上的 Score列进行更新操作。

OPEN Score _Cursor FETCH NEXT FROM Score_cursor While @@ fetch_status=0 Begin update 表名 set score =score/10 Where current of score_cursor Fetch next from score_cursor End Close score_cursor Deallocate score_cursor

课后小结：

1. 存储过程和触发器的基本概念 . 2. 存储过程和触发器的创建、修改和使用 . 3. 游标的使用

思考题：思考题：

一一 . . 填空题填空题1. 1. 本地存储过程是指在用户数据库中本地存储过程是指在用户数据库中 创建的存储过程，其名称不能以创建的存储过程，其名称不能以

（　　　）为前缀。（　　　）为前缀。2. 2. 如果存储过程名的前三个字符为如果存储过程名的前三个字符为 spsp

_ ,SQL Server_ ,SQL Server 会在（　　）数据库会在（　　）数据库中寻找该过程。中寻找该过程。二二 . . 简答题简答题11．试说明存储过程的特点及分类。 ．试说明存储过程的特点及分类。 22．举例说明存储过程的定义与调用。．举例说明存储过程的定义与调用。

Thank you very much!

只有在知道自己懂得很少的时候，才说得上有了深知。疑惑随着知识增长。 —— 歌德——