MySQL 預存程序中巢狀游標實戰範例

場景說明

需遍歷借款主表 rocky_borrow 符合條件的記錄，對每筆借款再查詢投標記錄 rocky_b_tenderrecord 取得首/尾投標時間，進而以此時間區間查詢帳務流水 rocky_accountlog 中重複用戶的記錄，寫入測試表 rocky_accountlog_test2。

完整程式碼（含註解）

DELIMITER $$
CREATE PROCEDURE proc_nested_cursor_demo()
BEGIN
    -- 外層游標變數
    DECLARE v_borrow_id INT;
    DECLARE v_addtime_begin VARCHAR(13);
    DECLARE v_addtime_end VARCHAR(13);
    DECLARE v_count INT;
    DECLARE s1 INT DEFAULT 1;

    -- 外層游標：符合條件的借款 ID
    DECLARE c_borrow CURSOR FOR
        SELECT ID
        FROM rocky_borrow
        WHERE BORROWTYPE = 2
          AND PUBLISH_TIME >= UNIX_TIMESTAMP('2014-05-27')
          AND PUBLISH_TIME <= UNIX_TIMESTAMP('2014-07-30')
        ORDER BY ID ASC;

    -- 取得總筆數控制迴圈
    SELECT COUNT(ID) INTO v_count
    FROM rocky_borrow
    WHERE BORROWTYPE = 2
      AND PUBLISH_TIME >= UNIX_TIMESTAMP('2014-05-27')
      AND PUBLISH_TIME <= UNIX_TIMESTAMP('2014-07-30');

    START TRANSACTION;
    OPEN c_borrow;

    WHILE s1 <= v_count DO
        FETCH c_borrow INTO v_borrow_id;

        -- 取得該借款首筆與末筆投標時間
        SELECT t1.addtime INTO v_addtime_begin
        FROM (SELECT * FROM rocky_b_tenderrecord
              WHERE BORROW_ID = v_borrow_id AND tender_type = 1
              ORDER BY ID ASC) t1
        GROUP BY t1.borrow_id;

        SELECT t1.addtime INTO v_addtime_end
        FROM (SELECT * FROM rocky_b_tenderrecord
              WHERE BORROW_ID = v_borrow_id AND tender_type = 1
              ORDER BY ID DESC) t1
        GROUP BY t1.borrow_id;

        -- 若時間區間有效，進入內層游標處理帳務流水
        IF v_addtime_begin IS NOT NULL AND v_addtime_end IS NOT NULL THEN
            BEGIN
                -- 內層游標變數
                DECLARE v_id, v_user_id, v_to_user INT;
                DECLARE v_type, v_remark, v_addip VARCHAR(1000);
                DECLARE v_total, v_money, v_use_money, v_no_use_money, v_collection, v_first_borrow_use_money DECIMAL(20,8);
                DECLARE v_addtime VARCHAR(13);
                DECLARE done VARCHAR(45) DEFAULT '1';
                DECLARE t_error INT DEFAULT 0;

                -- 內層游標：同一用戶在時間區間內有多筆特定類型流水
                DECLARE c_accountlog CURSOR FOR
                    SELECT ID, USER_ID, TYPE, TOTAL, MONEY, USE_MONEY, NO_USE_MONEY,
                           COLLECTION, TO_USER, REMARK, ADDTIME, ADDIP, FIRST_BORROW_USE_MONEY
                    FROM (
                        SELECT ID, USER_ID, TYPE, TOTAL, MONEY, USE_MONEY, NO_USE_MONEY,
                               COLLECTION, TO_USER, REMARK, ADDTIME, ADDIP, FIRST_BORROW_USE_MONEY
                        FROM rocky_accountlog
                        WHERE ADDTIME >= v_addtime_begin
                          AND ADDTIME <= v_addtime_end
                          AND (TYPE = 'tender_cold' OR TYPE = 'repayment_deduct')
                    ) t
                    GROUP BY t.user_id
                    HAVING COUNT(t.user_id) > 1;

                -- 游標結束處理器
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = NULL;

                OPEN c_accountlog;
                FETCH c_accountlog INTO v_id, v_user_id, v_type, v_total, v_money, v_use_money,
                                       v_no_use_money, v_collection, v_to_user, v_remark,
                                       v_addtime, v_addip, v_first_borrow_use_money;

                WHILE done IS NOT NULL DO
                    INSERT INTO rocky_accountlog_test2
                        (ACCOUNTLOG_ID, USER_ID, TYPE, TOTAL, MONEY, USE_MONEY,
                         NO_USE_MONEY, COLLECTION, TO_USER, REMARK, ADDTIME, ADDIP,
                         FIRST_BORROW_USE_MONEY, BORROW_ID)
                    VALUES
                        (v_id, v_user_id, v_type, v_total, v_money, v_use_money,
                         v_no_use_money, v_collection, v_to_user, v_remark, v_addtime, v_addip,
                         v_first_borrow_use_money, v_borrow_id);

                    FETCH c_accountlog INTO v_id, v_user_id, v_type, v_total, v_money, v_use_money,
                                           v_no_use_money, v_collection, v_to_user, v_remark,
                                           v_addtime, v_addip, v_first_borrow_use_money;
                END WHILE;

                CLOSE c_accountlog;
            END;
        END IF;

        SET s1 = s1 + 1;
    END WHILE;

    CLOSE c_borrow;
    COMMIT;
END $$
DELIMITER ;

關鍵技術點

1. 雙層游標巢狀：外層遍歷主表，內層處理明細，需在 BEGIN ... END 區塊內宣告內層游標與變數，避免作用域衝突。
2. CONTINUE HANDLER FOR NOT FOUND：標準游標結束檢測模式，將旗標變數設為 NULL 控制 WHILE 迴圈。
3. 事務控制：整批操作包在 START TRANSACTION ... COMMIT，確保資料一致性；若中途出錯可 ROLLBACK。
4. 游標查詢優化：內層游標使用衍生表 + GROUP BY ... HAVING 直接在 SQL 層面過濾重複用戶，減少應用層邏輯。

對比：SQL Server 游標巢狀

SQL Server 同樣支援巢狀游標，建議同樣搭配 TRY...CATCH 事務處理，釋放資源用 CLOSE ... DEALLOCATE。邏輯結構大同小異。