Oracle 實現 SELECT TOP N 分頁查詢完整指南：ROWNUM 技巧與四大應用場景

核心原理：子查詢排序 + ROWNUM 過濾

Oracle 無 TOP N 語法，標準模式為：

SELECT * FROM (
    SELECT * FROM table_name ORDER BY sort_column
) WHERE ROWNUM <= N;

關鍵點：ORDER BY 必須在內層子查詢完成，外層再用 ROWNUM 截取，否則會先取前 N 筆再排序，結果錯誤。

四大實用場景與 SQL 範本

1. 取前 N 筆 (基本 Top-N)

SELECT * FROM (
    SELECT * FROM customer ORDER BY name
) WHERE ROWNUM <= 3;
-- 結果: eighth, fifth, first (依 name 字母序)

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
) WHERE recno = 2;
-- 結果: fifth (第 2 筆)

3. 取排序後的第 N 筆 (單筆精準取值)

SELECT id, name FROM (
    SELECT ROWNUM recno, id, name FROM (
        SELECT * FROM customer ORDER BY name
    ) WHERE ROWNUM <= 2
) WHERE recno = 2;
-- 結果: fifth (第 2 筆)，內層只取 2 筆效能最佳

4. 取第 M 筆起的 X 筆 (分頁核心邏輯)

SELECT id, name FROM (
    SELECT ROWNUM recno, id, name FROM (
        SELECT * FROM customer ORDER BY name
    ) WHERE ROWNUM <= (M + X - 1)
) WHERE recno BETWEEN M AND (M + X - 1);

-- 例: 取第 2 筆起 3 筆 (M=2, X=3) → ROWNUM <= 4, recno BETWEEN 2 AND 4
-- 結果: fifth, first, forth

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

以 START_ROW (起始筆數)、PAGE_SIZE (頁大小) 為參數，動態生成上述 SQL，即可輕鬆實現通用分頁功能。

效能提醒

• 內層 ROWNUM <= N 能有效限制排序範圍，N 越小效能越好。
• 大數據量分頁建議搭配索引覆蓋查詢，避免全表排序。
• Oracle 12c+ 可直接使用 OFFSET ... FETCH NEXT ... ROWS ONLY 標準語法，更簡潔高效。