本文 AI 產出，尚未審核

MySQL 儲存程序（Stored Procedure）

建立與呼叫 PROCEDURE

簡介

在日常開發中，資料庫操作往往不只是單純的 SELECT、INSERT，還會涉及多筆資料的批次處理、複雜的商業邏輯或是需要保證原子性的交易。若把所有邏輯寫在應用程式層，會造成程式碼重複、維護成本升高，也容易因為不同程式呼叫方式不一致而產生錯誤。

MySQL 的 儲存程序（Stored Procedure） 正是為了解決這類問題而設計的。它允許我們把一段完整的 SQL 程式碼（甚至包含流程控制）封裝起來，形成一個可重複使用的「子程序」。開發者只需要呼叫一次即可完成多步驟的資料處理，且所有邏輯都保留在資料庫內，提升效能與安全性。

本篇文章將從 建立、呼叫、參數傳遞、錯誤處理 等核心概念出發，帶你一步步掌握 MySQL 儲存程序的實作與最佳使用方式，適合 初學者 也能提供 中級開發者 在實務上快速上手的參考。

核心概念

1. 為什麼需要 DELIMITER

在 MySQL 客戶端（如 mysql、MySQL Workbench）中，預設的語句結束符號是 ;。然而儲存程序本身內部會包含多條以 ; 結尾的 SQL 語句，若不先改變分隔符，系統會錯誤地在第一條語句結束時就認為整個程序已完成。

DELIMITER $$          -- 把結束符號改成 $$
CREATE PROCEDURE demo()
BEGIN
    SELECT 'Hello';
END$$
DELIMITER ;           -- 設回預設的 ;

技巧：在大多數圖形化工具（如 MySQL Workbench）已內建自動切換 DELIMITER，但在純文字終端機仍須手動設定。

2. 基本語法結構

CREATE [DEFINER = user] PROCEDURE procedure_name
    ([parameter_list])
    [characteristic ...]
BEGIN
    -- 程式內容
END
  • parameter_list：IN（輸入參數）、OUT（輸出參數）或 INOUT（兩者皆可）。
  • characteristic：如 SQL SECURITY DEFINER、DETERMINISTIC、LANGUAGE SQL（預設）等，用來說明程序的行為特性。

3. 參數類型與使用

參數類型 說明
IN 呼叫時傳入值，程序內只能讀取。
OUT 程序內設定值，呼叫者可在執行完後取得。
INOUT 既可傳入也可回傳，適合需要「加工」的情境。

範例 1：簡單的 IN 參數

DELIMITER $$

CREATE PROCEDURE GetEmployeeByDept(
    IN dept_id INT
)
BEGIN
    SELECT emp_id, emp_name, salary
    FROM employees
    WHERE department_id = dept_id;
END$$

DELIMITER ;

呼叫方式：

CALL GetEmployeeByDept(3);

範例 2：使用 OUT 參數回傳聚合結果

DELIMITER $$

CREATE PROCEDURE TotalSalesByMonth(
    IN p_year INT,
    IN p_month INT,
    OUT p_total DECIMAL(10,2)
)
BEGIN
    SELECT SUM(amount) INTO p_total
    FROM sales
    WHERE YEAR(sale_date) = p_year
      AND MONTH(sale_date) = p_month;
END$$

DELIMITER ;

呼叫與取得結果：

SET @total = 0;
CALL TotalSalesByMonth(2024, 9, @total);
SELECT @total AS SeptemberTotal;

範例 3：INOUT 參數實作「字串加密」簡易範例

DELIMITER $$

CREATE PROCEDURE SimpleEncrypt(
    INOUT p_text VARCHAR(255)
)
BEGIN
    -- 把每個字元的 ASCII 加 1，形成簡易的 Caesar cipher
    SET p_text = CONCAT(
        CHAR(ASCII(SUBSTRING(p_text,1,1))+1),
        SUBSTRING(p_text,2)
    );
END$$

DELIMITER ;

呼叫方式：

SET @msg = 'hello';
CALL SimpleEncrypt(@msg);
SELECT @msg;   -- 結果為 'iello'

4. 流程控制：IF、CASE、LOOP

儲存程序支援完整的流程控制語句，讓資料庫本身就能完成條件判斷與迴圈運算。

範例 4：使用 IF 判斷並回傳不同訊息

DELIMITER $$

CREATE PROCEDURE CheckStock(
    IN p_product_id INT,
    OUT p_msg VARCHAR(100)
)
BEGIN
    DECLARE v_qty INT;

    SELECT quantity INTO v_qty
    FROM inventory
    WHERE product_id = p_product_id;

    IF v_qty IS NULL THEN
        SET p_msg = '商品不存在';
    ELSEIF v_qty = 0 THEN
        SET p_msg = '已售罄';
    ELSEIF v_qty < 10 THEN
        SET p_msg = CONCAT('庫存不足 (', v_qty, ')');
    ELSE
        SET p_msg = CONCAT('庫存充足 (', v_qty, ')');
    END IF;
END$$

DELIMITER ;

範例 5：使用 LOOP 產生連續的月份資料

DELIMITER $$

