Java (JDBC) 調用 Oracle 存儲過程返回 REF CURSOR 完整範例

資料庫端：Package 定義 REF CURSOR 類型與程序

CREATE OR REPLACE PACKAGE chapter_13 AS
  TYPE rs IS REF CURSOR;
  PROCEDURE founder(oFields OUT rs);
END chapter_13;
/

CREATE OR REPLACE PACKAGE BODY chapter_13 AS
  PROCEDURE founder(oFields OUT rs) IS
  BEGIN
    OPEN oFields FOR SELECT * FROM person;
  END founder;
END chapter_13;
/

Java 端完整代碼 (JDBC Thin Driver)

package jdbc;

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

public class TestStoredProcedures {
    private Connection conn;

    public TestStoredProcedures() {
        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl", "jola", "jola");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws Exception {
        new TestStoredProcedures().process();
    }

    public void process() throws SQLException {
        long start = 0, end = 0;
        CallableStatement cstmt = null;
        try {
            start = System.currentTimeMillis();
            // SQL92 語法呼叫存儲過程
            cstmt = conn.prepareCall("{call chapter_13.founder(?)}");
            // 註冊 OUT 參數為 CURSOR 類型
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt.execute();

            // 取得 ResultSet
            ResultSet rs = (ResultSet) cstmt.getObject(1);
            while (rs.next()) {
                System.out.println(rs.getString("NAME"));
            }
            rs.close();

            end = System.currentTimeMillis();
            System.out.println("Average elapsed time = " + (end - start) / 8 + " ms");
        } catch (SQLException e) {
            System.err.println("SQL Error: " + e.getMessage());
        } finally {
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore) {}
            }
        }
    }

    protected void finalize() throws Throwable {
        if (conn != null) {
            try { conn.close(); } catch (SQLException ignore) {}
        }
        super.finalize();
    }
}

關鍵點總結

1. CallableStatement：使用 JDBC 標準 {call ...} 語法呼叫存儲過程。
2. registerOutParameter(index, OracleTypes.CURSOR)：**必須**顯式註冊 OUT 參數為 OracleTypes.CURSOR，否則 getObject 返回不了 ResultSet。
3. ResultSet 轉型：(ResultSet) cstmt.getObject(1) 直接拿到游標結果集，後續操作同普通查詢。
4. 資源關閉順序：先關 ResultSet，再關 CallableStatement，最後關 Connection (在 finalize 或 try-with-resources 中)。
5. 驅動類：oracle.jdbc.driver.OracleDriver (舊) 或 oracle.jdbc.OracleDriver (新，建議)。