MySQL 5.0 引入儲存程序，將業務邏輯封裝在資料庫層，能減少網路傳輸、提升效能並集中管理規則。本文從語法基礎到流程控制，配合完整範例，帶你快速上手。

基本語法結構

CREATE PROCEDURE sp_name ([param_list])
BEGIN
  -- SQL 陳述式區塊
END;

• 程序名稱建議加前綴（如 proc_、usp_）避免衝突
• 無參數時括號不可省略：CALL sp_name()

參數模式

模式	說明	範例
IN	輸入參數（預設），呼叫端傳入	IN p_id INT
OUT	輸出參數，程式內賦值供呼叫端接收	OUT p_name VARCHAR(50)
INOUT	雙向參數，可傳入亦可傳出	INOUT p_total DECIMAL(10,2)

呼叫與刪除

-- 呼叫
CALL proc_get_user(100, @out_name);
SELECT @out_name;

-- 刪除
DROP PROCEDURE IF EXISTS proc_get_user;

限制：無法在儲存程序內直接 DROP 另一個程序，只能 CALL。

區塊、標籤與跳出

-- 匿名區塊
BEGIN
  SELECT 1;
END;

-- 帶標籤區塊（可搭配 LEAVE/ITERATE）
my_block: BEGIN
  SELECT 2;
  LEAVE my_block;  -- 跳出此區塊
  SELECT 3;        -- 不會執行
END my_block;

條件控制：IF / CASE

IF condition THEN
  -- statements
ELSEIF other_condition THEN
  -- statements
ELSE
  -- statements
END IF;

-- CASE 簡單形式
CASE expr
  WHEN val1 THEN stmt1
  WHEN val2 THEN stmt2
  ELSE stmt_default
END CASE;

-- CASE 搜尋形式
CASE
  WHEN condition1 THEN stmt1
  WHEN condition2 THEN stmt2
  ELSE stmt_default
END CASE;

迴圈控制

WHILE 迴圈

[label:] WHILE condition DO
  statements
END WHILE [label];

LOOP 迴圈（需搭配 LEAVE 跳出）

[label:] LOOP
  statements
  IF exit_condition THEN LEAVE label; END IF;
END LOOP [label];

REPEAT UNTIL 迴圈

[label:] REPEAT
  statements
UNTIL condition
END REPEAT [label];

完整範例：帶參數、條件、迴圈、輸出

DELIMITER //
CREATE PROCEDURE proc_calc_factorial(IN n INT, OUT result BIGINT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE prod BIGINT DEFAULT 1;
  
  IF n < 0 THEN
    SET result = -1;  -- 錯誤碼
  ELSE
    calc_loop: WHILE i <= n DO
      SET prod = prod * i;
      SET i = i + 1;
    END WHILE calc_loop;
    SET result = prod;
  END IF;
END//
DELIMITER ;

-- 測試
CALL proc_calc_factorial(5, @res);
SELECT @res;  -- 120

常用管理指令

-- 列出所有程序基本資訊
SHOW PROCEDURE STATUS [LIKE 'pattern'];

-- 查看特定程序完整定義
SHOW CREATE PROCEDURE proc_name;

效能與最佳實踐提醒

• 儲存程序預先編譯、優化執行計畫，適合高頻、複雜的交易邏輯
• 避免在程序中進行大量單行處理（RBAR），改用集合操作
• 謹慎使用動態 SQL（PREPARE/EXECUTE），防止注入與計畫失效
• 定期 SHOW PROCEDURE STATUS 檢查創建時間、修改時間，配合版控管理