Java 呼叫 Oracle 回傳游標（REF CURSOR）預存程序實作指南

Oracle 預存程序無直接回傳值機制，若需回傳結果集（列表），標準做法是使用 REF CURSOR（弱型別游標） 作為 OUT 參數。本文完整示範從資料庫端建立 Package 定義游標型別、建立預存程序開啟游標，到 Java 端註冊 OracleTypes.CURSOR、取得 ResultSet 並逐筆處理的全流程。

資料庫端：兩步驟建立游標回傳機制

步驟 1：建立 Package 定義 REF CURSOR 型別

CREATE OR REPLACE PACKAGE TESTPACKAGE AS
  TYPE Test_CURSOR IS REF CURSOR;  -- 弱型別游標，可對應任意 SELECT 結構
END TESTPACKAGE;

說明： Package 為游標型別提供命名空間，避免型別定義分散。

步驟 2：建立預存程序開啟游標

CREATE OR REPLACE PROCEDURE TESTC (
  p_CURSOR OUT TESTPACKAGE.Test_CURSOR
) IS
BEGIN
  OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
END TESTC;

關鍵機制： OPEN ... FOR SELECT ... 將查詢結果集綁定給 OUT 參數游標，資料庫端不實際抓取資料，由呼叫端決定何時 FETCH。

Java 端：取得 ResultSet 並處理資料

package com.hyq.src;

import java.sql.*;
import oracle.jdbc.OracleTypes;

public class TestProcedureThree {

    public static void main(String[] args) {
        String url  = "jdbc:oracle:thin:@127.0.0.1:1521:hyq";
        String user = "hyq";
        String pwd  = "hyq";

        try (Connection conn = DriverManager.getConnection(url, user, pwd);
             CallableStatement cstmt = conn.prepareCall("{call hyq.testc(?)}")) {

            // 1. 註冊 OUT 參數為 CURSOR 型別（索引 1）
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);

            // 2. 執行
            cstmt.execute();

            // 3. 取得 ResultSet（需轉型）
            try (ResultSet rs = (ResultSet) cstmt.getObject(1)) {
                while (rs.next()) {
                    String col1 = rs.getString(1);
                    String col2 = rs.getString(2);
                    System.out.printf("欄位1=%s, 欄位2=%s%n", col1, col2);
                }
            } // ResultSet 自動關閉

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

關鍵技術點深度解析

1. OracleTypes.CURSOR 與 getObject()

• registerOutParameter(idx, OracleTypes.CURSOR) 告知驅動程式該參數為游標。
• cstmt.getObject(idx) 返回 oracle.jdbc.driver.OracleResultSetImpl，需強制轉型為 java.sql.ResultSet。
• 必須匯入 oracle.jdbc.OracleTypes，需專用 ojdbc*.jar，標準 JDK java.sql.Types 無 CURSOR 常數。

2. 弱型別 vs 強型別 REF CURSOR

本文採用弱型別，Java 端需自行確認欄位順序與型別。

3. 游標生命週期與資源管理

• 游標在 Java 端 ResultSet 關閉時自動釋放資料庫端資源。
• 若呼叫端拋出異常未關閉 ResultSet，可能導致資料庫端游標洩漏，務必使用 try-with-resources。
• 大量資料建議配合 fetchSize 優化網路往返：rs.setFetchSize(500)。

4. 多重游標回傳

-- 程序定義兩個 OUT 游標
CREATE OR REPLACE PROCEDURE DUAL_CURSOR (
  P_CUR1 OUT TESTPACKAGE.Test_CURSOR,
  P_CUR2 OUT TESTPACKAGE.Test_CURSOR
) AS
BEGIN
  OPEN P_CUR1 FOR SELECT * FROM TABLE_A;
  OPEN P_CUR2 FOR SELECT * FROM TABLE_B;
END;

// Java 端依序註冊與取得
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
try (ResultSet rs1 = (ResultSet) cstmt.getObject(1);
     ResultSet rs2 = (ResultSet) cstmt.getObject(2)) {
    // 分別處理 rs1、rs2
}

部署注意事項

• 驅動程式版本：專案 Classpath 必須包含與資料庫版本相容的 ojdbc8.jar（JDK 8+）或 ojdbc11.jar（JDK 11+），否則 ClassNotFoundException 或 NoClassDefFoundError: oracle/jdbc/OracleTypes。
• 權限：呼叫用戶需有 EXECUTE ON TESTPACKAGE 及 EXECUTE ON TESTC 權限。
• 網路延遲：大量資料回傳時，考慮分頁或在程序端先過濾/聚合，減少網路傳輸。

掌握 REF CURSOR 機制，即可在 Java 應用中高效處理 Oracle 回傳的動態結果集，滿足報表查詢、資料匯出、批次處理等企業級場景需求。