Oracle 預存程序建立與 JDBC 呼叫完整範例：REF CURSOR 回傳結果集

預存程序定義

建立套件規格與主體，定義 REF CURSOR 類型供 JDBC 取得結果集。

CREATE OR REPLACE PACKAGE PKG_UIREPORT_DEPTTRANSTABLE AS
  TYPE MY_CURSOR IS REF CURSOR;
  PROCEDURE ZCCCGC(
    NAME_OUT OUT MY_CURSOR,
    V_COUNT  OUT INT,
    LIKES    IN  NVARCHAR2,
    STARTS   IN  INT,
    LIMITS   IN  INT
  );
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_UIREPORT_DEPTTRANSTABLE AS
  PROCEDURE ZCCCGC(
    NAME_OUT OUT MY_CURSOR,
    V_COUNT  OUT INT,
    LIKES    IN  NVARCHAR2,
    STARTS   IN  INT,
    LIMITS   IN  INT
  ) IS
    STR VARCHAR2(1000);
  BEGIN
    -- 範例：插入暫存表
    INSERT INTO TTABLE ("NAME1","NAME2","NAME3")
    SELECT "NAME11","NAME22","NAME3" FROM "TABLE";

    -- 動態分頁查詢
    STR := 'SELECT "NAME1","NAME2","NAME3" FROM (
              SELECT ROW_.*, ROWNUM ROWNUM_
              FROM (SELECT * FROM TTABLE ' || LIKES || ') ROW_
              WHERE ROWNUM <= ' || LIMITS || ')
            WHERE ROWNUM_ > ' || STARTS;
    OPEN NAME_OUT FOR STR;

    -- 總筆數
    STR := 'SELECT COUNT(*) FROM TTABLE ' || LIKES;
    EXECUTE IMMEDIATE STR INTO V_COUNT;
  END ZCCCGC;
END;
/

JDBC 呼叫程式碼

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

public class ProcedureTest {
  public static void main(String[] args) {
    String dbDriver = "oracle.jdbc.driver.OracleDriver";
    String dbURL    = "jdbc:oracle:thin:@localhost:1521:orcl";
    String user     = "hr";
    String pass     = "zaylqn";

    try (Connection conn = DriverManager.getConnection(dbURL, user, pass)) {
      conn.setAutoCommit(false);

      // 對應 5 參數：OUT CURSOR, OUT INT, IN VARCHAR2, IN INT, IN INT
      String sql = "{CALL PKG_UIREPORT_DEPTTRANSTABLE.ZCCCGC(?,?,?,?,?)}";
      try (CallableStatement stmt = conn.prepareCall(sql)) {
        stmt.registerOutParameter(1, OracleTypes.CURSOR);
        stmt.registerOutParameter(2, OracleTypes.INTEGER);
        stmt.setString(3, " WHERE LOWER(\"NAME2\") LIKE LOWER('%xx%')");
        stmt.setInt(4, 0);      // STARTS
        stmt.setInt(5, 30);     // LIMITS
        stmt.execute();

        int total = stmt.getInt(2);
        System.out.println("總筆數：" + total);

        try (ResultSet rs = (ResultSet) stmt.getObject(1)) {
          while (rs.next()) {
            System.out.println(rs.getString(1) + " | " + rs.getString(2));
            System.out.println("----------------------------------------");
          }
        }
      }
      conn.commit();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

關鍵點總結

• REF CURSOR：Oracle 特有游標類型，對應 JDBC OracleTypes.CURSOR
• 動態 SQL：EXECUTE IMMEDIATE 配合 OPEN ... FOR 實現靈活查詢
• 參數順序：JDBC registerOutParameter 索引從 1 開始，需與 PL/SQL 宣告順序完全一致
• 資源管理：使用 try-with-resources 自動關閉 Connection/CallableStatement/ResultSet
• 分頁邏輯：採用 ROWNUM 雙層嵌套，相容 Oracle 11g 及以下版本

常見錯誤排查