JDBC 取得 Oracle 預存程序動態結果集完整指南

大多數情況下不需要從 Oracle 預存程序傳回結果集，除非迫不得已。MS SQL Server 或 Sybase 只需在預存程序結尾寫 SELECT ... 即可傳回結果集，但 Oracle 必須使用 Cursor。

Oracle Cursor 與 REF CURSOR

PL/SQL 中 Cursor 用於傳回一或多行記錄。若要從預存程序傳回結果集，需使用 Cursor 變數，其型別為 REF CURSOR。定義方式：

TYPE ref_cursor IS REF CURSOR;

完整實作範例

1. 資料表結構

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

2. 定義套件與預存程序

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

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;
END;

3. SQL*Plus 測試

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

4. Java 呼叫代碼

public void openCursor() {
    Connection conn = null;
    ResultSet rs = null;
    CallableStatement stmt = null;
    String sql = "{? = call PKG_HOTLINE.getHotline()}";
    try {
        conn = getConnection();
        stmt = conn.prepareCall(sql);
        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 (Exception ex) {
        ex.printStackTrace();
    } finally {
        closeConnection(conn, rs, stmt);
    }
}

關鍵步驟：registerOutParameter(1, OracleTypes.CURSOR) 註冊輸出參數為 Cursor 型別，執行後透過 getCursor(1) 取得 ResultSet。