MySQL 存儲過程游標與循環嵌套完整實戰指南

循環結構分類

基本循環範例

WHILE 循環

CREATE PROCEDURE p14()
BEGIN
    DECLARE v INT DEFAULT 0;
    WHILE v < 5 DO
        INSERT INTO t VALUES (v);
        SET v = v + 1;
    END WHILE;
END;

REPEAT 循環

CREATE PROCEDURE p15()
BEGIN
    DECLARE v INT DEFAULT 0;
    REPEAT
        INSERT INTO t VALUES (v);
        SET v = v + 1;
    UNTIL v >= 5
    END REPEAT;

LOOP 循環

CREATE PROCEDURE p16()
BEGIN
    DECLARE v INT DEFAULT 0;
    loop_label: LOOP
        INSERT INTO t VALUES (v);
        SET v = v + 1;
        IF v >= 5 THEN LEAVE loop_label; END IF;
    END LOOP;
END;

ITERATE (Continue) 用法

CREATE PROCEDURE p20()
BEGIN
    DECLARE v INT DEFAULT 0;
    loop_label: LOOP
        IF v = 3 THEN
            SET v = v + 1;
            ITERATE loop_label;  -- 跳過本次剩餘，直接下一輪
        END IF;
        INSERT INTO t VALUES (v);
        SET v = v + 1;
        IF v >= 5 THEN LEAVE loop_label; END IF;
    END LOOP;
END;

游標完整模板

BEGIN
    -- 1. 定義變量
    DECLARE p_feeCode VARCHAR(20);
    DECLARE p_feeName VARCHAR(20);
    DECLARE done INT DEFAULT 0;
    DECLARE flag INT DEFAULT 0;

    -- 2. 定義游標
    DECLARE feeCodeCursor CURSOR FOR SELECT feeCode FROM fee;

    -- 3. 定義 NOT FOUND 處理器 (游標耗盡標志)
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 4. 打開游標
    OPEN feeCodeCursor;

    loop_label: LOOP
        -- 5. 取數據
        FETCH feeCodeCursor INTO p_feeCode;
        
        -- 6. 判斷結束
        IF done = 1 THEN LEAVE loop_label; END IF;
        
        -- 7. 業務邏輯
        SET flag = 0;
        SELECT feeName INTO p_feeName FROM fee WHERE feeCode = p_feeCode;
        -- ... 其他查詢計算 ...
        
        IF flag = 0 THEN SET done = 0; END IF;  -- 重置標志供下輪
        
        INSERT INTO total_summary VALUES (...);
        COMMIT;
    END LOOP;

    -- 8. 關閉游標
    CLOSE feeCodeCursor;
END

雙層嵌套游標範例

BEGIN
    DECLARE done1 INT DEFAULT 0;
    DECLARE done2 INT DEFAULT 0;
    DECLARE feeCodeCursor CURSOR FOR SELECT feeCode FROM fee;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
    
    OPEN feeCodeCursor;
    REPEAT
        FETCH feeCodeCursor INTO p_feeCode;
        IF NOT done1 THEN
            BEGIN
                DECLARE projectIDCursor CURSOR FOR SELECT projectID FROM project;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;
                
                OPEN projectIDCursor;
                loop_label: LOOP
                    FETCH projectIDCursor INTO p_projectID;
                    IF done2 = 1 THEN LEAVE loop_label; END IF;
                    -- 內層業務邏輯 ...
                END LOOP;
                CLOSE projectIDCursor;
            END;
        END IF;
    UNTIL done1 END REPEAT;
    CLOSE feeCodeCursor;
END

關鍵最佳實踐

1. 必須聲明 NOT FOUND HANDLER，否則游標耗盡報錯
2. 每層循環獨立 done 變量 (done1/done2)，避免相互干擾
3. FETCH 後立即判斷 done，再執行業務邏輯
4. 循環結束必須 CLOSE 游標，釋放資源
5. 游標不支援 SCROLL，僅單向前向讀取
6. 大數據量考慮分批處理，避免長事務鎖定