【数据库——MySQL】（14）过程式对象程序设计——游标、触发器

1. 游标

游标一般和存储过程一起配合使用。

1.1 声明游标

要使用游标，需要用到 DECLARE CURSOR 语句：

DECLARE 游标名 CURSOR FOR SELECT语句

注：一个存储过程可以声明多个游标，但是一个块中的每一个游标必须具有唯一的名字。

1.2 打开游标

要使用游标，需要用到 OPEN 语句：

OPEN 游标名;

1.3 读取游标

要使用游标，需要用到 FETCH...INTO 语句：

FETCH 游标名 INTO 变量名, ...

1.4 关闭游标

要使用游标，需要用到 CLOSE 语句：

CLOSE 游标名;

注：游标使用完以后要 及时关闭！

1.5 游标示例

【例】在数据库 score 中查询某个学院某门课程的成绩（方法一）

drop PROCEDURE if EXISTS p1;
delimiter $
create PROCEDURE p1(in did char(10), in lid char(10))
begindeclare stu_id char(15);declare lname char(20);declare stu_score int;declare score_count int;		# 统计记录数declare i int default 1;# 设置游标declare c_score CURSOR forselect stu.id, lesson.lessonName, score.scorefrom stu join score on stu.id = score.stuIdjoin lesson on score.LessonId = lesson.lessonidjoin department on stu.departmentId = department.idwhere stu.departmentId = did and score.LessonId = lid;select count(*) into score_countfrom (select stu.id, lesson.lessonName, score.scorefrom stu join score on stu.id = score.stuIdjoin lesson on score.LessonId = lesson.lessonidjoin department on stu.departmentId = department.idwhere stu.departmentId = did and score.LessonId = lid)a;open c_score;    # 打开游标while i <= score_count doFETCH c_score into stu_id, lname, stu_score;		# 读取游标select stu_id, lname, stu_score;		# 使用游标set i = i + 1;end while;close c_score;		# 关闭游标end $
delimiter ;# 一般在命令列界面运行可以明显看出所要查看的数据
call p1('101', '101');

如果是在可视化界面运行 call p1('101', '101');，效果如下（不方便查看，数据是一条条输出的）：

在命令列界面运行 call p1('101', '101');，方便查看：

我们也尝试不用存储过程直接输出结果，发现结果是一样的（除了输出顺序不同）：

select stu.id, lesson.lessonName, score.scorefrom stu join score on stu.id = score.stuIdjoin lesson on score.LessonId = lesson.lessonidjoin department on stu.departmentId = department.idwhere stu.departmentId = '101' and score.LessonId = '101';

2. 触发器

2.1 创建触发器

用代码创建：

CREATE TRIGGER 触发器名 触发时刻 触发事件ON 表名 FOR EACH ROW 触发器动作

不过为了方便，我们可以直接使用可视化界面创建触发器。

2.2 修改触发器

建议直接在 Navicat 上进行创建、修改、删除触发器，在设计表中点击触发器即可。

2.3 删除触发器

前面说了可以直接使用 Navicat 删除触发器。

下面也给出相关代码：

DROP TRIGGER [数据库名.]触发器名

2.4 触发器类型

触发器只有 3 种类型：插入、更新 和 删除。每种类型又有 2 种情况：BEFORE 和 AFTER。

每个类型每种情况的触发器在一张表只能设置 1 个，即一张表最多只有 6 个触发器。

对于 事件、全局锁、表锁、行锁和死锁 的内容在此不过多赘述，大家要是感兴趣可以自行了解~

2.5 触发器示例

使用 bank 数据库，创建表 info，利用触发器完成以下内容。下面会给出如何用 SQL 语句创建触发器并完成相应功能。

表 info 的内容如下：

1. 插入存取款记录(触发器 tri_insertinfo)——需要判断用户账号是否存在。

   drop trigger if EXISTS tri_insertinfo;
   delimiter $
   create TRIGGER tri_insertinfo before insert on info for each row
   begindeclare a int;select count(*) into afrom accountwhere account.id = new.accountid;if a = 0 then SIGNAL SQLSTATE '12345' set message_text = '账号不存在';end if;if new.type = 'save' thenupdate accountset ck = ck + new.balancewhere account.id = new.accountid;elseupdate accountset ck = ck - new.balancewhere account.id = new.accountid;end if;end$
   delimiter ;
   

   查看当前所有用户存款：

   select * from account;
   

   

   给张三存入 500 元，并查看当前全部用户存款：

   insert into info values(null, '001', 'save', 500);
   select * from account;
   

   

   李四取出 500 元：

   insert into info values(null, '002', 'load', 500);
   select * from account;
   

   

   给用户编号为 005 （不存在用户）存入 500 元：

   insert into info values(null, '005', 'save', 500);		# 会显示账号不存在
   

   

2. 创建触发器 tri_deleteinfo 撤销存取款记录（info 中的记录）。

   drop trigger if EXISTS tri_deleteinfo;
   delimiter $
   create TRIGGER tri_deleteinfo after delete on info for each row
   beginif old.type = 'save' thenupdate accountset ck = ck - old.balancewhere account.id = old.accountid;elseupdate accountset ck = ck + old.balancewhere account.id = old.accountid;end if;end$
   delimiter ;
   

   撤销编号为 16 的取款操作：

   delete from info where id = 16;		# 撤销id为16的操作
   select * from account;
   

   
   可见李四的账户从 5550 恢复到上次的 6050。

   清除所有的存取款操作：

   delete from info;		# 撤销所有存取款操作，金额恢复
   select * from account;
   

   
   可见张三的账户金额出现了负数，这其实是异常结果，所以大家可以自行在源代码里面新增报告异常的语句。

   按照道理来说不会出现这种情况，可能是我之前操作数据时改变了记录~

3. 创建触发器 tri_updateinfo，用于修改用户存取款金额数。

   drop trigger if EXISTS tri_updateinfo;
   delimiter $
   create TRIGGER tri_updateinfo after update on info for each row
   beginif old.type = 'save' thenupdate accountset ck = ck - old.balance + new.balancewhere account.id = old.accountid;elseupdate accountset ck = ck + old.balance - new.balancewhere account.id = old.accountid;end if;end$
   delimiter ;
   

   其实这个操作基本上用不上，毕竟用户存、取多少就是多少，而且这个操作可以用前面两个触发器实现，因此这里不展示修改操作了~

参考书籍

《MySQL实用教程（第4版）》

上一篇文章：【数据库——MySQL】（13）过程式对象程序设计——存储函数、错误处理以及事务管理