Oracle存儲過程實現通用高效分頁：動態SQL + REF CURSOR + Java調用全鏈路

Web應用分頁查詢的Oracle最佳實踐：存儲過程封裝與客戶端調用

分頁是Web應用標配。本文提供一套通用、可維護、性能可控的Oracle分頁存儲過程方案：動態構建SQL、REF CURSOR返回結果集、支援任意表/條件/排序，並附Java JDBC調用範例。

架構設計

1. 包定義REF CURSOR類型：解耦調用端與實現細節。
2. 存儲過程參數化：表名、WHERE條件、排序欄位/方向、當前頁/頁大小、輸出總記錄/總頁數、結果游標。
3. 動態SQL兩階段：
   ① COUNT(*)獲取總筆數 → 計算總頁數、修正頁碼。
   ② 雙層ROWNUM包裹（內層排序+截取、外層篩選起始行）執行分頁查詢。
4. REF CURSOR輸出：OPEN v_cur FOR v_sql 避免中間結果集物化。

核心代碼

1. 定義包

CREATE OR REPLACE PACKAGE pkg_query AS
  TYPE cur_query IS REF CURSOR;
END pkg_query;

2. 分頁存儲過程

CREATE OR REPLACE PROCEDURE prc_query (
  p_table_name   IN     VARCHAR2,
  p_where_clause IN     VARCHAR2,
  p_order_col    IN     VARCHAR2,
  p_order_dir    IN     VARCHAR2,
  p_cur_page     IN OUT NUMBER,
  p_page_size    IN OUT NUMBER,
  p_total_rows   OUT    NUMBER,
  p_total_pages  OUT    NUMBER,
  v_cur          OUT    pkg_query.cur_query
) IS
  v_sql          VARCHAR2(32767);
  v_start_row    NUMBER;
  v_end_row      NUMBER;
BEGIN
  -- 1. 總記錄數
  v_sql := 'SELECT COUNT(*) FROM ' || p_table_name || ' WHERE 1=1';
  IF p_where_clause IS NOT NULL THEN v_sql := v_sql || p_where_clause; END IF;
  EXECUTE IMMEDIATE v_sql INTO p_total_rows;

  -- 2. 參數修正
  IF p_page_size <= 0 THEN p_page_size := 10; END IF;
  p_total_pages := CEIL(p_total_rows / p_page_size);
  IF p_cur_page < 1 THEN p_cur_page := 1; END IF;
  IF p_cur_page > p_total_pages THEN p_cur_page := p_total_pages; END IF;

  -- 3. 分頁範圍
  v_start_row := (p_cur_page - 1) * p_page_size + 1;
  v_end_row   := p_cur_page * p_page_size;

  -- 4. 雙層ROWNUM分頁查詢
  v_sql := 'SELECT * FROM (SELECT A.*, ROWNUM r FROM (SELECT * FROM ' || p_table_name;
  IF p_where_clause IS NOT NULL THEN v_sql := v_sql || ' WHERE 1=1' || p_where_clause; END IF;
  IF p_order_col IS NOT NULL THEN v_sql := v_sql || ' ORDER BY ' || p_order_col || ' ' || p_order_dir; END IF;
  v_sql := v_sql || ') A WHERE ROWNUM <= ' || v_end_row || ') B WHERE r >= ' || v_start_row;

  DBMS_OUTPUT.PUT_LINE(v_sql);  -- 除錯用
  OPEN v_cur FOR v_sql;
END prc_query;

3. Java JDBC調用範例

String sql = "{call prc_query(?,?,?,?,?,?,?,?,?)}";
CallableStatement cs = conn.prepareCall(sql);
cs.setString(1, "EMP");
cs.setString(2, " WHERE DEPTNO=10");
cs.setString(3, "ENAME");
cs.setString(4, "ASC");
cs.setInt(5, 1);      // 當前頁
cs.setInt(6, 10);     // 頁大小
cs.registerOutParameter(5, Types.INTEGER);  // 總記錄數
cs.registerOutParameter(6, Types.INTEGER);  // 總頁數
cs.registerOutParameter(7, Types.INTEGER);  // 修正後當前頁
cs.registerOutParameter(8, Types.INTEGER);  // 修正後頁大小
cs.registerOutParameter(9, OracleTypes.CURSOR); // 結果集
cs.execute();

ResultSet rs = (ResultSet) cs.getObject(9);
while (rs.next()) { /* 處理行 */ }
rs.close();
cs.close();

優化與注意事項

• 注入防護：表名、欄位名白名單驗證；WHERE條件建議改用綁定變數陣列或DBMS_ASSERT清洗。
• 大數據量深分頁：ROWNUM雙層在深頁（如第10000頁）效能下降，建議改用ROW_NUMBER() OVER()分析函數或OFFSET/FETCH（12c+）。
• 游標洩漏：Java端務必在finally塊關閉ResultSet、CallableStatement、Connection。
• 統計資訊：確保相關表/索引統計資訊最新，優化器才能選擇最優執行計劃。

此方案將分頁邏輯下沉資料庫層，應用層僅傳參數、取游標，實現零SQL硬編碼、易維護、易擴展，適合中小型Web系統快速落地。