第08章 存储过程、游标和触发器2022-11-301内容提要存储过程（Stored Procedure）是一组完成特定功能的MySQL语句
的集合，即将一些固定的操作集中起来由MySQL服务器来完成，应用程序只需调用它就可以实现某个特定的任务。存储过程是可以通过用户、其
他存储过程或触发器来调用执行。在MySQL中的游标（Cursor）是一种实现对select结果集中的数据进行访问和处理的机制，允许
用户访问单独的数据行。MySQL中的游标一般通过存储过程来实现其操作。触发器（Trigger）是一种特殊的存储过程。触发器通常在特
定的表上定义，当该表的相应事件发生时自动执行，用于实现强制业务规则和数据完整性等事件（Event）又称事件调度器(Event Sc
heduler)，有时也可称为临时触发器(temporal triggers)，因为事件调度器是基于特定时刻或时间周期触发来执行某
些任务，而触发器是基于对表进行操作所产生的事件触发的。本章将介绍存储过程、游标、触发器和事件的基本概念，及其的创建和管理的基本操作
。存储过程1.认识存储过程在MySQL数据库中，利用存储过程可以保证数据的完整性，提高执行重复任务的性能和数据的一致性。例如，银行
经常核算用户的利息。不同类别的用户的存款利率是不一样的。这就可以将计算利率的SQL代码写成一个存储过程。只要将客户的某一笔存款的存
款时间和存款额数输入，调用这个存储过程就可以核算出用户的利息。存储过程在被调用的过程中，参数可以被传递和返回，出错代码也可以被检验
。存储过程主要应用于控制访问权限、为数据库表中的活动创建审计追踪、将关系到数据库及其所有相关应用程序的数据定义语句和数据操作语句分
隔开。存储过程1.认识存储过程存储过程的优势。利用存储过程可以让系统达到如下目的：（1）提高了处理复杂任务的能力。主要用于在数据库
中执行操作的编程语句，通过接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。（2）增强了代码的复用率和共享性。存储过程一
旦创建后即可在程序中调用任意多次，这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。（3）减少了网络中数据的流量。因为存
储过程存储在服务器上，并在服务器上运行。一个需要数百行MySQL代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发
送数百行代码。存储过程1.认识存储过程存储过程的优势。利用存储过程可以让系统达到如下目的：（4）存储过程在服务器注册，加快了过程的
运行速度。存储程序只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般MySQL语句每执行一次就编译一次，所以使用存储
过程可提高数据库执行速度。（5）加强了系统的安全性。存储过程具有安全特性（例如权限）和所有权链接，用户可以被授予权限来执行存储过程
而不必直接对存储过程中引用的对象具有权限。可以强制应用程序的安全性，参数化存储过程有助于保护应用程序不受SQL注入式攻击。存储过程
1.认识存储过程创建存储过程格式。创建存储过程可以使用create procedure语句。要创建存储过程，必须具有create
routine的权限。create procedure的语法格式如下：create procedure sp_name([proc
_parameter[,...]])[characteristic ...] routine_body说明：（1）sp_name为
存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。（2）parameter为调用该存储过程使用的
参数，这条语句中的参数个数必须总是等于存储过程的参数个数。存储过程2. 存储过程的创建和管理存储过程的创建和执行【例8.1】创建存
储过程proc_stu，从数据库teaching的student表中检索出所有电话以131开头的学生的学号、姓名、出生日期和电话等
信息。mysql> use teaching;mysql> delimiter //mysql> create procedur
e proc_stu()-> reads sql data-> begin-> select studentno,sname,b
irthdate ,phone-> from student-> where phone like ''%131%'' order b
y studentno ;-> end//mysql> delimiter ;调用存储过程proc_stu()的代码和执行结果如
下：mysql> call proc_stu();存储过程2. 存储过程的创建和管理存储过程的创建和执行【例8.2】创建存储过程a
vg_score，输入课程号后，统计该课程的平均成绩。mysql> delimiter //mysql> create proce
dure avg_score(in c_no char(6))-> begin-> select courseno,avg(fin
al)-> from score-> where courseno=c_no ;-> end //mysql> delimiter
;调用存储过程avg_score ()的代码和执行结果如下：mysql> call avg_score(‘c05109’);存储
过程2. 存储过程的创建和管理存储过程的创建和执行【例8.3】创建存储过程select_score()，用指定的学号和课程号为参数
查询学生成绩。分析：创建带多个输入参数的存储过程。mysql> delimiter $$mysql> create proced
ure select_score(in s_no char(11),c_no char(6))-> begin-> select
from score-> where studentno=s_no and courseno= c_no ;-> en
d $$mysql> delimiter ;调用存储过程select_score()的代码和执行结果如下：mysql> call
select_score(''18125121107'',''c05109'');存储过程2. 存储过程的创建和管理存储过程的创建和执行【
例8.4】创建存储过程stu_score的，统计指定同学的考试门数分析：在本存储过程中，输入参数为学号s_no，输出参数为coun
t_num，select语句用count()计算指定学生的考试门数，最后将计算结果存入count_num中。 调用有输出参数的存
储过程时，可以通过会话变量@c_num实现。mysql> delimiter //mysql> create procedure
stu_scores(in s_no char(11), out count_num int)-> reads SQL da
ta-> begin-> select count() into count_num from score-> w
here studentno=s_no;-> end //mysql> delimiter ;调用存储过程stu_scores
()的代码和执行结果如下：mysql> call stu_scores(‘18125121107’, @c_num );mysq
l> select @c_num;说明：（1）存储过程是已保存的MySQL语句集合。对于一般的select语句，如果查询的数据要来
自于多个表，可以使用多表连接或子查询等方式。（2）当调用存储过程时，MySQL会根据提供的参数值，执行存储过程体中的SQL语句。存
储过程2. 存储过程的创建和管理存储过程的创建和执行【例8.5】创建存储过程do_query，输入指定学号，查看该生的成绩高于85
分的科目数，如果超过2科，则输出very good!，并输出该生的成绩单，否则输出come on!。 分析：存储过程do_q
uery中，利用if语句实现较为复杂的功能。该存储过程用declare语句声明了局部变量AA。根据指定学号，进行统计该生高于85分
的科目数，并使用select into语句为变量AA赋值，然后根据AA的值进行判断。mysql> create procedure
do_query(in s_no char(11), out str char(12))-> begin-> declare
AA tinyint default 0;-> select count() into AA from scor
e-> where studentno= s_no and final>85;-> if AA>=2 then->
begin-> set str=''very good! '';-> select from score
where studentno = s_no;-> end;-> elseif AA<2 then-> set
str=''come on! '';-> end if;-> end //mysql> delimiter ;调用存储过程do_que
ry ()的代码和执行结果如下：mysql> call do_query(‘18125111109’,@str);mysql>
select @str;mysql> call do_query(''18122210009'',@str);存储过程2. 存储过程
的创建和管理存储过程的创建和执行【例8.6】创建一个存储过程，向score表中插入一行记录，然后创建另一存储过程do_outer(
)，调用存储过程do_insert()，并查询输出score表中插入的记录。分析：利用存储过程中调用其他存储过程。在调用存储过程d
o_outer()时，先执行第一个存储过程do_insert()，插入了一行记录，然后再执行后面的语句，输出查询结果。--先创建第
1个存储过程do_insert()mysql> create procedure do_insert()-> insert int
o score values(‘18125111109’, ‘c05109’ ,89,92);--创建第2个存储过程do_out
er()，调用do_insert()mysql> delimiter $$mysql> create procedure do
_outer()-> begin-> call do_insert();-> select from score-> w
here studentno=''18125111109'';-> end $$调用存储过程do_outer ()的代码和执行结果如下
：mysql> call do_outer();存储过程2. 存储过程的创建和管理查看存储过程的定义。存储过程和函数创建以后，用户
可以查看存储过程和函数的状态和定义。用户可以通过show status语句来查看存储过程和函数的状态，也可以通过show crea
te语句来查看存储过程和函数的定义。用户也可以通过查询information_schema数据库下的Routines表来查看存储过
程和函数的信息。在前面学习存储函数的基础上，下面给出已经验证过的查看存储过程的状态和定义的方法的例子。mysql> show p
rocedure status like ''do_%'';mysql> show create procedure do_o
uter;mysql> select from information_schema.routines ->
where routine_name=''do_outer '' ;mysql> show create procedure do_
outer; 存储过程2. 存储过程的创建和管理条件和处理程序的定义。默认情况下，MySQL存储程序运行过程中发生错误时，将自动终
止程序的执行。此时，数据库开发人员有时希望自己控制程序的运行流程，并不希望MySQL将自动终止存储程序的执行，MySQL的错误处理
机制可以帮助数据库开发人员自行控制程序流程。定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决
这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中
都是通过declare关键字来定义条件和处理程序。通过定义条件来对可能涉及错误以及子程序中的一般流程进行控制。存储过程2. 存储过
程的创建和管理条件和处理程序的定义。 （1）定义条件。在MySQL中定义条件的基本语法如下。declare condition_
name condition for condition_type; ? condition_type: sqlstate [va
lue] sqlstate_value|mysql_error_code 说明：1）condition_name：表示错误触发条件
的名称。2）condition_type：表示条件的类型，分为MySQL错误代码或者ANSI标准错误代码。sqlstate_val
ue 为长度为5的字符串类型错误代码； mysql_error_code 为数值型错误代码。例如，ERROR 1147 (42S0
7)中，sqlstate_value的值为字符串“42S07”，mysql_error_code 为1147。3）此语句指定需要特
殊处理的条件，将指定的错误条件与一个名字联系起来。这个名字即错误名，可以在随后的定义处理程序中的declare handler语句
中应用。存储过程2. 存储过程的创建和管理【例8.7】 定义“ERROR 1147 (42S07)”这个错误，名称为cannot_
found。可以用两种不同的方法来定义，代码如下。--使用sqlstate_value方法定义 declare cannot_f
ound condition for sqlstate''42S07''; --使用mysql_error_code方法定义d
eclare cannot_found condition for 1147; 存储过程2. 存储过程的创建和管理（2）定义
处理程序。MySQL中定义处理程序的基本语法如下。declare handler_type handler for conditi
on_value[,…] sp_statement handler_type: continue|exit condition_
value: sqlstate [value]sqlstate_value|condition_name |sqlwarning
|not found|sqlexception|mysql_error_code （1）handler_type：错误处理类型，取
值包括continue和exit。continue表示遇到错误不处理， 继续执行其他MySQL语句；exit表示遇到错误马上退出其
他MySQL语句的执行。（2）condition_value：错误触发条件，表示满足什么条件时，自定义错误处理程序开始运行，错误触
发条件定义了自定义错误处理程序运行的时机。具体包括如下取值：sqlstate [value] sqlstate_value ：长度
为5的字符串类型错误代码；condition_name ：表示declare condition定义的错误条件名称；sqlwar
ning：匹配所有以01开头的sqlstate错误代码；not found ：匹配所有以02开头的sqlstate错误代码；sql
exception ：匹配其他非sqlwarning和not found捕获的错误代码； mysql_error_code ：为数
值型错误代码。（3）sp_statement：自定义错误处理程序，即遇到定义的错误时，MySQL会立即执行自定义错误处理程序中的M
ySQL语句，自定义错误处理程序也可以是一个begin-end语句块。存储过程2. 存储过程的创建和管理【例8.8】 定义条件和处
理程序实例-- 首先建立测试表mytest mysql> create table mytest(tf1 int,primar
y key(tf1)); mysql> delimiter //mysql> create procedure handlermy
test()-> begin-> declare continue handler for sqlstate ''23000''
set @x2=1;-> set @x=1;-> insert into mytest values(1);-> set @x
=2;-> insert into mytest values(1);-> set @x=3;-> Select @x,@x2
;-> end;-> //mysql> delimiter ;调用存储过程handlermytest()的代码和执行结果如下：my
sql> call handlermytest();说明：（1）定义了异常处理程序后，此时MySQL遇到错误也会按照异常定义那样继
续执行；但只有第1条数据被插入到表中，此时用户变量@x=3说明已经执行到了结尾。（2）自定义错误触发条件以及自定义错误处理程序可以
在触发器、函数以及存储过程中使用。实际软件开发过程中，建议数据库开发人员建立清晰的错误处理规范，必要时可以将自定义错误触发条件、自
定义错误处理程序封装在一个存储程序中。存储过程3. 修改存储过程有两种方法可以修改存储过程，一种方法是删除并重新创建存储过程，这种
方法和创建存储过程一样。另一种是使用alter procedure语句进行修改。使用alter procedure语句修改存储过程
的某些参数，修改存储过程语法格式如下。alter procedure sp_name [characteristic ...]
【例8.9】修改存储过程do_insert()的定义。将读写权限改为modifies sql data，并指明调用者可以执行。my
sql> alter procedure do_insert-> modifies sql data-> sql securi
ty invoker;存储过程4. 删除存储过程删除存储过程可以使用drop procedure语句。使用drop proced
ure删除已经存在的存储过程的语法格式如下：drop procedure [if exists] sp_name 说明：（1）
sp_name是要删除的存储过程的名称。（2）if exists子句是MySQL的扩展，如果程序或函数不存在，它防止发生错误。例如
，删除存储过程do_update()的代码。MySQL>drop procedure if exists do_insert；存储
过程5. 存储过程与函数的比较存储过程与函数之间的共同特点（1）存储过程或者函数可以重复使用，可以减少数据库开发人员，尤其是应用程
序开发人员的工作量。 （2）使用存储过程或者函数可以增强数据的安全访问控制。可以设定只有某些数据库用户才具有某些存储过程或者函数的
执行权。 存储过程与函数之间的不同之处（1）函数必须有且仅有一个返回值，且必须指定返回值为字符串、数值2个数据类型。存储过程可以没
有返回值，也可以有返回值，甚至可以有多个返回值，所有的返回值需要使用out或者inout参数定义。存储过程5. 存储过程与函数的比
较存储过程与函数之间的不同之处（2）函数体内可以使用select…into语句为某个变量赋值，但不能使用select语句返回结果集
。存储过程则没有这方面的限制，存储过程甚至可以返回多个结果集。（3）函数可以直接嵌入到SQL语句或者MySQL表达式中，最重要的是
函数可以用于扩展标准的SQL语句。存储过程一般需要单独调用，并不会嵌入到SQL语句中使用，调用时需要使用call关键字。（4） 函
数中的函数体限制比较多，比如函数体内不能使用以显式或隐式方式打开、开始或结束事务的语句，如start transaction、co
mmit、rollback或者set autocommit=0等语句；不能在函数体内使用预处理SQL语句。存储过程的限制相对就比较
少，基本上所有的SQL语句或MySQL命令都可以在存储过程中使用。（5）Java、PHP等应用程序调用函数时，通常将函数封装到SQ
L字符串中进行调用；而调用存储过程时，必须使用call关键字进行调用，如果应用程序希望获取存储过程的返回值，应用程序必须给存储过程
的out参数或者inout参数传递MySQL会话变量，才能通过该会话变量获取存储过程的返回值。 存储过程6. 利用MySQL Wo
rkbench工具管理存储过程利用MySQL Workbench工具管理存储过程主要包括对存储过程的创建、修改、查看、删除和执行操
作查看存储过程（1）启动MySQL Workbench工具，单击实例mysql57，选择当前数据库teaching。（2）在tea
ching数据库中选择Stored Procedures选项，展开Stored Procedures文件夹，可以看到已经创建的存储
过程，在如图8-1所示。在弹出菜单中，可以实现对存储过程进行复制到剪贴板、发送到SQL编辑器、新建、修改、删除和刷新等一系列操作。
存储过程6. 利用MySQL Workbench工具管理存储过程定义存储过程（1）创建存储过程，选择执行Create Stored
Procedures命令。则会进入如图8-2所示的创建存储过程的初始界面new_procedure-Routine。（2）在分别
编辑界面输入存储过程名do_case，在begin…end之间输入存储过程程序体的代码，如图8-3所示。若有错误，则会在行前提示。
存储过程6. 利用MySQL Workbench工具管理存储过程定义存储过程（3）自己检查无误后，单击Apply按钮，进入如图8-
4所示的代码对话框中，这是要向数据库teaching中存储的脚本。脚本内容为【例8.5】的do_query存储过程，将if语句改为
case语句模式来实现。（4）单击Apply按钮，进入如图8-5所示的对话框中，可以通过Show logs（Hide logs）转
换按钮查看存储过程的信息记录（Massage Log）窗口中的信息。可以查看到成功创建存储过程的提示：SQL script was
successfully applied to the database。单击finish按钮完成存储过程的创建。存储过程6.
利用MySQL Workbench工具管理存储过程定义存储过程（3）自己检查无误后，单击Apply按钮，进入如图8-4所示的代码对
话框中，这是要向数据库teaching中存储的脚本。脚本内容为【例8.5】的do_query存储过程，将if语句改为case语句模
式来实现。（4）单击Apply按钮，进入如图8-5所示的对话框中，可以通过Show logs（Hide logs）转换按钮查看存储
过程的信息记录（Massage Log）窗口中的信息。可以查看到成功创建存储过程的提示：SQL script was succes
sfully applied to the database。单击finish按钮完成存储过程的创建。存储过程6. 利用MySQL
Workbench工具管理存储过程执行存储过程（1）执行存储过程do_case，右击存储过程do_case，选择执行Copy t
o Clipboard ? Procedure Call命令，如图8-6所示。（2）进入执行存储过程的对话框后，在指定位置输入执行
存储过程的参数，如图8-7所示，先输入学号18122210009和会话变量@str。并且可以看到工具栏中的第3个执行存储过程的按钮
提示：执行选择的脚本参数，若未选择，就执行所有代码。（3）如图8-8所示，输入回话变量的执行命令“select @str”。单击执
行按钮，执行结果如图8-9所示。存储过程6. 利用MySQL Workbench工具管理存储过程执行存储过程（4）选择执行存储过程
的对话框，再次在指定位置输入执行存储过程的参数，输入学号18125111109和会话变量@str。单击执行按钮，如图8-10所示。
如果选择Result2选项卡，则会显示如图8-11所示的执行结果。由此可以看出本例与【例8.5】功能一样，对于不同的输入参数，存储
过程的执行结果有时不一样。存储过程6. 利用MySQL Workbench工具管理存储过程修改存储过程（1）选择存储过程avg_s
core，执行Alter Stored Procedures命令，如图8-12所示。（2）在修改存储过程编辑框中，输入修改项，如：
存储过程名改为max_score，函数avg(final)改为max(final)，如图8-13所示。（3）自己认真检查代码无误后
，单击Apply按钮，进入代码检查对话框中，向数据库teaching中存储的脚本。再次单击Apply按钮，进入完成修改对话框中，单
击finish按钮完成存储过程的修改。在利用MySQL Workbench工具删除存储过程时，只要对要删除的存储过程执行Drop
Stored Procedures命令即可。利用游标处理结果集在MySQL数据库中的大部分数据管理操作都与select语句有关。s
elect语句执行后一般会产生包含多条记录的、存放在客户机内存中的结果集。数据库开发人员编写存储过程或函数等存储程序时，有时需要访
问select结果集中的具体数据行，对结果集中的每条记录进行处理。游标在MySQL中是一种对select语句结果集进行访问的机制。
MySQL服务器会专门为游标开辟一定的内存空间，以存放游标操作的结果集数据，同时游标的使用也会根据具体情况对某些数据进行封锁。游标
能够实现允许用户访问单独的数据行，而不是只能对整个结果集进行操作。利用游标处理结果集游标主要包括结果集和游标位置两部分，游标结果集
是由定义游标的select语句的结果集，游标位置则是指向这个结果集中的某一行的指针。游标的使用过程如图8-14所示，可以概括为声明
游标、打开游标、从游标中提取数据以及关闭游标。利用游标处理结果集1．声明游标声明游标需要使用declare语句，声明游标的语法格式
如下。declare cursor_name cursor for select_statement; 例如，在teaching数
据库中为了teacher表创建一个普通的游标，定义、声明游标teach_cursor的语句如下：declare teach_cur
sor corsor for select teacherno,tname from teacher; 使用declare语句声明
游标后，此时与该游标对应的select语句并没有执行，MySQL服务器内存中并不存在与select语句对应的结果集。利用游标处理结
果集2．打开游标打开游标需要使用open语句，使用游标之前必须首先打开游标，打开游标的语法如下所示。open cursor_nam
e; 例如，打开前面创建的teach_cursor游标，使用如下语句。 open teach_cursor
; 使用open语句打开游标后，与该游标对应的select语句将被执行，MySQL服务器内存中将存放与select语句对应的结果集
。利用游标处理结果集3.从游标中提取数据在打开游标以后，就可以从游标中提取数据。从游标中提取数据需要使用fetch语句， fetc
h语句的功能是获取游标当前指针的记录，并传给指定变量列表。如果需要提取多行数据，则需要使用循环语句去执行fetch语句，MySQL
的游标是向前只读的，即只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。 fetch语句的语法结构如下。fe
tch cursor_name into var1[,var2,…]; （1）变量名的个数必须与声明游标时使用的select语句结
果集中的字段个数保持一致。第一次执行fetch语句时，fetch语句从结果集中提取第一条记录，再次执行fetch语句时，fetch
语句从结果集中提取第二条记录，…以此类推。（2）fetch语句每次从结果集中仅仅提取一条记录，因此fetch语句需要循环语句的配合
，才能实现整个结果集的遍历。fetch离不开循环语句。一般使用Loop和while比较清楚，而且代码简单。这里使用Loop为例，代
码如下。fetchLoop:Loop fetch teach_cursor into v_tno,v_tname; end Loo
p; 利用游标处理结果集3.从游标中提取数据MySQL是通过一个Error handler的声明来进行判断的。该语句语法格式如下：
declare continue handler for not found …; （3）当使用fetch语句从游标中提取最后一条
记录后，再次执行fetch语句时，将产生“ERROR 1329 (02000): No data to fetch”错误信息，数据
库开发人员可以针对MySQL错误代码1329，自定义错误处理程序以便结束“结果集”的遍历。（4）游标错误处理程序应该放在声明游标语
句之后。游标通常结合错误处理程序一起使用，用于结束结果集的访问。利用游标处理结果集4．关闭游标关闭游标使用close语句，关闭游标
的具体语法如下。close cursor_name; 关闭游标的目的在于释放游标打开时产生的结果集，以通知服务器释放游标所占用的资
源，节省MySQL服务器的内存空间。游标如果没有被明确地关闭，游标将在它被声明的begin-end语句块的末尾关闭。使用声明过的游
标不需要再次声明。如果不明确关闭游标，MySQL将会在到达end语句时自动关闭它。在检索游标teach_cursor后可用如下语句
来关闭它。close teach_cursor; 利用游标处理结果集4．关闭游标【例8.10】创建存储过程，利用循环语句控制fe
tch语句来检索游标teach_cursor中可用的数据的示例。代码和运行结果如下：mysql> use teaching;mys
ql> delimiter //mysql> create procedure proc_cursor()-> begin-> d
eclare v_tno varchar(6) default '' '';-> declare v_tname varchar
(8) default '' '';-> declare teach_cursor cuosor -> for selec
t teacherno, tname from teacher;-> declare continue handler for
not found set @dovar=1;#定义处理程序-> set @dovar =0;-> open teach_cur
sor;-> fetch_Loop:LOOP-> ferch teach_cursor into v_tno,v_tname;->
if @dovar=1 then-> leave fetch_Loop;-> else-> select v_tno,v_tna
me;-> end IF;-> end LOOP fetch_Loop;-> close teach_cursor;-> sele
ct @dovar;-> end ;//mysql> delimiter ;调用存储过程proc_cursor()的代码和执行结果
如下：mysql> call proc_cursor();利用游标处理结果集?利用declare定义一个句柄，当fetch抓取数据
时会自动调用该句柄。如果找不到数据，会自动调用最后的SQL语句set @dovar=1。其中not found等价于 sqlst
ate ''02000''。本例中，存储过程proc_cursor()的变量@dovar保存的就是fetch操作的结束信息。如果其值为
零，则表示有记录检索成功，输出相应的结果；如果值为1，则是fetch语句由于某种原因而操作失败。fetch语句获取数据到结果集最后
时，已经没有数据，所以执行处理程序，使得@dovar的值为1。触发器触发器（Trigger）是一种特殊的存储过程，可以是表定义的一
部分。触发器基于一个表创建，但可以针对多个表进行操作，所以触发器可以用来对表实施复杂的完整性约束。当预定义的事件（如用户修改指定表
或者视图中的数据时）发生时，触发器被自动激活，从而防止对数据进行不正确的修改。 触发器1. 认识触发器触发器是一种特殊的存储过程，
只要满足一定的条件，对数据进行insert、update和delete事件时，数据库系统就会自动执行触发器中定义的程序语句，以进行
维护数据完整性或其他一些特殊的任务。如图8-15所示，触发器可以分为insert、update和delete等3类，每一类根据执行
的先后顺序又可以分成before和arfter触发器。触发器1. 认识触发器触发器的优点（1）触发器自动执行，在表的数据做了任何修
改（比如手工输入或者使用程序采集的操作）之后立即激活。（2）触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码写在前台的做
法更安全合理。（3）触发器可以强制限制，这些限制比用check约束所定义的更复杂。与check约束不同的是，触发器可以引用其他表中
的列。触发器1. 认识触发器触发器的语法格式因为触发器是一种特殊的存储过程，所以触发器的创建和存储过程的创建方式有很多相似之处。创
建触发器的语法格式如下：create trigger trigger_name trigger_time trigger_even
ton table_name for each row trigger_statement 说明（1）create trigger
：创建触发器的关键词。触发器程序是与表有关的数据库对象，当表上出现特定事件时，将激活该对象（2）table_name：触发程序的相
关表。table_name必须引用永久性表。不能将触发程序与temporary表或视图关联起来。（3）trigger_time：是
触发程序的动作时间。它可以是before或after，以指明触发程序是在激活它的语句之前或之后触发。触发器1. 认识触发器触发器的
语法格式（4）trigger_event：指明了激活触发程序的语句的类型。不支持在同一个表内同时存在两个有相同激活触发程序的类型。
trigger_event可以是下述值之一。insert：将新行插入表时激活触发程序。例如，通过insert、load data和
replace语句。update：更改某一行时激活触发程序。例如，通过update语句。delete：从表中删除某一行时激活触发程
序。例如，通过delete和replace语句。（5）for each row：这个声明用来指定受触发事件影响的每一行，都要激活触
发器的动作。目前MySQL仅支持行级触发器，不支持语句级别的触发器（例如create table等语句）。for each row
表示更新（insert、update或者delete）操作影响的每一条记录都会执行一次触发程序。（6）trigger_statem
ent：当触发程序激活时执行的语句。如果打算执行多个语句，可使用begin ... end复合语句结构。这样，就能使用存储子程序中
允许的相同语句。（7）使用触发器时，触发器执行的顺序是before触发器、表数据修改操作、after触发器。其中，before表示
在触发事件发生之前执行触发程序，after表示在触发事件发生之后执行触发器。因此严格意义上讲一个数据库表最多可以设置6种类型的触发
器。 触发器1. 认识触发器触发程序中可以使用old关键字与new关键字。触发程序中可以使用的所谓old关键字与new关键字，实际
上是在触发器事件发生时，MySQL针对要修改数据的表，创建了与本表结构完全一样2个的临时表old和new，old表用于存放在数据修
改过程中既有数据，new表用于存放在数据修改过程中将要更新的数据。当向表插入新记录时，在触发程序中可以利用new关键字访问新记录，
当需要访问新记录的某个字段值时，可以使用“new.字段名”的方式访问。当从表中删除旧记录时，在触发程序中可以利用old关键字访问旧
记录，当需要访问旧记录的某个字段值时，可以使用“old.字段名”的方式访问。触发器1. 认识触发器触发程序中可以使用old关键字与
new关键字。当修改表的某条记录时，在触发程序中可以使用old关键字访问修改前的旧记录、使用new关键字访问修改后的新记录。当需要
访问旧记录的某个字段值时，可以使用“old.字段名”的方式访问。当需要访问修改后的新记录的某个字段值时，可以使用“new.字段名”
的方式访问。 old记录是只读的，只能引用，不能更改。在before触发程序中，可使用“set new.col_name = va
lue”语句更改new记录的值。对于insert语句，只有new是合法的；对于delete语句，只有old才合法；而update语
句可以与new或old同时使用。触发器2 .触发器的创建和管理触发器的创建和验证。触发器是由insert、update和delet
e等事件来触发某种特定操作。满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。
【例8.11】创建一个触发器，当更改表course中某门课的课程号时，同时将score表课程号全部更新。mysql> use te
aching;mysql> delimiter $$mysql> create trigger cno_update after
update-> on course for each row-> begin-> update score set cours
eno=new.courseno -> where courseno=old.courseno;-> end $$mysql
> delimiter ;验证触发器cno_update的功能，代码和执行结果如下。mysql> update course
set courseno =‘c07123’ where courseno=‘c08123’;mysql> select
from score where courseno =''c07123'';说明：（1）在本例中，update cours
e是触发事件，after是触发程序的动作时间，激发触发器update score表相应记录。使用select语句查看score表中
的情况，发现所有原c08123课程编号的记录已更新为c07123。（2）在MySQL触发器中的SQL语句可以关联表中的任意列。但不
能直接使用列的名称标识，那会使系统混淆。（3）在本例中，new和old同时使用。当在course表更新courseno,原来的co
urseno变为old. courseno，把score表old. courseno的记录要更新为new. courseno。触发
器2 .触发器的创建和管理触发器的创建和验证。【例8.12】在teacher表中，定义一个触发器，当一个教师的信息被删除时，把该教
师的编号和姓名添加到de_teacher表中。# 创建一个空表de_teacher,表由tno和tname两列组成。mysql>
create table de_teacher select teacherno,tname -> from
teacher where 1=0;#创建teacher表的触发器mysql> create trigger trig_teac
her->after delete on teacher for each row-> insert into de_tea
cher(teacherno,tname)-> values(old.teacherno, old.tname);验证触发器tri
g_teacher 的功能，代码和执行结果如下。mysql> delete from teacher where tnam
e=‘时观’;mysql> select from de_teacher;触发器2 .触发器的创建和管理查看触发器的定义。既然
触发器是一类特殊的存储过程，那么查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等，也可以通过类似的命令来完成。用户
可以通过show triggers语句来查看触发器的状态。用户也可以通过查询information_schema数据库下的tri
ggers表来查看触发器的信息。下面给出已经验证过的查看触发器的状态和定义的方法的例子。mysql> show triggers
; mysql> select from information_schema.triggers; mysql> select
from information_schema.triggers -> where trigger_
name=''de_teacher''; 触发器3. 使用触发器MySQL中的触发器在程序设计的应用非常广泛，常见的有实现数据完成性的
复杂约束、数据管理过程中的冗余数据处理以及外键约束的级联操作等，都可以利用触发器实现应用系统的自动维护。触发器应用举例。
对于InnoDB存储引擎的表而言，由于支持外键约束，在定义外键约束时，通过设置外键的级联选项cascade、set null或者n
o action（restrict），外键约束关系可以交由InnoDB存储引擎自动维护。触发器3. 使用触发器触发器应用举例。【例
8.13】创建一个触发器，当删除student表某个人的记录时，删除score表相应的成绩记录。mysql> delimiter
$$mysql> create trigger stu_delete after delete-> on student for
each row-> begin-> delete from score where studentno=old.student
no;-> end $$mysql> delimiter ; 验证触发器stu_delete的功能，代码和执行结果如下。m
ysql> delete from student where studentno=‘19112100072’;mysql
> select from score where studentno=''19112100072‘;说明：（1）在本例
中，使用select语句查看score表中的情况，可以看到已没有19112100072学生的成绩记录。（2）本例中，在studen
t执行delete事件之后，在触发器中引用的score表的studentno字段要用old. studentno表示。触发器3.
使用触发器触发器应用举例。【例8.14】在de_teacher表上创建before insert和after insert这两个触
发器。在向department表中插入数据时，观察这两个触发器的触发顺序。mysql> create table bef_aft
er select teacherno,tname -> from teacher where 1=0;mysql>
alter table bef_after -> add tig_time timestamp not NULL
DEFAULT NOW();mysql> create trigger before_insert before inse
rt-> on de_teacher for each row-> insert into bef_after-> set t
eacherno =‘t11111’, tname =‘卫小林’;mysql> create trigger after_in
sert after insert-> on de_teacher for each row-> insert into
bef_after-> set teacherno =‘t22222’, tname =‘泰小林’;验证触发器before_in
sert和after_insert 的功能，代码和执行结果如下。mysql> insert into de_teacher va
lues(‘t12345’, ‘王含晨’);mysql> select from bef_after;说明：MySQL中，触发
器执行的顺序是before触发器、表操作（insert、update 和delete）、after触发器。本例由于程序较短，运行速
度快，虽然记录的时间在1秒之内完成，但记录的插入顺序可以说明before触发器的执行早于after触发器。触发器3. 使用触发器使
用触发器的注意事项（1）触发程序中如果包含select语句，该select语句不能返回结果集。（2）同一个表不能创建两个相同触发时
间、触发事件的触发程序。（3）触发程序中不能使用以显式或隐式方式打开、开始或结束事务的语句，如start transaction、
commit、rollback或者set autocommit=0等语句。（4）MySQL触发器针对记录进行操作，当批量更新数据时
，引入触发器会导致更新操作性能降低。（5）在MyISAM存储引擎中，触发器不能保证原子性。InnoDB存储引擎支持事务，使用触发器
可以保证更新操作与触发程序的原子性，此时触发程序和更新操作是在同一个事务中完成。触发器3. 使用触发器使用触发器的注意事项（6）I
nnoDB存储引擎实现外键约束关系时，建议使用级联选项维护外键数据；MyISAM存储引擎虽然不支持外键约束关系时，但可以使用触发器
实现级联修改和级联删除，进而维护“外键”数据，模拟实现外键约束关系。（7）使用触发器维护InnoDB外键约束的级联选项时，数据库开
发人员究竟应该选择after触发器还是before触发器？答案是：应该首先维护子表的数据，然后再维护父表的数据，否则可能出现错误。
（8）MySQL的触发程序不能对本表进行更新语句（例如update语句）。触发程序中的更新操作可以直接使用set命令替代，否则可
能出现错误信息，甚至陷入死循环。（9）在before触发程序中，auto_increment字段的new值为0，不是实际插入新记录
时自动生成的自增型字段值。（10）添加触发器后，建议对其进行详细的测试，测试通过后再决定是否使用触发器。触发器4. 删除触发器删除
触发器指删除数据库中已经存在的触发器。MySQL使用drop trigger语句来删除触发器。其基本形式如下。drop trigg
er [schema_name.]trigger_name 例如，删除触发器stu_score的代码如下：mysql> drop
trigger stu_score; 事件及其应用1. 认识事件MySQL中的事件（Event）又称事件调度器(Event Sc
heduler) 是一种定时任务机制，可以用于定时执行诸如删除记录、对数据进行汇总等某些特定任务，来取代原先只能由操作系统的计划任
务来执行的工作。MySQL的事件调度器可以精确到每秒钟执行一个任务，比操作系统的计划任务（如：Linux下的cron或Window
s下的任务计划），只能精确到每分钟执行一次有实时优势。对于一些对数据实时性要求比较高的应用，如股票交易、火车购票、球赛技术统计等就
非常适合。一些对数据管理的定时性操作不再依赖外部程序，直接使用数据库本身提供的功能即可。事件及其应用1. 认识事件（1）开启事件
调度器。MySQL的事件调度器是MySQL数据库服务器的一部分，负责调用事件，并不断地监视一个事件是否需要调用。要创建事件，必须打
开调度器。可以使用系统变量@@event_scheduler来打开事件调度器，true（或1或on）为打开，false（或0或of
f）为关闭。要开启event_scheduler，可执行下面的语句。set @@global.event_scheduler =
true; 也可以在MySQL的配置文件my.ini中加上一行，然后重启MySQL服务器。event_scheduler = 1
事件及其应用1. 认识事件（2）查看事件调度器。要查看当前是否已开启事件调度器，可执行如下相关SQL语句。mysql> set
@@global.event_scheduler = true;mysql> show variables like ‘event
_scheduler’;mysql> select @@ event_scheduler;事件及其应用2. 创建事件创建事件可以
创建在某一时刻发生的事件、指定区间周期性发生的事件，以及在事件中调用存储过程或存储函数的实际应用。创建事件的一般格式。创建事件可以
使用create event语句。语法格式如下。create event [if not exists] event_name
on schedule schedule[on completion [not] preserve][enable|disab
le|disable on slave] [comment''comment'']do sql_statement; 其中，sche
dule：at timestamp [+interval interval]|every interval[starts time
stamp [+ interval interval]][ends timestamp[+ interval interval]]
interval：count { year|quarter|month|day|hour|minute |week|second
|year_month|day_hour|day_minute |day_second|hour_minute|hour_seco
nd|minute_second} 说明：（1）event_name：表示事件名。（2）schedule：是时间调度，表示事件何时
发生或者每隔多久发生一次。at子句：表示事件在某个时刻发生。timestamp表示一个具体的时间点，后面还可以加上一个时间间隔，表
示在这个时间间隔后事件发生。interval表示这个时间间隔，由一个数值和单位构成，count是间隔时间的数值。every子句：表
示在指定时间区间内每隔多长时间事件发生一次。starts子句指定开始时间，ends子句指定结束时间。（3）do sql_state
ment：事件启动时执行的SQL代码。如果包含多条语句，可以使用begin…end复合结构。事件及其应用2. 创建事件创建某个时
刻发生的事件【例8.14】创建现在立刻执行的事件direct1，创建一个表test1。mysql> use mysqltest;
mysql> create event direct1-> on schedule at now()-> do-> cre
ate table test1(timeline timestamp);mysql> show tables;mysql> sel
ect from test1;【例8.15】创建现在立刻执行的事件direct2，5秒后创建一个表test2。mysql> c
reate event direct2-> on schedule at current_timestamp +interv
al 5 second-> do-> create table test2(timeline timestamp);事件及其
应用2. 创建事件创建在指定区间周期性发生的事件【例8.16】创建事件test1_insert，每秒插入一条记录到数据表test
1。mysql> create event test1_insert-> on schedule every 1 seco
nd-> do-> insert into test1 values (current_timestamp);mysql>
select from test1; #5秒之后执行此语句【例8.17】创建事件startweeks，要求从
下周开始，每个周都清空test1表，并且在2017年的08月31日12:00时结束。mysql> delimiter $$mysq
l> create event startweeks-> on schedule every 1 week-> st
arts curdate()+interval 1 week-> ends ''2017-08-31 12:00:00''-> do-> begin-> truncate table test1;-> end $$mysql> delimiter; 事件及其应用2. 创建事件在事件中调用存储过程或存储函数【例8.18】存储过程proc_stu()用于来查询学生信息情况的，创建事件stu_week每周查看一次学生的情况。mysql> delimiter $$mysql> create event stu_week-> on schedule every 1 week-> do-> begin-> call teaching.proc_stu();-> end $$mysql> delimiter;事件及其应用3. 管理事件查看事件（1）MySQL中查看所有事件event的语法如下。show events [from schema_name][like ''pattern’|where expr] 可以直接利用命令“show events; ”查看数据库mysqltest 中的事件。为了直观一些，采用如下的方法查看。【例8.19】格式化显示所有事件event。 mysql> show events\G（2）MySQL中查看event的创建信息的语法如下。mysql> show create event event_name;例如，查看stu_week的创建信息的代码如下：mysql> show create event stu_week; 事件及其应用3. 管理事件修改事件。MySQL中可以通过alter event语句来修改事件的定义和相关属性。具体修改格式如下：alter event event_name[on schedule schedule][rename to new_event_name]on completion[not]preserve][comment''comment''][enable|disable][do sql_statement] 例如，可以临时关闭事件或再次让它活动，修改事件的名称并加上注释等。【例8.20】 对事件test1_insert进行操作如下。临时关闭test1_insert事件；开启test1_insert事件，将每天清空test1表改为7天清空一次；重命名事件test1_insert并加上注释。mysql> alter event test1_insert disable;mysql> alter event test1_insert enable;mysql> alter event test1_insert on schedule every 7 day;mysql> alter event test1_insert -> rename to insert_test1 comment ''表test1的数据操作‘;事件及其应用3. 管理事件删除事件。MySQL中用drop event删除事件。删除事件的语法格式如下。drop event [if exists][database name.]event_name 例如，删除事件insert_test1的代码如下。mysql>drop event insert_test1；小结本章介绍了MySQL数据库的存储过程，以及利用存储过程实现的游标和事件的创建、应用和管理。而触发器则是一种能够自动执行的特殊存储过程。存储过程和存储函数都是用户自己定义的SQL语句的集合。它们都存储在服务器端，只要调用就可以在服务器端执行。学习本章后应该重点掌握如下内容：存储过程的创建和使用，存储过程和存储函数的区别。触发器的创建和使用，利用触发器能够实现哪些操作。事件的创建过程和使用场合。游标有什么作用，如何利用游标访问结果集。 |
|
