【Oracle】DUAL 表の使い方完全ガイド｜仕組み・FAST DUAL 最適化・PL/SQL での代替・他 RDBMS 比較・実務パターンまで解説

-- DUAL は 1 行 1 列のテーブル
SELECT * FROM DUAL;
-- 結果:
-- DUMMY
-- -----
-- X

-- 列の情報
DESC DUAL;
-- Name    Null?    Type
-- DUMMY            VARCHAR2(1)

EXPLAIN PLAN FOR SELECT SYSDATE FROM DUAL;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 実行計画:
-- | Id | Operation        | Name |
-- |----|------------------|------|
-- |  0 | SELECT STATEMENT |      |
-- |  1 |  FAST DUAL       |      |   <-- テーブルスキャンではない

SELECT 100 + 200 AS result FROM DUAL;          -- 300
SELECT 1000 * 1.08 AS tax_incl FROM DUAL;       -- 1080
SELECT POWER(2, 10) AS result FROM DUAL;         -- 1024
SELECT MOD(17, 5) AS remainder FROM DUAL;        -- 2
SELECT ROUND(3.14159, 2) AS pi FROM DUAL;        -- 3.14

SELECT SYSDATE FROM DUAL;                        -- 現在日時
SELECT SYSTIMESTAMP FROM DUAL;                    -- ミリ秒+タイムゾーン付き
SELECT CURRENT_DATE FROM DUAL;                   -- セッションタイムゾーンの現在日時

-- 日付計算
SELECT SYSDATE + 7 AS one_week_later FROM DUAL;  -- 1 週間後
SELECT SYSDATE - 30 AS thirty_days_ago FROM DUAL; -- 30 日前
SELECT ADD_MONTHS(SYSDATE, 3) AS three_months_later FROM DUAL;
SELECT LAST_DAY(SYSDATE) AS month_end FROM DUAL;  -- 今月末

SELECT UPPER('hello') AS result FROM DUAL;        -- HELLO
SELECT SUBSTR('Oracle Database', 1, 6) FROM DUAL; -- Oracle
SELECT LENGTH('ABCDE') AS len FROM DUAL;          -- 5
SELECT REPLACE('2026-03-28', '-', '/') FROM DUAL; -- 2026/03/28
SELECT LPAD(42, 5, '0') AS padded FROM DUAL;     -- 00042

SELECT USER FROM DUAL;                            -- 現在のユーザー名
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL;
SELECT SYS_CONTEXT('USERENV', 'HOST') FROM DUAL;
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL;

-- 次の値を取得（NEXTVAL）
SELECT seq_order_id.NEXTVAL FROM DUAL;

-- 現在の値を確認（CURRVAL: 同セッションで NEXTVAL を実行済みの場合のみ）
SELECT seq_order_id.CURRVAL FROM DUAL;

-- TO_CHAR: 日付を文字列に変換
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- TO_DATE: 文字列を日付に変換
SELECT TO_DATE('2026-03-28', 'YYYY-MM-DD') FROM DUAL;

-- TO_NUMBER: 文字列を数値に変換
SELECT TO_NUMBER('12345') FROM DUAL;

DECLARE
    v_now   DATE;
    v_user  VARCHAR2(30);
    v_calc  NUMBER;
BEGIN
    -- NG: DUAL 経由（不必要なSQL実行）
    -- SELECT SYSDATE INTO v_now FROM DUAL;
    -- SELECT USER INTO v_user FROM DUAL;

    -- OK: 直接代入（DUAL 不要、高速）
    v_now  := SYSDATE;
    v_user := USER;
    v_calc := 100 * 1.08;

    DBMS_OUTPUT.PUT_LINE(v_now || ' / ' || v_user || ' / ' || v_calc);
END;
/

-- Oracle 23c 以降: FROM DUAL を省略可能
SELECT 1 + 1;               -- OK（23c 以降）
SELECT SYSDATE;             -- OK（23c 以降）
SELECT USER;                -- OK（23c 以降）

-- 23c 未満では上記は ORA-00923 エラー
-- 23c 未満: SELECT 1 + 1 FROM DUAL; と書く必要がある

-- 1 から 10 までの連番を生成
SELECT LEVEL AS num FROM DUAL CONNECT BY LEVEL <= 10;

-- 指定範囲（2025年1月〜12月）の月初日一覧
SELECT ADD_MONTHS(DATE '2025-01-01', LEVEL - 1) AS month_start
FROM DUAL
CONNECT BY LEVEL <= 12;

-- アルファベット A〜Z を生成
SELECT CHR(64 + LEVEL) AS letter FROM DUAL CONNECT BY LEVEL <= 26;

-- 2026-03-01 から 2026-03-31 までの日付一覧
SELECT DATE '2026-03-01' + LEVEL - 1 AS cal_date
FROM DUAL
CONNECT BY LEVEL <= DATE '2026-03-31' - DATE '2026-03-01' + 1;

-- 今月の全日付
SELECT TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS cal_date
FROM DUAL
CONNECT BY LEVEL <= LAST_DAY(SYSDATE) - TRUNC(SYSDATE, 'MM') + 1;

-- UNION ALL で定数行を作る従来の方法
SELECT 'Active' AS status FROM DUAL UNION ALL
SELECT 'Inactive' FROM DUAL UNION ALL
SELECT 'Pending' FROM DUAL;

-- サンプルデータの生成
SELECT 1 AS id, 'Alice' AS name, 8000 AS salary FROM DUAL UNION ALL
SELECT 2, 'Bob', 6000 FROM DUAL UNION ALL
SELECT 3, 'Carol', 7500 FROM DUAL;

-- TO_CHAR の書式テスト
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;

-- 正規表現のテスト
SELECT REGEXP_SUBSTR('abc-123-def', '[0-9]+') AS result FROM DUAL;  -- 123

-- CASE 式のテスト
SELECT CASE WHEN 1 = 1 THEN 'TRUE' ELSE 'FALSE' END AS result FROM DUAL;

-- NVL / COALESCE のテスト
SELECT NVL(NULL, 'default') AS result FROM DUAL;  -- default

-- テーブルに条件に合う行が 1 件でもあるか高速に確認
SELECT 1 FROM DUAL
WHERE EXISTS (
    SELECT 1 FROM orders WHERE status = 'PENDING'
);
-- 1 行返れば存在、0 行なら不在

-- PL/SQL での活用
DECLARE
    v_exists NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_exists FROM DUAL
    WHERE EXISTS (SELECT 1 FROM orders WHERE status = 'PENDING');
    IF v_exists = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Pending orders exist');
    END IF;
END;
/