MySQL 預存程序游標與多游標完整實戰指南

游標是 MySQL 預存程序中逐行處理結果集的核心機制。本文從單游標基礎語法入手，進階至多游標協同處理的三種模式，並解決「共用 HANDLER 變數導致第二個游標無法執行」的經典陷阱。

單游標標準範本

DELIMITER //

CREATE PROCEDURE `get_user_info`(IN p_date DATETIME)
BEGIN
    DECLARE v_username VARCHAR(12);
    DECLARE v_chinese INT;
    DECLARE v_math INT;
    DECLARE done INT DEFAULT FALSE;

    DECLARE cur CURSOR FOR 
        SELECT username, chinese, math 
        FROM user_info 
        WHERE DATEDIFF(create_date, p_date) = 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    IF p_date IS NULL THEN
        SET p_date = DATE_ADD(NOW(), INTERVAL -1 DAY);
    END IF;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_username, v_chinese, v_math;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE info_sum 
        SET total = v_chinese + v_math 
        WHERE username = v_username;
    END LOOP;
    CLOSE cur;
END//

DELIMITER ;

核心宣告順序（必須遵守）

1. 變數宣告（DECLARE var ...）
2. 游標宣告（DECLARE cur CURSOR FOR ...）
3. HANDLER 宣告（DECLARE CONTINUE HANDLER ...）

順序錯誤會導致 ERROR 1337 (HY000)。

多游標處理三種模式

模式一：順序處理 + 重置 HANDLER 變數（最常用）

DECLARE done INT DEFAULT FALSE;
DECLARE cur_1 CURSOR FOR SELECT name, pass FROM test_tbl;
DECLARE cur_2 CURSOR FOR SELECT id, name FROM test_tbl;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_1;
REPEAT
    FETCH cur_1 INTO v_name, v_pass;
    IF NOT done THEN
        SELECT v_name, v_pass;
    END IF;
UNTIL done END REPEAT;
CLOSE cur_1;

SET done = FALSE;  -- 關鍵：重置！

OPEN cur_2;
REPEAT
    FETCH cur_2 INTO v_id, v_name;
    IF NOT done THEN
        SELECT v_id, v_name;
    END IF;
UNTIL done END REPEAT;
CLOSE cur_2;

模式二：BEGIN...END 區塊隔離作用域（推薦，避免變數衝突）

BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_1 CURSOR FOR SELECT name, pass FROM test_tbl;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 處理 cur_1
END;

BEGIN
    DECLARE done INT DEFAULT FALSE;  -- 獨立變數
    DECLARE cur_2 CURSOR FOR SELECT id, name FROM test_tbl;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 處理 cur_2
END;

模式三：巢狀游標（少見，用於主從表關聯處理）

外層游標遍歷主表，內層游標依主表鍵值查詢從表。需注意內層游標在每次外層迭代時都要 OPEN/CLOSE，且 HANDLER 變數需區隔。

完整測試環境建立腳本

-- 建立測試表
DROP TABLE IF EXISTS `netingcn_proc_test`;
CREATE TABLE `netingcn_proc_test` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20),
    `password` VARCHAR(20),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `netingcn_proc_test` (`name`, `password`) VALUES
('procedure1', 'pass1'),
('procedure2', 'pass2'),
('procedure3', 'pass3'),
('procedure4', 'pass4');

效能與最佳實踐提醒

• 集合思維優先：能用單一 SQL（JOIN、UPDATE ... JOIN、INSERT ... SELECT）解決的，盡量避免游標逐行處理，效能差距可達數量級。
• 游標唯讀：MySQL 游標為 READ ONLY、NON-SCROLLABLE（只能向前）、ASENSITIVE（可能指向暫存表副本）。
• 記憶體管理：大量資料時，FETCH 進變數再處理，避免在循環內建立大型暫存表。
• 異常處理：善用 DECLARE EXIT HANDLER FOR SQLEXCEPTION 確保游標在異常時正確 CLOSE。

游標是處理複雜程序邏輯的利器，但請記住：「能用集合操作解決的，就不要用游標」。