MySQL 儲存過程入門完全指南：建立、呼叫、參數模式、常見坑點

為什麼用儲存過程？

• 將業務邏輯封裝在資料庫層，減少應用程式與 DB 往返。
• 複雜邏輯集中維護，避免 PHP/Java 程式碼混雜大量 SQL。
• 執行計畫快取，減少解析優化開銷。
• 權限控制：應用帳號只需 EXECUTE 權限，不需直接操作表。

一、建立基本語法

CREATE PROCEDURE `proc_name` (
    [IN | OUT | INOUT] param_name data_type,
    ...
)
BEGIN
    DECLARE var_name data_type [DEFAULT value];
    -- SQL 陳述式，每行結尾加分號
    SELECT ...;
    SET var = ...;
    IF ... THEN ... END IF;
    WHILE ... DO ... END WHILE;
END;

二、範例：兩數相加（IN 參數、回傳結果集）

DROP PROCEDURE IF EXISTS pr_add;

CREATE PROCEDURE pr_add (
    IN a INT,
    IN b INT
)
BEGIN
    DECLARE c INT;
    IF a IS NULL THEN SET a = 0; END IF;
    IF b IS NULL THEN SET b = 0; END IF;
    SET c = a + b;
    SELECT c AS sum;   -- 以結果集回傳
    -- RETURN c;  -- 錯誤：儲存過程不能用 RETURN，只有 FUNCTION 可用
END;

三、呼叫方式

-- 直接傳值
CALL pr_add(10, 20);

-- 使用用戶變數
SET @a = 10, @b = 20;
CALL pr_add(@a, @b);

四、參數模式詳解

OUT 參數範例

CREATE PROCEDURE get_count (OUT cnt INT)
BEGIN
    SELECT COUNT(*) INTO cnt FROM users;
END;

CALL get_count(@total);
SELECT @total;  -- 顯示結果

五、MySQL 儲存過程特有規則（與 SQL Server 差異）

1. 參數名**不加 @**（@a INT 錯誤，a INT 正確）。
2. 不支援參數預設值。
3. 不需 AS 關鍵字（SQL Server 需）。
4. 多行陳述式必須用 BEGIN ... END 包裹。
5. 每句結尾**必須加分號**。
6. 註釋：-- 內容（後須空格）、# 內容、/* ... */。
7. 不可用 RETURN 回傳值，改用 SELECT ... 或 OUT 參數。
8. 呼叫必須加括號 CALL proc()，即使無參數。
9. 無預設值 → 呼叫不可省略參數，需傳 NULL 佔位。

六、進階：條件判斷、迴圈、錯誤處理

CREATE PROCEDURE process_orders ()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE oid INT;
    DECLARE cur CURSOR FOR SELECT id FROM orders WHERE status='pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO oid;
        IF done THEN LEAVE read_loop; END IF;
        UPDATE orders SET status='processing' WHERE id=oid;
        -- 業務邏輯...
    END LOOP;
    CLOSE cur;
END;

七、管理指令

SHOW PROCEDURE STATUS WHERE Db='mydb';
SHOW CREATE PROCEDURE pr_add;
DROP PROCEDURE IF EXISTS pr_add;