Top > SQL 入門 > WITH

WITH 句 / 共通テーブル式 (Common Table Expression : SQL-99)

WITH 句は、副問い合わせに名前を付ける句である。この名前を付けられた副問い合わせは、「インラインビュー」 または 「一時表」として使用される。
同じインラインビューを何度も SQL の中に記述するよりも WITH 句で問い合わせに名前付けを行なうことで視認性が高まり、場合によりパフォーマンスが向上する可能性がある。

特に WITH では一時表を作ることがあるという特殊性(※)から実行計画に Merge Join Cartesian(直積)が出てビックリすることがあるかもしれない。

しかし、これは「大きな表と小さな表の結合」+「大きな表と別な小さな表の結合」の検索処理より
「小さな表同士の直積結合」+「直積した表と大きな表の結合」を選択した方が高速であるとオプティマイザが算出した結果なので涼しい顔で対応しよう。

(※) 非公式な MATERIALIZE、INLINE ヒント句をコストは高いが件数は常に少ないと把握している問合せブロックにつけて比較してみる。

WITH 句 の構文

  • 構文 〜 Oracle 11g
    WITH
      query_name AS ( select col [, col2 …] … ) [, query_name2 AS … ]
  • 構文 Oracle 11g Release2 カラムの別名と再帰がサポートされている。
    WITH
      query_name [ ( col1_alias [,col2_alias …] ) ] AS ( select col [, col2 …] 〜 )
      [
        SEARCH { DEPTH | BREADTH }  FIRST BY
               col1_alias [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, col2_alias …]
         SET ordering_col
      ] [
        CYCLE col1_alias [,col2_alias …]
         SET cycle_mark_c_alias TO cycle_value DEFAULT no_cycle_value
      ]
      [, query_name2 AS … ]

DEPTH : 深さ優先探索 | BREADTH : 幅優先探索 
cycle_value : サイクルしているときの値 | no_cycle_value : サイクルしていないときの値

  • 構文 Oracle 12c PL/SQL を使ったファンクションとプロシージャの組み込みがサポートされている。
    インラインでの表関数、集計関数の作成はできない。(12.2.0.1.0 時点)
    WITH
      FUNCTION function_name ...
       RETURN ...
      END;
      PROCEDURE procedure_name ...
       …
      END; -- 区切りのカンマはいらない
      -- クエリーブロックより PL/SQL の定義を先行させる。以降は同じ。
      query_name AS …

WITH 内がプロシージャの定義だけでも良い。

WITH と インラインビュー

同じインラインビューを同一 SQL 内で多用した場合と WITH 句を使用した場合の違いは WITH 句では、その結果を一時表として保存される可能性がある点である。
言い換えると WITH の結果セットをもつワークテープルがあるかのように振舞う場合がありインラインビューよりパフォーマンスが良くなる可能性がある。

明示的に「SQL 問合せ結果キャッシュ」のヒントを利用することで、コストは高いが件数の少ない問い合わせの結果をオンメモリのキャッシュ化して効率よくすることも可能。こちらはインラインビューでも可能な方法である。

RESULT_CACHE ヒント Oracle 11g

WITH 
 mysubquery AS  {
   select /*+ RESULT_CACHE */ col from table where ...
 }
 …

WITH 使用例

クエリーを複数記述するにはカンマ区切りで記述するが、それぞれが独立していても参照(依存)関係があっても良い。

  1. one という名前の副問い合わせを select 1 from dual で作る。
  2. ones_next という名前の副問い合わせを one の問い合わせを使って作る。(※)

(※) 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

簡単な再帰例 (≠ループ処理) Oracle 11g Release2

ones_next が 自分自身 ones_next を再帰的に参照する例

WITH ones_next (col) AS (
  select 1 from dual
  union all
  select 1 + col from ones_next where col < 5
)
select * from ones_next;
 
       COL
----------
         1
         2
         3
         4
         5

今年の曜日付きカレンダーを作る Oracle 12c

