CREATE TABLE `oder_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键‘,
  `detail_name` varchar(255) DEFAULT NULL COMMENT ‘订单明细‘,
  `price` decimal(10,2) DEFAULT NULL COMMENT ‘价格‘,
  `oid` int(11) DEFAULT NULL COMMENT ‘订单id‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT=‘订单明细表‘;

准备的数据

INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (1, ‘毛巾‘, 20.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (2, ‘牙膏‘, 15.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (3, ‘杯子‘, 5.00, 1);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (4, ‘毛巾‘, 15.00, 2);
INSERT INTO `zszxz`.`oder_detail`(`id`, `detail_name`, `price`, `oid`) VALUES (5, ‘杯子‘, 15.00, 2);

简单的使用游标

查询oid为1 的订单明细名称的结果集作为游标；

打开游标后抓取每行将结果赋值给变量name

CREATE PROCEDURE printName()
BEGIN
	-- 订单名称
    declare name varchar(20);
    -- 创建游标
    declare cur cursor for select detail_name from oder_detail where oid = ‘1‘;
    -- 打开游标
    open cur;
		fetch cur into name;
		select name;
    -- 关闭游标
    close cur;
END;

调用储存过程

call printName;

打印结果如下，只有一条数据，说明上述方式只在游标中抓取到一条数据，而且是表里面行号最小的行；

name
------
毛巾

在循环中使用游标

将查询oid为1的结果集赋值给游标；通过游标抓取每行将订单明细名称和价格分别赋值给变量 name 和 detail_price; 在循环无法继续时会出现 SQLSTATE ‘02000‘ ; 即此通过变量 continue 时设置 done 为1 代表 true，此时循环结束，跳出循环；

drop procedure if exists  printDetail;
CREATE PROCEDURE printDetail()
BEGIN
	-- 订单名称
    declare name varchar(20);
		-- 价格
    declare detail_price decimal(8,2);
    -- 结束标志变量（默认为假）
    declare done boolean default 0;
    -- 创建游标
    declare cur cursor for select detail_name,price from oder_detail where oid = ‘1‘;
    -- 指定游标循环结束时的返回值
    declare continue HANDLER for SQLSTATE ‘02000‘ set done = 1;
    -- 打开游标
    open cur;
    -- 循环游标数据
    detail_loop:loop
			-- 根据游标当前指向的一条数据
			fetch cur into name,detail_price;
			select name , detail_price;
			-- 判断游标的循环是否结束
			if done then
					-- 跳出游标循环
					leave detail_loop;    
			end if;
		-- 结束游标循环
    end loop;
    -- 关闭游标
    close cur;
END;

调用储存过程

-- 调用存储过程
call printDetail();

美中不足的是会多遍历最后一行，如果要精细处理还是需要自定义标志位进行跳出循环；

三触发器

3.1触发器的概念

触发器是指当表发生改变的时候触发的动作；听起来有点抽象，举个栗子，当你往表中插入数据的时候，此时表发生了改变，现在想要在每次插入数据之前检测所有的入参是否都是小写，此时就可以用触发器来检测；经过上面的分析知道使用一个基本的触发器，至少表要发生改变，还要满足一个被触发的事件；

表发生改变通常指增删改，其动作可以发生在增删改之前或者之后；触发事件就是我们需要写的储存过程；

update (after/ before)
insert (after/ before)
delete (after/ before)

3.2 触发器的基本语法

创建触发器： create trigger 触发器名称触发动作 on 表名 for each row [触发事件]
删除触发器：drop trigger 触发器名称；
查看触发器：show triggers;

tip : 触发器是依赖于表创建，没有表就没有触发器，比如视图，临时表都不是真实的表，它们是没有触发器；一般来说每个表都有触发器的限制，一般最多支持6个不同类型的触发器；由于使用触发器会频繁的改变表的每行，故其十分影响性能，特别对一些更新频率比较快的大表，如果设置触发器就非常占用系统资源；一般来说触发器用在表变动较小的小表, 不使用触发器就立即删除；

3.3 insert 触发器示例

创建触发器; 创建一个触发器 getPrice 作用于 oder_detail 表的每行，每当插入数据之后就查询这条订单明细的价格赋值给变量 @price ；小伙伴可能会疑惑 NEW 是何物，其是一张虚表，记录者被插入数据的行；故我们能在NEW表中获取每次插入的数据；

-- insert 触发器
CREATE TRIGGER getPrice AFTER INSERT ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;

检测插入触发器; 插入一条数据，使用查询语句查询变量显示为 20；

-- 检测插入触发器

INSERT INTO `oder_detail`( `detail_name`, `price`, `oid`) VALUES ( ‘脸盆‘, 20.00, 2);

select @price;

删除触发器;

-- 删除触发器
drop trigger getPrice;

3.4 update 触发器示例

将插入后触发器改为更新后的触发器如下，只需要改动 after insert 为 after update 即可；

CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT NEW.price INTO @price;

将之前的插入的SQL语句进行修改价格,并查询价格,此时价格为30；NEW虚表储存的是即将更新的数据；

UPDATE `oder_detail` SET `price` = 30.00 WHERE `id` = 6;

select @price;

删除触发器

-- 删除触发器
drop trigger getPrice;

将更新触发器的NEW表改为OLD表

CREATE TRIGGER getPrice AFTER update ON oder_detail FOR EACH ROW
SELECT OLD.price INTO @price;

更新价格为40

UPDATE `oder_detail` SET `price` = 40.00 WHERE `id` = 6;

此时查询价格为30，说明OLD表触发的是原始数据值；

select @price;

tip ：更新触发器主要是要搞懂OLD存放原始数据，NEW存放即将更新的数据；NEW表可以设置更改值，二OLD表是只读；

3.5 delete 触发器

将更新触发器改为 delete 触发器, 之前我们省略了 begin, end 如果是多条执行语句则需要加上；

CREATE TRIGGER getPrice AFTER delete ON oder_detail FOR EACH ROW
begin 
SELECT OLD.price INTO @price;
end;

删除之前的SQL数据

delete from oder_detail where `id` = 6;

查询价格为40，OLD表存放的是将要被删除的数据；

select @price;

SQL-mysql游标与触发器

码农公寓

一 前言

二 游标

2.1 游标的概念

2.2 游标的语法

2.3 使用游标

三 触发器