萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 分頁存儲過程實戰:動態 SQL 實現通用高效分頁

為何用存儲過程分頁?

修改邏輯只需改存儲過程,無需動應用代碼。本文提供一個通用分頁存儲過程,支援動態表名、條件、排序。

核心代碼結構

1. 建立套件定義 REF CURSOR

create or replace package pkg_query as
  type cur_query is ref cursor;
end pkg_query;

2. 分頁存儲過程

CREATE OR REPLACE PROCEDURE prc_query(
  p_tableName   in varchar2,  -- 表名
  p_strWhere    in varchar2,  -- 查詢條件
  p_orderColumn in varchar2,  -- 排序欄位
  p_orderStyle  in varchar2,  -- 排序方式
  p_curPage     in out Number, -- 當前頁
  p_pageSize    in out Number, -- 每頁筆數
  p_totalRecords out Number,   -- 總筆數
  p_totalPages   out Number,   -- 總頁數
  v_cur         out pkg_query.cur_query) -- 結果集
IS
  v_sql VARCHAR2(1000);
  v_startRecord Number;
  v_endRecord   Number;
BEGIN
  -- 1. 取總筆數
  v_sql := 'SELECT COUNT(*) FROM ' || p_tableName || ' WHERE 1=1';
  IF p_strWhere IS NOT NULL THEN v_sql := v_sql || p_strWhere; END IF;
  EXECUTE IMMEDIATE v_sql INTO p_totalRecords;

  -- 2. 參數驗證與總頁數計算
  IF p_pageSize < 1 THEN p_pageSize := 1; END IF;
  p_totalPages := CEIL(p_totalRecords / p_pageSize);
  IF p_curPage < 1 THEN p_curPage := 1; END IF;
  IF p_curPage > p_totalPages THEN p_curPage := p_totalPages; END IF;

  -- 3. 計算範圍並動態構建分頁查詢
  v_startRecord := (p_curPage - 1) * p_pageSize + 1;
  v_endRecord   := p_curPage * p_pageSize;

  v_sql := 'SELECT * FROM (SELECT A.*, ROWNUM r FROM (SELECT * FROM ' || p_tableName;
  IF p_strWhere IS NOT NULL THEN v_sql := v_sql || ' WHERE 1=1' || p_strWhere; END IF;
  IF p_orderColumn IS NOT NULL THEN v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle; END IF;
  v_sql := v_sql || ') A WHERE ROWNUM <= ' || v_endRecord || ') B WHERE r >= ' || v_startRecord;

  DBMS_OUTPUT.put_line(v_sql);
  OPEN v_cur FOR v_sql;
END prc_query;

3. Java 呼叫範例

String sql = "{ call prc_query(?,?,?,?,?,?,?,?,?) }";
CallableStatement call = con.prepareCall(sql);
// 設定參數、註冊 OUT CURSOR
call.registerOutParameter(9, OracleTypes.CURSOR);
call.execute();
ResultSet rs = (ResultSet) call.getObject(9);

注意事項

  • 動態 SQL 可能失去存儲過程預編譯優勢,但換取極高通用性。
  • 生產環境建議加入 SQL 注入防護(驗證表名、欄位名白名單)。
  • 大數據量場景可考慮 Keyset Pagination(游標分頁)替代 OFFSET。
copyright © 萬盛學電腦網 all rights reserved