Java JDBC 呼叫 Oracle 預存程序取得動態游標結果集完整指南

核心概念：REF CURSOR

Oracle PL/SQL 使用 Cursor 變數 返回多行結果集，其資料型別為 REF CURSOR。定義方式：

TYPE ref_cursor IS REF CURSOR;

完整範例：從建表到 Java 呼叫

1. 建立測試表

CREATE TABLE hotline (
  country VARCHAR2(50),
  pno     VARCHAR2(50)
);

2. 定義 Package Spec & Body

CREATE OR REPLACE PACKAGE PKG_HOTLINE IS
  TYPE HotlineCursorType IS REF CURSOR;
  FUNCTION getHotline RETURN HotlineCursorType;
END PKG_HOTLINE;
/

CREATE OR REPLACE PACKAGE BODY PKG_HOTLINE IS
  FUNCTION getHotline RETURN HotlineCursorType IS
    hotlineCursor HotlineCursorType;
  BEGIN
    OPEN hotlineCursor FOR SELECT * FROM hotline;
    RETURN hotlineCursor;
  END getHotline;
END PKG_HOTLINE;
/

3. SQL*Plus 測試

SQL> VAR rs REFCURSOR
SQL> EXEC :rs := PKG_HOTLINE.getHotline;
SQL> PRINT rs;

4. Java JDBC 程式碼 (JDK 1.4+ / ojdbc14/ojdbc6/ojdbc8)

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

public class RefCursorDemo {
    public void fetchHotline() {
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;
        final String sql = "{? = call PKG_HOTLINE.getHotline()}";

        try {
            conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@//host:1521/service", "user", "pwd");

            stmt = conn.prepareCall(sql);
            // 註冊 OUT 參數為 CURSOR 型別
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.execute();

            // 取得游標結果集
            rs = ((OracleCallableStatement) stmt).getCursor(1);

            while (rs.next()) {
                String country = rs.getString(1);
                String pno     = rs.getString(2);
                System.out.println("country: " + country + " | pno: " + pno);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeQuietly(rs);
            closeQuietly(stmt);
            closeQuietly(conn);
        }
    }

    private void closeQuietly(AutoCloseable c) {
        if (c != null) try { c.close(); } catch (Exception ignored) {}
    }
}

關鍵點總結

• CallableStatement SQL 語法：{? = call pkg.func()}，? 佔位對應 OUT REF CURSOR。
• registerOutParameter(idx, OracleTypes.CURSOR) 必須使用 OracleTypes.CURSOR 常數。
• 轉型為 OracleCallableStatement 呼叫 getCursor(int) 取得 ResultSet。
• 結果集屬於 隱性游標，不需在 PL/SQL 端手動 CLOSE，Java 端關閉 ResultSet 即釋放資源。
• 若需傳入參數，同樣用 setXxx() 綁定，OUT 參數位置不變。

常見坑點

1. 驅動程式版本與資料庫版本不匹配導致 ClassCastException。
2. 忘記關閉 ResultSet/Statement/Connection 造成游標洩漏，最終觸發 ORA-01000。
3. PL/SQL 回傳前未 OPEN 游標，或在邏輯分支中未覆蓋所有路徑。