mysql存储过程，使用双游标CURSOR嵌套循环

环境

MySQL：阿里云的MySQL5.7.24版本

吐槽

sql语句的调试挺累的，它只会提示哪个部分出错了，但不会告诉你出错的原因是什么？所以中间的执行错误我也不明白错的理由是什么。

sql涉及的业务逻辑

为了方便各位理解，我先大概说明下sql涉及的业务逻辑。每个话题对应各自的评论区，之前没有楼层的概念，现在加了楼层的概念，所以要把以前的评论刷一遍楼层数，所以需要嵌套循环更新，并按照创建时间升序。
涉及字段：

id  评论表主键id;
topic_id  话题id;
floor_num  楼层号;

完整的存储过程sql

drop procedure if exists floor_num;
delimiter // -- 修改语句结束标识符
create procedure floor_num()
begin
    declare cur_topic_id BIGINT(20);#游标遍历的话题id
    DECLARE done INT DEFAULT FALSE;#结束标识
    DECLARE topic_id_list CURSOR FOR SELECT topic_id from t_comment GROUP BY topic_id;#获取游标 查询评论表含有的话题id
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 结束标识
    OPEN topic_id_list;
    out_loop:LOOP#循环开始
    FETCH topic_id_list INTO cur_topic_id;
    IF done THEN-- 结束标识
        LEAVE out_loop;
     END IF;
        call inner_floor_num(cur_topic_id);#调用内部存储过程
    END LOOP out_loop;
  commit;
    CLOSE topic_id_list;
end //
delimiter ;

drop procedure if exists inner_floor_num;
delimiter // -- 修改语句结束标识符
create procedure inner_floor_num(IN cur_topic_id BIGINT(20))
begin
    declare i int default 1;#楼层号
    declare commentId BIGINT(20);#评论表id
    DECLARE done INT DEFAULT FALSE;#结束标识
    DECLARE comment_list CURSOR FOR SELECT id as comment_id from t_comment where topic_id = cur_topic_id ORDER BY create_time asc;#获取游标 查询单个话题下的评论记录
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 结束标识
    OPEN comment_list;
    inner_loop:LOOP#循环开始
    FETCH comment_list INTO commentId;
    IF done THEN-- 结束标识
        LEAVE inner_loop;
    END IF;
        update t_comment set floor_num = i where id = commentId;#更新语句
        set i =i+1;#楼层+1
    END LOOP inner_loop;
  commit;
    CLOSE comment_list;
end //
delimiter ;

call floor_num;#开始调用运行

踩坑点

1.一开始我在一个存储过程中直接写完两个游标嵌套循环，结果在第二层的获取游标语句报错了，但我看了半天没看出错误在哪，然后我试着写了第二个存储过程，将第二层获取游标及循环放到里面，错误就消失了，莫名其妙，我保证语法及变量没写错。
2.也是因为第二层游标获取报错，我试着换成while循环，运行成功了，但是数据有问题，比如有些记录没刷或者刷了重复的楼层，有点懵逼，看网上其他人也有类似问题，暂时不明白原理是什么。
3.记得加上结束标识，没有这个也会报错。

参考链接

https://www.cnblogs.com/speily/p/9289113.html
https://www.cnblogs.com/smartsmile-yxh/p/11546901.html
https://www.cnblogs.com/xuzhujack/p/12762595.html

最后编辑于：2020.09.15 10:50:31