MySQL 儲存程序進階實戰：交易、參數、巢狀呼叫、游標、迴圈完整範例

完整專案級範例：資料同步處理程序

這個範例展示生產環境常見的「增量同步」模式：記錄上次同步位置、交易保護、巢狀程序呼叫、游標逐列處理、例外回滾。

主程序：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. 計算本次處理範圍
    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 ;

子程序：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 VARCHAR(1);
    DECLARE iRep_status VARCHAR(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 SQLSTATE '02000' 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 操作
            INSERT INTO rs0811 (id, fnbm)
            SELECT id, fnbm FROM rep_shadow_rs WHERE rep_sync_id = iRep_Sync_id;
            
        ELSEIF iRep_operationtype = 'U' THEN
            -- Update 操作：依狀態決定 Insert 或 Delete
            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 操作
            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 ;

核心技術點解析

1. 交易邊界管理

• 主程序控制 START TRANSACTION / COMMIT / ROLLBACK
• EXIT HANDLER FOR SQLEXCEPTION 確保異常自動回滾
• 子程序不管理交易，專注業務邏輯

2. 輸出參數傳遞狀態

-- 呼叫方式
CALL pro_rep_shadow_rs(@rtn);
SELECT @rtn;  -- 0=失敗/無新資料, 1=成功

3. 游標標準流程

1. DECLARE cur CURSOR FOR SELECT ... — 宣告
2. DECLARE HANDLER FOR '02000' SET done=1 — 結束標記
3. OPEN cur — 開啟
4. FETCH cur INTO vars — 讀取（首次在迴圈前、後續在迴圈尾）
5. WHILE done=0 DO ... END WHILE — 迴圈
6. CLOSE cur — 關閉

4. 巢狀呼叫架構

協調器（主程序）負責：交易、日誌、範圍計算、狀態回傳
執行器（子程序）負責：游標迴圈、具體 DML 操作

補充：更多進階語法範例

簡單輸出參數程序

DELIMITER //
CREATE PROCEDURE simpleproce1 (OUT par1 INT)
BEGIN
    SELECT COUNT(*) INTO par1 FROM proce;
END;
//
DELIMITER ;
CALL simpleproce1(@a);
SELECT @a;

多輸出參數 + LIMIT 1

DELIMITER //
CREATE PROCEDURE simpleproce2 (OUT par1 INT, OUT par2 CHAR(30))
BEGIN
    SELECT id, name INTO par1, par2 FROM proce LIMIT 1;
END;
//
DELIMITER ;
CALL simpleproce2(@a, @b);
SELECT @a, @b;

函數建立與查看

DELIMITER //
CREATE FUNCTION hello(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ', s, '!');
//
DELIMITER ;
SELECT hello('world');

SHOW CREATE FUNCTION testprocedure.hello;
SHOW FUNCTION STATUS LIKE 'hello';

變數作用域與名稱衝突

-- 錯誤：變數名與欄位名相同導致歧義
DECLARE name VARCHAR(5) DEFAULT 'bob';
SELECT name, id INTO newname, xid FROM proce WHERE name = name;  -- WHERE 條件永真

-- 正確：使用不同變數名
DECLARE v_name VARCHAR(5) DEFAULT 'bob';
SELECT name, id INTO newname, xid FROM proce WHERE name = v_name;

例外處理器類型

迴圈三種寫法比較

-- WHILE：先檢查再執行
WHILE v < 5 DO
    INSERT ...;
    SET v = v + 1;
END WHILE;

-- REPEAT：先執行再檢查
REPEAT
    INSERT ...;
    SET v = v + 1;
UNTIL v >= 5
END REPEAT;

-- LOOP：無預設條件，需 LEAVE 跳出
loop_label: LOOP
    INSERT ...;
    SET v = v + 1;
    IF v >= 5 THEN LEAVE loop_label; END IF;
END LOOP;

標籤與跳轉控制

label_1: BEGIN
    label_2: WHILE 0=1 DO LEAVE label_2; END WHILE;
    label_3: REPEAT LEAVE label_3; UNTIL 0=0 END REPEAT;
    label_4: LOOP LEAVE label_4; END LOOP;
END label_1;

ITERATE 繼續下一輪

loop_label: LOOP
    IF v = 3 THEN
        SET v = v + 1;
        ITERATE loop_label;  -- 跳過 3，不插入
    END IF;
    INSERT INTO casetest VALUES(v);
    SET v = v + 1;
    IF v >= 5 THEN LEAVE loop_label; END IF;
END LOOP;

觸發器範例

CREATE TRIGGER trigtest BEFORE INSERT ON trig1
FOR EACH ROW BEGIN
    INSERT INTO trig2 SET a2 = NEW.a1;
    DELETE FROM trig3 WHERE a3 = NEW.a1;
    UPDATE trig4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;