MySQL 預存程序完整指南：建立、參數模式、變數、流程控制、Java JDBC 呼叫與最佳實踐

MySQL 預存程序完整指南（增訂版）

系統整理預存程序全生命週期管理、IN/OUT/INOUT 參數語義、區域變數、流程控制、動態 SQL、游標、條件處理，並附 Java JDBC CallableStatement 三種參數模式完整可執行範例。

DDL 操作速查

參數模式深度解析

完整範例：員工管理

-- 表
CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(50), salary DECIMAL(10,2));

-- 1. OUT：取得總人數
DROP PROCEDURE IF EXISTS sp_emp_count;
CREATE PROCEDURE sp_emp_count(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employee;
END;

-- 2. IN：依 ID 查詢
DROP PROCEDURE IF EXISTS sp_emp_by_id;
CREATE PROCEDURE sp_emp_by_id(IN emp_id INT)
BEGIN
    SELECT * FROM employee WHERE id = emp_id;
END;

-- 3. INOUT：薪資調整並返回新薪資
DROP PROCEDURE IF EXISTS sp_emp_raise;
CREATE PROCEDURE sp_emp_raise(INOUT emp_id INT, INOUT new_salary DECIMAL(10,2))
BEGIN
    UPDATE employee SET salary = new_salary WHERE id = emp_id;
    SELECT salary INTO new_salary FROM employee WHERE id = emp_id;
END;

-- 4. 複雜邏輯：部門薪資統計 (使用區域變數、流程控制)
DROP PROCEDURE IF EXISTS sp_dept_salary_stats;
CREATE PROCEDURE sp_dept_salary_stats(IN dept_name VARCHAR(50), OUT avg_sal DECIMAL(10,2), OUT emp_cnt INT)
BEGIN
    DECLARE v_sum DECIMAL(15,2) DEFAULT 0;
    DECLARE v_max DECIMAL(10,2);
    DECLARE v_min DECIMAL(10,2);
    
    SELECT COUNT(*), AVG(salary), MAX(salary), MIN(salary)
    INTO emp_cnt, avg_sal, v_max, v_min
    FROM employee WHERE dept = dept_name;
    
    IF emp_cnt = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '部門不存在或無員工';
    END IF;
END;

定界符與客戶端建立

DELIMITER //

CREATE PROCEDURE demo()
BEGIN
    SELECT 'Hello';
END //

DELIMITER ;

Java JDBC CallableStatement 完整範例

import java.sql.*;

public class SpDemo {
    static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false";
    static final String USER = "root", PASS = "123456";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            // 1. IN 參數
            try (CallableStatement cs = conn.prepareCall("{CALL sp_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. OUT 參數
            try (CallableStatement cs = conn.prepareCall("{CALL sp_emp_count(?)}")) {
                cs.registerOutParameter(1, Types.INTEGER);
                cs.execute();
                System.out.println("總人數：" + cs.getInt(1));
            }

            // 3. INOUT 參數
            try (CallableStatement cs = conn.prepareCall("{CALL sp_emp_raise(?, ?)}")) {
                cs.setInt(1, 1);
                cs.setBigDecimal(2, new java.math.BigDecimal("60000.00"));
                cs.registerOutParameter(1, Types.INTEGER);
                cs.registerOutParameter(2, Types.DECIMAL);
                cs.execute();
                System.out.println("ID:" + cs.getInt(1) + " 新薪資:" + cs.getBigDecimal(2));
            }

            // 4. 多 OUT 參數
            try (CallableStatement cs = conn.prepareCall("{CALL sp_dept_salary_stats(?, ?, ?)}")) {
                cs.setString(1, "RD");
                cs.registerOutParameter(2, Types.DECIMAL);
                cs.registerOutParameter(3, Types.INTEGER);
                cs.execute();
                System.out.println("RD 部門平均薪資:" + cs.getBigDecimal(2) + " 人數:" + cs.getInt(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

進階特性

• 動態 SQL：PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
• 游標：DECLARE cur CURSOR FOR SELECT ...; OPEN cur; FETCH cur INTO ...; CLOSE cur;
• 條件處理：DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @err=1;
• SIGNAL/RESIGNAL：主動拋出業務異常

最佳實踐與避坑

1. 刪除不帶括號，建立/呼叫必帶括號
2. 參數名避開資料欄位名，防歧義
3. 業務邏輯複雜時優先用應用層，預存程序用於資料密集型操作
4. MySQL 預存程序無法返回多結果集給 JDBC (5.0+ 支援有限)
5. 定期 SHOW PROCEDURE STATUS 清理廢棄程序
6. 效能敏感路徑用 EXPLAIN CALL ... (8.0+) 分析