欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-09-03 19:59:22
话不多直接上代码,注释很详细,下面代码确认运行无误,自己去感悟吧朋友 CREATE PROCEDURE SYC_FILE_DOC_INFO() BEGIN DECLARE A_PK_FILEDOC char(24); DECLARE A_PK_CORP char(6); DECLARE A_VCOD ......

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

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;