DELIMITER // -- 修改語句結束符，避免過程體內 ; 衝突
CREATE PROCEDURE proc_name(IN param INT, OUT result INT)
BEGIN
-- 聲明區 (順序固定：變量 → 游標 → 處理器)
DECLARE var INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT col FROM table WHERE cond;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 執行區
OPEN cur;
read_loop: LOOP
FETCH cur INTO var;
IF done THEN LEAVE read_loop; END IF;
-- 處理邏輯
END LOOP;
CLOSE cur;
END//
DELIMITER ;DELIMITER //
DROP PROCEDURE IF EXISTS getUserInfo//
CREATE PROCEDURE getUserInfo(IN p_date DATETIME)
BEGIN
DECLARE v_user VARCHAR(12);
DECLARE v_chinese, v_math INT;
DECLARE done INT DEFAULT 0;
-- 游標：查詢指定日期創建的用戶成績
DECLARE cur CURSOR FOR
SELECT username, chinese, math FROM userInfo
WHERE DATEDIFF(createDate, p_date) = 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 參數默認值：昨天
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_user, v_chinese, v_math;
IF done THEN LEAVE read_loop; END IF;
-- 業務邏輯：更新匯總表
UPDATE infoSum SET total = v_chinese + v_math WHERE UserName = v_user;
END LOOP read_loop;
CLOSE cur;
END//
DELIMITER ;DECLARE num INT DEFAULT 1;
DECLARE str VARCHAR(255) DEFAULT '';
REPEAT
SET str = CONCAT(str, num, ',');
SET num = num + 1;
UNTIL num > 5 END REPEAT;
-- 結果: '1,2,3,4,5,'DECLARE num INT DEFAULT 1;
DECLARE str VARCHAR(255) DEFAULT '';
WHILE num < 10 DO
SET str = CONCAT(str, num, ',');
SET num = num + 1;
END WHILE;DECLARE num INT DEFAULT 1;
DECLARE str VARCHAR(255) DEFAULT '';
loop_label: LOOP
IF num >= 10 THEN LEAVE loop_label; END IF; -- break
SET num = num + 1;
IF (num % 3) = 0 THEN ITERATE loop_label; END IF; -- continue (跳過 3 的倍數)
SET str = CONCAT(str, num, ',');
END LOOP loop_label;
-- 結果: '2,4,5,7,8,' (跳過 3,6,9)CREATE PROCEDURE test_proc_1()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tname, tpass VARCHAR(50);
DECLARE cur1 CURSOR FOR SELECT name, password FROM t;
DECLARE cur2 CURSOR FOR SELECT id, name FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT FETCH cur1 INTO tname, tpass;
IF NOT done THEN SELECT tname, tpass; END IF;
UNTIL done END REPEAT;
CLOSE cur1;
-- 關鍵：重置 done = 0，否則第二個游標不執行！
SET done = 0;
OPEN cur2;
REPEAT FETCH cur2 INTO tid, tname;
IF NOT done THEN SELECT tid, tname; END IF;
UNTIL done END REPEAT;
CLOSE cur2;
ENDCREATE PROCEDURE test_proc_2()
BEGIN
-- 第一個游標塊
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tname, tpass VARCHAR(50);
DECLARE cur1 CURSOR FOR SELECT name, password FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT FETCH cur1 INTO tname, tpass;
IF NOT done THEN SELECT tname, tpass; END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;
-- 第二個游標塊 (獨立變量作用域)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tid INT; DECLARE tname VARCHAR(50);
DECLARE cur2 CURSOR FOR SELECT id, name FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur2;
REPEAT FETCH cur2 INTO tid, tname;
IF NOT done THEN SELECT tid, tname; END IF;
UNTIL done END REPEAT;
CLOSE cur2;
END;
END-- 測試表
DROP TABLE IF EXISTS netingcn_proc_test;
CREATE TABLE netingcn_proc_test (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20), password VARCHAR(20)
) ENGINE=InnoDB;
INSERT INTO netingcn_proc_test (name, password) VALUES
('proc1','p1'),('proc2','p2'),('proc3','p3'),('proc4','p4');
-- 調用
CALL test_proc(); -- 單游標
CALL test_proc_1(); -- 多游標順序
CALL test_proc_2(); -- 多游標嵌套| 坑點 | 後果 | 正確姿勢 |
|---|---|---|
| 聲明順序錯誤 (游標前聲明變量，處理器最後) | 語法錯誤 | **變量 → 游標 → 處理器** 嚴格遵守 |
| FETCH 目標變量名與游標列名同名 | 變量值為 NULL (列名遮蔽) | 變量名加前綴 `v_`/`p_`，避免同名 |
| 多游標不重置 `done` / 不用嵌套塊 | 第二個游標不執行 | 模式 A: `SET done=0`；模式 B: `BEGIN...END` 隔離作用域 |
| 游標 SELECT 列數/類型與 FETCH 變量不匹配 | 數據截斷/報錯 | 嚴格對應，或用 `SELECT *` 配合定義完整記錄變量 (需 ROW 類型支持) |
| 忘記 `CLOSE cursor` | 資源洩漏、報錯 "Cursor is not open" | 必配 `OPEN`/`CLOSE`，異常處理中也要 CLOSE |
| 在游標循環中修改同表數據 | 可能觸發 "Table is read only" 或死鎖 | 避免同表讀寫衝突，或用臨時表緩存 ID 後批量更新 |
MySQL 存儲過程 + 游標是處理**行級邏輯**的利器，核心在於：**聲明順序固定、處理器捕獲 NOT FOUND、循環標籤配合 LEAVE/ITERATE、多游標隔離作用域**。但**優先考慮集合操作 (SQL)**，存儲過程僅作為補充工具用於特定批量/遷移場景。
