Oracle 透過 REF CURSOR 實現存儲過程/函數返回結果集給外部應用（Java/.NET/Python 等）。本文提供完整的 Package 定義、Package Body 實作、Java JDBC 呼叫程式碼，涵蓋 OUT 參數 與 RETURN 兩種模式，並說明常見坑點與資源釋放最佳實務。
CREATE OR REPLACE PACKAGE pkg_test AS
TYPE t_cursor IS REF CURSOR; -- 弱型別 REF CURSOR
PROCEDURE pro_test(p_cur OUT t_cursor); -- OUT 參數模式
FUNCTION fun_test RETURN t_cursor; -- RETURN 模式
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test AS
PROCEDURE pro_test(p_cur OUT t_cursor) IS
v_cur t_cursor;
BEGIN
-- 示範 DML 後再開啟游標
INSERT INTO tmp_table VALUES (111);
INSERT INTO tmp_table VALUES (222);
OPEN v_cur FOR SELECT * FROM tmp_table;
p_cur := v_cur; -- 指派給 OUT 參數
COMMIT;
END pro_test;
FUNCTION fun_test RETURN t_cursor IS
v_cur t_cursor;
BEGIN
OPEN v_cur FOR SELECT * FROM tmp_table;
RETURN v_cur; -- 直接返回
END fun_test;
END pkg_test;
/
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false); // 管理交易邊界
try (CallableStatement cs = conn.prepareCall("{? = call pkg_test.fun_test}")) {
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
try (ResultSet rs = (ResultSet) cs.getObject(1)) {
while (rs.next()) {
// 處理結果集
System.out.println(rs.getInt(1) + " " + rs.getString(2));
}
}
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
} finally {
conn.setAutoCommit(true);
}
try (CallableStatement cs = conn.prepareCall("{call pkg_test.pro_test(?)}")) {
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
try (ResultSet rs = (ResultSet) cs.getObject(1)) {
while (rs.next()) { /* ... */ }
}
}
| 項目 | 說明 |
|---|---|
| OracleTypes.CURSOR | 需 import oracle.jdbc.OracleTypes；若用標準 JDBC 無此常數，可用 Types.OTHER 但不建議。 |
| ResultSet 類型 | 返回的 ResultSet 僅支援 TYPE_FORWARD_ONLY、CONCUR_READ_ONLY。 |
| 游標生命週期 | Java 端關閉 ResultSet / CallableStatement 即釋放伺服器端游標；務必用 try-with-resources 確保釋放。 |
| 交易控制 | 存儲過程內若有 DML，由呼叫端決定 commit/rollback；避免在存儲過程內自行提交（除非自治交易）。 |
| 強型別 REF CURSOR | TYPE t_emp_cur IS REF CURSOR RETURN emp%ROWTYPE; 可在編譯期檢查欄位，建議正式專案使用。 |
| 多結果集 | 單一 REF CURSOR 只能返回一個結果集；需多個結果集請定義多個 OUT 參數。 |
HikariCP / Druid 等連線池，設定 maxLifetime 避免游標洩漏導致連線不可用。OFFSET ... FETCH），Java 端流式處理，避免記憶體暴漲。CallableStatement.executeBatch()，但 REF CURSOR 無法批次返回，需逐個呼叫。<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>21.9.0.0</version>
</dependency>
掌握 REF CURSOR 雙向互動模式，讓 Oracle 複雜商業邏輯封裝於存儲過程，Java 端僅負責協流與呈現，實現清晰的責任分離與高效能資料存取。
