您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 預存程序入門：IN/OUT 參數實戰與呼叫技巧

編輯：MySQL 綜合教學

MySQL 預存程序入門：IN/OUT 參數實戰與呼叫技巧

預存程序能將業務邏輯下沉至資料庫層，減少網路往返、利用伺服器端運算資源。本文以 employee 表為例，示範建立含 IN、OUT 參數的程序，並說明分隔符、呼叫方式與常見陷阱。

範例資料

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50),
  dept_id INT,
  salary DECIMAL(10,2)
) ENGINE=InnoDB;

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

1. 僅 IN 參數：依部門統計人數

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

-- 呼叫
CALL usp_totalEmployeeByDepartment(2);  -- 回傳 total=1

重點：DELIMITER // 暫時將陳述式結束符改為 //，避免程序內部 ; 被誤判為結束；結束時用 END // 並還原 DELIMITER ;。

2. IN + OUT 參數：依 ID 取得員工姓名

DELIMITER //
CREATE PROCEDURE usp_GetEmployeeName(
  IN p_emp_id INT,
  OUT p_emp_name VARCHAR(50)
)
BEGIN
  SELECT emp_name INTO p_emp_name FROM employee WHERE emp_id = p_emp_id;
END //
DELIMITER ;

-- 呼叫（需用戶變數接收 OUT）
CALL usp_GetEmployeeName(103, @name);
SELECT @name;  -- 回傳 'Jack'

關鍵：OUT 參數必須用 @變數名 接收，且 SELECT ... INTO 變數 只能回傳單列單欄，否則報錯 1172。

3. 進階：INOUT 雙向參數（薪資調整並回傳新值）

DELIMITER //
CREATE PROCEDURE usp_AdjustSalary(
  INOUT p_salary DECIMAL(10,2),
  IN p_pct DECIMAL(5,2)
)
BEGIN
  SET p_salary = p_salary * (1 + p_pct/100);
END //
DELIMITER ;

SET @s = 5000;
CALL usp_AdjustSalary(@s, 10);  -- 漲 10%
SELECT @s;  -- 5500.00

4. 從應用程式呼叫

Python (mysql-connector-python)

import mysql.connector
cnx = mysql.connector.connect(user='dev', password='****', database='test')
cursor = cnx.cursor()

# IN + OUT
cursor.callproc('usp_GetEmployeeName', (103, 0))  # 第二參數佔位
for result in cursor.stored_results():
    print(result.fetchall())  # [('Jack',)]
# OUT 參數值在 cursor.stored_results() 之外，需額外查詢
cursor.execute("SELECT @_usp_GetEmployeeName_1")
print(cursor.fetchone())  # ('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"

5. 常見錯誤與避坑

1064 語法錯誤：忘記 DELIMITER 變更，或 END 後未加自訂分隔符。
1327 / 1329：SELECT ... INTO 回傳 0 列或多列，務必加 LIMIT 1 或確保唯一鍵。
權限不足：呼叫者需具備 EXECUTE 權限：GRANT EXECUTE ON PROCEDURE test.usp_GetEmployeeName TO 'app'@'%';
遞迴深度：預設 max_sp_recursion_depth=0 禁止遞迴，需設為 >0 並小心終止條件。

6. 管理指令速查

-- 查看定義
SHOW CREATE PROCEDURE usp_GetEmployeeName;

-- 列出所有程序
SHOW PROCEDURE STATUS WHERE Db='test';

-- 刪除
DROP PROCEDURE IF EXISTS usp_GetEmployeeName;

結語：善用 IN/OUT/INOUT 參數，預存程序能封裝複雜業務邏輯、減少應用層資料傳輸。建議：核心高頻邏輯下沉，邊緣顯示邏輯留應用層；配合版本控管（將 CREATE PROCEDURE SQL 檔納入 Git），做到可追蹤、可回滾。