【MySQL】游标和触发器

一、游标

1.1 什么是游标

1、使用背景
在我们使用update或者delete操作数据时，一般都会根据条件语句查询出很多条记录组成的数据集，然后一次性批量操作

假设我们想要对这个结果集中的数据 一行一行的进行操作，比如某个条件满足后，就不继续往下操作了，这个时候就要用到游标了

游标可以在存储过程和函数中使用

2、使用步骤

• 声明游标（位置放在变量声明语句的后面，因为变量声明必须紧跟begin）
  MySQL、DB2、SQL server和Maria DB中的声明语法为
  DECLARE cursor_name CURSOR FOR 查询语句;
  Oracle、Postgresql中的声明语法为
  DECLARE cursor_name CURSOR IS 查询语句;
• 打开游标
  OPEN cursor_name;
• 使用游标：从游标中获取数据（注意：var_name必须在声明游标之前就定义好）
  FETCH cursor_name INTO var_name,var_name2...;
  这句话的作用是使用cursor_name来读取当前行，并将数据保存到变量var_name中，游标指针指向下一行，如果读取的数据行有多个字段，那就在INTO关键字后赋值给多个变量
• 关闭游标
  CLOSE cursor_name;

3、使用案例
新建一个博客表t_blog_view，设定一个值num
看看最少需要累加多少篇博客的浏览量才能达到这个值（根据浏览量做一个降序排列）

CREATE TABLE `t_blog_view`  (
  `blog_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '博客名称',
  `blog_author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '博客作者',
  `blog_views` int(20) NOT NULL COMMENT '博客浏览量'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `t_blog_view` VALUES ( 'Mybatis系列', 'Decade0712', 2000);
INSERT INTO `t_blog_view` VALUES ( 'Spring系列', 'Decade0712', 4000);
INSERT INTO `t_blog_view` VALUES ( '设计模式系列', 'Decade0712', 6000);
INSERT INTO `t_blog_view` VALUES ( 'JVM系列', '十年', 8000);
INSERT INTO `t_blog_view` VALUES ( 'MySQL基础', 'Decade0712', 4000);
INSERT INTO `t_blog_view` VALUES ( 'Java8新特性', '十年', 5500);

我们定义一个存储过程进行测试

DELIMITER //

CREATE PROCEDURE test_cursor(IN num DOUBLE,OUT res_count INT)
BEGIN
	# 声明局部变量
	DECLARE sum_views DOUBLE DEFAULT 0.0; # 记录累加浏览量
	DECLARE current_views DOUBLE;  # 记录当前博客浏览量
	DECLARE blog_count INT DEFAULT 0; # 记录累加博客数
	
	# 1、声明游标
	DECLARE blog_cursor CURSOR FOR SELECT blog_views from t_blog_view ORDER BY blog_views DESC;
	
	# 2、打开游标
	OPEN blog_cursor;
	
	# 3、使用游标，因为要累加所以使用循环语句
	REPEAT
		FETCH blog_cursor INTO current_views;
		
		SET sum_views = sum_views + current_views;
		SET blog_count = blog_count + 1;
		UNTIL sum_views >=  num
	END REPEAT;
	
	# 把累加的博客数赋值给输出变量
	SET res_count = blog_count;

	# 4、关闭游标
	CLOSE blog_cursor;
END //

DELIMITER ;

# 进行调用
CALL test_cursor(10086,@res_count);
SELECT  @res_count;

# 删除存储过程
DROP PROCEDURE test_cursor;

结果如下

4、优缺点

• 优点：使用游标，能够逐条读取结果集中的数据
• 缺点：使用游标，会对数据进行加锁，在业务并发量大时，会影响业务的效率，并且会消耗系统内存资源

二、触发器

1、使用场景
假设我们现在有2张表商品表和库存表，我们新引入一种商品时，除了要修改商品表的数据，还要修改库存表的数据

为了保证不遗漏任何一个动作，我们一般使用事务将其包裹起来，使这两个动作成为一个原子操作

或者使用触发器，让商品表数据插入的动作自动触发库存表数据插入的动作

2、概述
触发器是由事件来触发某个操作，包括INSERT、UPDATE、DELETE事件。事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句的时候就相当于事件发生了，就会自动激发触发器去执行相应的操作

3、触发器的创建

CREATE TRIGGER 触发器名
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

• 表名：触发器监控的对象
• BEFORE|AFTER：表示触发的时间，是在事件发生之前还是发生之后
• INSERT|UPDATE|DELETE：表示触发的事件，是插入数据事件、更新数据事件还是删除数据事件
• FOR EACH ROW：每操作一条表中的数据，就激发一次触发器
• 触发器执行的语句块：可以是单条语句块，也可以是BEGIN...END包裹的复杂语句块

4、举例
我们新建2张表，test_triggle和test_triggle_log，每当我们要向test_triggle中插入数据时，先在test_triggle_log中记录日志

CREATE TABLE test_triggle(
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);

CREATE TABLE test_triggle_log(
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);

创建触发器

DELIMITER //
CREATE TRIGGER test_trigger_tir
BEFORE INSERT ON test_triggle
FOR EACH ROW
BEGIN
	INSERT INTO test_triggle_log(t_log)
	VALUES('before insert to...');
END //
DELIMITER ;

INSERT INTO test_triggle(t_note)
VALUES('test insert DEMO1...');

SELECT * FROM test_triggle;
SELECT * FROM test_triggle_log;

结果如下，test_triggle_log表中自动增加了一条记录

案例二：我们向表test_triggle中插入数据前，需要先对这条记录的id做一个判断，如果是双数，就报错，否则就正常插入

注意：我们使用NEW表示INSERT要插入的那条数据，要获取某个字段，使用NEW.column即可
使用OLD表示DELETE要删除的那条数据

DELIMITER //
CREATE TRIGGER test_trigger_tir_single_num
BEFORE INSERT ON test_triggle
FOR EACH ROW
BEGIN
	# 创建一个变量，将当前记录的id对2取余的结果赋值给它，以便于后续做单双数的判断
	DECLARE is_single INT;
	SELECT NEW.id % 2 INTO is_single;
	
	IF is_single = 0 THEN
		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = 'id为双数无法插入';
	END IF;
END //
DELIMITER ;

# 测试
INSERT INTO test_triggle(id,t_note) VALUES(1,'test insert DEMO1...');
INSERT INTO test_triggle(id,t_note) VALUES(2,'test insert DEMO2...');

# 查看test_triggle表是否正常插入数据
SELECT * from test_triggle;

结果如下，id为双数时，无法插入数据

5、查看触发器

# 查看所有触发器的定义
SHOW TRIGGERS;

# 查看指定触发器的定义
SHOW CREATE TRIGGER 触发器名称;

# 从系统库information_schema中查看触发器的定义
SELECT * FROM information_schema.TRIGGERS;

6、删除触发器

DROP TRIGGER IF EXISTS 触发器名称;

7、触发器的优缺点

• 优点：
  • 可以保证数据的完整性
  • 可以通过触发器帮助我们记录数据的操作日志
  • 可以在操作数据前，对数据的合法性做一个检验
• 缺点：
  • 可读性差
  • 相关数据的变更，可能会导致触发器报错

8、注意点
如果在子表中定义了外键约束，且子表的中又定义了基于此表UPDATE或者DELETE操作的触发器
如果父表数据修改引发了子表的数据变化，在这种情况下并不会激发触发器

如有错误，欢迎指正！！！