您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 儲存程序完整範例：事務、參數、游標、巢狀呼叫與迴圈實戰

編輯：MySQL 資料庫相關知識

前言

MySQL 儲存程序是封裝業務邏輯、提升效能、保障資料一致性的利器。本文提供一個生產級範例 pro_rep_shadow_rs，完整展示輸出參數、事務控制、異常處理、巢狀呼叫、游標迴圈、條件判斷等核心特性，適合作為開發參考範本。

一、主程序：pro_rep_shadow_rs（協調者）

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 ROLLBACK;

    -- 1. 取得上次同步進度
    SELECT eid INTO iLast_rep_sync_id
    FROM rep_de_proc_log
    WHERE tbl = 'rep_shadow_rs';

    -- 2. 首次執行初始化
    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;

    -- 3. 計算本次處理起始 ID
    SET iLast_rep_sync_id = iLast_rep_sync_id + 1;
    SET rtn = 0;  -- 預設失敗

    -- 4. 開啟事務
    START TRANSACTION;

    -- 5. 取得來源表最大同步 ID
    SELECT MAX(rep_sync_id) INTO iMax_rep_sync_id
    FROM rep_shadow_rs;

    -- 6. 有新資料才處理
    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;

    -- 7. 提交事務並標記成功
    COMMIT;
    SET rtn = 1;
END;
|
DELIMITER ;

關鍵點解析

OUT 參數 rtn： 0=失敗/回滾，1=成功/提交，供呼叫端判斷。
同步日誌表 rep_de_proc_log： 記錄每張表最後處理的 rid(起始) 與 eid(結束) ID，實現增量同步。
EXIT HANDLER： 捕捉所有 SQL 例外自動 ROLLBACK，簡化錯誤流程。
巢狀呼叫： 核心邏輯委派給 pro_rep_shadow_rs_do，職責分離清晰。

二、子程序：pro_rep_shadow_rs_do（執行者）

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_flag        INT DEFAULT 0;

    -- 游標：抓取指定 ID 區間的待同步資料
    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 SQLSTATE '02000' SET stop_flag = 1;

    OPEN cur;
    FETCH cur INTO iId, iRep_Operationtype, iRep_status, iRep_Sync_id;

    WHILE stop_flag <> 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 ;

關鍵點解析

游標 CURSOR： 逐行處理區間資料，適用於需逐筆邏輯判斷的場景。
CONTINUE HANDLER FOR '02000'： 捕捉「無資料」狀態碼，優雅結束迴圈。
操作類型 I/U/D + 狀態 A/B： 實現「新增/更新(含狀態分流)/刪除」完整同步邏輯。
無返回值： 純執行端，錯誤由外層事務統一捕捉回滾。

三、呼叫與測試方式

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

四、最佳實踐提醒

大量資料慎用游標： 百萬級以上建議改用集合操作或分批處理，避免游標效能瓶頸。
事務粒度控制： 外層事務包含整個同步流程，確保「全成功或全失敗」。
異常處理策略： 生產環境建議改為 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; 保留錯誤資訊供監控告警。
權限最小化： 僅授予執行該程序所需的 INSERT/UPDATE/DELETE 權限。