Java 通過 JDBC CallableStatement 調用 Oracle 存儲過程返回 REF CURSOR,關鍵在於 註冊輸出參數類型為 OracleTypes.CURSOR,執行後 getObject() 強轉為 ResultSet 遍歷。
CREATE OR REPLACE PACKAGE chapter_13 AS
TYPE rs IS REF CURSOR;
PROCEDURE founder(p_fields OUT rs);
END chapter_13;
/
CREATE OR REPLACE PACKAGE BODY chapter_13 AS
PROCEDURE founder(p_fields OUT rs) IS
BEGIN
OPEN p_fields FOR SELECT * FROM person;
END founder;
END chapter_13;
/
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) {
e.printStackTrace();
}
}
public void process() {
CallableStatement cstmt = null;
ResultSet rs = null;
try {
long start = System.currentTimeMillis();
// SQL92 escape syntax
cstmt = conn.prepareCall("{call chapter_13.founder(?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR); // 關鍵行
cstmt.execute();
rs = (ResultSet) cstmt.getObject(1); // 強轉 ResultSet
while (rs.next()) {
System.out.println(rs.getString("NAME"));
}
System.out.println("Elapsed: " + (System.currentTimeMillis() - start) + " ms");
} catch (SQLException e) {
System.err.println("SQL Error: " + e.getMessage());
} finally {
closeQuietly(rs);
closeQuietly(cstmt);
}
}
private void closeQuietly(AutoCloseable c) {
if (c != null) try { c.close(); } catch (Exception ignore) {}
}
public static void main(String[] args) throws Exception {
new TestStoredProcedures().process();
}
}
| 步驟 | API | 說明 |
|---|---|---|
| 準備調用 | conn.prepareCall("{call pkg.proc(?)}") | SQL92 轉義語法,跨驅動可移植 |
| 註冊 OUT 參數 | cstmt.registerOutParameter(idx, OracleTypes.CURSOR) | 必須使用 OracleTypes.CURSOR,標準 java.sql.Types 無此常量 |
| 執行 | cstmt.execute() | 觸發服務端過程執行 |
| 獲取結果集 | (ResultSet) cstmt.getObject(idx) | 返回的 REF CURSOR 實際是 OracleResultSetImpl |
| 遍歷 | rs.next() / rs.getString(col) | 標準 JDBC ResultSet API |
OracleTypes.CURSOR → Invalid column typecstmt.getResultSet() 而非 getObject() → 返回 nullResultSet、CallableStatement、Connection → 連接洩漏-- Oracle 12c 存儲過程
PROCEDURE get_data(p_rc OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_rc FOR SELECT * FROM person;
DBMS_SQL.RETURN_RESULT(p_rc); -- 隱式返回,客戶端無需註冊 OUT 參數
END;
JDBC 4.1+ 可通過 Statement.getMoreResults() 獲取隱式結果集,簡化調用代碼。