MySQL存储过程中双层游标嵌套循环实例

用户操作统计表
CREATE TABLE `count_active_user` (
  `id` varchar(20) NOT NULL COMMENT 'ID-20190220',
  `type` varchar(50) NOT NULL COMMENT '分类',
  `user_id` varchar(32) NOT NULL COMMENT '用户ID',
  `count` int(3) DEFAULT NULL COMMENT '计数',
  PRIMARY KEY (`id`,`type`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `proc_person_data_to_enterprise`(IN userId varchar(32))
BEGIN
    declare cur_person_user_id varchar(32);
    declare cur_orguser_id varchar(32);
  declare cur_id varchar(32);
  declare cur_type varchar(32);
    declare cur_user_id varchar(32);
    declare cur_count int;
    declare done int default 0;
    declare cur_transferPerson CURSOR FOR  SELECT person_user_id,orguser_id FROM view_transfer_person where person_user_id = userId;
  declare cur_count_active_user CURSOR FOR  select * from count_active_user;
    -- 在游标循环到最后会将 done 设置为 1(如果不加该行  游标为空的时候  就会报错)
    declare continue handler for not found set done=1;
    OPEN cur_transferPerson; -- 打开游标
        myLoop1: LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到
        FETCH cur_transferPerson into cur_person_user_id,cur_orguser_id; -- 将游标当前读取行的数据顺序赋予自定义变量12
           IF done = 1 THEN -- 判断是否继续循环
               LEAVE myLoop1; -- 结束循环
           END IF;
          update attestation a set  a.user_id = cur_orguser_id where a.user_id  = cur_person_user_id;     
          OPEN cur_count_active_user;  
            myLoop2: LOOP  
             FETCH cur_count_active_user into cur_id,cur_type,cur_user_id,cur_count; 
             IF done = 1 THEN  
                           LEAVE myLoop2; 
             END IF;
       update count_active_user a set  a.count = a.count + cur_count where  a.user_id = cur_orguser_id 
           and cur_user_id = userId and cur_person_user_id =userId   and a.id = cur_id and a.type = cur_type;  
         END LOOP myLoop2;  
       CLOSE cur_count_active_user;  
             -- 注意这里，当内层游标循环完毕以后需要将done设置成0，否则外层循环只会进行一次
         SET done = 0;
    COMMIT; -- 提交事务
  END LOOP myLoop1; -- 结束自定义循环体
  CLOSE cur_transferPerson; -- 关闭游标
  DELETE from count_active_user   where  user_id =  userId ;   
END