游标按我的理解就是用在sql编程中对查询结果集的解析，类比jdbc中的resultset对象。FETCH 一行游标指针就往下面移动一行，直到所有行被遍历完成。
游标的使用分为4步：
1、定义游标，指定游标名和查询sql语句
2、打开游标
3、fetch 获取数据，赋值给变量
4、关闭游标
基本使用
1、数据准备
CREATE TABLE `goods` (
`gid` int(11) NOT NULL COMMENT '主键',
`name` char(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品',
`num` int(11) NULL DEFAULT NULL COMMENT '库存',
PRIMARY KEY (`gid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, 'cat', 34);
INSERT INTO `goods` VALUES (2, 'dog', 0);
INSERT INTO `goods` VALUES (3, 'pig', 12);
2、定义存储过程，简单使用下游标
CREATE DEFINER="root"@"%" PROCEDURE "p5"()
BEGIN
# 定义接受sql数据的变量
DECLARE row_gid int;
DECLARE row_name varchar(20);
DECLARE row_num int;
# 定义游标
DECLARE getgoods CURSOR FOR
select gid, name, num from goods;
# 打开游标
OPEN getgoods;
# 获取数据,使用一次FETCH就是获取一行
# 注意需要按列顺序的顺序赋值
FETCH getgoods INTO row_gid, row_name, row_num;
select row_gid, row_name, row_num;
FETCH getgoods INTO row_gid, row_name, row_num;
select row_gid, row_name, row_num;
FETCH getgoods INTO row_gid, row_name, row_num;
select row_gid, row_name, row_num;
FETCH getgoods INTO row_gid, row_name, row_num;
select row_gid, row_name, row_num;
# 关闭游标
CLOSE getgoods;
END
执行下 CALL p5(); 可以看到有三个结果集
image.png
然而我FETCH 了4次，只有3行。那么产生了游标越界错误。
1329 - No data - zero rows fetched, selected, or processed
使用REPEAT循环去FETCH 数据
使用循环去遍历游标，将查询sql所有的数据取出。使用count(*)总记录数去做循环的判断条件。若循环计数器大于等于总记录数则停止循环。
CREATE DEFINER="root"@"%" PROCEDURE "p6"()
BEGIN
# 定义接受sql数据的变量
DECLARE row_gid int;
DECLARE row_name varchar(20);
DECLARE row_num int;
# 总行数
DECLARE cnt INT DEFAULT 0;
# 循环变量i
DECLARE i INT DEFAULT 0;
# 定义游标
DECLARE getgoods CURSOR FOR select gid, name, num from goods;
# 查询总行数并赋值给 cnt
# 注意这句话一定要放到定义游标的下面，否则报错创建存储过程失败！
SELECT COUNT(*) INTO cnt FROM goods;
# 打开游标
OPEN getgoods;
# 开始循环
REPEAT
SET i := i+1;
FETCH getgoods INTO row_gid, row_name, row_num;
select row_gid, row_name, row_num;
UNTIL i>=cnt END REPEAT;
# 循环结束
# 关闭游标
CLOSE getgoods;
END
使用游标监听器 continue handler 和 exit handler 来完善游标的遍历
上面遍历游标，防止越界读取。使用的是count(*) 额外的去发出一条查询，有着额外的性能损耗。事实上不需要这样，mysql游标有提供它的方式。
我们可以使用 handler 监听器，在该监听器中定义sql。等到游标被遍历完便会自动执行这个sql。那么我们可以使用一个变量，初始化为0，默认未遍历完。在监听器sql中写入设置变量值为1。然后在循环中使用该变量判断即可
定义监听器
DECLARE CONTINUE HANDLER FOR NOT FOUND set flag :=1;
CREATE DEFINER="root"@"%" PROCEDURE "p7"()
BEGIN
DECLARE row_gid int;
DECLARE row_name varchar(20);
DECLARE row_num int;
# 定义循环退出标志符变量
DECLARE flag INT DEFAULT 0;
DECLARE getgoods CURSOR FOR select gid, name, num from goods;
# 定义监听器
DECLARE CONTINUE HANDLER FOR NOT FOUND set flag :=1;
OPEN getgoods;
REPEAT
FETCH getgoods INTO row_gid, row_name, row_num;
select row_gid, row_name, row_num;
UNTIL flag=1 END REPEAT;
CLOSE getgoods;
END
执行下 call p7();发现只有3行记录却放回4个结果集。而且第3个和第4个结果集是相同的。这是一个问题。
image.png
第4次执行fetch时是没数据的，那么触发not found set flag:=1 ，然后continue一下继续执行后面的sql语句，那么select row_gid, row_name, row_num;又被执行一次，最后一行就被执行了2次。
若not found set flag:=1 触发后，后面的select不再执行即可。那么我们可以使用 exit代替continute，exit和continute的区别是：exit触发后后面的语句不再执行！
那么使用exit可以完美解决如下：
CREATE DEFINER="root"@"%" PROCEDURE "p7"()
BEGIN
DECLARE row_gid int;
DECLARE row_name varchar(20);
DECLARE row_num int;
# 定义循环退出标志符变量
DECLARE flag INT DEFAULT 0;
DECLARE getgoods CURSOR FOR select gid, name, num from goods;
# 定义EXIT 监听器
DECLARE EXIT HANDLER FOR NOT FOUND set flag :=1;
OPEN getgoods;
REPEAT
FETCH getgoods INTO row_gid, row_name, row_num;
select row_gid, row_name, row_num;
UNTIL flag=1 END REPEAT;
CLOSE getgoods;
END
执行如下，3个返回集没问题
image.png
除了continue 和exit还有一种undo handler 。undo的功能是触发后，前面的语句撤销，目前mysql不支持
除了使用exit解决，continue 基础上也是可以的。让如果一定要使用continue 的话就需要修改下逻辑，提前FETCH 下了。为了避免查询结果为空而导致返回空结果集，就需要使用while循环代替pepeat循环。因为pepeat循环类似do while，它总是先执行一次循环体再判断的。
那么，while加上continue handler 的正确遍历方式如下：
CREATE DEFINER="root"@"%" PROCEDURE "p7"()
BEGIN
DECLARE row_gid int;
DECLARE row_name varchar(20);
DECLARE row_num int;
# 定义循环退出标志符变量
DECLARE flag INT DEFAULT 0;
DECLARE getgoods CURSOR FOR select gid, name, num from goods;
# 定义监听器
DECLARE CONTINUE HANDLER FOR NOT FOUND set flag :=1;
OPEN getgoods;
# 提前FETCH下
FETCH getgoods INTO row_gid, row_name, row_num;
# 换成while循环，就不会当返回集为null时查出数据为空的了
# 注意while循环的循环条件为true时才进入循环
WHILE flag=0 DO
select row_gid, row_name, row_num;
FETCH getgoods INTO row_gid, row_name, row_num;
END WHILE;
CLOSE getgoods;
END
