MySQL 5.0 預存程序教學第三講：變量、流程控制與循環構建完整指南

前言：從 SQL 集合操作走向程式化邏輯

MySQL 5.0 引入預存程序，支援變量宣告、條件判斷、循環控制，讓資料庫層能處理複雜業務邏輯。本講系統梳理核心語法與實戰模式。

變量宣告與作用域

DECLARE 語法

DECLARE var_name [, var_name] ... type [DEFAULT value];

• 僅能在 BEGIN ... END 複合語句區塊中使用
• **非會話變量** (無 @ 前綴)，型別必須顯式宣告
• 無 DEFAULT 時初始值為 NULL
• 可隨時用 SET var = expr 賦值

三種宣告風格對比

-- 風格 1：分行宣告 + 後設值
DECLARE a INT;
DECLARE b INT;
SET a = 5; SET b = 5;

-- 風格 2：合併宣告 + DEFAULT
DECLARE a, b INT DEFAULT 5;

-- 風格 3：混合 (不推薦，降低可讀性)
DECLARE a INT DEFAULT 5;
DECLARE b INT;
SET b = 5;

建議：風格 2 最簡潔，風格 1 適合複雜初始化邏輯。

作用域與巢狀區塊

CREATE PROCEDURE p_scope()
BEGIN
  DECLARE x1 CHAR(5) DEFAULT 'outer';
  BEGIN
    DECLARE x1 CHAR(5) DEFAULT 'inner';
    SELECT x1;  -- 'inner' (內層遮蔽外層)
  END;
  SELECT x1;  -- 'outer' (內層變量已銷毀)
END;

• 巢狀 BEGIN ... END 形成新作用域
• 同名變量內層優先，END 時內層變量銷毀
• 需跨區塊傳值：用 OUT 參數或賦值給會話變量 (@var)

條件控制：IF 與 CASE

IF ... THEN ... ELSE ... END IF

CREATE PROCEDURE p_if(IN param1 INT)
BEGIN
  DECLARE var1 INT;
  SET var1 = param1 + 1;
  
  IF var1 = 0 THEN
    INSERT INTO t VALUES (17);
  END IF;
  
  IF param1 = 0 THEN
    UPDATE t SET s1 = s1 + 1;
  ELSE
    UPDATE t SET s1 = s1 + 2;
  END IF;
END;

執行流程追蹤 (呼叫 p_if(0))：

1. var1 = 1 → 第一個 IF 條件假 → 跳過
2. param1 = 0 → 第二個 IF 條件真 → 執行 s1 = s1 + 1

CASE ... WHEN ... THEN ... ELSE ... END CASE

CREATE PROCEDURE p_case(IN param1 INT)
BEGIN
  DECLARE var1 INT;
  SET var1 = param1 + 1;
  
  CASE var1
    WHEN 0 THEN INSERT INTO t VALUES (17);
    WHEN 1 THEN INSERT INTO t VALUES (18);
    ELSE INSERT INTO t VALUES (19);
  END CASE;
END;

• CASE 適合「單一運算式對多值」分支，比多重 IF 更清晰
• NULL 值不匹配任何 WHEN，走 ELSE 分支

思考題：CALL p_case(NULL) 結果？

var1 = NULL + 1 = NULL → 無 WHEN 匹配 → 執行 ELSE → 插入 19。實測確認表新增一行值為 19。

循環構建：三種標準 + 一種非標準

1. WHILE ... END WHILE (前測試)

CREATE PROCEDURE p_while()
BEGIN
  DECLARE v INT DEFAULT 0;
  WHILE v < 5 DO
    INSERT INTO t VALUES (v);
    SET v = v + 1;
  END WHILE;
END;

• 條件在迴圈**前**檢查
• 必須初始化變量，否則 NULL 參與運算導致無限循環
• 呼叫後表新增 0,1,2,3,4 五行

2. REPEAT ... UNTIL ... END REPEAT (後測試)

CREATE PROCEDURE p_repeat()
BEGIN
  DECLARE v INT DEFAULT 0;
  REPEAT
    INSERT INTO t VALUES (v);
    SET v = v + 1;
  UNTIL v >= 5
  END REPEAT;
