Java CallableStatement 呼叫 Oracle 預存程序完整實戰

前言

JDBC CallableStatement 是呼叫 Oracle 預存程序的標準介面。本文涵蓋三種常見場景：無回傳值、有 OUT 參數、回傳結果集（REF CURSOR），並提供完整可執行的 Java 範例碼與資料庫端 DDL。

資料庫前置準備

-- 建立測試表
CREATE TABLE emp (
    id     VARCHAR2(10) PRIMARY KEY,
    name   VARCHAR2(50)
);

-- 建立套件規格（供 REF CURSOR 使用）
CREATE OR REPLACE PACKAGE my_pkg AS
    TYPE ref_cursor IS REF CURSOR;
END my_pkg;
/

場景一：無回傳值的預存程序（純 DML）

Oracle 端

CREATE OR REPLACE PROCEDURE proc_insert_emp (
    p_id   IN VARCHAR2,
    p_name IN VARCHAR2
) AS
BEGIN
    INSERT INTO emp (id, name) VALUES (p_id, p_name);
    -- 若需自動提交，可在此加 COMMIT；通常由呼叫端控制交易
END proc_insert_emp;
/

Java 端

package com.example.jdbc;

import java.sql.*;

public class CallProcNoReturn {
    private static final String URL      = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER     = "admin";
    private static final String PASSWORD = "password";
    private static final String DRIVER   = "oracle.jdbc.OracleDriver";

    public static void main(String[] args) {
        String sql = "{ call proc_insert_emp(?, ?) }";
        
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             CallableStatement cs = conn.prepareCall(sql)) {
            
            Class.forName(DRIVER);
            
            cs.setString(1, "1001");
            cs.setString(2, "Peter Chen");
            cs.execute();
            
            System.out.println("執行完成，已新增一筆員工資料。");
            
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

關鍵點： 使用 try-with-resources 自動關閉資源；execute() 適用於無結果集的 DDL/DML。

場景二：有 OUT 參數（單值回傳）

Oracle 端

CREATE OR REPLACE PROCEDURE proc_get_emp_name (
    p_id      IN  VARCHAR2,
    p_name    OUT VARCHAR2
) AS
BEGIN
    SELECT name INTO p_name FROM emp WHERE id = p_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name := 'NOT_FOUND';
END proc_get_emp_name;
/

Java 端

public class CallProcWithOutParam {
    // ... 連線設定同上 ...
    
    public static void main(String[] args) {
        String sql = "{ call proc_get_emp_name(?, ?) }";
        
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             CallableStatement cs = conn.prepareCall(sql)) {
            
            cs.setString(1, "1001");
            cs.registerOutParameter(2, Types.VARCHAR); // 註冊 OUT 參數型別
            cs.execute();
            
            String name = cs.getString(2); // 依參數位置取值（第 2 個參數）
            System.out.println("員工姓名：" + name);
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

注意： registerOutParameter 的索引對應預存程序參數宣告順序（從 1 開始），而非 SQL 呼叫語法中的佔位符順序。

場景三：回傳結果集（REF CURSOR）

Oracle 端

CREATE OR REPLACE PROCEDURE proc_query_emp_all (
    p_cursor OUT my_pkg.ref_cursor
) AS
BEGIN
    OPEN p_cursor FOR SELECT id, name FROM emp ORDER BY id;
END proc_query_emp_all;
/

Java 端

import oracle.jdbc.OracleTypes; // 需引用 ojdbc.jar

public class CallProcReturnResultSet {
    // ... 連線設定同上 ...
    
    public static void main(String[] args) {
        String sql = "{ call proc_query_emp_all(?) }";
        
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             CallableStatement cs = conn.prepareCall(sql)) {
            
            cs.registerOutParameter(1, OracleTypes.CURSOR); // 關鍵：指定 CURSOR 型別
            cs.execute();
            
            try (ResultSet rs = (ResultSet) cs.getObject(1)) {
                while (rs.next()) {
                    System.out.printf("ID: %-10s  Name: %s%n", 
                        rs.getString("id"), rs.getString("name"));
                }
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

關鍵差異：

• 必須 import oracle.jdbc.OracleTypes 並使用 OracleTypes.CURSOR。
• 取得 ResultSet 時需轉型：(ResultSet) cs.getObject(paramIndex)。
• ResultSet 應在獨立 try-with-resources 區塊中關閉，避免游標洩漏。

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

CREATE OR REPLACE PROCEDURE proc_multi_result (
    p_dept_id    IN  NUMBER,
    p_emp_count  OUT NUMBER,
    p_emp_cur    OUT SYS_REFCURSOR,
    p_dept_cur   OUT SYS_REFCURSOR
) AS
BEGIN
    SELECT COUNT(*) INTO p_emp_count FROM emp WHERE dept_id = p_dept_id;
    
    OPEN p_emp_cur FOR 
        SELECT id, name FROM emp WHERE dept_id = p_dept_id;
    
    OPEN p_dept_cur FOR 
        SELECT dept_id, dept_name FROM dept WHERE dept_id = p_dept_id;
END;
/

Java 端依序 registerOutParameter 每個 CURSOR 參數，執行後分別 getObject 取得多個 ResultSet。

最佳實務總結

1. 連線池：生產環境務必使用 HikariCP、Druid 等連線池，避免頻繁建立連線。
2. 交易控制：預存程序內避免 COMMIT/ROLLBACK，由 Java 端統一管理交易邊界。
3. 例外處理：Oracle 端使用 EXCEPTION 區塊捕捉錯誤，回傳錯誤代碼/訊息給 Java 端，而非拋出未處理例外。
4. 參數綁定：所有輸入值務必使用 setXxx 綁定，防止 SQL 注入。
5. 資源釋放：嚴格遵循 ResultSet → Statement → Connection 關閉順序，try-with-resources 可自動保證。

測試環境：Oracle 19c + JDK 11 + ojdbc11.jar。完整專案結構請參考 GitHub 範例倉庫。