基本語法結構

CREATE PROCEDURE sp_name ([IN|OUT|INOUT] param_name type, ...)
[characteristics...]
BEGIN
    -- SQL 語句
END

characteristics：LANGUAGE SQL、DETERMINISTIC、SQL SECURITY {DEFINER|INVOKER}、COMMENT '...'。

分隔符變更 (關鍵步驟)

DELIMITER $$
CREATE PROCEDURE spTest(IN p_title VARCHAR(200), IN p_desc TEXT)
DETERMINISTIC
BEGIN
    INSERT INTO category (title, description) VALUES (p_title, p_desc);
END $$
DELIMITER ;

必須先將語句結束符從 ; 改為 $$ (或其他)，避免過程體內部的分號被誤判為結束。

參數模式詳解

模式	方向	說明
IN	輸入	預設，呼叫端傳入，過程內可讀不可寫回
OUT	輸出	過程內賦值，返回給呼叫端，初始為 NULL
INOUT	雙向	呼叫端傳入，過程內可修改並返回

動態 SQL 實戰：PREPARE + EXECUTE

CREATE PROCEDURE up_common_select(IN t_name VARCHAR(50))
BEGIN
    DECLARE v_sql VARCHAR(500);
    SET v_sql = CONCAT('SELECT * FROM ', t_name);

    -- 必須使用 @user_variable，DECLARE 區域變數無法直接用於 PREPARE
    SET @v_sql = v_sql;
    PREPARE stmt FROM @v_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

關鍵避坑指南 (MySQL 5.0.13+ 才支援存儲過程中 PREPARE)

1. 表名/欄位名不能用 ? 佔位符：PREPARE stmt FROM 'SELECT * FROM ?' ❌ → 用 CONCAT() 拼接字串 ✅。
2. EXECUTE 參數必須是 @user_variable：EXECUTE stmt USING @var1, @var2 ✅；區域變數 DECLARE v INT 或參數 IN p INT ❌。
3. 顯式 DEALLOCATE PREPARE：釋放預處理語句資源，避免洩漏 (會話結束自動釋放，但最好顯式)。
4. DETERMINISTIC：相同輸入必產生相同輸出，啟用後優化器可快取結果；若含 NOW()、RAND() 請勿標記。
5. SQL SECURITY DEFINER (預設)：以建立者權限執行；INVOKER：以呼叫者權限執行，影響權限檢查。

管理指令

-- 刪除
DROP PROCEDURE IF EXISTS sp_name;

-- 查看定義
SHOW CREATE PROCEDURE sp_name;

-- 呼叫
CALL sp_name(arg1, arg2, ...);

函數 (FUNCTION) 差異

• 必須有 RETURNS type。
• 體內必須有 RETURN value。
• 不可包含動態 SQL (PREPARE)。
• 可在 SQL 語句中直接呼叫：SELECT my_func(col) FROM t;