WITH calendar_dy (dd, dy) AS ( /* 'yyyy' の部分をすべて 'mm' にすれば今月分になる */
  select trunc(sysdate, 'yyyy'), to_char(trunc(sysdate, 'yyyy'), 'dy') from dual
  union 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 火
.....

Oracle 11g において 上の記述は SYSDATE と DATE 型の データタイプコード が異なるためか
「ORA-01790: 式には対応する式と同じデータ型を持つ必要があります」 となる。
書き換え例

WITH dn(n) as (
  select 0 from dual union all
  select n + 1 from dn
  where n < add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate, 'yyyy') - 1
)
select trunc(sysdate, 'yyyy') + n from dn;

Oracle 10g で再帰を使うと「ORA-32033: 列はサポートされていません。」 とエラーになる。

ストアドしないファンクションを組み込んだ月締めカレンダーの例 Oracle 12c

当月末日締め、翌月 30日まで支払いの場合における任意の期間の日付、締め日と支払日の一覧(時分秒は切り捨て)

  • 締め日と支払日にはインラインの payday(対象日付, 締め日, 支払日, 支払猶予月) ファンクションを使用

参考 ⇒ カレンダー を作り、営業日で前倒し、後ろ倒しを考慮する 祝日、休日カレンダーの実用的な使用例

WITH
 function payday (
   dd date, closing_dd positive := NULL, usance_dd positive := NULL, usance_mm natural:= 1
 )
 return date
 is vDate DATE;
 begin
  if (closing_dd is null) then return dd; end if;
  vDate := 
    case when extract(day from dd) <= closing_dd then add_months(trunc(dd, 'mm'), usance_mm)
    else add_months(trunc(dd, 'mm'), usance_mm + 1)
  end;
  return least(vDate + nvl(usance_dd, closing_dd) - 1, last_day(vDate));
  end;
  cal (dd) AS (
    select date '2018-01-25' from dual
    union all
    select dd + 1 from cal where dd < '2018-02-02'
  )
 select dd "日付",
  to_char(payday(dd, 31, usance_mm => 0), 'yyyymm') "集計月",
  payday(dd, 31, usance_mm => 0) "締め日", payday(dd, 31, 30) "支払日"
 from cal 
/
 
日付       集計月   締め日     支払日
---------- -------- ---------- ----------
2018-01-25 201801   2018-01-31 2018-02-28
2018-01-26 201801   2018-01-31 2018-02-28
2018-01-27 201801   2018-01-31 2018-02-28
2018-01-28 201801   2018-01-31 2018-02-28
2018-01-29 201801   2018-01-31 2018-02-28
2018-01-30 201801   2018-01-31 2018-02-28
2018-01-31 201801   2018-01-31 2018-02-28
2018-02-01 201802   2018-02-28 2018-03-30
2018-02-02 201802   2018-02-28 2018-03-30
 
9行が選択されました。

一部が PL/SQL になっており必然的に SQL 中に セミコロン(;) が含まれる文になるので SQL*Plus から SQL を実行するにはスラッシュ(/) を使用する。
最後に入力する セミコロン(;) とスラッシュ(/) の意味

パラメータのデータ型を positiveN / naturalN で チェックの手抜きを画策 …
「ORA-03113: 通信チャネルでend-of-fileが検出されました」とエラーになる (12.2.0.1.0 時点)
POSITIVEN / NATURALN (PL/SQL 固有のデータタイプ )

インラインの PL/SQL と既存ストアドファンクションとの関係

ストアドしないファンクションを組み込んだ例」において、以下のようなユーザー定義の同名ストアドファンクションがあった場合、エラーにはならず WITH 句側のインラインのローカル・サブプログラムが実行される。
rivus.payday(dd) のように自スキーマ名を明示するとストアドファンクション側を呼び出すことができる。
標準関数と同名のファンクションを定義しても、標準関数への オーバーロード は発生しない(標準関数側が呼びだされる。12.2.0.1.0 時点)

create or replace function payday (dd date)
return date is begin
  return dd + 1;
end;

補足:スタンドアロン・サブプログラムはオーバーロードを使用することはできない(パッケージはできる)

 


副問い合わせ 関連

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle のライセンスがわからない…
Oracle Direct (ネットで聞いても最後はここで要確認)