MySQL 存儲過程中使用游標逐列處理資料完整範例

游標讓我們在存儲過程中像應用程式一樣「逐行讀取」結果集並處理。下面以「統計昨天每位使用者的語文、數學總分並更新彙總表」為例。

完整存儲過程碼

DELIMITER $$

DROP PROCEDURE IF EXISTS `getUserInfo` $$
CREATE PROCEDURE `getUserInfo`(IN `date_day` DATETIME)
BEGIN
  DECLARE `_userName` VARCHAR(12);
  DECLARE `_chinese` INT;
  DECLARE `_math` INT;
  DECLARE `done` INT DEFAULT 0;

  -- 1. 宣告游標
  DECLARE `rs_cursor` CURSOR FOR
    SELECT `username`, `chinese`, `math`
    FROM `userInfo`
    WHERE DATEDIFF(`createDate`, `date_day`) = 0;

  -- 2. 宣告 NOT FOUND 處理器
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = 1;

  -- 3. 參數預設：若未傳入日期，預設為昨天
  IF `date_day` IS NULL THEN
    SET `date_day` = DATE_ADD(NOW(), INTERVAL -1 DAY);
  END IF;

  -- 4. 開啟游標
  OPEN `rs_cursor`;

  `cursor_loop`: LOOP
    FETCH `rs_cursor` INTO `_userName`, `_chinese`, `_math`;

    IF `done` = 1 THEN
      LEAVE `cursor_loop`;
    END IF;

    -- 5. 業務邏輯：更新彙總表
    UPDATE `infoSum`
    SET `total` = `_chinese` + `_math`
    WHERE `UserName` = `_userName`;
  END LOOP `cursor_loop`;

  -- 6. 關閉游標
  CLOSE `rs_cursor`;
END$$

DELIMITER ;

關鍵點解析

1. DECLARE CURSOR FOR SELECT ...：定義結果集，不會立即執行查詢。
2. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1：當 FETCH 讀不到資料時，將 done 設為 1，避免無限迴圈。
3. OPEN / FETCH / CLOSE：標準三步驟，務必成對出現，否則會洩漏伺服器資源。
4. 變數名前加底線（_userName）避免與欄位名衝突。

呼叫測試

CALL getUserInfo('2024-01-15');  -- 指定日期
CALL getUserInfo(NULL);          -- 自動用昨天

效能提醒

• 游標屬於「逐行處理」（RBAR），大量資料效能遠遜於集合式 SQL（如單條 UPDATE ... JOIN）。
• 能用 UPDATE ... JOIN 或 INSERT ... ON DUPLICATE KEY UPDATE 解決時，優先用集合式寫法。
• 若必須用游標，確保 SELECT 有合適索引，減少掃描行數。

進階：動態游標（Prepared Statement）

若查詢條件需動態組裝，可參考 MySQL 動態游標學習，利用 PREPARE / EXECUTE 建立 View 再開游標。