Oracle 存儲過程返回游標 (REF CURSOR) 給 Java 完整範例

Oracle 透過 SYS_REFCURSOR 或自訂 REF CURSOR 型別，讓存儲過程返回結果集供 Java (JDBC) 消費。本文示範 Package 定義、Package Body 實作與 Java 呼叫全流程。

1. 定義 Package Spec

CREATE OR REPLACE PACKAGE TEST_PKG IS
  TYPE cur_emp IS REF CURSOR;  -- 公共游標型別
  PROCEDURE test_proc (v_empno IN NUMBER, emps OUT cur_emp);
END TEST_PKG;
/

2. 實作 Package Body

CREATE OR REPLACE PACKAGE BODY TEST_PKG IS
  PROCEDURE test_proc (v_empno IN NUMBER, emps OUT cur_emp) IS
  BEGIN
    OPEN emps FOR SELECT * FROM emp WHERE empno = 7369;
  END test_proc;
END TEST_PKG;
/

3. Java JDBC 呼叫

CallableStatement cstmt = conn.prepareCall("{call TEST_PKG.test_proc(?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);  // 需 import oracle.jdbc.OracleTypes
cstmt.setInt(1, 7369);  -- 此處索引 1 對應 v_empno，OUT 參數索引 2
cstmt.execute();

ResultSet rs = (ResultSet) cstmt.getObject(2);  // 取得 OUT 游標
while (rs.next()) {
  System.out.println(rs.getString("ename") + " " + rs.getString("job"));
}
rs.close();
cstmt.close();

關鍵細節

• Package Spec 必須宣告 REF CURSOR 型別，否則 Java 無法對應 OracleTypes.CURSOR。
• registerOutParameter 索引對應呼叫語法中 ? 的順序：範例中 {call TEST_PKG.test_proc(?)} 只有一個 ?，實際對應 v_empno (IN) 與 emps (OUT) 兩個參數，JDBC 驅動自動映射；建議顯式寫 {call TEST_PKG.test_proc(?, ?)} 並註冊兩個參數。
• 游標結果集屬於呼叫端，務必在處理完後 rs.close() 釋放資源。
• 若使用 SYS_REFCURSOR (9i+ 內建弱型別)，Package Spec 可省略自訂型別，直接 PROCEDURE test_proc (v_empno IN NUMBER, emps OUT SYS_REFCURSOR);。

REF CURSOR 是 Oracle 與 Java/.NET 等外部應用交換結果集的標準橋樑，掌握此模式即可構建高效能、可維護的資料存取層。