MySQL 游標完整使用指南：宣告、開啟、迴圈、異常處理與巢狀範例

MySQL 游標完整使用指南：宣告、開啟、迴圈、異常處理與巢狀範例

游標用於在預存程序中逐行處理查詢結果集，適合複雜邏輯、逐行計算、動態 SQL 構建等場景。

核心語法流程

1. 宣告：DECLARE cursor_name CURSOR FOR SELECT ...;
2. 異常處理：DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
3. 開啟：OPEN cursor_name;
4. 讀取：FETCH cursor_name INTO var1, var2...;
5. 迴圈處理：WHILE NOT done DO ... END WHILE; 或 LOOP ... END LOOP;
6. 關閉：CLOSE cursor_name;

範例一：WHILE 迴圈累加字串

DROP PROCEDURE IF EXISTS useCursor;
DELIMITER $$
CREATE PROCEDURE useCursor()
BEGIN
  DECLARE tmpName VARCHAR(20) DEFAULT '';
  DECLARE allName VARCHAR(255) DEFAULT '';
  DECLARE done INT DEFAULT 0;

  DECLARE cur1 CURSOR FOR SELECT name FROM test.level;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  FETCH cur1 INTO tmpName;

  WHILE NOT done DO
    SET tmpName = CONCAT(tmpName, ';');
    SET allName = CONCAT(allName, tmpName);
    FETCH cur1 INTO tmpName;
  END WHILE;

  CLOSE cur1;
  SELECT allName AS result;
END$$
DELIMITER ;

CALL useCursor();

範例二：LOOP + LEAVE 迴圈

DELIMITER $$
DROP PROCEDURE IF EXISTS cursor_example$$
CREATE PROCEDURE cursor_example()
READS SQL DATA
BEGIN
  DECLARE l_employee_id INT;
  DECLARE l_salary DECIMAL(8,2);
  DECLARE l_department_id INT;
  DECLARE done INT DEFAULT 0;

  DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  emp_loop: LOOP
    FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
    IF done = 1 THEN LEAVE emp_loop; END IF;
    -- 處理邏輯：如動態構建 SQL、寫入臨時表等
  END LOOP emp_loop;
  CLOSE cur1;
END$$
DELIMITER ;

範例三：REPEAT 迴圈

DELIMITER //
DROP PROCEDURE IF EXISTS test //
CREATE PROCEDURE test()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a VARCHAR(200) DEFAULT '';
  DECLARE c VARCHAR(200) DEFAULT '';
  DECLARE mycursor CURSOR FOR SELECT fusername FROM uchome_friend;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN mycursor;
  REPEAT
    FETCH mycursor INTO a;
    IF NOT done THEN
      SET c = CONCAT(c, a);
    END IF;
  UNTIL done END REPEAT;
  CLOSE mycursor;
  SELECT c;
END //
DELIMITER ;

關鍵注意事項

替代方案建議

• 集合操作：INSERT ... SELECT, UPDATE ... JOIN, MERGE (8.0+)
• 窗口函數：ROW_NUMBER(), LAG(), SUM() OVER() 等
• 應用層處理：取回結果集在程式碼中迴圈，減少資料庫 CPU 壓力