mysql 存储过程中双重循环嵌套的应用

话不多直接上代码,注释很详细,下面代码确认运行无误,自己去感悟吧朋友

create procedure syc_file_doc_info()
begin    
    declare a_pk_filedoc         char(24);
    declare a_pk_corp            char(6);
    declare a_vcode              varchar(30) default '' ;
    declare a_vname              varchar(100) default '' ;
    declare a_vmemo              varchar(200) default '' ;
    declare a_issealed           char(1);
    declare a_dr                 numeric(8,0) default 0;
    declare a_ts                 timestamp;
    declare a_isleaf             char(1);
    declare a_filelevel          numeric(8,0) default 0;
    declare a_ifiletype          numeric(8,0) default 0;
    declare b_pk_corp            char(6);
    -- 定义结束标识
    declare done int default 0;
    declare edone int default 0;
    begin
        -- 定义游标v_kf_datas以及结束标识
        declare v_kf_datas cursor for select distinct pk_corp from ynt_kfiledoc y where ifnull(dr,0) = 0;
        declare continue handler for not found set done = 1;-- 结束标识
        open v_kf_datas;-- 打开游标
            vkfdatasloop:loop -- 循环游标开始
                fetch v_kf_datas into b_pk_corp;
                if done = 1 then
                    leave vkfdatasloop;#跳出循环
                else
                set edone = 0;-- !!!!!重置标识
                    begin
                        -- 定义游标v_file_data以及结束标识
                        declare v_file_data cursor for select * from ynt_filedoc y where y.pk_corp = '000001' and y.vcode like '99%';
                        declare continue handler for not found set edone = 1;#结束标识
                        open v_file_data;-- 打开游标
                            vfiledataloop:loop -- 循环游标开始
                                fetch v_file_data into a_pk_filedoc,a_pk_corp,a_vcode,a_vname,a_vmemo,a_issealed,a_dr,a_ts,a_isleaf,a_filelevel,a_ifiletype;
                                if edone = 1 then
                                    leave vfiledataloop;
                                else
                                    -- 对表的操作
                                    insert into ynt_kfiledoc(pk_kfiledoc, pk_corp, pk_source, vcode, vname, vmemo, issealed, dr, ts, isleaf, filelevel, ifiletype)
                                    values(concat(b_pk_corp,substring(a_pk_filedoc,7, 12),fn_get_random_str(6)),b_pk_corp, a_pk_filedoc, a_vcode, a_vname, a_vmemo, a_issealed, 0, a_ts, a_isleaf, a_filelevel, a_ifiletype);
                                end if;
                            end loop;
                        close v_file_data;-- 关闭游标
                    end;
                end if;
            end loop; -- 结束游标
        close v_kf_datas;-- 关闭游标
        commit;
    end;
end;