MySQL – 儲存程序 (Stored Procedure)

變數與控制流程

---

簡介

在 MySQL 的開發環境中，儲存程序（Stored Procedure）是把一段複雜的 SQL 邏輯封裝起來、供多個應用程式或使用者重複呼叫的好幫手。相較於在應用程式層面寫大量的程式碼，將核心的資料處理搬到資料庫內部，可以減少網路傳輸、提升執行效率，同時也讓商業規則更集中、易於維護。

然而，儲存程序若只會寫簡單的 SELECT、INSERT，往往無法應付真實專案裡的「條件判斷」或「迴圈」需求。變數與控制流程（IF、CASE、LOOP…）正是讓儲存程序變得「程式化」的關鍵。掌握這些概念，你就能在資料庫端完成資料驗證、批次處理、錯誤回報等高階任務，讓整個系統更具彈性與可擴充性。

本文將以 淺顯易懂 的方式，從變數的宣告與使用、常見的流程控制語句，到實務開發中的最佳實踐與常見陷阱，提供完整且可直接套用的範例，幫助初學者到中級開發者快速上手 MySQL 儲存程序的變數與控制流程。

---

核心概念

1. 變數的宣告與範圍

類型	語法	說明
本地變數	DECLARE var_name datatype [DEFAULT default_value];	僅在儲存程序內部有效，宣告必須放在 BEGIN … END 區塊的最前面。
參數變數	`IN	OUT
全域變數	SET @var_name = value;	以 @ 開頭，作用於整個連線，儲存程序內外皆可存取。

注意：MySQL 的本地變數不能使用 @ 前綴，@ 代表的是會話層級的全域變數。

範例 1：簡單的本地變數宣告與使用

DELIMITER $$

CREATE PROCEDURE sp_demo_variable()
BEGIN
    -- 宣告本地變數
    DECLARE v_counter INT DEFAULT 0;
    DECLARE v_msg VARCHAR(50);

    -- 賦值
    SET v_counter = 5;
    SET v_msg = CONCAT('目前計數為 ', v_counter);

    -- 輸出結果
    SELECT v_msg AS message;
END$$

DELIMITER ;

這段程式會輸出 目前計數為 5，說明變數的宣告、賦值與使用流程。

---

2. 條件判斷 – IF / CASE

2.1 IF 結構

IF condition THEN
    statements;
ELSEIF another_condition THEN
    statements;
ELSE
    statements;
END IF;

範例 2：根據庫存量決定是否允許下單

DELIMITER $$

CREATE PROCEDURE sp_check_stock(
    IN p_product_id INT,
    IN p_qty INT,
    OUT p_allowed BOOLEAN
)
BEGIN
    DECLARE v_stock INT;

    SELECT stock INTO v_stock
    FROM products
    WHERE product_id = p_product_id;

    IF v_stock IS NULL THEN
        SET p_allowed = FALSE;            -- 商品不存在
    ELSEIF v_stock >= p_qty THEN
        SET p_allowed = TRUE;             -- 庫存足夠
    ELSE
        SET p_allowed = FALSE;            -- 庫存不足
    END IF;
END$$

DELIMITER ;

透過 IF 判斷，我們可以在資料庫層直接回報「是否可下單」的結果，減少應用程式端的多餘查詢。

2.2 CASE 表達式

CASE expression
    WHEN value1 THEN statements;
    WHEN value2 THEN statements;
    ELSE statements;
END CASE;

範例 3：將數字等級轉換為文字說明

DELIMITER $$

CREATE PROCEDURE sp_grade_desc(
    IN p_grade INT,
    OUT p_desc VARCHAR(20)
)
BEGIN
    CASE p_grade
        WHEN 1 THEN SET p_desc = '低';
        WHEN 2 THEN SET p_desc = '中';
        WHEN 3 THEN SET p_desc = '高';
        ELSE SET p_desc = '未定義';
    END CASE;
END$$

DELIMITER ;

---

3. 迴圈結構 – WHILE、REPEAT、LOOP

迴圈	語法	特點
WHILE	WHILE condition DO … END WHILE;	條件為 TRUE 時持續執行，類似 C 語言的 while。
REPEAT	REPEAT … UNTIL condition END REPEAT;	先執行一次，再判斷條件，類似 do…while。
LOOP	LOOP … END LOOP;	必須配合 LEAVE 或 ITERATE 控制跳出或回到迴圈開頭。

範例 4：使用 WHILE 計算 1~N 的總和

DELIMITER $$

CREATE PROCEDURE sp_sum_whole(
    IN p_n INT,
    OUT p_sum BIGINT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total BIGINT DEFAULT 0;

    WHILE i <= p_n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;

    SET p_sum = total;
END$$

DELIMITER ;

範例 5：REPEAT 迴圈實作批次更新（每 100 筆 commit）

DELIMITER $$

CREATE PROCEDURE sp_batch_update()
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE cur CURSOR FOR
        SELECT id FROM orders WHERE status = 'PENDING' LIMIT 1000;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    OPEN cur;
    read_loop: REPEAT
        FETCH cur INTO v_id;
        IF NOT v_done THEN
            UPDATE orders SET status = 'PROCESSED' WHERE id = v_id;
        END IF;
    UNTIL v_done END REPEAT;
    CLOSE cur;
END$$

DELIMITER ;

這裡使用 REPEAT … UNTIL 搭配 游標 (CURSOR) 以及 CONTINUE HANDLER，示範了在儲存程序內完成批次資料處理的常見模式。

---

4. 例外處理 – DECLARE HANDLER

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 錯誤處理邏輯，例如寫入 log
        SET @err_msg = CONCAT('Error at ', NOW());
    END;

範例 6：捕捉除以零錯誤，避免交易失敗

DELIMITER $$

CREATE PROCEDURE sp_divide(
    IN p_a DECIMAL(10,2),
    IN p_b DECIMAL(10,2),
    OUT p_result DECIMAL(10,2)
)
BEGIN
    DECLARE CONTINUE HANDLER FOR DIVISION_BY_ZERO
    BEGIN
        SET p_result = NULL;   -- 回傳 NULL 表示錯誤
    END;

    SET p_result = p_a / p_b;
END$$

DELIMITER ;

---

常見陷阱與最佳實踐

陷阱	說明	最佳做法
變數名稱與欄位名稱衝突	SELECT col INTO col FROM … 會產生混淆。	為變數加上前綴（如 v_）或使用不同大小寫。
DECLARE 必須在 BEGIN 之最前	若宣告放在程式碼中間，會出現編譯錯誤。	把所有 DECLARE 放在區塊開頭，保持一致。
游標未關閉	會佔用資源，導致連線數上限被耗盡。	使用 OPEN …; …; CLOSE …;，或在 EXIT HANDLER 中確保關閉。
未處理的例外	任何未捕捉的錯誤會導致整個儲存程序回滾。	依需求加入 DECLARE … HANDLER，並在錯誤發生時記錄 log。
過度使用全域變數 (@var)	會在同一連線的其他程序中被意外讀寫。	儘量使用本地變數，若必須跨程序傳值，使用 OUT 參數。
迴圈無法跳出	忘記 LEAVE 或 ITERATE，造成無限迴圈。	在迴圈內部設定明確的退出條件，使用 LEAVE 立即跳出。

其他最佳實踐

1. 保持單一職責：每個儲存程序只做一件事（例如「檢查庫存」或「產生報表」），方便測試與重用。
2. 使用參數代替硬編碼：把可變的條件、日期、狀態等寫成 IN 參數，提升彈性。
3. 加入日誌 (Logging)：在關鍵節點使用 INSERT INTO log_table …，方便事後追蹤與除錯。
4. 善用事務 (TRANSACTION)：對需要多表寫入的程序，使用 START TRANSACTION … COMMIT / ROLLBACK，確保資料一致性。
5. 測試與效能評估：使用 EXPLAIN、SHOW PROFILE 或 performance_schema 觀測執行計畫與耗時，避免過於複雜的迴圈導致瓶頸。

---

實際應用場景

場景	需求	變數與控制流程的角色
訂單處理	檢查庫存 → 扣除庫存 → 產生出貨單	IF 判斷庫存是否足夠、WHILE 逐筆扣庫存、DECLARE HANDLER 捕捉扣庫存失敗。
批次匯入	每日上傳 CSV，若資料格式錯誤則記錄錯誤行並繼續	REPEAT 搭配游標讀取每筆資料、CASE 分類不同錯誤類型、CONTINUE HANDLER 處理例外。
統計報表	產生每月銷售額、分區間排名	使用變數累加金額、LOOP 逐區間計算、IF 判斷是否達到排名門檻。
權限驗證	根據使用者角色決定可執行的 SQL	CASE 把角色代號映射成允許的操作、LEAVE 提前結束不符合條件的流程。
自動化清除	每週清除超過 90 天未使用的暫存資料	WHILE 搭配日期比較迭代刪除、DECLARE CONTINUE HANDLER 捕捉刪除衝突。

以上情境皆可透過 變數 來暫存中間結果，控制流程 來決定執行路徑，從而把原本散落在應用程式多處的商業邏輯集中於資料庫層，提升系統的一致性與效能。

---

總結

• 變數（本地、參數、全域）是儲存程序的基礎，正確的宣告與使用能讓程式碼更易讀且避免名稱衝突。
• IF、CASE 為條件分支的核心，讓我們能根據不同情況執行不同的 SQL。
• WHILE、REPEAT、LOOP 提供了迴圈能力，配合 LEAVE、ITERATE 以及 游標，可完成批次更新與資料清理等工作。
• DECLARE … HANDLER 讓儲存程序能優雅地捕捉例外，避免整個交易因單筆錯誤而失敗。
• 常見的陷阱（變數衝突、未關閉游標、無限迴圈）只要遵守 最佳實踐（統一前綴、集中 DECLARE、事前設計退出條件）即可輕鬆避免。

掌握以上概念與技巧，你就能在 MySQL 中寫出 可讀、可維護、效能佳 的儲存程序，將資料驗證、批次處理與商業規則直接搬到資料庫層，為系統帶來更高的穩定性與擴充性。祝你在實務開發中玩得開心、寫得順手！ 🚀