Oracle 分頁存儲過程最佳實踐：PL/SQL 封裝 + Java 呼叫的高效分頁架構

在 Oracle 中實現高效分頁，核心策略是 **「資料庫層完成資料切片，應用層僅負責呈現」**。本文提供一套生產級的分頁存儲過程設計：使用 ROWNUM 偽欄位配合雙層子查詢，封裝於 Package 中，再由 Java (或任意語言) 透過 REF CURSOR 呼叫，實現高效能、低耦合的分頁架構。

核心分頁邏輯：ROWNUM 雙層過濾

Oracle 無 LIMIT/OFFSET，標準寫法為：

SELECT * FROM (
  SELECT t1.*, ROWNUM rn FROM (
    SELECT * FROM emp ORDER BY sale DESC
  ) t1 WHERE ROWNUM <= :v_end
) WHERE rn >= :v_begin;

• 內層：排序 + ROWNUM <= 結束行 截取前 N 筆
• 外層：過濾 rn >= 起始行 取出當頁資料
• 關鍵：必須在最內層完成排序，否則分頁結果不穩定。

完整 Package 實作

1. 定義 REF CURSOR 類型 (Package Spec)

CREATE OR REPLACE PACKAGE pkg_pagination AS
  TYPE ref_cursor IS REF CURSOR;
  
  PROCEDURE get_page (
    p_table_name   IN  VARCHAR2,   -- 表名 (動態 SQL 需防注入)
    p_page_size    IN  NUMBER,     -- 每頁筆數
    p_page_no      IN  NUMBER,     -- 頁碼 (1-based)
    p_total_rows   OUT NUMBER,     -- 總筆數
    p_total_pages  OUT NUMBER,     -- 總頁數
    p_result_set   OUT ref_cursor  -- 結果集游標
  );
END pkg_pagination;
/

2. 核心邏輯實作 (Package Body)

CREATE OR REPLACE PACKAGE BODY pkg_pagination AS

  PROCEDURE get_page (
    p_table_name   IN  VARCHAR2,
    p_page_size    IN  NUMBER,
    p_page_no      IN  NUMBER,
    p_total_rows   OUT NUMBER,
    p_total_pages  OUT NUMBER,
    p_result_set   OUT ref_cursor
  ) IS
    v_sql       VARCHAR2(32767);
    v_begin     NUMBER;
    v_end       NUMBER;
  BEGIN
    -- 參數防禦
    IF p_page_size <= 0 OR p_page_no <= 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Page size and page no must be > 0');
    END IF;

    -- 計算總筆數 (動態 SQL)
    v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
    EXECUTE IMMEDIATE v_sql INTO p_total_rows;

    -- 計算總頁數
    p_total_pages := CEIL(p_total_rows / p_page_size);
    IF p_page_no > p_total_pages AND p_total_pages > 0 THEN
      p_page_no := p_total_pages; -- 自動修正超界頁碼
    END IF;

    -- 計算分頁邊界
    v_begin := (p_page_no - 1) * p_page_size + 1;
    v_end   := p_page_no * p_page_size;

    -- 動態構建分頁查詢 (簡化版：假設固定 ORDER BY sale DESC)
    v_sql := 'SELECT * FROM (
                SELECT t1.*, ROWNUM rn FROM (
                  SELECT * FROM ' || p_table_name || ' ORDER BY sale DESC
                ) t1 WHERE ROWNUM <= ' || v_end || '
              ) WHERE rn >= ' || v_begin;

    OPEN p_result_set FOR v_sql;
  END get_page;

END pkg_pagination;
/

Java 呼叫範例 (JDBC + REF CURSOR)

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

public class PaginationDemo {
  public static void main(String[] args) throws SQLException {
    String url = "jdbc:oracle:thin:@//host:1521/service";
    try (Connection conn = DriverManager.getConnection(url, "user", "pwd");
         CallableStatement cs = conn.prepareCall("{call pkg_pagination.get_page(?,?,?,?,?,?)}")) {
      
      // 綁定參數
      cs.setString(1, "EMP");       // p_table_name
      cs.setInt(2, 10);             // p_page_size
      cs.setInt(3, 2);              // p_page_no
      cs.registerOutParameter(4, Types.INTEGER);  // p_total_rows
      cs.registerOutParameter(5, Types.INTEGER);  // p_total_pages
      cs.registerOutParameter(6, OracleTypes.CURSOR); // p_result_set
      
      cs.execute();
      
      // 讀取輸出參數
      int totalRows = cs.getInt(4);
      int totalPages = cs.getInt(5);
      System.out.printf("Total: %d rows, %d pages%n", totalRows, totalPages);
      
      // 處理 REF CURSOR
      try (ResultSet rs = (ResultSet) cs.getObject(6)) {
        while (rs.next()) {
          System.out.println(rs.getInt("id") + " | " + 
                             rs.getString("ename") + " | " + 
                             rs.getBigDecimal("sale"));
        }
      }
    }
  }
}

進階優化與安全建議

12c+ 現代化寫法 (FETCH FIRST)

SELECT * FROM emp ORDER BY sale DESC
OFFSET :offset ROWS FETCH NEXT :page_size ROWS ONLY;

Oracle 12c 起支援 ANSI 標準分頁語法，執行計畫優於 ROWNUM 寫法，**強烈建議新專案直接採用**。

掌握「Package 封裝分頁邏輯 + REF CURSOR 回傳 + 應用層參數化呼叫」模式，即可構建高效、安全、可維護的 Oracle 分頁服務。