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;