MySQL 儲存程序 IN/OUT 參數完整教學：從建立到呼叫的命令列實戰

儲存程序參數模式

命令列建立儲存程序關鍵：DELIMITER

mysql> DELIMITER //  -- 將語句結束符改為 //，避免程序體內 ; 衝突
-- 建立程序...
mysql> DELIMITER ;  -- 恢復預設

案例一：IN 參數 - 依部門統計員工數

測試資料

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(20),
    dept_id INT,
    salary DECIMAL(10,2)
);

INSERT INTO employee VALUES 
(103, 'Jack', 1, 1400),
(104, 'John', 2, 1450),
(108, 'Alan', 3, 1150),
(107, 'Ram', NULL, 600);

建立程序

DELIMITER //
CREATE PROCEDURE `usp_totalEmployeeByDepartment`(IN dept_id_param INT)
BEGIN
    SELECT COUNT(*) AS total 
    FROM employee 
    WHERE dept_id = dept_id_param;
END//
DELIMITER ;

呼叫驗證

mysql> CALL usp_totalEmployeeByDepartment(2);
+-------+
| total |
+-------+
| 1     |
+-------+

案例二：IN + OUT 參數 - 依 ID 查詢員工姓名

建立程序

DELIMITER //
CREATE PROCEDURE `usp_GetEmployeeName`(IN emp_id_param INT, OUT emp_name_param VARCHAR(20))
BEGIN
    SELECT emp_name INTO emp_name_param 
    FROM employee 
    WHERE emp_id = emp_id_param;
END//
DELIMITER ;

呼叫與取得結果

mysql> CALL usp_GetEmployeeName(103, @name);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT @name;
+-------+
| @name |
+-------+
| Jack  |
+-------+

關鍵點

• OUT 參數必須用用戶變數（@var）接收
• SELECT ... INTO 將查詢結果賦值給 OUT 參數
• 若查詢無結果，OUT 參數為 NULL

案例三：INOUT 參數 - 累加器模式

DELIMITER //
CREATE PROCEDURE `usp_Accumulate`(INOUT counter INT, IN increment INT)
BEGIN
    SET counter = counter + increment;
END//
DELIMITER ;

-- 呼叫
SET @cnt = 10;
CALL usp_Accumulate(@cnt, 5);  -- @cnt 變為 15
CALL usp_Accumulate(@cnt, 3);  -- @cnt 變為 18
SELECT @cnt;  -- 18

進階：多重 OUT 參數與結果集

DELIMITER //
CREATE PROCEDURE `usp_EmployeeStats`(IN dept_id INT, 
    OUT total_cnt INT, OUT avg_sal DECIMAL(10,2), OUT max_sal DECIMAL(10,2))
BEGIN
    SELECT COUNT(*), AVG(salary), MAX(salary)
    INTO total_cnt, avg_sal, max_sal
    FROM employee
    WHERE dept_id = dept_id;
END//
DELIMITER ;

CALL usp_EmployeeStats(1, @cnt, @avg, @max);
SELECT @cnt, @avg, @max;

管理指令速查

常見錯誤與避雷

1. 忘記 DELIMITER：程序體內 ; 導致建立失敗
2. OUT 參數用一般變數：必須用 @user_var
3. 資料型別不匹配：OUT 參數型別需與 INTO 目標相容
4. 權限不足：需 CREATE ROUTINE、EXECUTE 權限
5. 隱性提交：程序內含 DDL 會導致事務提交

應用層呼叫範例

PHP (mysqli)

$mysqli->query("CALL usp_GetEmployeeName(103, @name)");
$result = $mysqli->query("SELECT @name AS emp_name");
$row = $result->fetch_assoc();
echo $row['emp_name'];  // Jack

Java (JDBC)

CallableStatement cs = conn.prepareCall("{CALL usp_GetEmployeeName(?, ?)}");
cs.setInt(1, 103);
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String name = cs.getString(2);  // Jack

最佳實踐建議

• 命名慣例：usp_ (User Stored Procedure) 前綴區分系統程序
• 參數命名：_param 後綴區分欄位名
• 錯誤處理：程序內使用 DECLARE ... HANDLER 捕獲異常
• 文件化：COMMENT 子句說明用途、參數、返回值
• 版本控制：程式碼納入 Git，避免直接在線上修改

1. 上一篇文章： PostgreSQL 遞歸查詢構建 Disqus 風格評論樹：MySQL 無法比擬的階層查詢能力
2. 下一篇文章： VB.NET 連接 MySQL 完整教學：Connector/NET 安裝、參考添加與資料操作封裝

MySQL 資料庫相關知識

MySQL 入門系列：客戶端程式 4 — 執行期動態取得連線參數

從硬編碼到執行期參數 客戶端程式 3 雖具模組化優勢，但連線

MySQL 版本區分全解析：Community、Enterprise、Cluster 與 Essential 差異

官網五大下載類別 版本授權特點 MySQL Communi

SQL 刪除資料表資料三大指令差異：DROP vs TRUNCATE vs DELETE 完整對照

三個指令皆可「移除資料」但行為、效能、可回復性截然不同。選錯

MySQL 匯入大型 SQL 腳本報錯 2006 解決方案：max_allowed_packet 與超時調優

錯誤現象 ERROR 2006 (HY000): MySQL