MySQL 5.0 存儲過程進階教程（三）：變量、條件判斷、循環控制、標號與 ITERATE

一、變量：DECLARE 與作用域

CREATE PROCEDURE p10 ()
BEGIN
    DECLARE a, b INT DEFAULT 5;  -- 宣告並初始化
    INSERT INTO t VALUES (a);
    SELECT s1 * a FROM t WHERE s1 >= b;
END;

• 區塊內變量非會話變量，無 @ 符號，需顯式 `DECLARE` 型別
• 無 DEFAULT 則初值為 NULL
• 作用域限於 `BEGIN/END`，巢狀區塊可同名遮蔽外層

作用域演示

CREATE PROCEDURE p11 ()
BEGIN
    DECLARE x1 CHAR(5) DEFAULT 'outer';
    BEGIN
        DECLARE x1 CHAR(5) DEFAULT 'inner';
        SELECT x1;  -- inner
    END;
    SELECT x1;      -- outer
END;

二、條件控制：IF / CASE

IF-THEN-ELSE

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

CASE 多支分支

CREATE PROCEDURE p13 (IN p1 INT)
BEGIN
    DECLARE v1 INT;
    SET v1 = p1 + 1;
    CASE v1
        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;

呼叫 `CALL p13(NULL)` 時 `v1=NULL`，走 ELSE 分支插入 19。

三、循環三兄弟：WHILE / REPEAT / LOOP

WHILE 範例

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

REPEAT 範例 (注意 UNTIL 無分號)

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

LOOP + LEAVE/ITERATE 範例

CREATE PROCEDURE p16 ()
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;

四、標號：結構化跳轉

• 可標註 `BEGIN`、`WHILE`、`REPEAT`、`LOOP`
• `LEAVE label`：跳出指定標號區塊
• `ITERATE label`：重新開始指定標號循環 (類 C `continue`)
• 結束標號可選：`END WHILE label`，建議加上提高可讀性

巢狀 LEAVE 範例

label_1: BEGIN
    label_2: BEGIN
        label_3: BEGIN
            IF p = 'a' THEN LEAVE label_1;  -- 直接跳出最外層
            ELSEIF p = 'b' THEN LEAVE label_2;
            ELSE LEAVE label_3; END IF;
        END label_3;
    END label_2;
END label_1;

ITERATE 實戰：跳過特定值

CREATE PROCEDURE p20 ()
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;

結果插入 0,1,2,4 (跳過 3)。

五、GOTO (不推薦)

語法：`LABEL label_name; ... GOTO label_name;` 非標準 SQL，未來可能移除，僅為相容保留。

六、大綜合範例 (含所有流程控制)

CREATE PROCEDURE p21 (IN p1 INT, OUT p2 INT)
LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER
BEGIN
    DECLARE v INT;
    label_goto: LABEL goto_label;
    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;

語法合法但含無窮迴圈，僅供語法參考。