collection-derived-table

collection-derived table を使用すると、1 つ以上の配列のエレメントを中間結果表の別々の行の列値に変換できます。

コレクション派生テーブル：

構文図を読むビジュアルシンタックスダイアグラムをスキップするUNNEST( , 通常配列式連想配列式 )WITH ORDINALITY1correlation-clause
注:
  • 1 UNNESTの引数が通常の配列式の場合のみ、順序指定が可能。 WITH ORDINALITY も指定されている場合、associative-array-expression は指定できません。
WITH ORDINALITY
結果表の最後の列として、データ・タイプ INTEGER の余分な列が返されることを指定します。 この列には、配列内のエレメントの位置が入ります。
correlation-clause
コレクション型導出表の結果表の表指定子として使用される相関名と、結果表の列名のリストを指定します。 この相関名は、結果表の列に対する参照を修飾するために使用できます。

結果列は、SELECT リストや残りの副選択で、相関文節で列に指定された名前を使用して参照できます。

コレクション型導出表は、配列がサポートされるコンテキストにおいて、FROM 文節で表参照として指定できます。

中間結果表は、以下のように派生します。

  • 通常配列を返す単一式が指定されると、中間結果表は、配列エレメントのデータ・タイプと同じ列データ・タイプの単一列の表になります。
  • 通常配列を返す複数式が指定されると、以下のようになります。
    • 最初の配列は結果表の最初の列、2 番目の配列は 2 番目の列、以降同様になります。
    • 各列のデータ・タイプは、対応する配列引数の配列エレメントのデータ・タイプと一致します。
    • 配列のカーディナリティーが同一でない場合、結果として生成される表のカーディナリティーは、最大のカーディナリティーを持つ配列と同じになります。
    • 行の配列指標の値が対応する配列のカーディナリティーより大きい場合は常に、表の列値は NULL 値に設定されます。 つまり、各配列が、2 つの列 (1 つは添字用の列、もう 1 つはデータ用の列) を持つ表として見なされる場合、UNNEST は添字の等価を結合述部として使用して、配列間で外部結合を実行します。
  • 連想配列を返す単一の associative-array-expression または array-function-invocation が指定されている場合は、以下のようになります。
    • 中間結果表は、最初の列のデータ・タイプが配列添字のデータ・タイプと同じであり、2 番目の列のデータ・タイプが配列エレメントのデータ・タイプと同じである 2 つの列を持つ表です。
    • 最初の列には、配列内のエレメントの添字が入ります。
    • 2 番目の列には、配列内のエレメントが入ります。
    • correlation-clause で列に指定された名前を使用して、これらの列を SELECT リストや残りの副選択で参照することができます。
  • すべての引数がヌル配列である場合、結果は空の表になります。

UNNEST の呼び出しによって生成される中間結果表は、結果的に 750 列よりも大きくなってはなりません。

array-function-invocation は、通常配列タイプまたは連想配列タイプを返す関数に解決される関数呼び出しです。 array-function-invocation には、共通表式の列に対する参照が含まれていてはなりません。

通常配列式
以下の項目のいずれか 1 つを指定します。
  • SQL 変数
  • SQL パラメーター
  • 変更の開始通常配列タイプのグローバル変数変更の終わり
  • array-function-invocation
  • 通常配列タイプへのパラメーター・マーカーの CAST 指定
連想配列式
以下の項目のいずれか 1 つを指定します。
  • SQL 変数
  • SQL パラメーター
  • 変更の開始連想配列タイプのグローバル変数変更の終わり
  • array-function-invocation
  • 連想配列タイプへのパラメーター・マーカーの CAST 指定

UNNEST 指定によって生成される結果列の名前は、collection-derived-table 文節の correlation-clause の一部として指定することができます。

例 1: PHONENUMBERS が、通常配列として定義されたユーザー定義配列タイプだとします。 RECENT_CALLS は、PHONENUMBERS タイプの配列変数です。 RECENT_CALLS には、以下の電話番号が入ります。

  • 9055553907
  • 4165554213
  • 4085553678

以下の SELECT ステートメントは、UNNEST を使用して配列から電話番号のリストを取得します。

SELECT T.ID, T.NUM
 FROM UNNEST(RECENT_CALLS) WITH ORDINALITY AS T(NUM, ID);

WITH ORDINALITY 文節は、結果表に配列内の各配列エレメントの順序位置を反映する追加の列が含まれることを指定します。 この追加の列は、UNNEST 操作からの結果表の最後の列になります。 WITH ORDINALITY 文節に続く相関文節は、追加の列の名前が ID、配列エレメント列の名前が NUM になることを指定します。 これらの列の名前は、照会の SELECT リストで明示的に参照できます。 この例の SELECT リストは、UNNEST の結果から列を再配列します。 結果の表は、次のようになります。

ID NUM
1 9055553907
2 4165554213
3 4085553678

SELECT ステートメントで、UNNEST 操作の結果生じる列は SELECT リストで再配列したので、各配列エレメントの位置を表す列は、最終結果表の最初の列になります。

例 2: PERSONAL_PHONENUMBERS が連想配列として定義されるユーザー定義配列タイプであると想定します。 PHONELIST は PERSONAL_PHONENUMBERS タイプの配列変数です。 値は、以下のステートメントにより、PHONELIST のエレメントに代入されます。
SET PHONELIST['Home'] = '4443051234';
SET PHONELIST['Work'] = '4443052345';
SET PHONELIST['Cell'] = '4447893456';

以下の SELECT ステートメントが実行されます。

SELECT T.ID, T.PHONE
 FROM UNNEST(PHONELIST) AS T(ID, PHONE);

結果表は、次のようになります (行の順序は異なる可能性があります)。

ID 電話番号
セル 4447893456
Home 4443051234
作業 4443052345

例 3: PHONES および IDS が、同じカーディナリティーの配列値を持つ 2 つの SQL 変数であると想定します。 以下の SQL ステートメントは、配列のコンテンツを 3 つの列 (配列ごとに 1 つずつ、位置用に 1 つ) と、配列エレメントごとに 1 つの行を持つ表に変換します。

以下の SELECT ステートメントが実行されます。

SELECT T.PHONE, T.ID, T.INDEX FROM UNNEST(PHONES, IDS)
WITH ORDINALITY AS T(PHONE, ID, INDEX)
ORDER BY T.INDEX;