Oracle 存儲過程常需同時返回結果集 (REF CURSOR)與純量輸出參數 (計數、狀態碼等)。JDBC 呼叫時需正確註冊 OracleTypes.CURSOR 與對應型別，並按順序提取。本文提供完整 Spring + C3P0 + CallableStatement 範例，並解析 Package 定義、參數綁定順序、資源釋放等關鍵細節。
CREATE OR REPLACE PACKAGE package_user AS
TYPE my_resultset_cursor IS REF CURSOR;
PROCEDURE person_query (
rs OUT my_resultset_cursor, -- 結果集
c OUT INTEGER -- 統計計數
);
END package_user;
/
CREATE OR REPLACE PACKAGE BODY package_user AS
PROCEDURE person_query (rs OUT my_resultset_cursor, c OUT INTEGER) AS
BEGIN
-- 1. 開啟游標返回查詢結果
OPEN rs FOR SELECT empno, ename FROM emp;
-- 2. 執行某業務邏輯 (示範 UPDATE)
UPDATE some_table SET col1 = 'AAA' WHERE ...;
-- 3. 計算輸出參數
SELECT COUNT(*) INTO c FROM emp;
END person_query;
END package_user;
/
com.oracle.database.jdbc
ojdbc8
21.9.0.0
org.springframework
spring-jdbc
5.3.x
com.mchange
c3p0
0.9.5.5
package com.example.demo;
import java.sql.*;
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import oracle.jdbc.OracleTypes;
public class JdbcRefCursorDemo {
public static void main(String[] args) throws Exception {
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
DataSource ds = (DataSource) ctx.getBean("dataSource");
Connection conn = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
conn = ds.getConnection();
// 呼叫語法: {call package.proc(?, ?)} 參數順序必須與過程定義一致
cstmt = conn.prepareCall("{ CALL package_user.person_query(?, ?) }");
// 1. 註冊輸出參數型別 (順序與 CALL 句法 ? 位置對應)
cstmt.registerOutParameter(1, OracleTypes.CURSOR); // REF CURSOR
cstmt.registerOutParameter(2, OracleTypes.INTEGER); -- 計數
// 2. 執行
cstmt.execute();
// 3. 取得 REF CURSOR (轉型為 ResultSet)
rs = (ResultSet) cstmt.getObject(1);
// 4. 遍歷結果集
while (rs.next()) {
System.out.println(rs.getInt(1) + "\t" + rs.getString(2));
}
// 5. 取得純量輸出參數
int count = cstmt.getInt(2);
System.err.println("Total count: " + count);
} finally {
// 6. 逆序關閉資源
closeQuietly(rs);
closeQuietly(cstmt);
closeQuietly(conn);
}
}
private static void closeQuietly(AutoCloseable ac) {
if (ac != null) try { ac.close(); } catch (Exception ignore) {}
}
}
| 環節 | 關鍵點 | 錯誤示範與後果 |
|---|---|---|
| REF CURSOR 註冊 | registerOutParameter(idx, OracleTypes.CURSOR) | 用 Types.OTHER 或 Types.REF_CURSOR (JDBC 4.0+) 可能在舊驅動失效 |
| 參數順序 | 嚴格對應 {call pkg.proc(?, ?)} 中 ? 位置 | 順序顛倒 → ClassCastException 或數據錯誤 |
| 結果集提取 | cstmt.getObject(idx) 強轉 ResultSet | 直接 cstmt.getResultSet() 僅適用單結果集且為第一參數 |
| 純量輸出讀取 | cstmt.getInt/String/BigDecimal(idx) | 在 ResultSet 關閉前讀取較安全，部分驅動關閉 RS 會清空輸出參數 |
| 資源關閉順序 | ResultSet → CallableStatement → Connection | 先關 Connection 會導致隱式關閉 Statement/RS，但最顯式控制 |
PROCEDURE multi_cursor (rs1 OUT SYS_REFCURSOR, rs2 OUT SYS_REFCURSOR, cnt OUT NUMBER);
-- Java
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.registerOutParameter(3, OracleTypes.NUMBER);
cstmt.execute();
ResultSet rs1 = (ResultSet) cstmt.getObject(1);
ResultSet rs2 = (ResultSet) cstmt.getObject(2);
// 分別遍歷
SimpleJdbcCall call = new SimpleJdbcCall(ds)
.withCatalogName("PACKAGE_USER")
.withProcedureName("PERSON_QUERY")
.declareParameters(
new SqlOutParameter("RS", OracleTypes.CURSOR, new RowMapper() { ... }),
new SqlOutParameter("C", Types.INTEGER)
);
Map result = call.execute();
List> rows = (List) result.get("RS");
Integer count = (Integer) result.get("C");
cstmt.setFetchSize(1000) 減少網路往返。List.addAll 導致 OOM，邊讀邊處理/寫入下游。| 異常 | 原因 | 解決 |
|---|---|---|
| java.sql.SQLException: Invalid column type: 2012 | OracleTypes.CURSOR 常數值 -10 (2012) 驅動版本不匹配 | 升級 ojdbc 至與 DB 版本相容版本 |
| ORA-01001: invalid cursor | ResultSet 已關閉或未正確開啟 | 檢查存儲過程 OPEN CURSOR 邏輯、確保未提前關閉 |
| ClassCastException: oracle.jdbc.driver.T4CResultSetAccessor | getObject 返回型別非預期 | 確認 registerOutParameter 使用 OracleTypes.CURSOR |
| 數據庫會話洩漏 | 異常路徑未關閉連線 | 統一使用 try-with-resources 或 finally 關閉 |
JDBC 呼叫 Oracle 存儲過程獲取 REF CURSOR + 輸出參數，核心在於：Package 定義強型別 REF CURSOR → CallableStatement 按位置註冊 OracleTypes.CURSOR 與純量型別 → execute() → getObject 轉 ResultSet → 讀取純量輸出 → 逆序關閉資源。採用 Spring SimpleJdbcCall 可進一步簡化樣板碼，提升可維護性。
