互联网数据库Mysql高级——触发器、游标

学习目标：

既然我已经踏上这条道路，那么，任何东西都不应妨碍我沿着这条路走下去，去学习呗！GO ！GO ！GO

1.了解触发器的作用

2.了解游标的作用

3.熟练掌握触发器的创建、删除操作

4.熟练掌握定义触发器的规则

---

触发器（trigger）

一、触发器简介

       触发器是和表关联的特殊的存储过程，可以在插入，删除或修改表中的数据时触发执行，比数据库本身标准的功能有更精细和更复杂的数据控制能力。
       触发器的优点：

     ①安全性：可以基于数据库的值使用户具有操作数据库的某种权利。例如，不允许下班后修改数据库数据；

     ②审计：可以跟踪用户对数据库的操作；

     ③实现复杂的数据完整性规则。例如，触发器可回退任何企图吃进超过自己保证金的期货；

     ④提供了运行计划任务的另一种方法。例如，如果公司的帐号上的资金低于 5 万元则立即给财务人员发送警告数据。

---

二、MySQL 中使用触发器

     1、创建触发器

创建触发器的技巧就是记住触发器的四要素：

     监控地点：table；

     监控事件：insert/update/delete；

     触发时间：after/before；

     触发事件：insert/update/delete。

创建触发器的基本语法如下所示：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt

     trigger_name：触发器的名称；

     tirgger_time：触发时机，为 BEFORE 或者 AFTER；

     trigger_event：触发事件，为 INSERT、DELETE 或者 UPDATE；

     tb_name：表示建立触发器的表名，在哪张表上建立触发器；

     trigger_stmt：触发器的程序体，可以是一条 SQL 语句或者是用 BEGIN 和 END 包含的多条语句；

     FOR EACH ROW 表示任何一条记录上的操作满足触发事件都会触发该触发器。

注意：对同一个表相同触发时间的相同触发事件，只能定义一个触发器。

案例：使用的数据库（shop）中有一张商品表（product）和订单表（orders），表结构及测试数据如下所示：

create table orders(
     oid int(11) not null auto_increment comment '订单编号',
     pid int(11) not null comment '商品编号',
     num int(11) not null comment '购买数量',
     primary key(oid)
) engine=innodb default charset=utf8;
 

create table product(
     pid int(11) not null auto_increment comment '商品编号',
     pname varchar(255) not null comment '商品名称',
     num int(11) not null comment '库存',
     primary key(pid)
) engine=innodb auto_increment=4 default charset=utf8;

insert into product values ('1','p30','10');
insert into product values ('2','mate20','50');
insert into product values ('3','nova5','100');

当用户购买商品时，需要往订单表插入订单记录，同时更新商品表库存信息。例如用户买了 3 个 p30，我们需要做两件事：
（1）往订单表插入一条记录：

insert into orders values(null,1,3);

（2）更新 p30 的库存：

update product set num = num-3 where pid=1;

以上这两条 sql 语句实现的是一个业务操作，从数据库的层面来讲，这样的业务我们就可以用触发器来处理。

案例：创建触发器，当用户购买 p30 时，同时更新 p30 库存记录，代码如下所示：

-- 创建触发器
CREATE trigger mytg1
after insert on orders
for each row 
BEGIN  
   update product set num = num-3 where pid=1;
end;
-- 往订单表插入记录
insert into orders values(null,1,3);
-- 查询商品表商品库存更新情况
select * from product;

运行结果如下：

       测试数据商品表 p30 有 10 台，当订单表插入 3 台 p30 的订单记录后，触发器就会执行更新商品表的操作，使 p30 库存减

少 3。

       但是这里还存在一个问题，因为代码中的数值是写死的，无论用户买什么商品，买几个，每次购买都会让 p30 的库存减

3，所以我们需要将固定数值替换成变量，这就要用到触发订单表触发器的新纪录。

---

     2、触发器新旧记录

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据：

       在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

       在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；

       在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据。

案例：创建触发器，当用户购买商品时，同时更新对应商品库存记录

-- 删除之前的触发器
drop trigger if exists mytg1;
-- 创建触发器
create trigger mytg1
after insert on orders
for each ROW
begin
     update product set num=num-new.num where pid=new.pid; 
