MySQL 存儲過程實戰範例：事務控制、輸出參數、游標迴圈、嵌套呼叫

主程序：增量同步邏輯

DROP PROCEDURE IF EXISTS pro_rep_shadow_rs;
DELIMITER |

CREATE PROCEDURE pro_rep_shadow_rs(OUT rtn INT)
BEGIN
    -- 變數宣告（必須在最前面）
    DECLARE iLast_rep_sync_id INT DEFAULT -1;
    DECLARE iMax_rep_sync_id INT DEFAULT -1;

    -- 異常處理：遇到 SQL 異常自動回滾
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        SET rtn = 0;
    END;

    -- 取得上次同步位點
    SELECT eid INTO iLast_rep_sync_id 
    FROM rep_de_proc_log WHERE tbl = 'rep_shadow_rs';

    -- 首次執行初始化
    IF iLast_rep_sync_id = -1 THEN
        INSERT INTO rep_de_proc_log(rid, eid, tbl) VALUES (0, 0, 'rep_shadow_rs');
        SET iLast_rep_sync_id = 0;
    END IF;

    SET iLast_rep_sync_id = iLast_rep_sync_id + 1;
    SET rtn = 0;  -- 預設失敗

    START TRANSACTION;

    -- 取得最大同步 ID
    SELECT MAX(rep_sync_id) INTO iMax_rep_sync_id FROM rep_shadow_rs;

    -- 有新資料才處理
    IF iMax_rep_sync_id >= iLast_rep_sync_id THEN
        CALL pro_rep_shadow_rs_do(iLast_rep_sync_id, iMax_rep_sync_id);
        UPDATE rep_de_proc_log 
        SET rid = iLast_rep_sync_id, eid = iMax_rep_sync_id 
        WHERE tbl = 'rep_shadow_rs';
    END IF;

    COMMIT;
    SET rtn = 1;  -- 成功
END |

DELIMITER ;

子程序：游標逐行處理

DROP PROCEDURE IF EXISTS pro_rep_shadow_rs_do;
DELIMITER |

CREATE PROCEDURE pro_rep_shadow_rs_do(
    IN last_rep_sync_id INT,
    IN max_rep_sync_id INT
)
BEGIN
    DECLARE iRep_operationtype CHAR(1);
    DECLARE iRep_status CHAR(1);
    DECLARE iRep_Sync_id INT;
    DECLARE iId INT;
    DECLARE stop INT DEFAULT 0;

    -- 宣告游標
    DECLARE cur CURSOR FOR 
        SELECT id, Rep_operationtype, Rep_status, rep_sync_id
        FROM rep_shadow_rs
        WHERE rep_sync_id BETWEEN last_rep_sync_id AND max_rep_sync_id;

    -- 游標結束處理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stop = 1;

    OPEN cur;

    FETCH cur INTO iId, iRep_operationtype, iRep_status, iRep_Sync_id;

    WHILE stop <> 1 DO
        IF iRep_operationtype = 'I' THEN
            INSERT INTO rs0811 (id, fnbm) 
            SELECT id, fnbm FROM rep_shadow_rs WHERE rep_sync_id = iRep_Sync_id;
        ELSEIF iRep_operationtype = 'U' THEN
            IF iRep_status = 'A' THEN
                INSERT INTO rs0811 (id, fnbm) 
                SELECT id, fnbm FROM rep_shadow_rs WHERE rep_sync_id = iRep_Sync_id;
            ELSEIF iRep_status = 'B' THEN
                DELETE FROM rs0811 WHERE id = iId;
            END IF;
        ELSEIF iRep_operationtype = 'D' THEN
            DELETE FROM rs0811 WHERE id = iId;
        END IF;

        FETCH cur INTO iId, iRep_operationtype, iRep_status, iRep_Sync_id;
    END WHILE;

    CLOSE cur;
END |

DELIMITER ;

關鍵技術點解析

呼叫測試

CALL pro_rep_shadow_rs(@rtn);
SELECT @rtn;  -- 1 成功，0 失敗