common-table-expression

共通表式を使用すると、結果表を table-name (表名) によって定義して、 それをその後に続く全選択の任意の FROM 節に指定できるようにすることができます。

構文図を読むビジュアル構文図をスキップ table-name (,列名)1 AS ( 全選択 )
注:
  • 1 共通表式が再帰的である場合、または全選択の結果として列名が重複する場合は、列名を指定する必要があります。

単一の WITH キーワードの後に、複数の共通表式を指定することができます。 指定する各共通表式は、 それ以降の共通表式の FROM 節の中でも名前によって参照することができます。

列のリストを指定する場合、その中の列の名前の数は、 全選択の結果表内の列数と同じ数でなければなりません。 各 column-name (列名) は、固有、しかも非修飾でなければなりません。 これらの列名を指定しない場合、 共通表式の定義に使用された全選択の選択リストから名前が得られます。

共通表式の table-name は、 同じステートメントの他の共通表式の table-name すべてと異なるものでなければなりません (SQLSTATE 42726)。 共通表式が INSERT ステートメントに指定されている場合、table-name を、 その挿入の対象である表またはビューの名前にすることはできません (SQLSTATE 42726)。 共通表式の table-name は、その全選択を通じて、 どの FROM 節の中でも表名として指定することができます。 共通表式の table-name は、(カタログの中で) 同じ修飾名の既存の表、ビュー、 または別名をオーバーライドするものとなります。

同じステートメントの中に複数の共通表式が定義されている場合、 共通表式相互間の循環参照があってはなりません (SQLSTATE 42835)。 循環参照 が生じるのは、 2 つの共通表式 dt1 と dt2 が作成された場合に、 dt1 が dt2 を参照し、 dt2 が dt1 を参照するようになる場合です。

共通表式の全選択の FROM 節に data-change-table-reference が入っている場合は、 その共通表式がデータを変更するように指示を受けます。 データを変更する共通表式は、 その共通表式がステートメントの別の箇所で使用されているかどうかに関係なく、 ステートメントが処理されるときに常に評価されます。 データの読み取りまたは変更を行う共通表式が 1 つでもある場合は、 すべての共通表式が発生した順に処理されます。 そして、データの読み取りまたは変更を行う各共通表式は、制約やトリガーもすべて含めて完全に実行されます。 1 つの共通表式が完全に実行されるまで、次の共通表式は実行されません。

共通表式は、 CREATE VIEW および INSERT の全選択 (fullselect) の前でもオプションとして使用できます。

共通表式は、以下の場合に使用できます。
  • ビューの代わりに使用して、 ビューが作成されないようにするため (ビューを一般的に使用する必要がなく、 定位置の更新や削除を使わない場合)
  • スカラー副選択や可変の関数または外部処理を伴う関数から得られる列によりグループ化できるようにする場合
  • 必要な結果表がホスト変数に基づいたものである場合
  • 同じ結果表を全選択で共有する必要がある場合
  • 結果表を再帰的に派生させる必要がある場合
  • 照会の中で複数の SQL データ変更ステートメントを処理する必要がある場合

共通表式の全選択の FROM 節の中にそれ自体への参照が入っている場合、 その共通表式は、再帰的共通表式 です。 再帰処理を使用した照会は、部品表 (BOM)、予約システム、 およびネットワーク・プランなどのアプリケーションをサポートする上で役立ちます。

再帰的共通表式では、以下のことが成り立っていなければなりません。

  • 再帰サイクルの一部をなす各全選択は、 SELECT または SELECT ALL で始まっていなければなりません。 SELECT DISTINCT は使用できません (SQLSTATE 42925)。 また、集合の和を求める場合には UNION ALL を使用する必要があります (SQLSTATE 42925)。
  • 共通表式の table-name (表名) の後には、 必ず列名を指定する必要があります (SQLSTATE 42908)。
  • 最初の UNION の最初の全選択 (初期化全選択) には、 どの FROM 節の共通表式のどの列に対する参照も入っていてはなりません (SQLSTATE 42836)。
  • 共通表式の列名が反復全選択において参照される場合、 その列のデータ・タイプ、長さ、およびコード・ページは、 初期化全選択に基づいて決められます。 反復全選択の中の対応する列のデータ・タイプと長さは、 初期化全選択に基づいて決められたデータ・タイプと長さと同じでなければならず、 コード・ページは一致していなければなりません (SQLSTATE 42825)。 ただし、文字ストリング・タイプの場合は、 2 つのデータ・タイプの長さが違っても構いません。 この場合、反復全選択の列の長さは、 初期化全選択から決められた長さに常に割り当て可能な長さでなければなりません。
  • 再帰サイクルの一部をなす各全選択には、 集約関数、GROUP BY 節、または HAVING 節が入っていてはなりません (SQLSTATE 42836)。

    これらの全選択の FROM 節には、 再帰サイクルの一部である共通表式に対する参照を多くても 1 つまで組み入れることができます (SQLSTATE 42836)。

  • 反復全選択および全体再帰的全選択には、 ORDER BY 節を組み入れることはできません (SQLSTATE 42836)。
  • 副照会 (スカラーまたは定量化されたもの) が再帰サイクルの一部であってはなりません (SQLSTATE 42836)。
再帰的共通表式を開発するときには、 無限再帰サイクル (ループ) が作成される恐れについて常に注意してください。 再帰サイクルは、必ず停止するようにしてください。 これは、関係しているデータが循環している場合に特に重要です。 再帰的共通表式には、無限ループを防止する述部を組み入れるようにしてください。 再帰的共通表式には、以下のものを組み入れるようにしてください。
  • 反復全選択の中に、定数ずつ増分される整数列。
  • "counter_col < constant" または "counter _col < :hostvar" の形式の 反復全選択の WHERE 節の述部。

この構文が再帰的共通表式に見つからないなら、警告が出されます (SQLSTATE 01605)。