MySQL 存儲過程完整指南：從創建到流程控制與內建函數大全

MySQL 存儲過程完整指南：從創建到流程控制與內建函數大全

存儲過程是預編譯的 SQL 程式碼集合，儲存在資料庫中，支援參數、變量、流程控制與錯誤處理，能提升效能、減少網路傳輸、封裝業務邏輯並強化安全性。本文涵蓋 MySQL 5.0+ 存儲過程的創建、參數模式、變量作用域、條件/迴圈控制、標籤與 ITERATE、常用字串/數學/日期函數，並附完整範例。

一、基礎操作

1.1 查詢現有存儲過程

SHOW PROCEDURE STATUS;
SELECT name FROM mysql.proc WHERE db = 'your_db' AND type = 'PROCEDURE';
SELECT routine_name FROM information_schema.routines WHERE routine_schema = 'your_db';

1.2 創建、調用、刪除

DELIMITER //
CREATE PROCEDURE proc1(OUT s INT)
BEGIN
    SELECT COUNT(*) INTO s FROM user;
END //
DELIMITER ;

CALL proc1(@cnt);
SELECT @cnt;

DROP PROCEDURE proc1;

關鍵點： DELIMITER // 暫時改分隔符，避免過程內分號被誤判；結束後 DELIMITER ; 還原。使用圖形工具（如 MySQL Workbench）可免改分隔符。

二、參數模式 IN / OUT / INOUT

2.1 IN 範例

DELIMITER //
CREATE PROCEDURE demo_in(IN p_in INT)
BEGIN
    SELECT p_in;
    SET p_in = 2;
    SELECT p_in;
END //
DELIMITER ;

SET @p_in = 1;
CALL demo_in(@p_in);  -- 過程內變 2，但 @p_in 仍為 1

2.2 OUT 範例

DELIMITER //
CREATE PROCEDURE demo_out(OUT p_out INT)
BEGIN
    SELECT p_out;  -- NULL
    SET p_out = 2;
    SELECT p_out;  -- 2
END //
DELIMITER ;

SET @p_out = 1;
CALL demo_out(@p_out);  -- @p_out 變為 2

2.3 INOUT 範例

DELIMITER //
CREATE PROCEDURE demo_inout(INOUT p_inout INT)
BEGIN
    SELECT p_inout;  -- 1
    SET p_inout = 2;
    SELECT p_inout;  -- 2
END //
DELIMITER ;

SET @p_inout = 1;
CALL demo_inout(@p_inout);  -- @p_inout 變為 2

三、變量與用戶變量

3.1 局部變量 DECLARE

DECLARE l_int INT UNSIGNED DEFAULT 4000000;
DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95;
DECLARE l_date DATE DEFAULT '1999-12-31';
DECLARE l_varchar VARCHAR(255) DEFAULT 'test';

3.2 賦值 SET

SET var = expr, var2 = expr2 ...;

3.3 用戶變量 @var（會話級、全域可見）

-- 客戶端使用
SELECT 'Hello' INTO @x;
SET @y = 'World';
SET @z = 1+2+3;

-- 存儲過程中使用
CREATE PROCEDURE GreetWorld()
SELECT CONCAT(@greeting, ' World');
SET @greeting = 'Hello';
CALL GreetWorld();  -- Hello World

-- 過程間傳遞
CREATE PROCEDURE p1() SET @last = 'p1';
CREATE PROCEDURE p2() SELECT CONCAT('Last: ', @last);
CALL p1(); CALL p2();  -- Last: p1

注意：濫用用戶變量會降低可讀性，優先用局部變量與參數。

四、註釋風格

• -- 單行註釋
• /* ... */ 多行註釋（C 風格）

五、流程控制語句

5.1 變量作用域

內層 `BEGIN ... END` 區塊可宣告同名變量遮蔽外層，離開區塊即銷毀，需透過 OUT 參數或用戶變量保留值。

5.2 條件判斷

IF-THEN-ELSE

IF var = 0 THEN
    INSERT INTO t VALUES(17);
END IF;

IF param = 0 THEN
    UPDATE t SET s1 = s1 + 1;
ELSE
    UPDATE t SET s1 = s1 + 2;
END IF;

CASE

CASE var
    WHEN 0 THEN INSERT INTO t VALUES(17);
    WHEN 1 THEN INSERT INTO t VALUES(18);
    ELSE INSERT INTO t VALUES(19);
END CASE;

5.3 迴圈

WHILE（先判斷再執行）

WHILE var < 6 DO
    INSERT INTO t VALUES(var);
    SET var = var + 1;
END WHILE;

REPEAT（先執行再判斷）

REPEAT
    INSERT INTO t VALUES(v);
    SET v = v + 1;
UNTIL v >= 5
END REPEAT;

LOOP + LEAVE（無預設條件，靠 LEAVE 跳出）

LOOP_LABEL: LOOP
    INSERT INTO t VALUES(v);
    SET v = v + 1;
    IF v >= 5 THEN LEAVE LOOP_LABEL; END IF;
END LOOP;

5.4 標籤與 ITERATE

標籤可用於 `BEGIN`、`WHILE`、`REPEAT`、`LOOP` 前，配合 `LEAVE` 跳出、`ITERATE` 重新開始該標籤區塊。

LOOP_LABEL: LOOP
    IF v = 3 THEN
        SET v = v + 1;
        ITERATE LOOP_LABEL;  -- 跳過下方 INSERT，直接下一輪
    END IF;
    INSERT INTO t VALUES(v);
    SET v = v + 1;
    IF v >= 5 THEN LEAVE LOOP_LABEL; END IF;
END LOOP;

六、常用內建函數速查

6.1 字串函數

注意： SUBSTRING('abcd', 0, 2) 回傳空串，SUBSTRING('abcd', 1, 2) 回傳 'ab'。

6.2 數學函數

ROUND(1.23) → 1（整數）；ROUND(1.567, 2) → 1.57（浮點）。

6.3 日期時間函數

七、管理與查看

• 查看定義： SHOW CREATE PROCEDURE db.proc_name;
• 修改： ALTER PROCEDURE proc_name ...（僅修改特性，不改邏輯）
• 刪除： DROP PROCEDURE proc_name;

八、最佳實踐建議

1. 業務邏輯複雜、多步驟、需原子性 → 存儲過程
2. 參數嚴格區分 IN/OUT/INOUT，避免副作用
3. 優先用局部變量，減少用戶變量
4. 大量資料操作用游標 + 批次提交，避免長事務鎖死
5. 生產環境謹慎使用動態 SQL（PREPARE/EXECUTE），防注入
6. 版本控制存儲過程腳本，納入 CI/CD 流程

掌握存儲過程，能將資料密集型邏輯下沉到資料庫層，大幅降低應用層複雜度與網路延遲，是高效能資料庫應用的關鍵技能。