您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 預存程序回傳結果集完整指南：SYS_REFCURSOR 與 JDBC/C3P0 整合

編輯：Oracle 資料庫基礎

何時需要回傳結果集？

多數情況下應避免從預存程序回傳結果集，改用應用層組裝 SQL。僅在以下情境必要：

複雜業務邏輯封裝於資料庫層
多應用系統共用同一查詢邏輯
動態 SQL 結果欄位不固定

完整實作範例

1. 建立測試表

CREATE TABLE HOTLINE (
  COUNTRY VARCHAR2(50),
  PNO     VARCHAR2(50)
);
INSERT INTO HOTLINE VALUES ('Taiwan', '0800-123-456');
INSERT INTO HOTLINE VALUES ('USA',    '1-800-555-0199');
COMMIT;

2. 定義套件與函數回傳 SYS_REFCURSOR

CREATE OR REPLACE PACKAGE PKG_HOTLINE IS
  TYPE HOTLINE_CURSOR_TYPE IS REF CURSOR;
  FUNCTION GET_HOTLINE RETURN HOTLINE_CURSOR_TYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_HOTLINE IS
  FUNCTION GET_HOTLINE RETURN HOTLINE_CURSOR_TYPE IS
    C_HOTLINE HOTLINE_CURSOR_TYPE;
  BEGIN
    OPEN C_HOTLINE FOR SELECT * FROM HOTLINE;
    RETURN C_HOTLINE;
  END;
END;
/

Oracle 內建 SYS_REFCURSOR 為弱類型 REF CURSOR，可直接使用無需自訂類型。

3. SQL*Plus 測試

VAR RC REFCURSOR;
EXEC :RC := PKG_HOTLINE.GET_HOTLINE;
PRINT RC;

4. Java JDBC 呼叫（標準驅動）

public void queryWithJdbc() {
  String sql = "{? = CALL PKG_HOTLINE.GET_HOTLINE()}";
  try (Connection conn = getConnection();
       CallableStatement stmt = conn.prepareCall(sql)) {
    
    stmt.registerOutParameter(1, OracleTypes.CURSOR);
    stmt.execute();
    
    // 標準 Oracle 驅動需轉型
    ResultSet rs = ((OracleCallableStatement) stmt).getCursor(1);
    
    while (rs.next()) {
      System.out.println("Country: " + rs.getString(1) + " | Phone: " + rs.getString(2));
    }
  } catch (Exception e) {
    e.printStackTrace();
  }
}

5. C3P0 連線池環境調整


// C3P0 包裝的 CallableStatement 不支援 getCursor()
ResultSet rs = (ResultSet) stmt.getObject(1);  // 必須用 getObject

關鍵差異：直連用 getCursor(1)；C3P0/DBCP 等連線池必須用 getObject(1)，否則拋 ClassCastException。

進階模式：多結果集

PROCEDURE GET_MULTI(
  CURSOR1 OUT SYS_REFCURSOR,
  CURSOR2 OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN CURSOR1 FOR SELECT * FROM TABLE_A;
  OPEN CURSOR2 FOR SELECT * FROM TABLE_B;
END;

JDBC 依序 getObject(1), getObject(2) 取得多個 ResultSet。