Oracle® 非公式 SQL, PL/SQL & DBA's ガイドサイト

表関数を手軽に使う活用例（コレクション・ネスト解除）

表関数は
select .. table(function_name) のように 表関数の引数に関数の結果を用いる方法 もあるが

select .. from table(collection) のように表に格納できるタイプの コレクション型 を用いて コレクションに対して様々な操作をすることも可能である。
（コレクション・ネスト解除、または、コレクション・アンネストと呼ばれる）

コレクション型の仮想テーブルに対して SQL 文を使用することで SQL の標準関数を利用することができる ＝処理プログラムを記述せずに済み SQL の膨大なライブラリ群を利用できる。つまり、配列を使って集計操作、分析関数、グループ化、並び替えまでも利用できるということである。

ここまで簡単に使うことができる表関数（コレクション・ネスト解除）機能であるが、場合によっては面倒な準備が１つ必要である。 それは、SQL 文に組み込むためには スキーマレベルのコレクションの定義 (CREATE TYPE) が必要であるということ。
これをローカルレベルの定義で行なうと PLS-00642: SQL文ではローカル・コレクション型は使用できません エラーが発生する。

スキーマレベルのコレクション定義には別の スキーマ・オブジェクト のメタ情報を使用できない。そのため 組み込みデータ型 を使って一から定義しなければならない。
オブジェクト表 (CRAETE TABLE OF type_name 〜) のような CREATE TYPE OF {table_name|subquery} があったら、うれしいところである。

IN 条件に複数（1000 要素以上）のバインド変数を割り当てる方法

IN 条件（IN 句）においてバインド変数を使用する場合、1つのプレースホルダには1つの値のみ割り当てることができ、複数の値をバインドすることができない。 しかし、以下の方法を使用することで IN 条件に複数の値をバインドすることと同じ結果(※)を実現できる。（この例では パラメータ付きのカーソル を使用しているが仕組みは一緒のはず）

ネストした表に TABLE ファンクションを使用することでコレクション属性のネスト解除してフラット化して IN に組み込んでいる。副問い合わせ式となっているために リテラル表現（スカラー式） の場合に制限される 1000 要素の制限にもかからない。（このサンプルでは 2000 個の要素を含めている）

IN において要素数が 1000を超えると ORA-01795: リストに指定できる式の最大数は1000です。というエラーが発生する。

(※) 結果は同じでもプロセスが異なる可能性がある。

IN (リテラル...) と OR は コスト計算次第で「OR 拡張」されることによって複数のステートメントに分割して UNION ALL 結合することで効率よい形に再構築される可能性があるが、IN (副問い合わせ) の場合にも 「OR 拡張」されるかまでは確認していません。（想像ですが OR 拡張にリライトする作業は相当難しそう）

• IN 条件 + コレクション・アンネストによる副問い合わせの例

CREATE OR REPLACE TYPE RIVUS.tNumberList IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE RIVUS.COLLECTION_BIND
IS
  vNums  tNumberList;
 
  CURSOR cIDName(vNums tNumberList) IS
    SELECT USER_ID, USER_NAME
      FROM USER_MASTER
    WHERE
      USER_ID IN (SELECT * FROM TABLE(vNums))
    --            ↑ コレクション・アンネストによる副問い合わせ式
    ORDER BY USER_ID;
  TYPE tIDNAMES IS TABLE OF cIDName%ROWTYPE INDEX BY BINARY_INTEGER;
BEGIN
  vNums := tNumberList();
  vNums.EXTEND(2000);
  FOR i in 1..2000 LOOP
    vNums(i) := i;
  END LOOP;
 
  FOR vRec in cIDName(vNums)
  LOOP
    DBMS_OUTPUT.PUT_LINE(vRec.USER_ID || vRec.USER_NAME);
  END LOOP;
END;
/

配列に分析関数を適用する。配列をソートしてコレクションに戻す。

数列 vNums {100,50,30,15,-1,0} を昇順にソートし結果を vSorted 配列に代入する。 vTotal 配列にも同様に累積算の結果を代入する。 入力変数と出力変数を同じ変数にすると正しく動作しない。

CREATE OR REPLACE TYPE RIVUS.tNumberList IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE RIVUS.COLLECTION_SORT
IS
  vNums  tNumberList;
  vSorted  tNumberList;
  vTotal  tNumberList;
BEGIN
  vNums := tNumberList(100,50,30,15,1,0);
  SELECT COLUMN_VALUE, SUM(COLUMN_VALUE) OVER (ORDER BY COLUMN_VALUE)
    BULK COLLECT INTO vSorted, vTotal
    FROM TABLE(vNums)
  ORDER BY COLUMN_VALUE;
 
  FOR i in 1..vSorted.COUNT 
  LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(vSorted(i),'000')||' / '||TO_CHAR(vTotal(i), '000'));
  END LOOP;
END;
/
SQL> CALL COLLECTION_SORT();
000 / 000
001 / 001
015 / 016
030 / 046
050 / 096
100 / 196
コールが完了しました。

関連事項

• 表関数
• ネストした表（コレクション型）