CREATE PROCEDURE GenerateMonthSeries(
    IN p_start DATE,
    IN p_end   DATE
)
BEGIN
    DECLARE cur DATE;
    SET cur = p_start;

    WHILE cur <= p_end DO
        INSERT INTO month_series(month_start) VALUES (cur);
        SET cur = DATE_ADD(cur, INTERVAL 1 MONTH);
    END WHILE;
END$$

DELIMITER ;

呼叫：

CALL GenerateMonthSeries('2024-01-01', '2024-12-01');

5. 交易控制（Transaction）與錯誤處理

儲存程序內可以自行管理 交易，確保多個寫入操作要麼全部成功、要麼全部回滾。配合 DECLARE ... HANDLER，可以捕捉例外並作適當處理。

範例 6：完整的交易與錯誤處理

DELIMITER $$

CREATE PROCEDURE TransferFunds(
    IN p_from_account INT,
    IN p_to_account   INT,
    IN p_amount       DECIMAL(10,2),
    OUT p_status      VARCHAR(20)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = '失敗';
    END;

    START TRANSACTION;

    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account
      AND balance >= p_amount;

    IF ROW_COUNT() = 0 THEN
        ROLLBACK;
        SET p_status = '餘額不足';
        LEAVE proc_end;
    END IF;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    COMMIT;
    SET p_status = '成功';

    proc_end: BEGIN END;   -- 標示結束點（僅示範用途）
END$$

DELIMITER ;

呼叫與檢查結果：

SET @msg = '';
CALL TransferFunds(101, 202, 500.00, @msg);
SELECT @msg AS TransferResult;

常見陷阱與最佳實踐

陷阱 說明 解決方案 / 最佳實踐
忘記切換 DELIMITER 程序內的 ; 會提前結束語句，導致語法錯誤。 一定 在建立/修改程序前先 DELIMITER $$，結束後恢復 ;。
參數名稱與欄位名稱同名 MySQL 會把參數視為欄位，導致 "ambiguous column" 錯誤。 使用 前綴（如 p_、in_）或在程式內使用 SELECT ... INTO 明確區分。
缺乏錯誤處理 交易失敗卻未回滾，可能留下不一致的資料。 加入 DECLARE EXIT HANDLER FOR SQLEXCEPTION 或 SQLWARNING，配合 ROLLBACK。
過度依賴儲存程序 把所有邏輯都搬到 DB，會降低程式碼可測試性與可讀性。 只 把與資料密切相關、需要批次或原子性保證的邏輯放在程序裡，其餘維持在應用層。
未設定適當的安全性 SQL SECURITY DEFINER 會以建立者權限執行，可能造成權限洩漏。 依需求選擇 DEFINER 或 INVOKER，並確保建立者權限最小化。
大量資料寫入未使用批次 單筆 INSERT 迴圈會極度緩慢。 利用 LOOP 或 INSERT … SELECT 產生批次寫入，或在程序外使用批次 API。

最佳實踐小結：

  1. 命名規則：參數前加 p_、變數前加 v_，提升可讀性。
  2. 分段測試：先在 SELECT 中測試邏輯，再搬到 BEGIN…END。
  3. 使用 EXPLAIN：檢查程序內的查詢效能，必要時加索引。
  4. 文件化：在程式碼上方寫清楚參數意義、回傳值與例外情況。
  5. 版本控制：將 CREATE PROCEDURE 或 DROP PROCEDURE IF EXISTS 放在 migration 檔案中，避免環境不一致。

實際應用場景

場景 為何適合使用儲存程序
月結報表 需要一次性聚合多張表、計算複雜指標，使用程序可一次完成，降低網路往返次數。
批次資料清理 每天凌晨執行的資料清除或歸檔任務，利用 LOOP 與交易確保資料完整性。
多步驟交易（如銀行轉帳） 必須保證「扣款」與「入帳」同時成功，程序內的 START TRANSACTION … COMMIT 能提供原子性。
動態查詢生成 根據使用者傳入的條件組合多段 WHERE，在程序內組裝 SQL，避免在程式碼中拼接字串。
權限驗證與審計 把權限檢查與操作寫在同一個程序裡，確保每次呼叫都有一致的審計紀錄。

總結

MySQL 的 儲存程序 為資料庫提供了 封裝、重用與交易控制 的強大能力。透過本文，我們了解了：

  • DELIMITER 的必要性與正確使用方式。
  • IN / OUT / INOUT 參數的差異與實作範例。
  • 流程控制、迴圈、錯誤處理 與 交易 的完整範例。
  • 常見的 陷阱、最佳實踐 以及 實務場景。

掌握這些概念後，你就能在專案中：

  1. 把繁雜的資料處理邏輯搬到資料庫層，減少程式碼重複。
  2. 確保多筆寫入操作的 原子性，提升資料一致性。
  3. 以 統一的入口 供前端或其他服務呼叫，簡化維護與測試。

最後提醒，儲存程序不是萬能的解藥，仍需根據系統架構與團隊習慣適度使用。希望本篇教學能幫助你在 MySQL 開發旅程中，快速上手並寫出 高效、可靠、易維護 的資料庫程式碼。祝開發順利！ 🚀