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) DEFAULT0;
DECLARE A_TS                TIMESTAMP;
DECLARE A_ISLEAF            char(1);
DECLARE A_FILELEVEL          numeric(8,0) DEFAULT0;
DECLARE A_IFILETYPE          numeric(8,0) DEFAULT0;
DECLARE B_PK_CORP            char(6);
-- 定义结束标识
DECLARE done INT DEFAULT0;
DECLARE edone INT DEFAULT0;
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 -- 循环游标开始
mysql的存储过程怎么理解
FETCH v_kf_datas INTO B_PK_CORP;
IF done =1THEN
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 =1THEN
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_IFILE END IF;
END LOOP;
CLOSE v_file_data;-- 关闭游标
END;
END IF;
END LOOP; -- 结束游标
CLOSE v_kf_datas;-- 关闭游标
COMMIT;
END;
END;