本文 AI 產出，尚未審核

MySQL 儲存程序（Stored Procedure） – 游標（Cursor）基礎

簡介

在資料庫開發中，**游標（Cursor）**是讓開發者能夠逐筆（row‑by‑row）處理查詢結果的工具。雖然集合式（set‑based）操作在大多數情況下效能更佳，但在某些需要「逐列」檢查、累計或產生複雜商業邏輯的情境，游標仍是不可或缺的利器。

本單元將以 MySQL 8.0 以上 為基礎，說明如何在 儲存程序（Stored Procedure） 中宣告、開啟、使用與關閉游標，並提供實務範例，協助從初學者到中階開發者快速上手、避免常見陷阱。

核心概念

1. 游標的基本流程

在 MySQL 中使用游標，一般遵循以下四個步驟：

  1. 宣告（DECLARE） 游標與相關變數。
  2. 開啟（OPEN） 游標，讓它執行 SELECT 並取得結果集合。
  3. 取值（FETCH） 每一筆資料到變數，通常放在 LOOP 或 WHILE 迴圈中。
  4. 關閉（CLOSE） 游標，釋放資源。

注意：游標只能在儲存程序、函式或觸發器的 DECLARE 區塊（位於 BEGIN 前）中宣告，且必須在同一個儲存程序內使用、關閉。

2. DECLARE 區塊的限制

CREATE PROCEDURE proc_name()
BEGIN
    DECLARE cursor_name CURSOR FOR SELECT ... ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 變數宣告必須在游標宣告之後
    DECLARE done INT DEFAULT FALSE;
    DECLARE col1 VARCHAR(50);
    DECLARE col2 INT;
    ...
END;
  • DECLARE 必須在 任何其他語句（如 IF、LOOP）之前。
  • CONTINUE HANDLER 常用來捕捉 NOT FOUND（即游標已讀完）事件，避免程式因未處理的例外而中止。

3. 游標的類型

類型 說明
READ ONLY 預設類型，僅能讀取資料，效率最高。
SCROLL 允許向前、向後移動（MySQL 8.0 仍未完整支援）。
FOR UPDATE 允許在游標中對資料執行更新或刪除（需配合事務）。

在大多數情況下，使用 READ ONLY 即可，除非確實需要在迭代過程中修改同一筆資料。

4. 逐筆處理 vs. 集合式處理

方式 優點 缺點
游標（逐筆） 程式邏輯直觀、可處理複雜條件 效能較差、佔用較多資源
集合式（單一 SQL） 效能佳、資源使用低 需要較強的 SQL 思維，寫法較複雜

實務建議：先嘗試集合式解法，只有在真的無法以單一 SQL 完成時，才考慮使用游標。

程式碼範例

以下提供 5 個常見且實用的游標範例，每個範例皆附上完整說明與註解。

範例 1：最簡單的游標迭代

DELIMITER $$

CREATE PROCEDURE sp_simple_cursor()
BEGIN
    /* 1. 變數宣告 */
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(50);
    DECLARE done INT DEFAULT FALSE;

    /* 2. NOT FOUND handler */
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    /* 3. 游標宣告 */
    DECLARE cur_emp CURSOR FOR
        SELECT employee_id, employee_name
        FROM employees
        WHERE status = 'ACTIVE';

    /* 4. 開啟游標 */
    OPEN cur_emp;

    /* 5. 逐筆取值 */
    read_loop: LOOP
        FETCH cur_emp INTO v_id, v_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        /* 這裡可以寫任何逐筆處理的邏輯 */
        SELECT CONCAT('ID:', v_id, ' Name:', v_name) AS info;
    END LOOP;

    /* 6. 關閉游標 */
    CLOSE cur_emp;
END$$

DELIMITER ;

說明：此範例僅示範如何宣告、開啟、取值、關閉游標，並在每筆資料上輸出簡單訊息。

範例 2：使用游標累計總和

DELIMITER $$

