WITH 句 / 共通テーブル式 (Common Table Expression : SQL-99)
WITH 句は、副問い合わせに名前を付ける句である。この名前を付けられた副問い合わせは、「インラインビュー」 または 「一時表」として使用される。
同じインラインビューを何度も SQL の中に記述するよりも WITH 句で問い合わせに名前付けを行なうことで視認性が高まり、場合によりパフォーマンスが向上する可能性がある。
特に WITH では一時表を作ることがあるという特殊性(※)から実行計画に Merge Join Cartesian（直積）が出てビックリすることがあるかもしれない。
WITH
FUNCTION function_name ...
RETURN ...
END;
PROCEDURE procedure_name ...
…
END; -- 区切りのカンマはいらない
-- クエリーブロックより PL/SQL の定義を先行させる。以降は同じ。
query_name AS …
WITH 内がプロシージャの定義だけでも良い。
WITH と インラインビュー
同じインラインビューを同一 SQL 内で多用した場合と WITH 句を使用した場合の違いは WITH 句では、その結果を一時表として保存される可能性がある点である。
言い換えると WITH の結果セットをもつワークテープルがあるかのように振舞う場合がありインラインビューよりパフォーマンスが良くなる可能性がある。
(※) one と ones_next の記述順序は逆にできず 「ORA-32031: WITH句の問合せ名の参照が無効です。」 となる。
主問い合わせで ones_next を参照する
WITH
one AS (
select 1 one_col1, 10 one_col2 from dual
),
ones_next AS (
select one_col1 + 1 from one
)
select * from ones_next;
ONE_COL1+1
----------
2
簡単な再帰例 （≠ループ処理）
ones_next が 自分自身 ones_next を再帰的に参照する例
WITH ones_next (col) AS (
select 1 from dualunion all
select 1 + col from ones_next where col < 5
)
select * from ones_next;
COL
----------
1
2
3
4
5
今年の曜日付きカレンダーを作る
WITH calendar_dy (dd, dy) AS ( /* 'yyyy' の部分をすべて 'mm' にすれば今月分になる */selecttrunc(sysdate, 'yyyy'), to_char(trunc(sysdate, 'yyyy'), 'dy') from dualunion all
select dd+1,to_char(dd+1,'dy') from calendar_dy where trunc(dd,'yyyy')=trunc(dd+1,'yyyy')
)
select * from calendar_dy;
DD DY
---------- ------------
2017-01-01 日
2017-01-02 月
2017-01-03 火
.....
カラムの別名と再帰がサポートされている。
PL/SQL を使ったファンクションとプロシージャの組み込みがサポートされている。