Oracle 帶排序功能的分頁存儲過程 (支援動態 ORDER BY)

程式碼

CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT(
  v_page_size       IN  INT,           -- 頁大小
  v_current_page    IN  INT,           -- 當前頁碼 (從 1 開始)
  v_table_name      IN  VARCHAR2,      -- 表名
  v_order_field     IN  VARCHAR2,      -- 排序欄位，'NO' 表示不排序
  v_order_sequence  IN  VARCHAR2,      -- 排序方向：'asc' 或 'desc' (含前導底線 '_asc'/'_desc')
  p_cursor          OUT refcursor_pkg.return_cursor  -- 假設已定義 REF CURSOR 類型
) AS
  v_sql         VARCHAR2(3000);
  v_sql_count   VARCHAR2(3000);
  v_sql_order   VARCHAR2(2000);
  v_count       INT;
  v_endrownum   INT;
  v_startrownum INT;
BEGIN
  -- 1. 組裝 ORDER BY 子句
  IF v_order_field != 'NO' THEN
    v_sql_order := ' ORDER BY ' || v_order_field || ' ' || v_order_sequence;
  ELSE
    v_sql_order := '';
  END IF;

  -- 2. 統計總筆數
  v_sql_count := 'SELECT COUNT(ROWNUM) FROM ' || v_table_name;
  EXECUTE IMMEDIATE v_sql_count INTO v_count;

  -- 3. 計算當前頁 ROWNUM 範圍 (關鍵：DESC 排序時 ROWNUM 邏輯需反轉)
  IF v_order_sequence = 'desc' THEN
    v_endrownum   := v_count - (v_current_page - 1) * v_page_size;
    v_startrownum := v_endrownum - v_page_size + 1;
  ELSE
    v_endrownum   := v_current_page * v_page_size;
    v_startrownum := v_endrownum - v_page_size + 1;
  END IF;

  -- 4. 雙層 ROWNUM 分頁查詢 (內層含 ORDER BY)
  v_sql := 'SELECT * FROM (SELECT ' || v_table_name || '.*, ROWNUM rn FROM ' || v_table_name ||
           ' WHERE ROWNUM <= ' || TO_CHAR(v_endrownum) || ' ' || v_sql_order || ') ' ||
           'WHERE rn >= ' || TO_CHAR(v_startrownum) || ' ' || v_sql_order;

  OPEN p_cursor FOR v_sql;
END TABLEPAGE_SELECT;
/

設計要點

1. 動態排序：傳入欄位名與方向 (_asc/_desc)，靈活應對多欄位排序 (如 v_order_field='a desc, b asc')。
2. DESC 排序的 ROWNUM 修正：Oracle ROWNUM 在 ORDER BY **之前**賦值。若直接 ORDER BY DESC 再取 ROWNUM，會導致分頁錯亂。本處理透過「總筆數 - 偏移量」反向計算 v_endrownum/v_startrownum，配合內層 WHERE ROWNUM <= end 截取，外層 WHERE rn >= start 篩選，實現正確倒序分頁。
3. 動態 SQL：使用 EXECUTE IMMEDIATE 統計總數，OPEN ... FOR 開啟動態游標。
4. 依賴：需預先建立 Package refcursor_pkg 定義 TYPE return_cursor IS REF CURSOR;。

使用範例

-- 查詢第 2 頁，每頁 20 筆，按 CREATE_DATE 降序
BEGIN
  TABLEPAGE_SELECT(20, 2, 'MY_TABLE', 'CREATE_DATE', '_desc', :cursor);
END;
/