MySQL 預存程序實戰：游標循環、動態 SQL、參數傳遞與分隔符重定義完整範例

MySQL 預存程序實戰：游標循環、動態 SQL、參數傳遞與分隔符重定義完整範例

MySQL 預存程序支援複雜業務邏輯封裝在資料庫層，減少網路往返。本文通過三個遞進案例，展示動態 SQL 執行、游標逐行處理、帶參數條件複製表資料的完整寫法，並重點講解 DELIMITER 重定義技巧。

環境準備

DROP TABLE IF EXISTS student1, student2;
CREATE TABLE student1 (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT, age INT);
CREATE TABLE student2 (id INT AUTO_INCREMENT PRIMARY KEY, name TEXT, age INT);

INSERT INTO student1 (name, age) VALUES 
('xiaoming',18),('xiaohong',17),('xiaogang',19),('xiaoyu',18),('xiaohua',20);

關鍵語法：分隔符重定義

預存程序體內包含分號 ;，若不重定義結束符，解析器會在首個分號處截斷。標準做法：

DELIMITER $$
CREATE PROCEDURE proc_name() BEGIN ... END $$
DELIMITER ;

本文統一使用 $$ 作為臨時結束符。

案例 1：動態 SQL 執行並輸出結果

DROP PROCEDURE IF EXISTS test_pro1 $$
CREATE PROCEDURE test_pro1()
BEGIN
    SET @sql = 'SELECT * FROM student1 WHERE age < 19';
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$

• PREPARE/EXECUTE/DEALLOCATE 三部曲實現動態 SQL。
• 適用於表名、欄位名、WHERE 條件需動態拼接的場景。

案例 2：游標循環複製資料（無參數）

DROP PROCEDURE IF EXISTS test_pro2 $$
CREATE PROCEDURE test_pro2()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_name TEXT DEFAULT '';
    DECLARE v_age INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT name, age FROM student1 WHERE age < 19;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_name, v_age;
        IF done THEN LEAVE read_loop; END IF;
        INSERT INTO student2 (name, age) VALUES (v_name, v_age);
    END LOOP;
    CLOSE cur;
END $$

• DECLARE CONTINUE HANDLER FOR NOT FOUND 捕獲游標結束，避免無限循環。
• LOOP ... LEAVE 結構比 WHILE 更清晰。

案例 3：帶參數條件複製（IN 參數）

DROP PROCEDURE IF EXISTS test_pro3 $$
CREATE PROCEDURE test_pro3(IN p_age INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_name TEXT DEFAULT '';
    DECLARE v_age INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT name, age FROM student1 WHERE age < p_age;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_name, v_age;
        IF done THEN LEAVE read_loop; END IF;
        INSERT INTO student2 (name, age) VALUES (v_name, v_age);
    END LOOP;
    CLOSE cur;
END $$

呼叫與驗證

CALL test_pro1();           -- 直接輸出查詢結果
CALL test_pro2();           -- 複製 age<19 的 3 筆
CALL test_pro3(18);         -- 複製 age<18 的 1 筆

SELECT * FROM student2;     -- 驗證結果

進階技巧與注意事項

1. OUT/INOUT 參數：返回處理筆數、狀態碼給呼叫端。

   CREATE PROCEDURE copy_cnt(IN p_age INT, OUT cnt INT) ...

2. 事務控制：程序內可 START TRANSACTION; ... COMMIT/ROLLBACK; 保證原子性。
3. 權限：建立者需 CREATE ROUTINE 權限，呼叫者需 EXECUTE 權限。
4. 效能：大量資料游標行級處理較慢，優先考慮 INSERT ... SELECT 集合操作；游標僅用於需逐行邏輯（如呼叫外部 API、複雜條件分支）場景。
5. 除錯：SELECT ... INTO @var 配合 SELECT @var 觀察中間值；或寫入臨時日誌表。

觸發器類比

觸發器同樣需 DELIMITER 重定義，且支援 BEFORE/AFTER INSERT/UPDATE/DELETE 事件，常用於審計日誌、衍生欄位維護。

掌握預存程序核心範式，能將高內聚業務邏輯下沉資料庫層，提升系統整體效能與一致性。