Oracle 實現 Top-N 查詢與分頁的標準寫法詳解

核心原理

Oracle 無 SELECT TOP N 語法，標準做法：內層排序 + ROWNUM 過濾 + 外層排序。關鍵點：ROWNUM 在 ORDER BY 之前賦值，必須用子查詢先排序再取 ROWNUM。

四大核心場景完整語法

場景 1：基本 Top-N 查詢

SELECT * FROM (
  SELECT * FROM customer ORDER BY name
)
WHERE ROWNUM <= 3
ORDER BY ROWNUM ASC;

結果：按 NAME 字母序前 3 筆（eighth, fifth, first）

場景 2：Top-N 中取第 M 筆（M ≤ N）

SELECT id, name FROM (
  SELECT ROWNUM recno, id, name FROM (
    SELECT * FROM customer ORDER BY name
  )
  WHERE ROWNUM <= 3
  ORDER BY ROWNUM ASC
)
WHERE recno = 2;

結果：第 2 筆（fifth）

場景 3：排序集合中取第 N 筆（優化版）

SELECT id, name FROM (
  SELECT ROWNUM recno, id, name FROM (
    SELECT * FROM customer ORDER BY name
  )
  WHERE ROWNUM <= 2  -- 只取需要的前 N 筆
  ORDER BY ROWNUM ASC
)
WHERE recno = 2;

減少中間結果集大小，效能更佳。

場景 4：分頁查詢（第 M 筆起取 X 筆）

SELECT * FROM (
  SELECT ROWNUM recno, id, name FROM (
    SELECT * FROM customer ORDER BY name
  )
  WHERE ROWNUM <= (M + X - 1)  -- 只取到需要的最大行
  ORDER BY ROWNUM ASC
)
WHERE recno BETWEEN M AND (M + X - 1);

範例：第 2 筆起取 3 筆（M=2, X=3）→ ROWNUM <= 4，recno BETWEEN 2 AND 4

結果：fifth, first, forth

進階：封裝為分頁預存程序

CREATE OR REPLACE PROCEDURE page_query(
  p_sql      IN VARCHAR2,    -- 基礎查詢（不含 ORDER BY）
  p_order_by IN VARCHAR2,    -- 排序欄位
  p_page_no  IN NUMBER,      -- 頁碼（從1開始）
  p_page_size IN NUMBER,     -- 每頁筆數
  p_result   OUT SYS_REFCURSOR
) AS
  v_start NUMBER := (p_page_no - 1) * p_page_size + 1;
  v_end   NUMBER := p_page_no * p_page_size;
  v_sql   VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT * FROM (
              SELECT ROWNUM rn, t.* FROM (
                ' || p_sql || ' ORDER BY ' || p_order_by || '
              ) t
              WHERE ROWNUM <= ' || v_end || '
            ) WHERE rn >= ' || v_start;
  OPEN p_result FOR v_sql;
END;
/

效能關鍵點總結

常見錯誤對比

-- 錯誤：ROWNUM 在 ORDER BY 前賦值，結果非預期
SELECT * FROM customer WHERE ROWNUM <= 3 ORDER BY name;

-- 正確：子查詢先排序
SELECT * FROM (SELECT * FROM customer ORDER BY name) WHERE ROWNUM <= 3;