Oracle存儲過程分頁查詢最佳實踐：動態SQL雙層ROWNUM與REF CURSOR模式

通用分頁存儲過程參考實現：參數化、可審計、易擴展

本文提供經生產環境驗證的Oracle分頁存儲過程標準模板：動態構建COUNT與分頁查詢、雙層ROWNUM截取、REF CURSOR輸出、完整參數校驗與修正，附Java/JDBC調用範例與性能優化建議，可直接納入代碼庫作為項目規範。

設計原則

• 零硬編碼SQL：表名、條件、排序全參數化。
• 雙階段查詢：先COUNT(*)獲取總數 → 計算分頁邊界 → 執行分頁查詢。
• 雙層ROWNUM：內層ROWNUM <= :end_row 截取前N行，外層WHERE rn >= :start_row 過濾起始行，兼容10g/11g/12c/19c。
• REF CURSOR：避免中間結果集物化，流式返回，內存友好。
• 參數自修正：頁碼越界自動鉗制、頁大小非法自動修正。

標準模板代碼

1. 類型定義包

CREATE OR REPLACE PACKAGE pkg_pagination AS
  TYPE ref_cursor IS REF CURSOR;
END pkg_pagination;

2. 分頁存儲過程

CREATE OR REPLACE PROCEDURE sp_paginate (
  p_table_name    IN  VARCHAR2,        -- 表名 (需白名單校驗)
  p_where_clause  IN  VARCHAR2 DEFAULT NULL, -- WHERE條件 (不含WHERE關鍵字)
  p_order_by      IN  VARCHAR2 DEFAULT NULL, -- ORDER BY子句 (不含ORDER BY關鍵字)
  p_page_no       IN OUT NUMBER,       -- 輸入:請求頁碼 / 輸出:實際頁碼
  p_page_size     IN OUT NUMBER,       -- 輸入:頁大小 / 輸出:實際頁大小
  p_total_rows    OUT NUMBER,          -- 總記錄數
  p_total_pages   OUT NUMBER,          -- 總頁數
  p_result_set    OUT pkg_pagination.ref_cursor -- 結果游標
) IS
  v_sql       VARCHAR2(32767);
  v_start_row NUMBER;
  v_end_row   NUMBER;
BEGIN
  /*--- 1. 參數預處理 ---*/
  IF p_page_size IS NULL OR p_page_size <= 0 THEN p_page_size := 20; END IF;
  IF p_page_no IS NULL OR p_page_no <= 0 THEN p_page_no := 1; END IF;

  /*--- 2. 總記錄數 ---*/
  v_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
  IF p_where_clause IS NOT NULL THEN
    v_sql := v_sql || ' WHERE ' || p_where_clause;
  END IF;
  EXECUTE IMMEDIATE v_sql INTO p_total_rows;

  /*--- 3. 計算分頁元數據 ---*/
  p_total_pages := CEIL(p_total_rows / p_page_size);
  IF p_page_no > p_total_pages THEN p_page_no := p_total_pages; END IF;
  IF p_page_no < 1 THEN p_page_no := 1; END IF;

  v_start_row := (p_page_no - 1) * p_page_size + 1;
  v_end_row   := p_page_no * p_page_size;

  /*--- 4. 構建分頁查詢 (雙層ROWNUM) ---*/
  v_sql := 'SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM ' || p_table_name;
  IF p_where_clause IS NOT NULL THEN
    v_sql := v_sql || ' WHERE ' || p_where_clause;
  END IF;
  IF p_order_by IS NOT NULL THEN
    v_sql := v_sql || ' ORDER BY ' || p_order_by;
  END IF;
  v_sql := v_sql || ') a WHERE ROWNUM <= ' || v_end_row || ') WHERE rn >= ' || v_start_row;

  /*--- 5. 開啟游標返回 ---*/
  OPEN p_result_set FOR v_sql;
EXCEPTION
  WHEN OTHERS THEN
    -- 記錄錯誤日志 (建議接入統一日志框架)
    DBMS_OUTPUT.PUT_LINE('Pagination Error: ' || SQLERRM);
    RAISE;
END sp_paginate;

3. Java/JDBC調用範例 (Spring JdbcTemplate)

@Autowired JdbcTemplate jdbcTemplate;

public PageResult> queryPage(String table, String where, String orderBy, int pageNo, int pageSize) {
  SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
      .withCatalogName("PKG_PAGINATION")
      .withProcedureName("SP_PAGINATE")
      .declareParameters(
          new SqlParameter("p_table_name", Types.VARCHAR),
          new SqlParameter("p_where_clause", Types.VARCHAR),
          new SqlParameter("p_order_by", Types.VARCHAR),
          new SqlInOutParameter("p_page_no", Types.INTEGER),
          new SqlInOutParameter("p_page_size", Types.INTEGER),
          new SqlOutParameter("p_total_rows", Types.INTEGER),
          new SqlOutParameter("p_total_pages", Types.INTEGER),
          new SqlOutParameter("p_result_set", OracleTypes.CURSOR)
      );

  Map in = new HashMap<>();
  in.put("p_table_name", table);
  in.put("p_where_clause", where);
  in.put("p_order_by", orderBy);
  in.put("p_page_no", pageNo);
  in.put("p_page_size", pageSize);

  Map out = call.execute(in);
  
  List> rows = (List>) out.get("p_result_set");
  return PageResult.of(rows, (Integer)out.get("p_total_rows"), (Integer)out.get("p_page_no"), (Integer)out.get("p_page_size"));
}

安全與性能加固清單

進階擴展方向

• Keyset Pagination (Seek Method)：WHERE id > :last_seen_id ORDER BY id LIMIT :page_size，適合無限滾動、極大數據集，無總數統計開銷。
• 緩存總數：低頻變更表用Redis緩存COUNT(*)，TTL 5-15分鐘。
• 異步導出：大數據導出改用DBMS_PARALLEL_EXECUTE分塊導出CSV/Excel，避免長事務阻塞。

將分頁邏輯下沉存儲過程，應用層僅傳參數、消費游標，實現SQL集中治理、性能可控、安全可審計。建議作為組件庫標準模塊，禁止業務代碼手寫分頁SQL。