一、游标
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、使用游标，因为要累加所以使用循环语句REPEATFETCH blog_cursor INTO current_views;SET sum_views = sum_views + current_views;SET blog_count = blog_count + 1;UNTIL sum_views >= numEND 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
BEGININSERT 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 THENSIGNAL 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操作的触发器
如果父表数据修改引发了子表的数据变化，在这种情况下并不会激发触发器
如有错误，欢迎指正！！！
