スキーマレベルのコレクション定義には別の スキーマ・オブジェクト のメタ情報を使用できない。そのため 組み込みデータ型 を使って一から定義しなければならない。
オブジェクト表 (CRAETE TABLE OF type_name 〜) のような CREATE TYPE OF {table_name|subquery} があったら、うれしいところである。
IN 条件に複数（1000 要素以上）のバインド変数を割り当てる方法
IN 条件（IN 句）においてバインド変数を使用する場合、1つのプレースホルダには1つの値のみ割り当てることができ、複数の値をバインドすることができない。（⇒ 動的SQL（システム固有の動的 SQL））
しかし、以下の方法を使用することで IN 条件に複数の値をバインドすることと同じ結果(※)を実現できる。（この例では パラメータ付きのカーソル を使用しているが仕組みは一緒のはず）
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;
/
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
コールが完了しました。