end;
-- 往订单表插入记录
insert into orders values(null,2,1);
-- 查询商品表商品库存更新情况
select * from product;

运行结果如下：

这次从订单表中插入的是 1 台 mate20，上图可以看到商品表中 mate20 的库存减少 1。

       这个案例用户下订单，同时更新商品库存的问题解决了，但是问题并不总是那么简单，一个完整的应用程序还要考虑很多问

题：比如用户不想要了，撤销订单，商品的库存是否要恢复呢？

案例：创建触发器，当用户删除订单时，同时更新对应商品库存记录。

-- 创建触发器
create trigger mytg2
after delete on orders
for each row
begin
update product set num = num+old.num where pid=old.pid;
end;
-- 删除订单记录
delete from orders where oid = 2;
-- 查询商品表商品库存更新情况
select * from product;

运行结果如下：

从运行结果中可以看到，mate20 的订单撤销后，库存数据恢复了。

---

     3、before 和 after 的区别

如果我们购买的商品数量超过库存会怎样？

-- 往订单表插入记录
insert into orders values(null,1,20);
-- 查询商品表商品库存更新情况
select * from product;

运行结果如下：

       P30 的库存被买成负数了，这明显是错误的，想要避免这个问题，就需要使用 before 触发器，下订单前，先查看商品库

存，如果购买数量超过库存，则修改订单数量为商品最大库存。

案例：创建触发器，当用户删除订单时，同时更新对应商品库存记录，当订单商品数量超过库存时，修改订单数量为最大库存。

-- 创建 before 触发器
create trigger mytg3
   before insert on orders
   for each row
begin
    declare n int default 0;-- 当前库存
    -- 查询购买商品的库存
    select num into n from product where pid = new.pid;
   if new.num>n then
      set new.num = n;
   end if;
    update product set num = num-new.num where pid=new.pid;
end;
-- 往订单表插入记录
insert into orders values(null,1,20);
-- 查询商品表商品库存更新情况
select * from product;
-- 查询订单表
select * from orders;

运行结果如下：


       从运行结果可以看到，虽然用户购买了 20 台 p30，但实际只购买了 7 台，并且商品的库存也变为 0，不会出现负数了。

       由此可见，实际开发当中，增删改查这样的基本操作并不是关键，业务本身才是关键，有一点没有考虑到，就可能造成损失。

---

游标（cursor）

一、游标简介

       游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

       游标有下面这些特征：

              ① 游标是只读的，也就是不能更新它；

              ② 游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；

              ③ 避免在已经打开游标的表上更新数据。

---

二、MySQL 中游标的使用

       ** 创建游标：

            创建游标的语法包含四个部分：

              定义游标：declare 游标名 cursor for select 语句；

              打开游标：open 游标名；

              获取结果：fetch 游标名 into 变量名[，变量名]；

              关闭游标:close 游标名；

案例：创建一个过程 p1，使用游标返回 test 数据库中 student 表的第一个学生信息。

-- 定义过程
create procedure p1()
begin 
     declare id int;
     declare name varchar(20);
     declare age int;
     -- 声明游标
     declare mc cursor for select * from student;
     -- 打开游标
     open mc;
     -- 提取结果
     fetch mc into id,name,age;
     -- 显示获取的结果
     select id,name,age;
     close mc;
end;
-- 调用过程
call p1();

运行结果如下：

       上面的代码可以从查询的学生信息中提取一行数据（第一行），如果要提取所有数据，需要循环操作。

案例：在 test 数据库创建一个 student2 表，创建一个过程 p2，使用游标提取 student 表中所有学生信息插入到 student2 表

中。

-- 定义过程
create procedure p2()
begin 
   declare id int;
   declare name varchar(20);
   declare age int;
   declare flag int default 0;
   -- 声明游标
   declare mc cursor for select * from student;
   declare continue handler for not found set flag=1;
   -- 打开游标
   open mc;
   -- 提取结果
   a:loop -- 循环提取数据
       fetch mc into id,name,age;
       if flag=1 then -- 当无法fetch事触发continue handler
           leave a;
       end if;
       -- 将提取的每一行数据插入到student2表中
       insert into student2 values(id,name,age);
   end loop;
   close mc;
end;
-- 调用过程
call p2();
-- 查询student2表
select * from student2;

运行结果如下：