萬盛學電腦網

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

Oracle 帶動態排序的分頁查詢儲存過程

支援任意 ORDER BY 欄位與排序方向 (ASC/DESC) 的通用分頁程序,採用 ROWNUM 雙層嵌套寫法。

程式碼

CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT (
  v_page_size       IN  INT,
  v_current_page    IN  INT,
  v_table_name      IN  VARCHAR2,
  v_order_field     IN  VARCHAR2,   -- 'col1' 或 'col1, col2','NO' 表示不排序
  v_order_sequence  IN  VARCHAR2,   -- 'ASC' 或 'DESC'
  p_cursor          OUT SYS_REFCURSOR
) 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(*) FROM ' || v_table_name;
  EXECUTE IMMEDIATE v_sql_count INTO v_count;

  -- 3. 計算分頁範圍 (注意 DESC 時行號反向)
  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 <= end,外層過濾 RN >= start)
  v_sql := 'SELECT * FROM (SELECT t.*, ROWNUM rn FROM ' || v_table_name ||
           ' t 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;

使用範例

VAR rc REFCURSOR
EXEC TABLEPAGE_SELECT(10, 2, 'EMP', 'ENAME', 'ASC', :rc);
PRINT rc

注意事項

  • 動態 SQL 需防範注入,生產環境建議驗證 v_table_name、v_order_field 合法性。
  • 大表建議改用 ROW_NUMBER() OVER (ORDER BY ...) (10g+) 或 OFFSET ... FETCH (12c+) 寫法,效能更穩定。
  • v_order_field 可傳入多欄位如 'deptno, hiredate'。
copyright © 萬盛學電腦網 all rights reserved