【Oracle】疑似列完全ガイド｜ROWNUM・ROWID・LEVEL・CONNECT_BY_ROOT・DUAL の使い方まで解説

-- NG: ORDER BY の後に ROWNUM でフィルタしようとしても正しく動かない
-- ORDER BY よりも ROWNUM の割り当てが先に行われるため、上位3件のソートにならない
SELECT employee_id, salary FROM employees WHERE ROWNUM <= 3 ORDER BY salary DESC;
-- ↑ ランダムな3件を取って、その3件を salary でソートする（上位3件ではない）

-- OK: サブクエリでソートしてから ROWNUM でフィルタする
SELECT employee_id, salary FROM (
    SELECT employee_id, salary FROM employees ORDER BY salary DESC
) WHERE ROWNUM <= 3;
-- ↑ 全件を salary 降順にソートしてから、最初の3件を取る（上位3件）

-- Oracle 12c 以降: FETCH FIRST N ROWS ONLY を推奨（最もシンプル）
SELECT employee_id, salary FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

-- ROWNUM を使ったページネーション（12c 未満）
-- ページ2（11〜20件目）を取得する例
SELECT * FROM (
    SELECT employee_id, salary, ROWNUM AS rn FROM (
        SELECT employee_id, salary FROM employees ORDER BY salary DESC
    ) WHERE ROWNUM <= 20  -- 上限
) WHERE rn >= 11;          -- 下限

-- 12c 以降のページネーション
SELECT employee_id, salary FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;  -- 11〜20件目

-- ROWNUM は 1 から始まるため ROWNUM = 0 や ROWNUM = 5 のような比較は0件になる
-- ROWNUM <= N のみが意図通りに動く（= / > / >= は最初の行で成立しないため0件になる）
SELECT * FROM employees WHERE ROWNUM = 1;   -- OK: 最初の1件
SELECT * FROM employees WHERE ROWNUM > 1;   -- 0件（最初の行は ROWNUM=1 なので条件不成立、以降は ROWNUM が2以上にならない）

-- ROWID の参照
SELECT ROWID, employee_id, last_name FROM employees WHERE department_id = 60;
-- ROWID の形式: AAAFvBAAFAAAABkAAA
-- AAAF = オブジェクト番号
-- vB  = 相対ファイル番号
-- AAAF = ブロック番号
-- AAA  = 行番号

-- ROWID を使って特定の行を高速に取得する（INDEX ROWID SCAN: 最速）
SELECT * FROM employees WHERE ROWID = 'AAAFvBAAFAAAABkAAA';

-- 重複行を削除する（ROWID を使って残す1行を特定する）
-- 同じ email が複数ある場合、1件だけ残して残りを削除する例
DELETE FROM customers
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)         -- 最小 ROWID（最も古い行）を残す
    FROM customers
    GROUP BY email            -- 重複チェックするキー列
);

-- または相関サブクエリを使う方法（大量データではパフォーマンスに注意）
DELETE FROM customers c
WHERE ROWID > (
    SELECT MIN(ROWID)
    FROM customers
    WHERE email = c.email
);

-- 重複行の確認（削除前に確認する）
SELECT email, COUNT(*), MIN(ROWID) AS keep_rowid
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

-- ROWID の文字列変換（DBMS_ROWID パッケージ）
SELECT
    DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS block_num,
    DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)   AS row_num,
    DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS file_num
FROM employees WHERE ROWNUM = 1;

-- LEVEL: 階層クエリの深さ（ルートが 1、その子が 2...）
SELECT
    LEVEL,
    employee_id,
    last_name,
    manager_id,
    LPAD(' ', (LEVEL - 1) * 3) || last_name AS tree_display   -- インデントで木構造を表示
FROM employees
START WITH manager_id IS NULL                 -- ルートの条件
CONNECT BY PRIOR employee_id = manager_id     -- 親子関係の定義
ORDER SIBLINGS BY last_name;                  -- 同一レベル内でのソート

-- CONNECT_BY_ROOT: ルートノードの値を取得する
SELECT
    employee_id,
    last_name,
    CONNECT_BY_ROOT last_name AS root_name   -- このノードのルートの名前
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- SYS_CONNECT_BY_PATH: ルートからの経路を文字列で返す
SELECT
    employee_id,
    last_name,
    SYS_CONNECT_BY_PATH(last_name, ' / ') AS full_path  -- 例: / King / Kochhar / Greenberg
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- CONNECT_BY_ISLEAF: 末端ノード（子がない）なら 1、そうでなければ 0
SELECT
    employee_id, last_name, LEVEL,
    CONNECT_BY_ISLEAF AS is_leaf   -- 1: 末端、0: 中間
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- CONNECT_BY_ISCYCLE: 循環参照を検出する（NOCYCLE オプションと組み合わせる）
SELECT employee_id, last_name, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;   -- 循環があってもエラーにならない

-- ORA_ROWSCN: 行が最後にコミットされた SCN（System Change Number）を返す
-- ※ デフォルトではブロック単位の SCN（ROWDEPENDENCIES 未設定の場合）
SELECT employee_id, last_name, ORA_ROWSCN,
       SCN_TO_TIMESTAMP(ORA_ROWSCN) AS last_committed_at
FROM employees
WHERE department_id = 60;

-- 最近変更された行を確認する（概算・ブロック単位の精度）
SELECT employee_id, last_name, SCN_TO_TIMESTAMP(ORA_ROWSCN) AS approx_updated_at
FROM employees
WHERE ORA_ROWSCN > TIMESTAMP_TO_SCN(SYSTIMESTAMP - INTERVAL '1' HOUR)
ORDER BY ORA_ROWSCN DESC;

-- ROWDEPENDENCIES テーブルオプション: 行レベルの SCN を記録する（精度向上）
-- 通常のテーブルはブロックレベルの SCN しか持たないため行レベルの精度はない
-- CREATE TABLE ... ROWDEPENDENCIES; で作成すると行ごとの SCN が保存される
-- 既存テーブルへの変更は DBMS_REDEFINITION が必要

-- 現在の日時を取得する
SELECT SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL;

-- 計算式の評価
SELECT 1 + 2 * 3 AS result FROM DUAL;
SELECT ROUND(3.14159, 2) AS pi FROM DUAL;

-- シーケンスの次の値を取得する
SELECT order_id_seq.NEXTVAL FROM DUAL;
SELECT order_id_seq.CURRVAL FROM DUAL;

-- 文字列・日付・数値の変換
SELECT TO_DATE('2026-04-09', 'YYYY-MM-DD') AS dt FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS ts FROM DUAL;

-- 条件式のテスト
SELECT CASE WHEN 5 > 3 THEN 'yes' ELSE 'no' END AS result FROM DUAL;
SELECT NVL(NULL, 'default') FROM DUAL;

-- 複数行を生成する（CONNECT BY LEVEL を使う便利なテクニック）
SELECT LEVEL AS n FROM DUAL CONNECT BY LEVEL <= 5;
-- → 1, 2, 3, 4, 5 という数値シーケンスを生成する

-- 月の初日〜末日のカレンダーを生成する
SELECT
    ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 0) + LEVEL - 1 AS calendar_date
FROM DUAL
CONNECT BY LEVEL <= EXTRACT(DAY FROM LAST_DAY(SYSDATE));

-- Oracle 12c 以降: DUAL を使わなくても計算できる（FROM 句が省略可能）
-- ただし Oracle では FROM DUAL が慣習