【Oracle】OFFSET / FETCH でページネーションを実装する方法｜構文・バインド変数・ROWNUM 書き換え・ORDER BY の注意点まで解説

SELECT columns
FROM table_name
[WHERE conditions]
ORDER BY sort_column
OFFSET m {ROW | ROWS}
FETCH {FIRST | NEXT} n {ROW | ROWS} {ONLY | WITH TIES};

-- 給与の高い順で 11〜20 件目を取得（2 ページ目）
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- (1) 先頭 5 件だけ取得（OFFSET なし）
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

-- (2) 先頭 5 件 + 同率を含める
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

-- (3) 全体の 10% を取得
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;

-- (4) 11〜20 件目を取得（ページネーション）
SELECT * FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- (5) OFFSET のみ（FETCH なし: OFFSET 以降のすべての行）
SELECT * FROM employees
ORDER BY employee_id
OFFSET 10 ROWS;  -- 11 件目以降をすべて取得

-- :page_num = ページ番号（1 始まり）
-- :page_size = 1 ページあたりの件数

SELECT employee_id, last_name, salary
FROM employees
ORDER BY employee_id
OFFSET (:page_num - 1) * :page_size ROWS
FETCH NEXT :page_size ROWS ONLY;

-- 例: page_num=3, page_size=20 → OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
-- → 41〜60 件目を取得

DECLARE
    v_page_num  NUMBER := 3;
    v_page_size NUMBER := 20;
    v_offset    NUMBER;
    TYPE t_emp IS TABLE OF employees%ROWTYPE;
    v_emps t_emp;
BEGIN
    v_offset := (v_page_num - 1) * v_page_size;
    SELECT * BULK COLLECT INTO v_emps
    FROM employees
    ORDER BY employee_id
    OFFSET v_offset ROWS FETCH NEXT v_page_size ROWS ONLY;

    FOR i IN 1..v_emps.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_emps(i).employee_id || ': ' || v_emps(i).last_name);
    END LOOP;
END;
/

-- 方法(1): 別クエリで COUNT（シンプル）
SELECT COUNT(*) AS total_count FROM employees WHERE status = 'ACTIVE';
-- 総ページ数: CEIL(total_count / page_size)

SELECT * FROM employees WHERE status = 'ACTIVE'
ORDER BY employee_id
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- 方法(2): ウィンドウ関数で 1 クエリに統合
SELECT employee_id, last_name, salary,
       COUNT(*) OVER () AS total_count
FROM employees
WHERE status = 'ACTIVE'
ORDER BY employee_id
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;
-- 各行に total_count が付与される（全行をスキャンするためやや重い）

-- NG: salary が同じ行の順序が不定 → ページ切り替えで行が重複/欠落する
SELECT * FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- OK: 一意な列（employee_id）を ORDER BY の末尾に追加
SELECT * FROM employees
ORDER BY salary DESC, employee_id ASC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
-- salary が同じ行は employee_id 順で安定する → ページが安定

EXPLAIN PLAN FOR
SELECT * FROM employees
ORDER BY salary DESC, employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 典型的な実行計画:
-- | Id | Operation                | Name      |
-- |----|--------------------------|-----------||
-- |  0 | SELECT STATEMENT         |           |
-- |  1 |  VIEW                    |           |
-- |  2 |   WINDOW SORT PUSHED RANK|           |
-- |  3 |    TABLE ACCESS FULL     | EMPLOYEES |
-- WINDOW SORT PUSHED RANK: 上位 N 件を効率的に取得する最適化

-- 画面入力: page=3, size=20, sort=salary DESC
-- 検索条件: department_id = 10

SELECT employee_id, last_name, salary, hire_date
FROM employees
WHERE department_id = 10
ORDER BY salary DESC, employee_id
OFFSET (3 - 1) * 20 ROWS FETCH NEXT 20 ROWS ONLY;
-- → OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
-- → 41〜60 件目を取得

-- データ取得
SELECT employee_id, last_name, salary,
       COUNT(*) OVER () AS total_count
FROM employees
WHERE department_id = :dept_id
ORDER BY employee_id
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY;

-- API レスポンスのイメージ:
-- { "data": [...], "total": 157, "page": 3, "size": 20, "pages": 8 }

-- PL/SQL で 1000 件ずつバッチ処理
DECLARE
    v_offset NUMBER := 0;
    v_batch  NUMBER := 1000;
    v_count  NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM target_table WHERE status = 'PENDING';

    WHILE v_offset < v_count LOOP
        UPDATE target_table
        SET status = 'PROCESSED', updated_at = SYSDATE
        WHERE ROWID IN (
            SELECT ROWID FROM target_table
            WHERE status = 'PENDING'
            ORDER BY created_at
            OFFSET v_offset ROWS FETCH NEXT v_batch ROWS ONLY
        );
        COMMIT;
        v_offset := v_offset + v_batch;
    END LOOP;
END;
/

-- 給与ランキング 5 位の社員だけを取得
SELECT * FROM employees
ORDER BY salary DESC, employee_id
OFFSET 4 ROWS FETCH NEXT 1 ROW ONLY;
-- OFFSET 4 = 先頭 4 件スキップ → 5 件目の 1 行を取得