话不多直接上代码,注释很详细,下面代码确认运行无误,自己去感悟吧朋友
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 THENLEAVE vkfdatasLoop;#跳出循环ELSESET 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 THENLEAVE 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;