END;

• 條件在迴圈**後**檢查，**至少執行一次**
• UNTIL 後**無分號** (可加但非標準)
• 功能等同 DO ... WHILE

3. LOOP ... END LOOP (無內建條件，配合 LEAVE)

CREATE PROCEDURE p_loop()
BEGIN
  DECLARE v INT DEFAULT 0;
  loop_label: LOOP
    INSERT INTO t VALUES (v);
    SET v = v + 1;
    IF v >= 5 THEN
      LEAVE loop_label;
    END IF;
  END LOOP;
END;

• 最靈活，適合複雜中斷邏輯
• 標籤 (Label)：label_name: 前綴，LEAVE label_name 跳出
• 標籤可用於 BEGIN/WHILE/REPEAT/LOOP 前

4. GOTO (非標準、不推薦、未來將移除)

CREATE PROCEDURE p_goto()
BEGIN
  DECLARE v INT DEFAULT 0;
  my_label: LOOP
    IF v = 3 THEN
      GOTO my_label;  -- 死循環示範
    END IF;
    SET v = v + 1;
  END LOOP;
END;

警告：破壞結構化程式設計，難以維護，僅為相容性保留，**嚴禁生產代碼使用**。

進階流程控制：ITERATE (相當於 continue)

CREATE PROCEDURE p_iterate()
BEGIN
  DECLARE v INT DEFAULT 0;
  loop_label: LOOP
    IF v = 3 THEN
      SET v = v + 1;
      ITERATE loop_label;  -- 跳過本輪剩餘，直接下一輪
    END IF;
    INSERT INTO t VALUES (v);
    SET v = v + 1;
    IF v >= 5 THEN
      LEAVE loop_label;
    END IF;
  END LOOP;
END;

執行追蹤：

1. v=0,1,2 → 正常插入 0,1,2
2. v=3 → IF 真 → v=4 → ITERATE → **跳過插入 3**、直接下一輪
3. v=4 → 插入 4 → v=5 → LEAVE 結束
4. 結果：表新增 0,1,2,4 (缺 3)

標籤完整規範

• 格式：label_name: 置於 BEGIN/WHILE/REPEAT/LOOP **前**
• 結束標籤 (可選)：END WHILE label_name、END REPEAT label_name、END LOOP label_name、END label_name — 必須與開始標籤同名，建議加上提升可讀性
• LEAVE label：跳出指定標籤區塊
• ITERATE label：重新開始指定標籤迴圈

綜合範例：語法大雜燴 (僅展示合法性，勿執行)

CREATE PROCEDURE p_all(IN p1 INT, OUT p2 INT)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN
  DECLARE v INT;
  DECLARE goto_label LABEL;  -- 標籤宣告 (GOTO 用)
  
  start_label: LOOP
    IF v = v THEN LEAVE start_label; ELSE ITERATE start_label; END IF;
  END LOOP start_label;
  
  REPEAT
    WHILE 1 = 0 DO BEGIN END; END WHILE;
  UNTIL v = v END REPEAT;
  
  GOTO goto_label;
END;

包含：參數模式 (IN/OUT/INOUT)、特性子句 (LANGUAGE/DETERMINISTIC/SQL SECURITY)、變量宣告、標籤、IF/WHILE/LOOP/REPEAT/LEAVE/ITERATE/GOTO — **語法合法但邏輯無意義**，展示語法完整性。

最佳實踐總結

效能與維護提醒

• 預存程序在資料庫層執行，**減少網路往返**，適合批次處理、複雜事務
• 但**除錯困難**、版本控管弱、業務邏輯分散 (應用層 + DB 層)
• 建議：**核心業務規則留應用層**，僅將**高頻、資料密集、原子性強**的邏輯下沉預存程序
• 使用 DETERMINISTIC、READS SQL DATA/MODIFIES SQL DATA 正確宣告特性，助優化器決策

下一講預告

游標 (CURSOR)、條件處理 (DECLARE ... HANDLER)、動態 SQL (PREPARE/EXECUTE)、觸發器與事件排程器 — 解鎖預存程序完整威力。