MySQL 預存程序完整指南
預存程序將業務邏輯封裝在資料庫層，減少網路往返、提升安全性與維護性。本文系統整理建立/修改/刪除/查看語法、IN/OUT/INOUT 參數模式、變數宣告、流程控制、動態 SQL，並附 Java JDBC 呼叫完整範例。
核心語法速查
| 操作 | 語法 |
|---|
| 建立 | CREATE PROCEDURE name([IN|OUT|INOUT] param type) BEGIN ... END |
| 修改 | ALTER PROCEDURE name ... |
| 刪除 | DROP PROCEDURE [IF EXISTS] name |
| 查看定義 | SHOW CREATE PROCEDURE name |
| 列出所有 | SHOW PROCEDURE STATUS |
| 呼叫 | CALL name([params]) |
參數模式詳解
| 模式 | 方向 | 初始值 | 用途 |
|---|
| IN | 呼叫者 → 程序 | 呼叫者提供 | 輸入參數，程序內修改不影響外部 |
| OUT | 程序 → 呼叫者 | NULL | 輸出參數，程序內賦值返回 |
| INOUT | 雙向 | 呼叫者提供 | 輸入並經處理後輸出 |
實戰範例
-- 1. 輸出參數：取得員工總數
DROP PROCEDURE IF EXISTS proc_emp_count;
CREATE PROCEDURE proc_emp_count(OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM employee;
END;
CALL proc_emp_count(@total);
SELECT @total;
-- 2. 輸入參數：依 ID 查詢員工
DROP PROCEDURE IF EXISTS proc_emp_by_id;
CREATE PROCEDURE proc_emp_by_id(IN emp_id INT)
BEGIN
SELECT * FROM employee WHERE id = emp_id;
END;
SET @id = 1;
CALL proc_emp_by_id(@id);
-- 或 CALL proc_emp_by_id(1);
-- 3. INOUT：傳入基數，返回平方
DROP PROCEDURE IF EXISTS proc_square;
CREATE PROCEDURE proc_square(INOUT num INT)
BEGIN
SET num = num * num;
END;
SET @n = 5;
CALL proc_square(@n);
SELECT @n; -- 25
變數與流程控制
-- 區域變數宣告（必須在 BEGIN 區塊開頭）
DECLARE v_count INT DEFAULT 0;
DECLARE v_name VARCHAR(50);
-- 賦值
SET v_count = 10;
SET v_name = 'Tom';
-- SELECT INTO
SELECT COUNT(*), MAX(name) INTO v_count, v_name FROM employee;
-- 條件判斷
IF v_count > 0 THEN
SELECT '有資料';
ELSEIF v_count = 0 THEN
SELECT '無資料';
ELSE
SELECT '異常';
END IF;
-- 迴圈
WHILE v_count < 10 DO
SET v_count = v_count + 1;
END WHILE;
-- 游標（略）
定界符變更（客戶端建立時必須）
DELIMITER //
CREATE PROCEDURE demo()
BEGIN
SELECT 1;
END //
DELIMITER ;
Java JDBC 呼叫完整範例
import java.sql.*;
public class StoredProcDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test?useSSL=false";
String user = "root", pass = "123456";
try (Connection conn = DriverManager.getConnection(url, user, pass)) {
// 1. 呼叫輸入參數程序
try (CallableStatement cs = conn.prepareCall("{CALL proc_emp_by_id(?)}")) {
cs.setInt(1, 1);
try (ResultSet rs = cs.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
}
}
}
// 2. 呼叫輸出參數程序
try (CallableStatement cs = conn.prepareCall("{CALL proc_emp_count(?)}")) {
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println("總員工數：" + cs.getInt(1));
}
// 3. 呼叫 INOUT 程序
try (CallableStatement cs = conn.prepareCall("{CALL proc_square(?)}")) {
cs.setInt(1, 7);
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
System.out.println("平方：" + cs.getInt(1)); // 49
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
注意事項
- 刪除程序不帶括號：
DROP PROCEDURE name
- 建立/呼叫必須帶括號：
CREATE PROCEDURE name() / CALL name()
- 參數名避免與資料欄位同名，否則產生歧義
- MySQL 5.0+ 才支援預存程序，舊版本不可用
- 過度使用預存程序會增加資料庫 CPU 壓力，建議核心業務邏輯才用