CREATE PROCEDURE sp_sum_salary(IN dept_id INT, OUT total_salary DECIMAL(12,2))
BEGIN
    DECLARE v_salary DECIMAL(12,2);
    DECLARE done INT DEFAULT FALSE;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DECLARE cur_sal CURSOR FOR
        SELECT salary
        FROM employees
        WHERE department_id = dept_id;

    SET total_salary = 0;

    OPEN cur_sal;
    read_loop: LOOP
        FETCH cur_sal INTO v_salary;
        IF done THEN LEAVE read_loop; END IF;
        SET total_salary = total_salary + v_salary;
    END LOOP;
    CLOSE cur_sal;
END$$

DELIMITER ;

說明：此程序接受部門編號，利用游標逐筆累加該部門的薪資，最終透過 OUT 參數返回總額。

範例 3：在游標中更新資料（FOR UPDATE）

DELIMITER $$

CREATE PROCEDURE sp_update_stock()
BEGIN
    DECLARE v_product_id INT;
    DECLARE v_qty INT;
    DECLARE done INT DEFAULT FALSE;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    /* FOR UPDATE 讓我們可以在同一筆資料上執行 UPDATE */
    DECLARE cur_stock CURSOR FOR
        SELECT product_id, quantity
        FROM inventory
        WHERE quantity < 10
        FOR UPDATE;

    OPEN cur_stock;
    read_loop: LOOP
        FETCH cur_stock INTO v_product_id, v_qty;
        IF done THEN LEAVE read_loop; END IF;

        /* 例如補貨 100 單位 */
        UPDATE inventory
        SET quantity = quantity + 100
        WHERE product_id = v_product_id;
    END LOOP;
    CLOSE cur_stock;
END$$

DELIMITER ;

說明：使用 FOR UPDATE 讓游標鎖定符合條件的列，避免同時有其他交易修改同筆資料，適合在事務（TRANSACTION）內使用。

範例 4：多個游標同時使用

DELIMITER $$

CREATE PROCEDURE sp_multi_cursor()
BEGIN
    DECLARE done_a INT DEFAULT FALSE;
    DECLARE done_b INT DEFAULT FALSE;
    DECLARE v_a_id INT;
    DECLARE v_a_name VARCHAR(30);
    DECLARE v_b_id INT;
    DECLARE v_b_amount DECIMAL(10,2);

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_a = TRUE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_b = TRUE;

    DECLARE cur_a CURSOR FOR SELECT id, name FROM table_a;
    DECLARE cur_b CURSOR FOR SELECT id, amount FROM table_b;

    OPEN cur_a;
    OPEN cur_b;

    read_a: LOOP
        FETCH cur_a INTO v_a_id, v_a_name;
        IF done_a THEN LEAVE read_a; END IF;

        /* 內層游標遍歷 B 表，與 A 表的當前列做比較 */
        read_b: LOOP
            FETCH cur_b INTO v_b_id, v_b_amount;
            IF done_b THEN LEAVE read_b; END IF;

            IF v_a_id = v_b_id THEN
                INSERT INTO result_table (a_id, a_name, b_amount)
                VALUES (v_a_id, v_a_name, v_b_amount);
            END IF;
        END LOOP read_b;

        /* 重新設定 B 游標，準備下一輪 A 的迭代 */
        CLOSE cur_b;
        OPEN cur_b;
        SET done_b = FALSE;
    END LOOP read_a;

    CLOSE cur_a;
    CLOSE cur_b;
END$$

DELIMITER ;

說明：此範例示範 嵌套游標 的使用方式：外層遍歷 table_a，內層遍歷 table_b，找出兩表相同 id 的紀錄並寫入結果表。

範例 5：結合事務（TRANSACTION）與游標

DELIMITER $$

CREATE PROCEDURE sp_transfer_funds(
    IN src_account INT,
    IN dst_account INT,
    IN amount DECIMAL(12,2)
)
BEGIN
    DECLARE v_balance DECIMAL(12,2);
    DECLARE done INT DEFAULT FALSE;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transfer failed, transaction rolled back';
    END;

    START TRANSACTION;

    /* 先檢查來源帳戶餘額 */
    DECLARE cur_src CURSOR FOR
        SELECT balance FROM accounts WHERE account_id = src_account FOR UPDATE;
    OPEN cur_src;
    FETCH cur_src INTO v_balance;
    CLOSE cur_src;

    IF v_balance < amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;

    /* 進行轉帳 */
    UPDATE accounts SET balance = balance - amount WHERE account_id = src_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = dst_account;

    COMMIT;
