MYSQL游标嵌套循环示范

MYSQL游标嵌套循环示例

本文将举例演示游标的嵌套用法，首先建造表结构，如下：

drop procedure if exists `sp_nested_cursor`;create procedure `sp_nested_cursor`()begindeclare v_uid bigint(22);    declare v_code varchar(100);    declare v_value varchar(100);    declare _done TINYINT(1) default 0;declare cur_user cursor for select id from `tb_user`;    declare continue handler for not found set _done = 1;        open cur_user;    loop_xxx:loop    fetch cur_user into v_uid;        if _done=1 then        leave loop_xxx;        end if;        begin        declare _inner tinyint(1) default 0;        declare cur_param cursor for select code, value                                          from `tb_user_param`                                          where user_id=v_uid;            declare continue handler for not found set _inner = 1;             open cur_param;            loop_yyy:loop            fetch cur_param into v_code, v_value;                if _inner=1 then                leave loop_yyy;                end if;                insert into tb_key_value values (v_uid, v_code, v_value);            end loop;            commit;        end;    end loop;end;call `sp_nested_cursor`();