MySQL 存儲過程參數詳解：IN、OUT、INOUT 三類型行為差異與選型指南

參數類型對比表

實戰演示

IN：傳入只讀

CREATE PROCEDURE `demo_in`(IN p_id INT)
BEGIN
  IF p_id IS NOT NULL THEN SET p_id = p_id + 1; END IF;
  SELECT p_id AS inner_val;
END;

SET @id = 10;
CALL demo_in(@id);
SELECT @id AS outer_val;  -- 仍為 10

OUT：僅輸出，初始為 NULL

CREATE PROCEDURE `demo_out`(OUT p_id INT)
BEGIN
  SELECT p_id AS init_val;  -- NULL
  SET p_id = 1;
  SELECT p_id AS final_val; -- 1
END;

SET @id = 100;
CALL demo_out(@id);
SELECT @id AS outer_val;  -- 1

INOUT：雙向傳遞

CREATE PROCEDURE `demo_inout`(INOUT p_id INT)
BEGIN
  SELECT p_id AS init_val;   -- 10
  SET p_id = p_id + 1;
  SELECT p_id AS final_val;  -- 11
END;

SET @id = 10;
CALL demo_inout(@id);
SELECT @id AS outer_val;  -- 11

選型決策樹

1. **只需傳值給存儲過程，不需返回** → IN
2. **只需從存儲過程取回值，不需傳入** → OUT
3. **需傳入初始值，經計算後返回新值** → INOUT

進階用法：動態結果集

CREATE PROCEDURE `get_stats`(
  IN  p_date_from DATE,
  IN  p_date_to   DATE,
  OUT p_total     BIGINT,
  OUT p_avg_amt   DECIMAL(10,2)
)
BEGIN
  SELECT COUNT(*), AVG(amount) 
  INTO p_total, p_avg_amt
  FROM orders
  WHERE created_at BETWEEN p_date_from AND p_date_to;
END;

CALL get_stats('2024-01-01', '2024-01-31', @total, @avg);
SELECT @total, @avg;

注意事項

• ⚠️ OUT/INOUT 參數**必須使用用戶變量（@var）接收**，不能直接用字面值：CALL proc(10); -- 錯誤
• ⚠️ 存儲過程內對 IN 參數的修改**不會回傳**，若需雙向請用 INOUT。
• ⚠️ OUT 參數在過程開始時**強制置為 NULL**，調用者原值被忽略。
• ✅ 8.0+ 支援 DEFAULT 值：CREATE PROCEDURE p(IN x INT DEFAULT 0)