END$$

DELIMITER ;

說明：此程序在 事務 中使用 FOR UPDATE 游標鎖定來源帳戶，確保餘額檢查與扣除的原子性，若任一步驟失敗即 ROLLBACK，避免資金不一致。

常見陷阱與最佳實踐

陷阱 可能的結果 建議的解決方案
忘記關閉游標 佔用伺服器資源，導致連線數過高 一定在結束前 CLOSE 游標，最好放在 FINALLY 類似的結構（使用 DECLARE EXIT HANDLER）
未設 CONTINUE HANDLER FOR NOT FOUND 當游標讀完時產生未捕捉的例外，使程序提前終止 在 DECLARE 區塊加入 CONTINUE HANDLER，將旗標變數設為 TRUE
在迴圈內不斷 OPEN 游標 重複開啟同一游標造成資源浪費 僅在需要重新定位時才 CLOSE 後 OPEN，或使用 FETCH 搭配 偏移量
在大量資料上使用游標 效能急遽下降，CPU、IO 使用率飆升 儘量改寫為 集合式 SQL；若必須使用，先加上適當的 索引，並限制結果筆數
忘記使用事務保護更新 可能發生「讀-寫不一致」或死鎖 在需要 FOR UPDATE 的情況下，START TRANSACTION、COMMIT/ROLLBACK 必不可少

最佳實踐

  1. 先思考集合式解法：只有在無法用單一 INSERT/UPDATE/DELETE 完成時才使用游標。
  2. 使用 DECLARE CONTINUE HANDLER 捕捉 NOT FOUND，避免無限迴圈。
  3. 盡量在事務內使用 FOR UPDATE 游標，保證資料一致性。
  4. 限制游標返回的欄位與筆數，減少記憶體占用。
  5. 在程式碼結尾加入 CLOSE，即使發生例外也要確保釋放資源（可用 EXIT HANDLER）。

實際應用場景

場景 為什麼適合使用游標 範例概念
批次產生報表（如月結帳單） 需要逐筆計算每筆交易的累計、折扣、稅額等 範例 2 的累計薪資概念可延伸至帳單金額累積
資料清理或搬遷（逐筆檢查資料完整性） 某些清理規則過於複雜，無法一次寫成 WHERE 條件 範例 3 的補貨邏輯可改寫為「低庫存自動補貨」
金融交易（資金轉帳、風險檢查） 必須在同一筆交易內鎖定相關帳戶，避免競爭條件 範例 5 完整展示了事務 + 游標的安全轉帳流程
多表比對（資料同步、主從比對） 需要將兩個來源表的每筆資料做逐一比對、產生差異 範例 4 的嵌套游標即為此類需求的典型寫法
條件性觸發（根據前置資料決定後續動作） 觸發器內部無法直接使用 SELECT ... INTO 多筆結果，游標是唯一選項 在觸發器中使用 DECLARE CURSOR 逐筆處理符合條件的紀錄

總結

  • 游標 為 MySQL 儲存程序提供了逐筆處理資料的能力，雖然效率較集合式低，但在 複雜商業邏輯、事務安全、多表比對 等情境仍不可或缺。
  • 正確的 宣告、開啟、取值、關閉 流程、CONTINUE HANDLER、以及 事務 的結合，是避免資源泄漏與資料不一致的關鍵。
  • 開發時應先嘗試 集合式 解法，僅在無法以單一 SQL 完成時才使用游標；同時遵守 最佳實踐（限制欄位、加索引、適時關閉）可大幅減少效能問題。

掌握了上述概念與範例後，你就能在 MySQL 中自信地使用游標，為系統打造出既正確又高效的資料處理流程。祝開發順利！