PL/SQLの最適化とチューニング

12 PL/SQLの最適化とチューニング

この章では、PL/SQLコンパイラでコードがどのように最適化されるかを説明し、効率的なPL/SQLコードを記述する方法と既存のPL/SQLコードを改善する方法について説明します。

ここでのトピック

関連項目:

カーソル変数のデメリットの詳細は、『Oracle Database開発ガイド』を参照してください。

12.1 PL/SQLオプティマイザ

Oracle Database 10gリリース1より前のPL/SQLコンパイラは、パフォーマンスの向上のための変更を適用せずに、作成されたソース・テキストをシステム・コードに変換していました。今回のリリースのPL/SQLでは、コードのパフォーマンスが向上するように再調整する機能を持つオプティマイザが使用されます。

このオプティマイザは、デフォルトで使用可能です。まれに、オプティマイザのオーバーヘッドによって、非常に大規模なアプリケーションをコンパイルするときに時間が長くかかる場合がありますが、この場合、(デフォルト値2のかわりに)コンパイル・パラメータPLSQL_OPTIMIZE_LEVEL=1を設定して、最適化レベルを低くすることができます。また、非常にまれですが、PL/SQLによって予測より早く例外が呼び出されたり、例外が1つも呼び出されなくなる場合があります。PLSQL_OPTIMIZE_LEVEL=1に設定すると、コードは再調整されません。

関連項目:

PLSQL_OPTIMIZE_LEVELコンパイル・パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
PLSQL_OPTIMIZE_LEVELコンパイル・パラメータの変更例については、『Oracle Database開発ガイド』を参照してください。
静的ディクショナリ・ビューALL_PLSQL_OBJECT_SETTINGSの詳細は、『Oracle Databaseリファレンス』を参照してください。

12.1.1 サブプログラムのインライン化

コンパイラが実行できる最適化の1つとして、サブプログラムのインライン化があります。

サブプログラムのインライン化によって、サブプログラムの起動は、起動先サブプログラムのコピーに置き換えられます(起動先と起動元のサブプログラムが同じプログラム・ユニット内に存在する場合)。サブプログラムのインライン化を実行できるようにするには、PLSQL_OPTIMIZE_LEVELコンパイル・パラメータのデフォルト値(2)を受け入れるか、またはこのパラメータを3に設定します。

PLSQL_OPTIMIZE_LEVEL=2の場合、次のようにINLINEプラグマを使用して、インライン化する各サブプログラムを指定する必要があります。

PRAGMA INLINE (subprogram, 'YES')

subprogramがオーバーロードされた場合、前述のプラグマは、指定された名前を持つすべてのサブプログラムに適用されます。

PLSQL_OPTIMIZE_LEVEL=3の場合、PL/SQLコンパイラは、サブプログラムをインライン化する機会を探ります。インライン化するサブプログラムをユーザーが指定する必要はありません。ただし、(前述の構文に従って)INLINEプラグマを使用し、サブプログラムのインライン化に高い優先順位を指定することができ、その後、他の考慮事項または制限のためインライン化が望ましくない場合を除き、コンパイラによってサブプログラムがインライン化されます。

特定のサブプログラムがインライン化されている場合は、ほぼ常にパフォーマンスが向上します。ただし、コンパイラは最適化プロセスの初期の段階でサブプログラムをインライン化するため、サブプログラムのインライン化によって、後の段階でのより強力な最適化が妨げられる可能性があります。

サブプログラムのインライン化によって特定のPL/SQLプログラムのパフォーマンスが低下する場合は、PL/SQL階層型プロファイラを使用して、インライン化を無効にするサブプログラムを識別します。サブプログラムのインライン化を無効にするには、INLINEプラグマを次のように使用します。

PRAGMA INLINE (subprogram, 'NO')

INLINEプラグマは、直後にある宣言または文および一部の種類の文にのみ影響します。

INLINEプラグマが宣言の直前にある場合は、次のものに影響します。

その宣言に指定されたサブプログラムのすべての起動
その宣言内のすべての初期化値(レコードのデフォルトの初期化値を除く)

INLINEプラグマは、次に示すいずれかの文の直前にある場合、その文に指定されたサブプログラムのすべての起動に影響します。

代入
CALL
条件付き
CASE
CONTINUE WHEN
EXECUTE IMMEDIATE
EXIT WHEN
LOOP
RETURN

INLINEプラグマは、前述のリストにない文には影響しません。

複数のプラグマが、同じ宣言または文に影響する場合があります。各プラグマは、文に対して独自の影響を及ぼします。PRAGMA INLINE(subprogram,'YES')およびPRAGMA INLINE(identifier,'NO')に同じsubprogramが含まれている場合、'NO'は'YES'をオーバーライドします。1つのPRAGMA INLINE(subprogram,'NO')は、任意の数のPRAGMA INLINE(subprogram,'YES')をオーバーライドしますが、これらのプラグマの順序は重要ではありません。

関連項目:

PL/SQL階層プロファイラの詳細は、『Oracle Database開発ガイド』を参照してください
PLSQL_OPTIMIZE_LEVELコンパイル・パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。
静的ディクショナリ・ビューALL_PLSQL_OBJECT_SETTINGSの詳細は、『Oracle Databaseリファレンス』を参照してください。

例12-1 サブプログラムのインライン化の指定

この例では、PLSQL_OPTIMIZE_LEVEL=2の場合、INLINEプラグマは、プロシージャの起動p1(1)およびp1(2)には影響しますが、プロシージャの起動p1(3)およびp1(4)には影響しません。

PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'YES');
x:= p1(1) + p1(2) + 17;    -- These 2 invocations to p1 are inlined
...
x:= p1(3) + p1(4) + 17;    -- These 2 invocations to p1 are not inlined
...

例12-2 オーバーロードされたサブプログラムのインライン化の指定

この例では、PLSQL_OPTIMIZE_LEVEL=2の場合、INLINEプラグマは、p2という名前の両方のファンクションに影響します。

FUNCTION p2 (p boolean) return PLS_INTEGER IS ...
FUNCTION p2 (x PLS_INTEGER) return PLS_INTEGER IS ...
...
PRAGMA INLINE(p2, 'YES');
x := p2(true) + p2(3);

...

例12-3 サブプログラムの非インライン化の指定

この例では、INLINEプラグマは、プロシージャの起動p1(1)およびp1(2)には影響しますが、プロシージャの起動p1(3)およびp1(4)には影響しません。

PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'NO');
x:= p1(1) + p1(2) + 17;    -- These 2 invocations to p1 are not inlined
...
x:= p1(3) + p1(4) + 17;    -- These 2 invocations to p1 might be inlined
...

例12-4 PRAGMA INLINE ... 'NO'によるPRAGMA INLINE ... 'YES'のオーバーライド

この例では、2番目のINLINEプラグマは1番目と3番目のINLINEプラグマをオーバーライドします。

PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'YES');
PRAGMA INLINE (p1, 'NO');
PRAGMA INLINE (p1, 'YES');
x:= p1(1) + p1(2) + 17;    -- These 2 invocations to p1 are not inlined
...

12.2 チューニングの候補

次のようなPL/SQLコードは、ほぼ確実にチューニングのメリットを得ることができます。

新しいPL/SQL言語機能を利用していない古いコード。

ヒント:

古いコードのチューニングに取りかかる前に、現在のシステムのベンチマークを取り、プログラムによって起動される古いサブプログラムをプロファイルします(「PL/SQLプログラムのプロファイルおよびトレース」を参照)。PL/SQLオプティマイザ(「PL/SQLオプティマイザ」を参照)の各種の自動最適化機能により、チューニングの実行前にパフォーマンスが改善する場合があります。
DBMS_SQLパッケージを使用して記述された古い動的SQL文。

コンパイル時に動的SQL文の入力変数と出力変数の数およびデータ型がわかる場合、その文をシステム固有の動的SQLで記述しなおすことができます(システム固有の動的SQLは、DBMS_SQLパッケージを使用する同等のコードより著しく高速に実行されます(特に、コードをコンパイラによって最適化できる場合))。詳細は、「PL/SQLの動的SQL」を参照してください。
SQL文の処理に長時間を費やすコード。

「SQL文のチューニング」を参照してください。
問合せで起動され、数百万回実行される可能性があるファンクション。

「問合せにおけるファンクションの起動のチューニング」を参照してください。
問合せ結果のループに長時間を費やすコード。

ループのチューニングを参照してください。
数値計算を大量に実行するコード。

「計算集中型PL/SQLコードのチューニング」を参照してください。
PL/SQLからSQLに直接渡されるデータベース定義言語(DDL)文を発行するのではなく、PL/SQL文の処理に長時間を費やすコード。

「システム固有の実行のためのPL/SQLユニットのコンパイル」を参照してください。

12.3 CPUオーバーヘッドの最小化

ここでのトピック

12.3.1 SQL文のチューニング

PL/SQLプログラムの速度が低下する最も一般的な原因は、速度の遅いSQL文です。PL/SQLプログラムのSQL文をできるだけ効率的にするには:

適切な索引を使用します。

詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
問合せヒントを使用して、不要な全表スキャンを回避します。

詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
DBMS_STATSパッケージのサブプログラムを使用して、すべての表について現在の統計を収集します。

詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
次のものを使用して、実行計画およびSQL文のパフォーマンスを分析します。
- EXPLAIN PLAN文
  
  詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
- TKPROFユーティリティを持つSQLトレース機能
  
  詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
バルクSQL(PL/SQLとSQL間の通信のパフォーマンス・オーバーヘッドを最小化するPL/SQL機能のセット)を使用します。

詳細は、「バルクSQLおよびバルク・バインド」を参照してください。

12.3.2 問合せにおけるファンクションの起動のチューニング

問合せで起動されるファンクションは、数百万回実行される可能性があります。問合せで不必要にファンクションを起動しないようにして、起動はできるだけ効率的にします。

問合せの表にファンクション索引を作成してください。CREATE INDEX文には少し時間がかかる場合もありますが、各行のファンクション値がキャッシュされるため、問合せを非常に高速に実行できます。

問合せによって列をファンクションに渡す場合、問合せではその列でユーザー作成索引を使用できないため、表(非常に大きい可能性がある)の行ごとにファンクションが起動される可能性があります。ファンクションの起動数を最小限に抑えるには、ネストした問合せを使用します。内側の問合せで結果セットが少ない行数になるようにフィルタリングし、外側の問合せでそれらの行のみを対象にファンクションを起動します。

関連項目:

CREATE INDEX文の構文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
PL/SQLファンクションの結果をキャッシュする方法の詳細は、「PL/SQLファンクション結果キャッシュ」を参照してください

例12-5 ネストした問合せによるパフォーマンスの改善

この例では、2つの問合せで同じ結果セットを生成しますが、2番目の問合せは1番目の問合せより効率的です。(この例では、EMPLOYEES表が非常に小さいため、回数および時間の違いはごくわずかです。非常に大きい表では、これらの違いが顕著になります。)

DECLARE
  starting_time  TIMESTAMP WITH TIME ZONE;
  ending_time    TIMESTAMP WITH TIME ZONE;
BEGIN
  -- Invokes SQRT for every row of employees table:
 
  SELECT SYSTIMESTAMP INTO starting_time FROM DUAL;
 
  FOR item IN (
    SELECT DISTINCT(SQRT(department_id)) col_alias
    FROM employees
    ORDER BY col_alias
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias);
  END LOOP;
 
  SELECT SYSTIMESTAMP INTO ending_time FROM DUAL;
 
  DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time));
 
  -- Invokes SQRT for every distinct department_id of employees table:
 
  SELECT SYSTIMESTAMP INTO starting_time FROM DUAL;
 
  FOR item IN (
    SELECT SQRT(department_id) col_alias
    FROM (SELECT DISTINCT department_id FROM employees)
    ORDER BY col_alias
  )
  LOOP
    IF item.col_alias IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias);
    END IF;
  END LOOP;
 
  SELECT SYSTIMESTAMP INTO ending_time FROM DUAL;
 
  DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time));
END;
/

結果は次のようになります。

Square root of dept. ID = 3.16227766016837933199889354443271853372
Square root of dept. ID = 4.47213595499957939281834733746255247088
Square root of dept. ID = 5.47722557505166113456969782800802133953
Square root of dept. ID = 6.32455532033675866399778708886543706744
Square root of dept. ID = 7.07106781186547524400844362104849039285
Square root of dept. ID = 7.74596669241483377035853079956479922167
Square root of dept. ID = 8.36660026534075547978172025785187489393
Square root of dept. ID = 8.94427190999915878563669467492510494176
Square root of dept. ID = 9.48683298050513799599668063329815560116
Square root of dept. ID = 10
Square root of dept. ID = 10.48808848170151546991453513679937598475
Time = +000000000 00:00:00.046000000
Square root of dept. ID = 3.16227766016837933199889354443271853372
Square root of dept. ID = 4.47213595499957939281834733746255247088
Square root of dept. ID = 5.47722557505166113456969782800802133953
Square root of dept. ID = 6.32455532033675866399778708886543706744
Square root of dept. ID = 7.07106781186547524400844362104849039285
Square root of dept. ID = 7.74596669241483377035853079956479922167
Square root of dept. ID = 8.36660026534075547978172025785187489393
Square root of dept. ID = 8.94427190999915878563669467492510494176
Square root of dept. ID = 9.48683298050513799599668063329815560116
Square root of dept. ID = 10
Square root of dept. ID = 10.48808848170151546991453513679937598475
Time = +000000000 00:00:00.000000000

12.3.3 サブプログラムの起動のチューニング

サブプログラムにOUTまたはIN OUTパラメータがある場合、NOCOPYヒントを使用してこれらのパラメータを宣言することによって、起動のオーバーヘッドを減らすことができます。

OUTパラメータまたはIN OUTパラメータが、コレクション、レコード、ADTのインスタンスなどの大規模なデータ構造を表す場合、それらのコピーによって実行速度が低下し、(特にADTのインスタンスで)メモリー使用量が増大します。

ADTメソッドが起動されるたびに、PL/SQLによってそのADTのすべての属性がコピーされます。メソッドが正常に終了すると、そのメソッドが属性に対して行った変更が、PL/SQLによって適用されます。メソッドが未処理例外を戻して終了すると、PL/SQLは、属性を変更しません。

プログラムで、サブプログラムが未処理例外を戻して終了したときにOUTパラメータまたはIN OUTパラメータに起動前の値を保持する必要がない場合、パラメータ宣言にNOCOPYヒントを含めます。NOCOPYヒントは、コンパイラが対応する実パラメータを値ではなく参照によって渡すように要求しますが、その保証はしません。

注意:

サブプログラムが未処理例外を戻して終了したときに実パラメータまたはADT属性で起動前の値を確実に保持するためには、NOCOPYに依存しないでください(特定の起動について、コンパイラはこのヒントに従う場合と従わない場合があります)。かわりに、サブプログラムですべての例外が処理されるようにします。

関連項目:

NOCOPYヒントの詳細は、「NOCOPY」を参照してください
ADTのメンバー・メソッドとともにNOCOPYを使用する方法は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください

例12-6 NOCOPYサブプログラム・パラメータ

この例では、コンパイラがdo_nothing2の起動に関してNOCOPYヒントに従うと、do_nothing2の起動がdo_nothing1の起動より高速に実行されます。

DECLARE
  TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;
  emp_tab EmpTabTyp := EmpTabTyp(NULL);  -- initialize
  t1 NUMBER;
  t2 NUMBER;
  t3 NUMBER;

  PROCEDURE get_time (t OUT NUMBER) IS
  BEGIN
    t := DBMS_UTILITY.get_time;
  END;

  PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
  BEGIN
    NULL;
  END;

  PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
  BEGIN
    NULL;
  END;

BEGIN
  SELECT * INTO emp_tab(1)
  FROM employees
  WHERE employee_id = 100;

  emp_tab.EXTEND(49999, 1);  -- Copy element 1 into 2..50000
  get_time(t1);
  do_nothing1(emp_tab);  -- Pass IN OUT parameter
  get_time(t2);
  do_nothing2(emp_tab);  -- Pass IN OUT NOCOPY parameter
  get_time(t3);
  DBMS_OUTPUT.PUT_LINE ('Call Duration (secs)');
  DBMS_OUTPUT.PUT_LINE ('--------------------');
  DBMS_OUTPUT.PUT_LINE ('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));
  DBMS_OUTPUT.PUT_LINE ('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);
END;
/

12.3.4 ループのチューニング

PL/SQLアプリケーションはループの周囲に構築されることが多いため、ループ自体とループ内のコードの両方を最適化することが重要です。

結果セットを2回以上ループしたり、結果セットのループ時に他の問合せを発行する必要がある場合、元の問合せを変更して、目的の結果を正確に取得することが可能な場合があります。複数の問合せを結合できるSQLの集合演算子を調査してください(『Oracle Database SQL言語リファレンス』を参照)。

副問合せを使用して、複数の段階でフィルタリングおよびソートを行うこともできます(「副問合せによる問合せ結果セットの処理」を参照)。

関連項目:

「バルクSQLおよびバルク・バインド」

12.3.5 計算集中型PL/SQLコードのチューニング

ここでの推奨事項は、特に計算集中型PL/SQLコードに適用されます(ただし、それらのコードに限定されません)。

ここでのトピック

12.3.5.1 ハードウェア算術を使用するデータ型の使用

NUMBERデータ型ファミリ(「NUMBERデータ型ファミリ」を参照)のデータ型を使用することは避けてください。これらのデータ型の内部的な表現形式は、パフォーマンスよりも移植性および任意の位取りと精度に重点を置いて設計されています。これらの型のデータに対する操作では、ライブラリ算術計算が使用されますが、PLS_INTEGER型、BINARY_FLOAT型およびBINARY_DOUBLE型のデータに対する操作では、ハードウェア算術計算が使用されます。

ローカル整変数には、PLS_INTEGERを使用します(「PLS_INTEGERおよびBINARY_INTEGERデータ型」を参照)。パフォーマンスを重視するコードで使用する変数(NULL値を持つことがなく、オーバーフロー・チェックを必要としないもの)には、SIMPLE_INTEGERを使用します(PLS_INTEGERのSIMPLE_INTEGERサブタイプを参照)。

浮動小数点変数には、BINARY_FLOATまたはBINARY_DOUBLEを使用します(『Oracle Database SQL言語リファレンス』を参照)。パフォーマンスを重視するコードで使用する変数(NULL値を持つことがなく、オーバーフロー・チェックを必要としないもの)には、SIMPLE_FLOATまたはSIMPLE_DOUBLEを使用します(「BINARY_FLOATおよびBINARY_DOUBLEの追加のPL/SQLサブタイプ」を参照)。

ノート:

BINARY_FLOATおよびBINARY_DOUBLEとそれらのサブタイプは、正確さが非常に重要な財務処理コードにはあまり適していません。これらの型は、常に正確に小数値を表すわけではなく、NUMBER型とは異なり四捨五入処理が行われるためです。

多くのSQL数値ファンクション(『Oracle Database SQL言語リファレンス』を参照)は、BINARY_FLOATパラメータおよびBINARY_DOUBLEパラメータを受け入れるバージョンでオーバーロードされます。これらのデータ型の変数をこのようなファンクションに渡したり、このようなファンクションに式を渡すときに変換ファンクションのTO_BINARY_FLOAT(『Oracle Database SQL言語リファレンス』を参照)およびTO_BINARY_DOUBLE(『Oracle Database SQL言語リファレンス』を参照)を起動することによって、計算集中型コードをスピードアップできます。

12.3.5.2 パフォーマンスを重視するコードでの制約付きサブタイプの回避

パフォーマンスを重視するコードでは、制約付きサブタイプ(「制約付きサブタイプ」を参照)を使用することは避けてください。制約付きサブタイプの変数またはパラメータに代入を行うたびに、代入される値が制約に違反していないことを保証するために実行時に余分なチェックが必要になります。

関連項目:

「PL/SQLの事前定義のデータ型」(事前定義の制約付きサブタイプを含む)

12.3.5.3 暗黙的なデータ型変換の最少化

PL/SQLは実行時に、必要に応じて異なるデータ型を暗黙的(自動的)に変換します。たとえば、PLS_INTEGER変数をNUMBER変数に代入すると、PL/SQLによって、PLS_INTEGER値がNUMBER値に変換されます(各値の内部表現は異なるため)。

可能であれば、暗黙的な変換は常に最小限に抑えてください。たとえば:

表の列に変数を挿入する場合、または表の列から変数に値を代入する場合、その変数に表の列と同じデータ型を指定します。

ヒント:

%TYPE属性を使用して変数を宣言します(「%TYPE属性」を参照)。
各リテラルが、割当て先の変数または記述対象の式と同じデータ型になるようにします。
SQLデータ型からPL/SQLデータ型に値を変換し、変換した値を式で使用します。

たとえば、NUMBER値をPLS_INTEGER値に変換し、そのPLS_INTEGER値を式で使用します。PLS_INTEGER演算はハードウェア算術計算を使用するため、ライブラリ算術計算を使用するNUMBER演算より処理速度が速くなります。PLS_INTEGERデータ型の詳細は、「PLS_INTEGERおよびBINARY_INTEGERデータ型」を参照してください。
あるSQLデータ型の値を別のSQLデータ型の変数に代入する前に、SQL変換ファンクションを使用してソース値をターゲット・データ型に明示的に変換します(SQL変換ファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください)。
異なるデータ型のパラメータを受け入れるバージョンでサブプログラムをオーバーロードし、そのパラメータ型の各バージョンを最適化します。オーバーロードされたサブプログラムの詳細は、「オーバーロードされたサブプログラム」を参照してください。

関連項目:

SQLデータ型(PL/SQLデータ型にも相当)の暗黙的な変換の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
「同じデータ型ファミリ内のベース型を使用したサブタイプ」

12.3.6 SQL文字関数の使用

SQLには、PL/SQLコードよりも効率的な低レベルのコードを使用する高度に最適化された文字ファンクションが多くあります。PL/SQLコードを記述するかわりにこれらのファンクションを使用することで、同じ処理を実行できます。

参照:

文字値を戻すSQL文字関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
NLS文字値を戻すSQL文字関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
数値を戻すSQL文字関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
SQL文字ファンクションREGEXP_LIKEを使用するPL/SQLコードの例は、例6-6を参照してください。

12.3.7 最低コストの条件テストの先頭への配置

PL/SQLは、結果が判別できた時点でただちに論理式の評価を停止します。可能であれば常に、最低コストの条件を論理式の最初に配置して評価することで、この短絡評価を利用してください。たとえば、変数テストに失敗した場合、PL/SQLでファンクションを起動する必要がなくなるように、ファンクションの戻り値をテストする前に、PL/SQL変数の値をテストします。

IF boolean_variable OR (number > 10) OR boolean_function(parameter) THEN ...

関連項目:

短絡評価

12.4 バルクSQLおよびバルク・バインド

バルクSQLは、PL/SQLとSQL間の通信のパフォーマンス・オーバーヘッドを最小化します。バルクSQLを構成するPL/SQL機能は、FORALL文およびBULK COLLECT句です。SQL文に指定されたPL/SQL変数に値を代入することを、バインドと呼びます。

PL/SQLとSQLは、次のように通信します。PL/SQLエンジンは、SELECT INTO文またはDML文を実行するために、SQLエンジンに問合せまたはDML文を送信します。SQLエンジンは、問合せまたはDML文を実行し、結果をPL/SQLエンジンに戻します。

FORALL文は、DML文をPL/SQLからSQLに1文ずつではなくバッチで送信します。BULK COLLECT句は、結果をSQLからPL/SQLに1つずつではなくバッチで戻します。問合せまたはDML文が4つ以上のデータベース行に影響する場合は、バルクSQLでパフォーマンスを大幅に向上できます。

ノート:

リモート表では、バルクSQLは実行できません。

PL/SQLバインド操作は、次のカテゴリに分類されます。

バインド・カテゴリ	このバインドが実行される場合
インバインド	`INSERT`文、`UPDATE`文または`MERGE`によってPL/SQL変数またはホスト変数がデータベースに格納される場合
アウトバインド	`INSERT`文、`UPDATE`文または`DELETE`文の`RETURNING` `INTO`句によってPL/SQL変数またはホスト変数にデータベースの値が代入される場合
`DEFINE`	`SELECT`文または`FETCH`文によってPL/SQL変数またはホスト変数にデータベースの値が代入される場合

インバインドおよびアウトバインドでは、バルクSQLはバルク・バインドを使用します(つまり、値のコレクション全体を一度にバインドします)。n個の要素があるコレクションの場合、バルクSQLは単一の操作で、n回分のSELECT INTO文またはDML文に相当する処理を実行できます。バルクSQLを使用する問合せでは、行ごとにFETCH文を使用することなく、任意の数の行を戻すことができます。

ノート:

パラレルDMLは、バルクSQLを使用すると無効になります。

ここでのトピック

12.4.1 FORALL文

FORALL文は、バルクSQLの機能であり、DML文をPL/SQLからSQLに1文ずつではなくバッチで送信します。

FORALL文を理解するため、最初に例12-7のFOR LOOP文について考えてみます。これらのDML文は、PL/SQLからSQLに1文ずつ送信されます。

DELETE FROM employees_temp WHERE department_id = depts(10);
DELETE FROM employees_temp WHERE department_id = depts(30);
DELETE FROM employees_temp WHERE department_id = depts(70);

次に、例12-8のFORALL文について考えてみます。3つの同じDML文がPL/SQLからSQLにバッチとして送信されます。

通常、FORALL文は、同等のFOR LOOP文よりはるかに高速です。ただし、FOR LOOP文には複数のDML文を含めることができますが、FORALL文に含めることができるDML文は1つのみです。FORALL文によってSQLに送信されるDML文のバッチは、VALUES句とWHERE句のみが異なります。これらの句の値は、データが移入された既存のコレクションから取得される必要があります。

ノート:

FORALL文のDML文では、複数のコレクションを参照できますが、パフォーマンス上のメリットは、FORALLの索引変数を索引として使用するコレクション参照に対してのみ適用されます。

例12-9では、2つのデータベース表に同じコレクション要素を挿入します(1番目の表にはFOR LOOP文を使用し、2番目の表にはFORALL文を使用して、各文の実行にかかる時間を表示します)。(時間は実行ごとに異なります。)

例12-10では、FORALL文がコレクションのサブセットに適用されます。

ここでのトピック

関連項目:

制限などのFORALL文の構文およびセマンティクスの詳細は、「FORALL文」を参照してください
一般的な暗黙カーソル属性と、FORALL文で使用できる他の暗黙カーソル属性の詳細は、「暗黙カーソル」を参照してください

例12-7 FOR LOOP文のDELETE文

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE
  TYPE NumList IS VARRAY(20) OF NUMBER;
  depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
  FOR i IN depts.FIRST..depts.LAST LOOP
    DELETE FROM employees_temp
    WHERE department_id = depts(i);
  END LOOP;
END;
/

例12-8 FORALL文のDELETE文

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE
  TYPE NumList IS VARRAY(20) OF NUMBER;
  depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
  FORALL i IN depts.FIRST..depts.LAST
    DELETE FROM employees_temp
    WHERE department_id = depts(i);
END;
/

例12-9 FOR LOOP文およびFORALL文におけるINSERT文の時間の違い

DROP TABLE parts1;
CREATE TABLE parts1 (
  pnum INTEGER,
  pname VARCHAR2(15)
);
 
DROP TABLE parts2;
CREATE TABLE parts2 (
  pnum INTEGER,
  pname VARCHAR2(15)
);

DECLARE
  TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
  pnums   NumTab;
  pnames  NameTab;
  iterations  CONSTANT PLS_INTEGER := 50000;
  t1  INTEGER;
  t2  INTEGER;
  t3  INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- populate collections
    pnums(j) := j;
    pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;

  t1 := DBMS_UTILITY.get_time;

  FOR i IN 1..iterations LOOP
    INSERT INTO parts1 (pnum, pname)
    VALUES (pnums(i), pnames(i));
  END LOOP;

  t2 := DBMS_UTILITY.get_time;

  FORALL i IN 1..iterations
    INSERT INTO parts2 (pnum, pname)
    VALUES (pnums(i), pnames(i));

  t3 := DBMS_UTILITY.get_time;

  DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)/100));
  DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));
  COMMIT;
END;
/

結果は次のようになります。

Execution Time (secs)
---------------------
FOR LOOP: 5.97
FORALL:   .07
 
PL/SQL procedure successfully completed.

例12-10 コレクションのサブセットに対するFORALL文

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS SELECT * FROM employees;

DECLARE
  TYPE NumList IS VARRAY(10) OF NUMBER;
  depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN
  FORALL j IN 4..7
    DELETE FROM employees_temp WHERE department_id = depts(j);
END;
/

12.4.1.1 疎コレクションに対するFORALL文の使用

FORALL文の境界句で疎コレクションを参照する場合、INDICES OF句またはVALUES OF句を使用して既存の索引値のみを指定します。

INDICES OFは、文字列で索引付けされている連想配列を除く任意のコレクションに使用できます。VALUES OFは、PLS_INTEGERで索引付けされているPLS_INTEGER要素のコレクションにのみ使用できます。

PLS_INTEGERで索引付けされているPLS_INTEGER要素のコレクションには、索引コレクションを使用できます。索引コレクションは、他のコレクションの要素に対するポインタのコレクションです(索引付けされたコレクション)。

索引コレクションは、異なるFORALL文で同じコレクションの異なるサブセットを処理する場合に便利です。時間とメモリーを大量に費やす可能性のあるサブセットを表す新しいコレクションに元のコレクションの要素をコピーするかわりに、索引コレクションを使用して各サブセットを表し、各索引コレクションを異なるFORALL文のVALUES OF句で使用します。

関連項目:

「疎コレクションおよびSQL%BULK_EXCEPTIONS」

例12-11 疎コレクションおよびそのサブセットに対するFORALL文

この例では、INDICES OF句付きでFORALL文を使用し、表に疎コレクションの要素を移入します。その後、VALUES OF句付きで2つのFORALL文を使用し、2つの表にコレクションのサブセットを入れます。

DROP TABLE valid_orders;
CREATE TABLE valid_orders (
  cust_name  VARCHAR2(32),
  amount     NUMBER(10,2)
);
 
DROP TABLE big_orders;
CREATE TABLE big_orders AS
  SELECT * FROM valid_orders
  WHERE 1 = 0;
 
DROP TABLE rejected_orders;
CREATE TABLE rejected_orders AS
  SELECT * FROM valid_orders
  WHERE 1 = 0;
 
DECLARE
  SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
  TYPE cust_typ IS TABLE OF cust_name;
  cust_tab  cust_typ;  -- Collection of customer names
 
  SUBTYPE order_amount IS valid_orders.amount%TYPE;
  TYPE amount_typ IS TABLE OF NUMBER;
  amount_tab  amount_typ;  -- Collection of order amounts
 
  TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
 
  /* Collections for pointers to elements of cust_tab collection
     (to represent two subsets of cust_tab): */
 
  big_order_tab       index_pointer_t := index_pointer_t();
  rejected_order_tab  index_pointer_t := index_pointer_t();
 
  PROCEDURE populate_data_collections IS
  BEGIN
    cust_tab := cust_typ(
      'Company1','Company2','Company3','Company4','Company5'
    );
 
    amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
  END;
 
BEGIN
  populate_data_collections;
 
  DBMS_OUTPUT.PUT_LINE ('--- Original order data ---');
 
  FOR i IN 1..cust_tab.LAST LOOP
    DBMS_OUTPUT.PUT_LINE (
      'Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)
    );
  END LOOP;
 
  -- Delete invalid orders:
 
  FOR i IN 1..cust_tab.LAST LOOP
    IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
      cust_tab.delete(i);
      amount_tab.delete(i);
    END IF;
  END LOOP;
 
  -- cust_tab is now a sparse collection.
 
  DBMS_OUTPUT.PUT_LINE ('--- Order data with invalid orders deleted ---');
 
  FOR i IN 1..cust_tab.LAST LOOP
    IF cust_tab.EXISTS(i) THEN
      DBMS_OUTPUT.PUT_LINE (
        'Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)
      );
    END IF;
  END LOOP;
 
  -- Using sparse collection, populate valid_orders table:
 
  FORALL i IN INDICES OF cust_tab
    INSERT INTO valid_orders (cust_name, amount)
    VALUES (cust_tab(i), amount_tab(i));
 
  populate_data_collections;  -- Restore original order data
 
  -- cust_tab is a dense collection again.
 
  /* Populate collections of pointers to elements of cust_tab collection
     (which represent two subsets of cust_tab): */
 
  FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
    IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
      rejected_order_tab.EXTEND;
      rejected_order_tab(rejected_order_tab.LAST) := i; 
    END IF;
 
    IF amount_tab(i) > 2000 THEN
      big_order_tab.EXTEND;
      big_order_tab(big_order_tab.LAST) := i;
    END IF;
  END LOOP;
 
  /* Using each subset in a different FORALL statement,
     populate rejected_orders and big_orders tables: */
 
  FORALL i IN VALUES OF rejected_order_tab
    INSERT INTO rejected_orders (cust_name, amount)
    VALUES (cust_tab(i), amount_tab(i));
 
  FORALL i IN VALUES OF big_order_tab
    INSERT INTO big_orders (cust_name, amount)
    VALUES (cust_tab(i), amount_tab(i));
END;
/

結果:

--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000

正しい注文詳細が格納されたかどうかの検証:

SELECT cust_name "Customer", amount "Valid order amount"
FROM valid_orders
ORDER BY cust_name;

結果:

Customer                         Valid order amount
-------------------------------- ------------------
Company1                                    5000.01
Company3                                     150.25
Company4                                       4000
 
3 rows selected.

問合せ:

SELECT cust_name "Customer", amount "Big order amount"
FROM big_orders
ORDER BY cust_name;

結果:

Customer                         Big order amount
-------------------------------- ----------------
Company1                                  5000.01
Company4                                     4000
 
2 rows selected.

問合せ:

SELECT cust_name "Customer", amount "Rejected order amount"
FROM rejected_orders
ORDER BY cust_name;

結果:

Customer                         Rejected order amount
-------------------------------- ---------------------
Company2                                             0
Company5
 
2 rows selected.

12.4.1.2 FORALL文での未処理例外

SAVE EXCEPTIONS句なしのFORALL文で、いずれかのDML文によって未処理例外が呼び出されると、PL/SQLによってFORALL文が停止され、前のDML文で行われたすべての変更がロールバックされます。

たとえば、例12-8のFORALL文は、次のDML文を(そのいずれかによって未処理例外が呼び出されないかぎり)記載された順序で実行します。

DELETE FROM employees_temp WHERE department_id = depts(10);
DELETE FROM employees_temp WHERE department_id = depts(30);
DELETE FROM employees_temp WHERE department_id = depts(70);

3番目の文で未処理例外が呼び出されると、PL/SQLによって、1番目と2番目の文による変更がロールバックされます。2番目の文で未処理例外が呼び出されると、PL/SQLによって、1番目の文による変更がロールバックされ、3番目の文は実行されません。

FORALL文で呼び出される例外は、次のいずれかの方法で処理できます。

各例外が呼び出された時点で(「FORALL例外の即座の処理」を参照)
FORALL文の実行が完了した後、SAVE EXCEPTIONS句を含めることによって(「FORALL文完了後のFORALL例外の処理」を参照)

12.4.1.3 FORALL例外の即時処理

FORALL文で呼び出された例外を即座に処理するには、SAVE EXCEPTIONS句を省略して適切な例外ハンドラを記述します。

1つのDML文で処理済例外が発生した場合、PL/SQLはその文によって行われた変更をロールバックしますが、それより前のDML文で行われた変更はロールバックしません。

例12-12では、FORALL文が3つのUPDATE文を実行するように設計されています。ただし、2番目の文によって例外が呼び出されます。例外は例外ハンドラによって処理され、エラー・メッセージの表示と1番目のUPDATE文による変更のコミットが行われます。3番目のUPDATE文は実行されません。

例外処理の詳細は、「PL/SQLのエラー処理」を参照してください。

例12-12 FORALL例外の即時処理

DROP TABLE emp_temp;
CREATE TABLE emp_temp (
  deptno NUMBER(2),
  job VARCHAR2(18)
);
 
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
  TYPE NumList IS TABLE OF NUMBER;
 
  depts          NumList := NumList(10, 20, 30);
  error_message  VARCHAR2(100);
 
BEGIN
  -- Populate table:
 
  INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk');
  INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper');
  INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst');
  COMMIT;
 
  -- Append 9-character string to each job:
 
  FORALL j IN depts.FIRST..depts.LAST
    UPDATE emp_temp SET job = job || ' (Senior)'
    WHERE deptno = depts(j);
 
EXCEPTION
  WHEN OTHERS THEN
    error_message := SQLERRM;
    DBMS_OUTPUT.PUT_LINE (error_message);
 
    COMMIT;  -- Commit results of successful updates
    RAISE;
END;
/

結果:

Procedure created.

プロシージャの起動:

BEGIN
  p;
END;
/

結果:

ORA-12899: value too large for column "HR"."EMP_TEMP"."JOB" (actual: 19,
maximum: 18)
BEGIN
*
ERROR at line 1:
ORA-12899: value too large for column "HR"."EMP_TEMP"."JOB" (actual: 19,
maximum: 18)
ORA-06512: at "HR.P", line 27
ORA-06512: at line 2

問合せ:

SELECT * FROM emp_temp;

結果:

    DEPTNO JOB
---------- ------------------
        10 Clerk (Senior)
        20 Bookkeeper
        30 Analyst
 
3 rows selected.

12.4.1.4 FORALL文が完了した後のFORALL例外の処理

一部のDML文が失敗した場合でもFORALL文の処理を継続するには、SAVE EXCEPTIONS句を含めます。DML文が失敗しても、PL/SQLによって例外は呼び出されず、かわりに、その失敗に関する情報が保存されます。FORALL文の完了後、PL/SQLによって、FORALL文に対して1つの例外(ORA-24381)が呼び出されます。

ORA-24381の例外ハンドラでは、暗黙カーソル属性SQL%BULK_EXCEPTIONSから個々のDML文の失敗に関する情報を取得できます。

SQL%BULK_EXCEPTIONSは、直前のFORALL文の実行中に失敗したDML文に関する情報の連想配列とほぼ同じです。

SQL%BULK_EXCEPTIONS.COUNTは、失敗したDML文の数です。SQL%BULK_EXCEPTIONS.COUNTが0(ゼロ)でない場合、1からSQL%BULK_EXCEPTIONS. COUNTまでの各索引値iに対して、次のようになります。

SQL%BULK_EXCEPTIONS(i).ERROR_INDEXは、失敗したDML文の数です。
SQL%BULK_EXCEPTIONS(i).ERROR_CODEは、失敗に関するOracle Databaseエラー・コードです。

たとえば、FORALL SAVE EXCEPTIONS文で100個のDML文を実行し、10番目と64番目の文がそれぞれエラー・コードORA-12899およびORA-19278を戻して失敗した場合、次のようになります。

SQL%BULK_EXCEPTIONS.COUNT = 2
SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10
SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899
SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64
SQL%BULK_EXCEPTIONS(2).ERROR_CODE = 19278

ノート:

SAVE EXCEPTIONS句なしのFORALL文によって例外が呼び出されると、SQL%BULK_EXCEPTIONS.COUNT = 1になります。

エラー・コードを使用して、次のようにSQLERRMファンクション(「SQLERRMファンクション」を参照)で関連するエラー・メッセージを取得できます。

SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE))

ただし、SQLERRMによって戻されるエラー・メッセージには、置換引数は含まれません(例12-12と例12-13のエラー・メッセージを比較してください)。

例12-13は、例12-12とほぼ同じですが、次の点が異なります。

FORALL文に、SAVE EXCEPTIONS句が含まれます。
例外処理部に、ORA-24381(バルク操作が例外を呼び出して保存したときにPL/SQLによって暗黙的に呼び出される内部的に定義された例外)に対応する例外ハンドラが含まれます。この例では、ORA-24381にユーザー定義の名前dml_errorsを割り当てています。
dml_errorsの例外ハンドラは、SQL%BULK_EXCEPTIONSとSQLERRM(およびいくつかのローカル変数)を使用して、エラー・メッセージと、エラーの原因となった文、コレクション項目および文字列を表示します。

例12-13 FORALL文が完了した後のFORALL例外の処理

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
  TYPE NumList IS TABLE OF NUMBER;
  depts        NumList := NumList(10, 20, 30);
 
  error_message  VARCHAR2(100);
  bad_stmt_no    PLS_INTEGER;
  bad_deptno     emp_temp.deptno%TYPE;
  bad_job        emp_temp.job%TYPE;
 
  dml_errors  EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
  -- Populate table:
 
  INSERT INTO emp_temp (deptno, job) VALUES (10, 'Clerk');
  INSERT INTO emp_temp (deptno, job) VALUES (20, 'Bookkeeper');
  INSERT INTO emp_temp (deptno, job) VALUES (30, 'Analyst');
  COMMIT;
 
  -- Append 9-character string to each job:
 
  FORALL j IN depts.FIRST..depts.LAST SAVE EXCEPTIONS
    UPDATE emp_temp SET job = job || ' (Senior)'
    WHERE deptno = depts(j); 
 
EXCEPTION
  WHEN dml_errors THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      error_message := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      DBMS_OUTPUT.PUT_LINE (error_message);
 
      bad_stmt_no := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
      DBMS_OUTPUT.PUT_LINE('Bad statement #: ' || bad_stmt_no);
 
      bad_deptno := depts(bad_stmt_no);
      DBMS_OUTPUT.PUT_LINE('Bad department #: ' || bad_deptno);
 
      SELECT job INTO bad_job FROM emp_temp WHERE deptno = bad_deptno;
 
      DBMS_OUTPUT.PUT_LINE('Bad job: ' || bad_job);
    END LOOP;
 
    COMMIT;  -- Commit results of successful updates

    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unrecognized error.');
      RAISE;
END;
/

結果:

Procedure created.

プロシージャの起動:

BEGIN
  p;
END;
/

結果:

ORA-12899: value too large for column  (actual: , maximum: )
Bad statement #: 2
Bad department #: 20
Bad job: Bookkeeper
 
PL/SQL procedure successfully completed.

問合せ:

SELECT * FROM emp_temp;

結果:

    DEPTNO JOB
---------- ------------------
        10 Clerk (Senior)
        20 Bookkeeper
        30 Analyst (Senior)
 
3 rows selected.

12.4.1.4.1 疎コレクションおよびSQL%BULK_EXCEPTIONS

FORALL文の境界句で疎コレクションを参照する場合に、DML文が失敗する原因となったコレクション要素を検出するには、索引がSQL%BULK_EXCEPTIONS(i).ERROR_INDEXである要素を検出するまで、各要素を1つずつ確認する必要があります。FORALL文で、VALUES OF句を使用して別のコレクションに対するポインタのコレクションを参照する場合、他のコレクションの要素で索引がSQL%BULK_EXCEPTIONS(i).ERROR_INDEXであるものを検出する必要があります。

12.4.1.5 FORALL文の影響を受ける行の数の取得

FORALL文の完了後、各DML文によって影響を受けた行の数を、暗黙カーソル属性SQL%BULK_ROWCOUNTから取得できます。

FORALL文の影響を受けた行の合計数を取得するには、「SQL%ROWCOUNT属性: 影響を受けた行数」で説明されている、暗黙カーソル属性のSQL%ROWCOUNTを使用します。

SQL%BULK_ROWCOUNTは、i番目の要素が、直前に完了したFORALL文のi番目のDML文によって影響を受けた行の数に相当する連想配列とほぼ同じです。要素のデータ型はINTEGERです。

ノート:

サーバーがOracle Database 12c以降で、そのクライアントがOracle Database 11gリリース2以前(またはその逆)の場合、SQL%BULK_ROWCOUNTが戻す最大値は4,294,967,295です。

例12-14では、SQL%BULK_ROWCOUNTを使用してFORALL文の各DELETE文で削除された行の数を表示し、SQL%ROWCOUNTを使用して削除された行の合計数を表示します。

例12-15では、SQL%BULK_ROWCOUNTを使用してFORALL文の各INSERT SELECT構文で挿入された行の数を表示し、SQL%ROWCOUNTを使用して挿入された行の合計数を表示します。

例12-14 FORALLの各DELETEで影響を受けた行の数の表示

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts NumList := NumList(30, 50, 60);
BEGIN
  FORALL j IN depts.FIRST..depts.LAST
    DELETE FROM emp_temp WHERE department_id = depts(j);

  FOR i IN depts.FIRST..depts.LAST LOOP
    DBMS_OUTPUT.PUT_LINE (
      'Statement #' || i || ' deleted ' ||
      SQL%BULK_ROWCOUNT(i) || ' rows.'
    );
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Total rows deleted: ' || SQL%ROWCOUNT);
END;
/

結果:

Statement #1 deleted 6 rows.
Statement #2 deleted 45 rows.
Statement #3 deleted 5 rows.
Total rows deleted: 56

例12-15 FORALLの各INSERT SELECTで影響を受けた行の数の表示

DROP TABLE emp_by_dept;
CREATE TABLE emp_by_dept AS
  SELECT employee_id, department_id
  FROM employees
  WHERE 1 = 0;

DECLARE
  TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
  deptnums  dept_tab;
BEGIN
  SELECT department_id BULK COLLECT INTO deptnums FROM departments;

  FORALL i IN 1..deptnums.COUNT
    INSERT INTO emp_by_dept (employee_id, department_id)
      SELECT employee_id, department_id
      FROM employees
      WHERE department_id = deptnums(i)
      ORDER BY department_id, employee_id;

  FOR i IN 1..deptnums.COUNT LOOP
    -- Count how many rows were inserted for each department; that is,
    -- how many employees are in each department.
    DBMS_OUTPUT.PUT_LINE (
      'Dept '||deptnums(i)||': inserted '||
      SQL%BULK_ROWCOUNT(i)||' records'
    );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total records inserted: ' || SQL%ROWCOUNT);
END;
/

結果:

Dept 10: inserted 1 records
Dept 20: inserted 2 records
Dept 30: inserted 6 records
Dept 40: inserted 1 records
Dept 50: inserted 45 records
Dept 60: inserted 5 records
Dept 70: inserted 1 records
Dept 80: inserted 34 records
Dept 90: inserted 3 records
Dept 100: inserted 6 records
Dept 110: inserted 2 records
Dept 120: inserted 0 records
Dept 130: inserted 0 records
Dept 140: inserted 0 records
Dept 150: inserted 0 records
Dept 160: inserted 0 records
Dept 170: inserted 0 records
Dept 180: inserted 0 records
Dept 190: inserted 0 records
Dept 200: inserted 0 records
Dept 210: inserted 0 records
Dept 220: inserted 0 records
Dept 230: inserted 0 records
Dept 240: inserted 0 records
Dept 250: inserted 0 records
Dept 260: inserted 0 records
Dept 270: inserted 0 records
Dept 280: inserted 0 records
Total records inserted: 106

12.4.2 BULK COLLECT句

BULK COLLECT句は、バルクSQLの機能であり、結果をSQLからPL/SQLに1つずつではなくバッチで戻します。

BULK COLLECT句は、次の場所に使用できます。

SELECT INTO文
FETCH文
次の文のRETURNING INTO句:
- DELETE文
- INSERT文
- UPDATE文
- EXECUTE IMMEDIATE文

BULK COLLECT句を使用して、前述の各文で結果セット全体を取り出し、それを単一の操作で1つ以上のコレクション変数に格納します(一度に1つの結果行を取り出すループ文を使用するより効率的な操作です)。

ノート:

PL/SQLは、LOOP文の内側でFETCH文を処理する場合と同様の方法で、BULK COLLECT句を処理します。PL/SQLは、BULK COLLECT句付きの文が行を戻さない場合でも、例外を呼び出しません。例12-22に示すように、ターゲット・コレクションが空かどうかを確認する必要があります。

ここでのトピック

12.4.2.1 BULK COLLECT句を使用したSELECT INTO文

BULK COLLECT句付きのSELECT INTO文(SELECT BULK COLLECT INTO文とも呼ばれます)は、結果セット全体を選択して1つ以上のコレクション変数に入れます。

詳細は、「SELECT INTO文」を参照してください。

注意:

SELECT BULK COLLECT INTO文は、エイリアシングに対して不安定であるため、予期しない結果が発生する可能性があります。詳細は、「SELECT BULK COLLECT INTO文およびエイリアシング」を参照してください。

例12-16では、SELECT BULK COLLECT INTO文を使用して、データベースの2つの列を選択して2つのコレクション(ネストした表)に入れています。

例12-17では、SELECT BULK COLLECT INTO文を使用して、結果セットを選択してレコードのネストした表に入れています。

ここでのトピック

例12-16 2つのネストした表に対するデータベースの2つの列のバルク選択

DECLARE
  TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
  TYPE NameTab IS TABLE OF employees.last_name%TYPE;
 
  enums NumTab;
  names NameTab;
 
  PROCEDURE print_first_n (n POSITIVE) IS
  BEGIN
    IF enums.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE ('Collections are empty.');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');
 
      FOR i IN 1 .. n LOOP
        DBMS_OUTPUT.PUT_LINE (
          '  Employee #' || enums(i) || ': ' || names(i));
      END LOOP;
    END IF;
  END;
 
BEGIN
  SELECT employee_id, last_name
  BULK COLLECT INTO enums, names
  FROM employees
  ORDER BY employee_id;
 
  print_first_n(3);
  print_first_n(6);
END;
/

結果:

First 3 employees:
Employee #100: King
Employee #101: Kochhar
Employee #102: De Haan
First 6 employees:
Employee #100: King
Employee #101: Kochhar
Employee #102: De Haan
Employee #103: Hunold
Employee #104: Ernst
Employee #105: Austin

例12-17 レコードのネストした表に対するバルク選択

DECLARE
  CURSOR c1 IS
    SELECT first_name, last_name, hire_date
    FROM employees;
  
  TYPE NameSet IS TABLE OF c1%ROWTYPE;
 
  stock_managers  NameSet;  -- nested table of records
 
BEGIN 
  -- Assign values to nested table of records:
 
  SELECT first_name, last_name, hire_date
    BULK COLLECT INTO stock_managers
    FROM employees
    WHERE job_id = 'ST_MAN'
    ORDER BY hire_date;
 
  -- Print nested table of records:
 
    FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
      DBMS_OUTPUT.PUT_LINE (
        stock_managers(i).hire_date || ' ' ||
        stock_managers(i).last_name  || ', ' ||
        stock_managers(i).first_name
      );
    END LOOP;END;
/

結果:

01-MAY-03 Kaufling, Payam
18-JUL-04 Weiss, Matthew
10-APR-05 Fripp, Adam
10-OCT-05 Vollman, Shanta
16-NOV-07 Mourgos, Kevin

12.4.2.1.1 SELECT BULK COLLECT INTO文およびエイリアシング

次の書式の文について説明します。

SELECT column BULK COLLECT INTO collection FROM table ...

columnおよびcollectionは、それぞれIN NOCOPYおよびOUT NOCOPYサブプログラム・パラメータに類似しており、PL/SQLによって参照方式で渡されます。参照方式で渡されるサブプログラム・パラメータと同様に、エイリアシングによって予期しない結果が発生する場合があります。

関連項目:

「参照渡しパラメータによるサブプログラムのパラメータのエイリアシング」

例12-18では、コレクションnumbers1から特定の値を選択し、その値を同じコレクションに格納することを意図しています。ところが、numbers1のすべての要素が削除されるという予期しない結果が発生します。この問題の回避策は、例12-19および例12-20を参照してください。

例12-19では、カーソルを使用して例12-18で意図されていた結果を実現します。

例12-20では、コレクションnumbers1から特定の値を選択し、その値を別のコレクションnumbers2に格納します。例12-20の実行速度は例12-19の場合より速くなります。

例12-18 予期しない結果が発生するSELECT BULK COLLECT INTO文

CREATE OR REPLACE TYPE numbers_type IS
  TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) AUTHID DEFINER IS
  numbers1  numbers_type := numbers_type(1,2,3,4,5);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before SELECT statement');
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
  
  FOR j IN 1..numbers1.COUNT() LOOP
    DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
  END LOOP;
 
  --Self-selecting BULK COLLECT INTO clause:
 
  SELECT a.COLUMN_VALUE
  BULK COLLECT INTO numbers1
  FROM TABLE(numbers1) a
  WHERE a.COLUMN_VALUE > p.i
  ORDER BY a.COLUMN_VALUE;
 
  DBMS_OUTPUT.PUT_LINE('After SELECT statement');
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
END p;
/

pの起動:

BEGIN
  p(2);
END;
/

結果:

Before SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
After SELECT statement
numbers1.COUNT() = 0
 
PL/SQL procedure successfully completed.

pの起動:

BEGIN
  p(10);
END;
/

結果:

Before SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
After SELECT statement
numbers1.COUNT() = 0

例12-19 カーソルによる例12-18の回避策

CREATE OR REPLACE TYPE numbers_type IS
  TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) AUTHID DEFINER IS
  numbers1  numbers_type := numbers_type(1,2,3,4,5);
  
  CURSOR c IS
    SELECT a.COLUMN_VALUE
    FROM TABLE(numbers1) a
    WHERE a.COLUMN_VALUE > p.i
    ORDER BY a.COLUMN_VALUE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Before FETCH statement');
    DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
 
    FOR j IN 1..numbers1.COUNT() LOOP
      DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
    END LOOP;
 
  OPEN c;
  FETCH c BULK COLLECT INTO numbers1;
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE('After FETCH statement');
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
 
  IF numbers1.COUNT() > 0 THEN
    FOR j IN 1..numbers1.COUNT() LOOP
      DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
    END LOOP;
  END IF;
END p;
/

pの起動:

BEGIN
  p(2);
END;
/

結果:

Before FETCH statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
After FETCH statement
numbers1.COUNT() = 3
numbers1(1) = 3
numbers1(2) = 4
numbers1(3) = 5

pの起動:

BEGIN
  p(10);
END;
/

結果:

Before FETCH statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
After FETCH statement
numbers1.COUNT() = 0

例12-20 2番目のコレクションによる例12-18の回避策

CREATE OR REPLACE TYPE numbers_type IS
  TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) AUTHID DEFINER IS
  numbers1  numbers_type := numbers_type(1,2,3,4,5);
 numbers2  numbers_type := numbers_type(0,0,0,0,0);
  
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before SELECT statement');
  
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
  
  FOR j IN 1..numbers1.COUNT() LOOP
    DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT());
 
  FOR j IN 1..numbers2.COUNT() LOOP
    DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j));
  END LOOP;
 
  SELECT a.COLUMN_VALUE
  BULK COLLECT INTO numbers2      -- numbers2 appears here
  FROM TABLE(numbers1) a        -- numbers1 appears here
  WHERE a.COLUMN_VALUE > p.i
  ORDER BY a.COLUMN_VALUE;
 
  DBMS_OUTPUT.PUT_LINE('After SELECT statement');
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
 
  IF numbers1.COUNT() > 0 THEN
    FOR j IN 1..numbers1.COUNT() LOOP
      DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
    END LOOP;
  END IF;
 
  DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT());
 
  IF numbers2.COUNT() > 0 THEN
    FOR j IN 1..numbers2.COUNT() LOOP
      DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j));
    END LOOP;
  END IF;
END p;
/

pの起動:

BEGIN
  p(2);
 END;
/

結果:

Before SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
numbers2.COUNT() = 5
numbers2(1) = 0
numbers2(2) = 0
numbers2(3) = 0
numbers2(4) = 0
numbers2(5) = 0
After SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
numbers2.COUNT() = 3
numbers2(1) = 3
numbers2(2) = 4
numbers2(3) = 5
 
PL/SQL procedure successfully completed.

pの起動:

BEGIN
  p(10);
END;
/

結果:

Before SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
numbers2.COUNT() = 5
numbers2(1) = 0
numbers2(2) = 0
numbers2(3) = 0
numbers2(4) = 0
numbers2(5) = 0
After SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
numbers2.COUNT() = 0

12.4.2.1.2 SELECT BULK COLLECT INTO文の行の制限

大量の行を戻すSELECT BULK COLLECT INTO文では、大規模なコレクションが生成されます。行数およびコレクション・サイズを制限するには、次のいずれかを使用します。

ROWNUM疑似列(『Oracle Database SQL言語リファレンス』を参照)
SAMPLE句(『Oracle Database SQL言語リファレンス』を参照)
FETCH FIRST句(『Oracle Database SQL言語リファレンス』を参照)

例12-21は、SELECT BULK COLLECT INTO文により戻される行数を制限するいくつかの方法を示しています。

例12-21 ROWNUM、SAMPLEおよびFETCH FIRSTによるバルク選択の制限

DECLARE
  TYPE SalList IS TABLE OF employees.salary%TYPE;
  sals SalList;
BEGIN
  SELECT salary BULK COLLECT INTO sals FROM employees
    WHERE ROWNUM <= 50;
 
  SELECT salary BULK COLLECT INTO sals FROM employees
    SAMPLE (10);
 
  SELECT salary BULK COLLECT INTO sals FROM employees
    FETCH FIRST 50 ROWS ONLY;
END;
/

12.4.2.1.3 コレクションのループのガイドライン

結果セットをコレクションに格納する場合、行をループして異なる列を参照することは簡単です。この方法では非常に高速に処理されますが、メモリーも集中的に使用されます。この方法を頻繁に使用する場合、次のことに注意してください。

結果セットを1回ループするには、カーソルFOR LOOPを使用します(「カーソルFOR LOOP文による問合せ結果セットの処理」を参照)。

この方法を使用すると、結果セットのコピーを格納する際のメモリーのオーバーヘッドを回避できます。
結果セットをループして特定の値を検索したり、結果をフィルタリングしてより小さい結果セットにするかわりに、SELECT INTO文の問合せで検索またはフィルタリングを行います。

たとえば、単純な問合せではWHERE句を使用し、複数の結果セットを比較する問合せでは、INTERSECTやMINUSなどの集合演算子を使用します。集合演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
結果セットをループして各結果行に対して別の問合せを実行するかわりに、SELECT INTO文の問合せで副問合せを使用します(「副問合せによる問合せ結果セットの処理」を参照)。
結果セットをループして各結果行に対して別のDML文を実行するかわりに、FORALL文を使用します(「FORALL文」を参照)。

12.4.2.2 BULK COLLECT句を使用したFETCH文

BULK COLLECT句付きのFETCH文(FETCH BULK COLLECT文とも呼ばれます)は、結果セット全体をフェッチして1つ以上のコレクション変数に入れます。

詳細は、「FETCH文」を参照してください。

例12-22では、FETCH BULK COLLECT文を使用し、結果セット全体をフェッチして2つのコレクション(ネストした表)に入れます。

例12-23では、FETCH BULK COLLECT文を使用し、結果セットをフェッチしてレコードのコレクション(ネストした表)に入れます。

例12-22 2つのネストした表へのバルク・フェッチ

DECLARE
  TYPE NameList IS TABLE OF employees.last_name%TYPE;
  TYPE SalList IS TABLE OF employees.salary%TYPE;

  CURSOR c1 IS
    SELECT last_name, salary
    FROM employees
    WHERE salary > 10000
    ORDER BY last_name;

  names  NameList;
  sals   SalList;

  TYPE RecList IS TABLE OF c1%ROWTYPE;
  recs RecList;

  v_limit PLS_INTEGER := 10;

  PROCEDURE print_results IS
  BEGIN
    -- Check if collections are empty:

    IF names IS NULL OR names.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No results!');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Result: ');
      FOR i IN names.FIRST .. names.LAST
      LOOP
        DBMS_OUTPUT.PUT_LINE('  Employee ' || names(i) || ': $' || sals(i));
      END LOOP;
    END IF;
  END;

BEGIN
  DBMS_OUTPUT.PUT_LINE ('--- Processing all results simultaneously ---');
  OPEN c1;
  FETCH c1 BULK COLLECT INTO names, sals;
  CLOSE c1;
  print_results();
  DBMS_OUTPUT.PUT_LINE ('--- Processing ' || v_limit || ' rows at a time ---');
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
    EXIT WHEN names.COUNT = 0;
    print_results();
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE ('--- Fetching records rather than columns ---');
  OPEN c1;
  FETCH c1 BULK COLLECT INTO recs;
  FOR i IN recs.FIRST .. recs.LAST
  LOOP
    -- Now all columns from result set come from one record
    DBMS_OUTPUT.PUT_LINE (
      '  Employee ' || recs(i).last_name || ': $' || recs(i).salary
    );
  END LOOP;
END;
/

結果:

--- Processing all results simultaneously ---
Result:
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $10418.1
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $10036.95
Employee King: $24000
Employee Kochhar: $17000
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Weiss: $10418.1
Employee Zlotkey: $10500
--- Processing 10 rows at a time ---
Result:
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $10418.1
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $10036.95
Employee King: $24000
Result:
Employee Kochhar: $17000
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Weiss: $10418.1
Employee Zlotkey: $10500
--- Fetching records rather than columns ---
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $10418.1
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $10036.95
Employee King: $24000
Employee Kochhar: $17000
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Weiss: $10418.1
Employee Zlotkey: $10500

例12-23 レコードのネストした表へのバルク・フェッチ

DECLARE
  CURSOR c1 IS
    SELECT first_name, last_name, hire_date
    FROM employees;
  
  TYPE NameSet IS TABLE OF c1%ROWTYPE;
  stock_managers  NameSet;  -- nested table of records
 
  TYPE cursor_var_type is REF CURSOR;
  cv cursor_var_type;
 
BEGIN 
  -- Assign values to nested table of records:
 
  OPEN cv FOR
    SELECT first_name, last_name, hire_date
    FROM employees
    WHERE job_id = 'ST_MAN'
    ORDER BY hire_date;
 
  FETCH cv BULK COLLECT INTO stock_managers;
  CLOSE cv;
 
  -- Print nested table of records:
 
    FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
      DBMS_OUTPUT.PUT_LINE (
        stock_managers(i).hire_date || ' ' ||
        stock_managers(i).last_name  || ', ' ||
        stock_managers(i).first_name
      );
    END LOOP;END;
/

結果:

01-MAY-03 Kaufling, Payam
18-JUL-04 Weiss, Matthew
10-APR-05 Fripp, Adam
10-OCT-05 Vollman, Shanta
16-NOV-07 Mourgos, Kevin

12.4.2.2.1 FETCH BULK COLLECT文の行の制限

大量の行を戻すFETCH BULK COLLECT文では、大規模なコレクションが生成されます。行数およびコレクション・サイズを制限するには、LIMIT句を使用します。

例12-24では、LOOP文が繰り返されるたびに、FETCH文によって10行(またはそれ以下)が連想配列empidsにフェッチされます(前の値は上書きされます)。LOOP文の終了条件に注意してください。

例12-24 LIMITによるバルクFETCHの制限

DECLARE
  TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  CURSOR c1 IS
    SELECT employee_id
    FROM employees
    WHERE department_id = 80
    ORDER BY employee_id;

  empids  numtab;
BEGIN
  OPEN c1;
  LOOP  -- Fetch 10 rows or fewer in each iteration
    FETCH c1 BULK COLLECT INTO empids LIMIT 10;
    DBMS_OUTPUT.PUT_LINE ('------- Results from One Bulk Fetch --------');
    FOR i IN 1..empids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE ('Employee Id: ' || empids(i));
    END LOOP;
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
  CLOSE c1;
END;
/

結果:

------- Results from One Bulk Fetch --------
Employee Id: 145
Employee Id: 146
Employee Id: 147
Employee Id: 148
Employee Id: 149
Employee Id: 150
Employee Id: 151
Employee Id: 152
Employee Id: 153
Employee Id: 154
------- Results from One Bulk Fetch --------
Employee Id: 155
Employee Id: 156
Employee Id: 157
Employee Id: 158
Employee Id: 159
Employee Id: 160
Employee Id: 161
Employee Id: 162
Employee Id: 163
Employee Id: 164
------- Results from One Bulk Fetch --------
Employee Id: 165
Employee Id: 166
Employee Id: 167
Employee Id: 168
Employee Id: 169
Employee Id: 170
Employee Id: 171
Employee Id: 172
Employee Id: 173
Employee Id: 174
------- Results from One Bulk Fetch --------
Employee Id: 175
Employee Id: 176
Employee Id: 177
Employee Id: 179

12.4.2.3 BULK COLLECT句を使用したRETURNING INTO句

BULK COLLECT句付きのRETURNING INTO句(RETURNING BULK COLLECT INTO句とも呼ばれます)は、INSERT文、UPDATE文、DELETE文またはEXECUTE IMMEDIATE文で使用できます。RETURNING BULK COLLECT INTO句を使用すると、文によってその結果セットが1つ以上のコレクションに格納されます。

詳細は、「RETURNING INTO句」を参照してください。

例12-25では、RETURNING BULK COLLECT INTO句付きのDELETE文を使用して、表から行を削除し、それらの行を2つのコレクション(ネストした表)に戻します。

例12-25 2つのネストした表に対する削除行の戻し

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id;

DECLARE
  TYPE NumList IS TABLE OF employees.employee_id%TYPE;
  enums  NumList;
  TYPE NameList IS TABLE OF employees.last_name%TYPE;
  names  NameList;
BEGIN
  DELETE FROM emp_temp
  WHERE department_id = 30
  RETURNING employee_id, last_name
  BULK COLLECT INTO enums, names;

  DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
  FOR i IN enums.FIRST .. enums.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Employee #' || enums(i) || ': ' || names(i));
  END LOOP;
END;
/

結果:

Deleted 6 rows:
Employee #114: Raphaely
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares

12.4.3 FORALL文とBULK COLLECT句の併用

FORALL文では、DML文にRETURNING BULK COLLECT INTO句を含めることができます。FORALL文が繰り返されるたびに、DML文は前の値を上書きすることなく、指定された値を指定されたコレクションに格納します(この動作は、FOR LOOP文における同じDML文の処理と同様です)。

例12-26では、FORALL文で、RETURNING BULK COLLECT INTO句が含まれるDELETE文を実行します。FORALL文が繰り返されるたびに、DELETE文は、削除された行のemployee_idおよびdepartment_idの値をそれぞれコレクションe_idsおよびd_idsに格納します。

例12-27は、例12-26と類似していますが、FORALL文のかわりにFOR LOOP文を使用しています。

例12-26 FORALL文でのRETURN BULK COLLECT INTO付きのDELETE

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id, department_id;

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts  NumList := NumList(10,20,30);

  TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
  e_ids  enum_t;

  TYPE dept_t IS TABLE OF employees.department_id%TYPE;
  d_ids  dept_t;

BEGIN
  FORALL j IN depts.FIRST..depts.LAST
    DELETE FROM emp_temp
    WHERE department_id = depts(j)
    RETURNING employee_id, department_id
    BULK COLLECT INTO e_ids, d_ids;

  DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');

  FOR i IN e_ids.FIRST .. e_ids.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE (
      'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)
    );
  END LOOP;
END;
/

結果:

Deleted 9 rows:
Employee #200 from dept #10
Employee #201 from dept #20
Employee #202 from dept #20
Employee #114 from dept #30
Employee #115 from dept #30
Employee #116 from dept #30
Employee #117 from dept #30
Employee #118 from dept #30
Employee #119 from dept #30

例12-27 FOR LOOP文でのRETURN BULK COLLECT INTO付きのDELETE

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts  NumList := NumList(10,20,30);
 
  TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
  e_ids  enum_t;
 
  TYPE dept_t IS TABLE OF employees.department_id%TYPE;
  d_ids  dept_t;
 
BEGIN
  FOR j IN depts.FIRST..depts.LAST LOOP
    DELETE FROM emp_temp
    WHERE department_id = depts(j)
    RETURNING employee_id, department_id
    BULK COLLECT INTO e_ids, d_ids;
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');
 
  FOR i IN e_ids.FIRST .. e_ids.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE (
      'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)
    );
  END LOOP;
END;
/

結果:

Deleted 6 rows:
Employee #114 from dept #30
Employee #115 from dept #30
Employee #116 from dept #30
Employee #117 from dept #30
Employee #118 from dept #30
Employee #119 from dept #30

12.4.4 ホスト配列のクライアント・バルク・バインド

クライアント・プログラム(OCIプログラムやPro*Cプログラムなど)は、PL/SQLの無名ブロックを使用してホスト配列をバルク・バインド入出力できます。これは、コレクションをデータベース・サーバーとの間でやり取りするのに最も効率的な方法です。

クライアント・プログラムで、値を宣言し、無名ブロックで参照されるホスト変数にそれらの値を代入します。無名ブロックでは、各ホスト変数名にコロン(:)を接頭辞として付け、PL/SQLのコレクション変数名と区別します。クライアント・プログラムが実行されると、データベース・サーバーでPL/SQLの無名ブロックが実行されます。

例12-28では、無名ブロックでFORALL文を使用してホスト入力配列をバルク・バインドします。FORALL文において、DELETE文は、スカラーlower、upper、emp_idおよび配列deptsという4つのホスト変数を参照します。

例12-28 無名ブロックによる入力ホスト配列のバルク・バインド

BEGIN
  FORALL i IN :lower..:upper
    DELETE FROM employees
    WHERE department_id = :depts(i);
END;
/

12.5 複数変換用のパイプライン・テーブル・ファンクションの連鎖

パイプライン・テーブル・ファンクションの連鎖は、複数のデータ変換を実行する場合に効率的な方法です。

ノート:

パイプライン・テーブル・ファンクションは、データベース・リンクを介して実行することはできません。パイプライン・テーブル・ファンクションの戻り型がユーザー定義のSQL型であり、単一データベース内でのみ使用可能であるためです(『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照)。パイプライン・テーブル・ファンクションの戻り型がPL/SQL型のように見える場合でも、そのPL/SQL型は実際にはデータベースによって対応するユーザー定義のSQL型に変換されます。

ここでのトピック

12.5.1 テーブル・ファンクションの概要

テーブル・ファンクションは、行のコレクション(連想配列、ネストした表またはVARRAY)を戻すユーザー定義のPL/SQLファンクションです。

SELECT文のTABLE句の内部でテーブル・ファンクションを起動することで、データベース表のようにこのコレクションから要素を選択できます。TABLE演算子は省略可能です。

たとえば:

SELECT * FROM TABLE(table_function_name(parameter_list))

次のように、同等の問合せをTABLE演算子なしで記述できます。

SELECT * FROM table_function_name(parameter_list)

テーブル・ファンクションは、入力として行のコレクションを使用することができます(入力パラメータとしてネストした表、VARRAYまたはカーソル変数を含めることができます)。したがって、テーブル・ファンクションtf1からの出力をテーブル・ファンクションtf2に入力したり、tf2からの出力をテーブル・ファンクションtf3に入力することなどができます。

テーブル・ファンクションのパフォーマンスを向上するには、次の操作を実行します。

PARALLEL_ENABLEオプションを使用して、ファンクションのパラレル実行を有効にします。

パラレル実行が有効化されたファンクションは、同時に実行できます。
Oracle Streamsを使用して、次のプロセスにファンクションの結果をストリーム化して直接送ります。

ストリームによって、プロセス間の中間的なステージングが排除されます。
PIPELINEDオプションを使用して、ファンクションの結果をパイプライン化します。

パイプライン・テーブル・ファンクションは、行を処理した直後に起動元に行を戻し、行の処理を継続します。問合せで1つの結果行を戻す前にコレクション全体を組み立ててサーバーに戻す必要がないため、応答時間が短縮されます。(オブジェクト・キャッシュでコレクション全体をマテリアライズする必要がないため、ファンクションのメモリー消費量も減少します。)

注意:

パイプライン・テーブル・ファンクションは、常にデータの現在の状態を参照します。コレクションに対するカーソルのオープン後にコレクションのデータが変更されると、カーソルにその変更が反映されます。PL/SQL変数は単一セッション内でのみ有効であり、トランザクション対応ではありません。このため、表データへの適用性がよく知られている読取り一貫性は、PL/SQLコレクション変数には適用されません。

関連項目:

パイプライン・テーブル・ファンクションの連鎖
SELECT文のTABLE句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
パイプライン・テーブル・ファンクションおよびパラレル・テーブル・ファンクションを使用する方法の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。

12.5.2 パイプライン・テーブル・ファンクションの作成

パイプライン・テーブル・ファンクションは、スタンドアロン・ファンクションまたはパッケージ・ファンクションのいずれかである必要があります。

PIPELINEDオプション(必須)

スタンドアロン・ファンクションでは、CREATE FUNCTION文にPIPELINEDオプションを指定します(構文は、「CREATE FUNCTION文」を参照)。パッケージ・ファンクションでは、ファンクション宣言とファンクション定義の両方にPIPELINEDオプションを指定します(構文は、「ファンクションの宣言および定義」を参照してください)。

PARALLEL_ENABLEオプション(推奨)

パフォーマンスを向上するには、PARALLEL_ENABLEオプションを指定して、パイプライン・テーブル・ファンクションのパラレル実行を有効にします。

AUTONOMOUS_TRANSACTIONプラグマ

パイプライン・テーブル・ファンクションでDML文を実行する場合、AUTONOMOUS_TRANSACTIONプラグマ(「AUTONOMOUS_TRANSACTIONプラグマ」を参照)を使用すると、ファンクションが自律型になります。その後、パラレル実行中に、ファンクションの各インスタンスが独立したトランザクションを作成します。

DETERMINISTICオプション(推奨)

パイプライン・テーブル・ファンクションを同じ問合せまたは別の問合せで複数回起動すると、基礎となる実装が複数回実行されます。ファンクションが決定的である場合、DETERMINISTICオプションを指定します(DETERMINISTIC句を参照)。

パラメータ

通常、パイプライン・テーブル・ファンクションには、1つ以上のカーソル変数パラメータが含まれます。ファンクション・パラメータとしてのカーソル変数の詳細は、「サブプログラム・パラメータとしてのカーソル変数」を参照してください。

関連項目:

カーソル変数の一般情報は、「カーソル変数」を参照してください
サブプログラムのパラメータの詳細は、「サブプログラムのパラメータ」を参照してください

RETURNデータ型

パイプライン・テーブル・ファンクションが戻す値のデータ型は、スキーマ・レベルまたはパッケージ内で定義されたコレクション型である必要があります(そのため、結合配列型にはできません)。コレクション型の要素は、PL/SQLによってのみサポートされるデータ型(PLS_INTEGERやBOOLEANなど)ではなく、SQLデータ型にする必要があります。コレクション型の詳細は、「コレクション型」を参照してください。SQLデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

SQLデータ型のANYTYPE、ANYDATAおよびANYDATASETを使用して、オブジェクト型やコレクション型などの他のSQL型の型記述、データ・インスタンスおよびデータ・インスタンス・セットを動的にカプセル化してアクセスできます。また、これらの型を使用すると、匿名コレクション型などの名前を持たない型を作成できます。これらの型の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

PIPE ROW文

パイプライン・テーブル・ファンクションの内部でPIPE ROW文を使用すると、制御を起動元に戻すことなく、起動元にコレクション要素を戻すことができます。構文およびセマンティクスの詳細は、「PIPE ROW文」を参照してください。

RETURN文

他のすべてのファンクションと同様に、パイプライン・テーブル・ファンクションのすべての実行パスは、RETURN文に導かれ、それによって制御が起動元に戻される必要があります。ただし、パイプライン・テーブル・ファンクションでは、RETURN文で起動元に値を戻す必要はありません。その構文およびセマンティクスは、「RETURN文」を参照してください。

例

例12-29 パイプライン・テーブル・ファンクションの作成および起動

この例では、パイプライン・テーブル・ファンクションf1を含むパッケージを作成し、f1が戻す行のコレクションから要素を選択します。

CREATE OR REPLACE PACKAGE pkg1 AUTHID DEFINER AS
  TYPE numset_t IS TABLE OF NUMBER;
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

要素(1,2,3,... x)のコレクションを戻すパイプライン・テーブル・ファンクションf1を作成します。

CREATE OR REPLACE PACKAGE BODY pkg1 AS
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN 1..x LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END f1;
END pkg1;

SELECT * FROM TABLE(pkg1.f1(5));

結果:

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
 
5 rows selected.

SELECT * FROM pkg1.f1(2);

結果:

COLUMN_VALUE
------------
           1
           2

12.5.3 変換ファンクションとしてのパイプライン・テーブル・ファンクション

カーソル変数パラメータを持つパイプライン・テーブル・ファンクションは、変換ファンクションとして使用できます。ファンクションは、カーソル変数を使用して入力行をフェッチします。ファンクションは、PIPE ROW文を使用して、変換された1つ以上の行を起動元にパイプします。FETCH文およびPIPE ROW文がLOOP文の内側にある場合、ファンクションで複数の入力行を変換できます。

例12-30では、パイプライン・テーブル・ファンクションによって、employees表の各選択行がネストした表の2つの行に変換され、起動元のSELECT文にパイプされます。仮カーソル変数パラメータに対応する実パラメータは、CURSOR式です(詳細は、「パイプライン・テーブル・ファンクションへのCURSOR式の引渡し」を参照してください)。

例12-30 パイプライン・テーブル・ファンクションによる各行の2つの行への変換

CREATE OR REPLACE PACKAGE refcur_pkg AUTHID DEFINER IS
  TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE outrec_typ IS RECORD (
    var_num    NUMBER(6),
    var_char1  VARCHAR2(30),
    var_char2  VARCHAR2(30)
  );
  TYPE outrecset IS TABLE OF outrec_typ;
  FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
  FUNCTION f_trans (p refcur_t) RETURN outrecset PIPELINED IS
    out_rec outrec_typ;
    in_rec  p%ROWTYPE;
  BEGIN
    LOOP
      FETCH p INTO in_rec;  -- input row
      EXIT WHEN p%NOTFOUND;

      out_rec.var_num := in_rec.employee_id;
      out_rec.var_char1 := in_rec.first_name;
      out_rec.var_char2 := in_rec.last_name;
      PIPE ROW(out_rec);  -- first transformed output row

      out_rec.var_char1 := in_rec.email;
      out_rec.var_char2 := in_rec.phone_number;
      PIPE ROW(out_rec);  -- second transformed output row
    END LOOP;
    CLOSE p;
    RETURN;
  END f_trans;
END refcur_pkg;
/

SELECT * FROM TABLE (
  refcur_pkg.f_trans (
    CURSOR (SELECT * FROM employees WHERE department_id = 60)
  )
);

結果:

   VAR_NUM VAR_CHAR1                      VAR_CHAR2
---------- ------------------------------ ------------------------------
       103 Alexander                      Hunold
       103 AHUNOLD                        590.423.4567
       104 Bruce                          Ernst
       104 BERNST                         590.423.4568
       105 David                          Austin
       105 DAUSTIN                        590.423.4569
       106 Valli                          Pataballa
       106 VPATABAL                       590.423.4560
       107 Diana                          Lorentz
       107 DLORENTZ                       590.423.5567

10 rows selected.

12.5.4 パイプライン・テーブル・ファンクションの連鎖

パイプライン・テーブル・ファンクションのtf1とtf2を連鎖するには、tf1の出力をtf2の入力にします。たとえば:

SELECT * FROM TABLE(tf2(CURSOR(SELECT * FROM TABLE(tf1()))));

tf1によってパイプ出力される行は、tf2の仮入力パラメータと互換性のある実パラメータである必要があります。

連鎖されたパイプライン・テーブル・ファンクションでパラレル実行が有効化されていると、各ファンクションは異なるプロセス(またはプロセスのセット)で実行されます。

関連項目:

「パイプライン・テーブル・ファンクションへのCURSOR式の引渡し」

12.5.5 パイプライン・テーブル・ファンクションの結果からのフェッチ

名前付きカーソルは、パイプライン・テーブル・ファンクションを起動する問合せに関連付けることができます。このようなカーソルに特別なフェッチ・セマンティクスはなく、また、このようなカーソル変数に特別な代入セマンティクスはありません。

ただし、SQLオプティマイザでは、PL/SQL文にまたがる最適化は行われません。したがって、例12-31で、2番目のPL/SQL文には2つのSQL文を実行するオーバーヘッドがありますが、1番目のPL/SQL文は2番目の文より遅くなり、この結果は、1番目のPL/SQL文の2つのSQL文の間でファンクション結果がパイプされる場合でも変わりません。

例12-31で、fおよびgがパイプライン・テーブル・ファンクションであり、各ファンクションでカーソル変数パラメータを受け入れるとします。1番目のPL/SQL文は、カーソル変数rを、fを起動する問合せに関連付けて、rをgに渡します。2番目のPL/SQL文は、CURSOR式をfとgの両方に渡します。

関連項目:

「サブプログラム・パラメータとしてのカーソル変数」

例12-31 パイプライン・テーブル・ファンクションの結果からのフェッチ

DECLARE
  r SYS_REFCURSOR;
  ...
  -- First PL/SQL statement (slower):
BEGIN
  OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab)));
  SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));

  -- NOTE: When g completes, it closes r.
END;

-- Second PL/SQL statement (faster):

SELECT * FROM TABLE(g(CURSOR(SELECT * FROM
  TABLE(f(CURSOR(SELECT * FROM tab))))));
/

12.5.6 パイプライン・テーブル・ファンクションへのCURSOR式の引渡し

例12-31のように、パイプライン・テーブル・ファンクションのカーソル変数パラメータの実パラメータには、カーソル変数またはCURSOR式を使用できますが、後者の方が効率的です。

ノート:

SQLのSELECT文でCURSOR式をファンクションに渡す場合、参照先のカーソルは、ファンクションの実行開始時にオープンされ、ファンクションの完了時にクローズされます。

関連項目:

CURSOR式の一般情報は、「CURSOR式」を参照してください

例12-32では、2つのカーソル変数パラメータがあるパイプライン・テーブル・ファンクションを含むパッケージを作成し、実パラメータにCURSOR式を使用してSELECT文のファンクションを起動します。

例12-33では、パイプライン・テーブル・ファンクションを集計ファンクションとして使用し、入力行のセットを取得して1つの結果を戻します。ファンクションの結果は、SELECT文で選択します。(疑似列COLUMN_VALUEの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。)

例12-32 2つのカーソル変数パラメータを使用するパイプライン・テーブル・ファンクション

CREATE OR REPLACE PACKAGE refcur_pkg AUTHID DEFINER IS
  TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;
  TYPE outrec_typ IS RECORD (
    var_num    NUMBER(6),
    var_char1  VARCHAR2(30),
    var_char2  VARCHAR2(30)
  );
  TYPE outrecset IS TABLE OF outrec_typ;
  FUNCTION g_trans (p1 refcur_t1, p2 refcur_t2) RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE PACKAGE BODY refcur_pkg IS
  FUNCTION g_trans (
    p1 refcur_t1,
    p2 refcur_t2
  ) RETURN outrecset PIPELINED
  IS
    out_rec outrec_typ;
    in_rec1 p1%ROWTYPE;
    in_rec2 p2%ROWTYPE;
  BEGIN
    LOOP
      FETCH p2 INTO in_rec2;
      EXIT WHEN p2%NOTFOUND;
    END LOOP;
    CLOSE p2;
    LOOP
      FETCH p1 INTO in_rec1;
      EXIT WHEN p1%NOTFOUND;
      -- first row
      out_rec.var_num := in_rec1.employee_id;
      out_rec.var_char1 := in_rec1.first_name;
      out_rec.var_char2 := in_rec1.last_name;
      PIPE ROW(out_rec);
      -- second row
      out_rec.var_num := in_rec2.department_id;
      out_rec.var_char1 := in_rec2.department_name;
      out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
      PIPE ROW(out_rec);
    END LOOP;
    CLOSE p1;
    RETURN;
  END g_trans;
END refcur_pkg;
/

SELECT * FROM TABLE (
  refcur_pkg.g_trans (
    CURSOR (SELECT * FROM employees WHERE department_id = 60),
    CURSOR (SELECT * FROM departments WHERE department_id = 60)
  )
);

結果:

   VAR_NUM VAR_CHAR1                      VAR_CHAR2
---------- ------------------------------ ------------------------------
       103 Alexander                      Hunold
        60 IT                             1400
       104 Bruce                          Ernst
        60 IT                             1400
       105 David                          Austin
        60 IT                             1400
       106 Valli                          Pataballa
        60 IT                             1400
       107 Diana                          Lorentz
        60 IT                             1400
 
10 rows selected.

例12-33 集計ファンクションとしてのパイプライン・テーブル・ファンクション

DROP TABLE gradereport;
CREATE TABLE gradereport (
  student VARCHAR2(30),
  subject VARCHAR2(30),
  weight NUMBER,
  grade NUMBER
);

INSERT INTO gradereport (student, subject, weight, grade)
VALUES ('Mark', 'Physics', 4, 4);
 
INSERT INTO gradereport (student, subject, weight, grade) 
VALUES ('Mark','Chemistry', 4, 3);
 
INSERT INTO gradereport (student, subject, weight, grade) 
VALUES ('Mark','Maths', 3, 3);
 
INSERT INTO gradereport (student, subject, weight, grade) 
VALUES ('Mark','Economics', 3, 4);

CREATE PACKAGE pkg_gpa AUTHID DEFINER IS
  TYPE gpa IS TABLE OF NUMBER;
  FUNCTION weighted_average(input_values SYS_REFCURSOR)
    RETURN gpa PIPELINED;
END pkg_gpa;

CREATE PACKAGE BODY pkg_gpa IS
  FUNCTION weighted_average (input_values SYS_REFCURSOR)
    RETURN gpa PIPELINED
  IS
    grade         NUMBER;
    total         NUMBER := 0;
    total_weight  NUMBER := 0;
    weight        NUMBER := 0;
  BEGIN
    LOOP
      FETCH input_values INTO weight, grade;
      EXIT WHEN input_values%NOTFOUND;
      total_weight := total_weight + weight;  -- Accumulate weighted average
      total := total + grade*weight;
    END LOOP;
    PIPE ROW (total / total_weight);
    RETURN; -- returns single result
  END weighted_average;
END pkg_gpa;

この問合せは、テーブル・ファンクションをTABLE演算子なしで起動する方法を示しています。

SELECT w.column_value "weighted result" 
FROM pkg_gpa.weighted_average (
    CURSOR (SELECT weight, grade FROM gradereport)
  ) w;

結果:

weighted result
---------------
            3.5
 
1 row selected.

12.5.7 パイプライン・テーブル・ファンクションの結果に対するDML文

パイプライン・テーブル・ファンクションが戻す表を、DELETE文、INSERT文、UPDATE文またはMERGEのターゲット表にすることはできません。ただし、このような表のビューを作成し、そのビューに対してINSTEAD OFトリガーを作成できます。INSTEAD OFトリガーの詳細は、「INSTEAD OF DMLトリガー」を参照してください。

関連項目:

CREATE VIEW文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

12.5.8 NO_DATA_NEEDED例外

事前定義の例外NO_DATA_NEEDEDを理解する必要があるのは、次の2つの場合です。

PIPE ROW文が存在するブロックにOTHERS例外ハンドラを含める場合
PIPE ROW文を使用するコードの次に、クリーンアップ・プロシージャを続ける必要がある場合

通常、クリーンアップ・プロシージャは、コードで必要とされなくなったリソースを解放します。

パイプライン・テーブル・ファンクションの起動元がファンクションからの行をそれ以上必要としなくなると、PIPE ROW文によってNO_DATA_NEEDEDが呼び出されます。パイプライン・テーブル・ファンクションでNO_DATA_NEEDEDを処理しない場合、例12-34に示すとおり、ファンクションの起動は終了しますが、起動元の文は終了しません。パイプライン・テーブル・ファンクションでNO_DATA_NEEDEDを処理する場合、例12-35に示すとおり、例外ハンドラによって不要になったリソースを解放できます。

例12-34では、パイプライン・テーブル・ファンクションpipe_rowsでNO_DATA_NEEDED例外を処理しません。pipe_rowsを起動するSELECT文では、4つの行のみを必要とします。そのため、pipe_rowsの5回目の起動時に、PIPE ROW文によって例外NO_DATA_NEEDEDが呼び出されます。pipe_rowsの5回目の起動は終了しますが、SELECT文は終了しません。

PIPE ROW文が存在するブロックの例外処理部に、予期しない例外を処理するためのOTHERS例外ハンドラを含める場合、その例外処理部に予期されるNO_DATA_NEEDED例外のための例外ハンドラも含める必要があります。そうしない場合、NO_DATA_NEEDED例外は、OTHERS例外ハンドラで処理され、予期しないエラーとして扱われます。次の例外ハンドラでは、リカバリ不可能なエラーとして扱われるかわりに、NO_DATA_NEEDED例外が再度呼び出されます。

EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    RAISE;
  WHEN OTHERS THEN
    -- (Put error-logging code here)
    RAISE_APPLICATION_ERROR(-20000, 'Fatal error.');
END;

例12-35では、External_Sourceパッケージに次のパブリック項目が含まれると想定します。

プロシージャInit: Next_Rowが必要とするリソースの割当てと初期化を行います。
ファンクションNext_Row: 特定の外部ソースのデータを戻し、外部ソースのデータがなくなるとユーザー定義の例外Done(同じくパッケージ内のパブリック項目)を呼び出します。
プロシージャClean_Up: Initで割り当てたリソースを解放します。

パイプライン・テーブル・ファンクションget_external_source_dataは、External_Source.Next_Rowを起動して、次のいずれかの状態になるまで外部ソースから行をパイプします。

外部ソースの行がなくなった場合。

この場合、External_Source.Next_Rowファンクションによってユーザー定義の例外External_Source.Doneが呼び出されます。
get_external_source_dataで行を必要としなくなった場合

この場合、get_external_source_dataのPIPE ROW文によってNO_DATA_NEEDED例外が呼び出されます。

どちらの場合でも、get_external_source_dataのブロックbにある例外ハンドラによって、Next_Rowが使用していたリソースを解放するExternal_Source.Clean_Upが起動されます。

例12-34 パイプライン・テーブル・ファンクションで処理されないNO_DATA_NEEDED

CREATE TYPE t IS TABLE OF NUMBER
/
CREATE OR REPLACE FUNCTION pipe_rows RETURN t PIPELINED AUTHID DEFINER IS
  n NUMBER := 0;
BEGIN
  LOOP
    n := n + 1;
    PIPE ROW (n);
  END LOOP;
END pipe_rows;
/
SELECT COLUMN_VALUE
  FROM TABLE(pipe_rows())
  WHERE ROWNUM < 5
/

結果:

COLUMN_VALUE
------------
           1
           2
           3
           4

4 rows selected.

例12-35 パイプライン・テーブル・ファンクションで処理されるNO_DATA_NEEDED

CREATE OR REPLACE FUNCTION get_external_source_data
  RETURN t PIPELINED AUTHID DEFINER IS
BEGIN
  External_Source.Init();           -- Initialize.
  <<b>> BEGIN
    LOOP                            -- Pipe rows from external source.
      PIPE ROW (External_Source.Next_Row());
    END LOOP;
  EXCEPTION
    WHEN External_Source.Done THEN  -- When no more rows are available,
      External_Source.Clean_Up();   --  clean up.
    WHEN NO_DATA_NEEDED THEN        -- When no more rows are needed,
      External_Source.Clean_Up();   --  clean up.
      RAISE NO_DATA_NEEDED;           -- Optional, equivalent to RETURN.
  END b;
END get_external_source_data;
/

12.6 多相テーブル・ファンクションの概要

多相テーブル・ファンクション(PTF)とは、オペランドに複数の型を指定できるテーブル・ファンクションです。戻り値の型はPTF起動の引数リストによって決定されます。通常は表の型への実際の引数によって行の出力形状が決まりますが、異なる場合もあります。

多相テーブル・ファンクションについて

多相テーブル・ファンクション(PTF)はユーザー定義のファンクションで、SQL問合せブロックのFROM句から起動できます。定義時に行の型が宣言されない表を処理したり、定義時に行の型が必ずしも宣言されない結果表を生成したりできます。多相テーブル・ファンクションは動的SQL機能を利用して、強力かつ複雑なカスタム・ファンクションを作成します。これが便利なのは、アプリケーションのインタフェースに、任意的な入力表または問合せに対応できる汎用的な拡張機能が求められる場合です。

PTF作成者は、表を定義する手続き型メカニズムへのインタフェースを作成します。PTF作成者はPTFの定義、ドキュメント化および実装を行います。

問合せ作成者は、公開されたインタフェースを記述して、問合せ内でPTFを起動することしかできません。

データベースはPTFの指揮者として機能します。これがPTFのコンパイルと実行状態を管理します。データベースおよびPTF作成者は、関連するSQLで起動されるプロシージャのファミリや、呼び出されたPTFコンポーネント・プロシージャ、場合によってはプライベート・データ(変数やカーソルなど)を見ることができます。

多相テーブル・ファンクションの型

多相テーブル・ファンクションの型は仮引数リストのセマンティクスに基づいて指定されます。

入力TABLE引数がRow Semanticsである場合、入力は1行です。
入力TABLE引数がTable Semanticsである場合、入力は行のセットです。Table Semantics PTFを問合せから呼び出すときに、PARTITION BY句またはORDER BY句のいずれかまたは両方を使用して表引数を拡張できます。

12.6.1 多相テーブル・ファンクションの定義

PTF作成者は多相テーブル・ファンクション(PTF)の定義、ドキュメント化および実装を行います。

PTFは2つの部分で構成されます。

1. PTF実装のクライアント・インタフェースを含むPL/SQLパッケージ。

2. PTFおよび関連付けられている実装パッケージを指定するスタンドアロン・ファンクションまたはパッケージ・ファンクション。

12.6.2 多相テーブル・ファンクションの実装

多相テーブル・ファンクション(PTF)実装のクライアント・インタフェースは固定名を持つサブプログラムのセットであり、すべてのPTFがこれを提供する必要があります。

多相テーブル・ファンクションの実装ステップ

DESCRIBEファンクション(必須)と、OPEN、FETCH_ROWSおよびCLOSEプロシージャ(任意)を含む実装パッケージを作成します。
PTFを指定したファンクションの仕様部を作成します。パッケージ作成後の最上位レベルでファンクションを作成するか、または実装パッケージ(最初のステップで作成したパッケージ)内でパッケージ・ファンクションとしてファンクションを作成します。多相テーブル・ファンクションにはファンクション定義( FUNCTION BODY)がありません。定義は関連付けられている実装パッケージにカプセル化されます。

ファンクション定義では次を指定します。
- 多相テーブル・ファンクション(PTF)名
- TABLE型の仮引数を1つ、および任意の数の非TABLE型の引数
- PTFの戻り型TABLE
- PTFファンクションの型(rowまたはtableセマンティクス)
- PTF実装パッケージ名

関連項目:

DESCRIBEのみの多相テーブル・ファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』
PTF実装パッケージの指定方法とDBMS_TFユーティリティの使用方法の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』
スタンドアロンまたはパッケージ多相テーブル・ファンクションを作成する構文およびセマンティクの詳細は、PIPELINED句

12.6.3 多相テーブル・ファンクションの起動

多相テーブル・ファンクションを起動するには、SQL問合せブロックのFROM句でファンクション名を指定し、その後に引数リストを指定します。

PTF引数は通常のテーブル・ファンクションに渡すことができる標準スカラー引数として指定することもできますが、これに加えてPTFでは表引数を取ることができます。表引数は、WITH句問合せまたはFROM句で許可されているスキーマ・レベルのオブジェクト(表、ビューまたはテーブル・ファンクションなど)のいずれかです。

構文

table_argument ::= table [ PARTITION BY column_list ] [ORDER BY order_column_list]

column_list ::= identifier | ( identifier[, identifier…])

order_column_list ::= order_column_name | (order_column_name [, order_column_name…])

order_column_name ::= identifier [ ASC | DESC ][ NULLS FIRST | NULLS LAST ]

セマンティクス

各識別子は対応する表内の1つの列です。

PTFにはTable Semanticsがあります。

必要に応じて、問合せでTable Semantics PTF入力をパーティション化したり順序付けしたりできます。これはRow Semantics PTF入力では許可されません。

多相テーブル・ファンクション(PTF)をDML文のターゲットにすることはできません。PTFの表引数は名前で渡されます。

たとえば、noop PTFは次のような問合せで使用できます。

SELECT * 
FROM noop(emp);

または

WITH e AS 
 (SELECT * FROM emp NATURAL JOIN dept)
SELECT t.* FROM noop(e) t;

入力表引数は、基本的な表名である必要があります。

表の識別子の名前解決ルールは次のとおりです(優先順)。

識別子が列名(外側の問合せブロックからの相関列など)に解決されます。
識別子が現在の問合せブロック内または一部の外側の問合せブロック内の共通表式(CTE)名に解決されます。CTEは一般にWITH句として知られています。
識別子がスキーマ・レベルの表、ビューまたはテーブル・ファンクション(通常または多相で、スキーマ・レベルまたはパッケージ内で定義される)に解決されます。

表の式の多くのタイプは、FROM句で許可されるとしても、PTFの表引数として直接使用することはできません(ANSI結合、バインド変数、インライン・ビュー、CURSOR演算子、TABLE演算子など)。このような表の式をPTF引数として使用するには、これらの式をCTEでラップし、CTE名をPTFに渡す間接的な方法を使用します。

PTFはFROM句で表参照として使用できるため、ANSI結合やLATERAL構文の一部にすることができます。また、PTFはPIVOTまたはUNPIVOTおよびMATCH_RECOGNIZEのソース表にすることができます。表やビューを対象にした一部の表変更の句(SAMPLING、PARTITION、CONTAINERSなど)は、PTFでは許可されません。

PTFの直接的なファンクション合成は許可されます(ネストされたPTFカーソル式の起動やPTF(TF())ネスティングなど)。ただし、ネストされたPTFは許可されません(PTF(PTF())のようなネスティング)。

PTFのスカラー引数として任意のSQLスカラー式を指定できます。定数のスカラー値はDESCRIBEファンクションにそのまま渡されますが、その他の値はすべてNULLとして渡されます。これらの値が行の形状を決定するものでなければ、通常これがPTF実装で問題になることはありませんが、そうでない場合DESCRIBEファンクションでエラーが発生することがあります。通常、PTF付属のドキュメントに、どれがスカラー・パラメータで(存在する場合)、それゆえNULL以外の定数値を必要とするとの記載があります。問合せの実行中(OPEN、FETCH_ROWS、CLOSEの間)に式が評価され、これらのPTF実行プロシージャに実際の値が渡されます。戻り値の型はPTF起動の引数リストによって決定されます。

問合せ引数はWITH句を使用して、PTFに渡されます。

テーブル・ファンクションの引数リストまたは空のlist ()がある場合、TABLE演算子は省略できます。

12.6.3.1 可変個引数擬似演算子

可変個引数擬似演算子は、可変数のオペランドで動作します。

Oracle Databaseリリース18c以上では、可変個引数擬似演算子の概念をSQL式言語に導入し、多相テーブル・ファンクション(PTF)をサポートします。擬似演算子を使用すると、識別子のリスト(列名など)をPTFに渡すことができます。疑似演算子は、PTFへの引数としてのみ表示され、他のSQL演算子やPL/SQLファンクションの起動などと同様にSQLコンパイラによって解析されます。疑似演算子の引数は可変数ですが、少なくとも1つは必要です。疑似演算子には関連付けられている実行関数がなく、PTFコンパイルが終了した後にSQLカーソルから完全に削除されます。SQLのコンパイル中に、疑似演算子は対応するDBMS_TF型に変換されてからDESCRIBEメソッドに渡されます。これらの演算子に関連付けられた出力タイプはありません。汎用のSQL式に擬似演算子を埋め込むことはできません。

12.6.3.2 COLUMNS擬似演算子

COLUMNS擬似演算子を使用して、SQL問合せブロックのFROM句で、多相テーブル・ファンクション(PTF)の起動の引数を指定できます。

COLUMNS疑似演算子の引数は、列名のリスト、または関連付けられたタイプを使用して列名のリストを指定します。

構文

column_operator ::= COLUMNS ( column_list )

column_list ::= column_name_list | column_type_list

column_name_list ::= identifier [, identifier ... ]

column_type_list::= identifier column_type [, identifier column_type…]

セマンティクス

COLUMNS疑似演算子はPTFへの引数としてのみ指定できます。PTF式自体以外のその他のSQL式では使用できません。

column_typeはスカラー型である必要があります。

12.6.4 多相テーブル・ファンクションのコンパイルおよび実行

データベースは多相テーブル・ファンクション(PTF)の指揮者の役割を担います。そのため、PTFのコンパイルから実行、および関連する状態までを管理します。

データベースは次を管理します。

コンパイル状態: DESCRIBEによって生成される不変の状態で、実行前に必要です。
実行状態: Table semantics PTFの実行プロシージャによって使用される状態です。

関連項目:

データベースがPTFのコンパイルと実行状態を管理する方法の詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンス

12.6.5 多相テーブル・ファンクションの最適化

多相テーブル・ファンクション(PTF)は、データベースの分析機能を拡張するための効率的でスケーラブルなフレームワークを提供します。

主な利点は次のとおりです。

最小限のデータ移動: 関心のある列のみがPTFに渡されます。
述語/射影/パーティション化が基礎となる表/問合せ(意味的に可能な場合)にプッシュされます。
PTF内外へのデータの一括移動
並列度は、PTFおよび問合せに指定されたパーティション(ある場合)のタイプに基づきます

12.6.6 Skip_col多相テーブル・ファンクションの例

このPTF例は、Row Semantics、Describeのみ、パッケージ・テーブル・ファンクション、およびオーバーロード機能を示しています。

関連項目:

その他の多相テーブル・ファンクションの(PTF)の例については、Oracle Database PL/SQLパッケージおよびタイプ・リファレンス

例12-36 Skip_col多相テーブル・ファンクションの例

skip_col多相テーブル・ファンクション(PTF)は、PTF入力引数で指定した列を除く、表内のすべての列を返します。skip_col PTFは列名(overload 1)または列データ型(overload 2)に基づいて列をスキップします。

Live SQL:

このサンプルは、18c Skip_col多相テーブル・ファンクションのOracle Live SQLで表示および実行できます

skip_col多相テーブル・ファンクション(PTF)に対して、DESCRIBEファンクションを含むskip_col_pkgという名前の実装パッケージを作成します。PTFによって生成された行の形状を決定するために、DESCRIBEファンクションが起動されます。DBMS_TF.DESCRIBE_T表を戻します。オーバーロードされています。FETCH_ROWSプロシージャは、指定された行のサブセットに対して関連する新しい列値を生成する必要があるため、必須ではありません。

CREATE PACKAGE skip_col_pkg AS

  -- OVERLOAD 1: Skip by name --
  FUNCTION skip_col(tab TABLE, 
                    col COLUMNS)
           RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;

  FUNCTION describe(tab IN OUT DBMS_TF.TABLE_T, 
                    col        DBMS_TF.COLUMNS_T)
           RETURN DBMS_TF.DESCRIBE_T;

  -- OVERLOAD 2: Skip by type --
  FUNCTION skip_col(tab       TABLE, 
                    type_name VARCHAR2,
                    flip      VARCHAR2 DEFAULT 'False') 
           RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;

  FUNCTION describe(tab       IN OUT DBMS_TF.TABLE_T, 
                    type_name        VARCHAR2, 
                    flip             VARCHAR2 DEFAULT 'False') 
           RETURN DBMS_TF.DESCRIBE_T;

END skip_col_pkg;

多相テーブル・ファンクション定義を含む実装パッケージ本体を作成します。

CREATE PACKAGE BODY skip_col_pkg AS

/* OVERLOAD 1: Skip by name 
 * Package PTF name:  skip_col_pkg.skip_col
 * Standalone PTF name: skip_col_by_name
 *
 * PARAMETERS:
 * tab - The input table
 * col - The name of the columns to drop from the output
 *
 * DESCRIPTION:
 *   This PTF removes all the input columns listed in col from the output
 *   of the PTF.
*/  
 FUNCTION  describe(tab IN OUT DBMS_TF.TABLE_T, 
                    col        DBMS_TF.COLUMNS_T)
            RETURN DBMS_TF.DESCRIBE_T
  AS 
    new_cols DBMS_TF.COLUMNS_NEW_T;
    col_id   PLS_INTEGER := 1;
  BEGIN 
    FOR i IN 1 .. tab.column.count() LOOP
      FOR j IN 1 .. col.count() LOOP
        tab.column(i).PASS_THROUGH := tab.column(i).DESCRIPTION.NAME != col(j);
        EXIT WHEN NOT tab.column(i).PASS_THROUGH;
      END LOOP;
    END LOOP;

    RETURN NULL;
  END;  

/* OVERLOAD 2: Skip by type
 * Package PTF name:  skip_col_pkg.skip_col
 * Standalone PTF name: skip_col_by_type
 *
 * PARAMETERS:
 *   tab       - Input table
 *   type_name - A string representing the type of columns to skip
 *   flip      - 'False' [default] => Match columns with given type_name
 *               otherwise         => Ignore columns with given type_name
 *
 * DESCRIPTION:
 *   This PTF removes the given type of columns from the given table. 
*/ 
  FUNCTION describe(tab       IN OUT DBMS_TF.TABLE_T, 
                    type_name        VARCHAR2, 
                    flip             VARCHAR2 DEFAULT 'False') 
           RETURN DBMS_TF.DESCRIBE_T 
  AS 
    typ CONSTANT VARCHAR2(1024) := UPPER(TRIM(type_name));
  BEGIN 
    FOR i IN 1 .. tab.column.count() LOOP
       tab.column(i).PASS_THROUGH := 
         CASE UPPER(SUBSTR(flip,1,1))
           WHEN 'F' THEN DBMS_TF.column_type_name(tab.column(i).DESCRIPTION)!=typ
           ELSE          DBMS_TF.column_type_name(tab.column(i).DESCRIPTION) =typ
         END /* case */;
    END LOOP;

    RETURN NULL;
  END;

END skip_col_pkg;

overload 1にskip_col_by_nameという名前のスタンドアロン多相テーブル・ファンクションを作成します。TABLE型の正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTFタイプを指定し、使用するPTF実装パッケージをskip_col_pkgに指定します。

CREATE FUNCTION skip_col_by_name(tab TABLE, 
                                 col COLUMNS)
                  RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;

overload 2にskip_col_by_typeという名前のスタンドアロン多相テーブル・ファンクションを作成します。TABLE型の正式引数を1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTFタイプを指定し、使用するPTF実装パッケージをskip_col_pkgに指定します。


CREATE FUNCTION skip_col_by_type(tab TABLE, 
                                 type_name VARCHAR2,
                                 flip VARCHAR2 DEFAULT 'False')
                  RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;

型がNUMBERでない列のみをSCOTT.DEPT表からレポートするために、パッケージskip_col PTF (overload 1)を起動します。

SELECT * FROM skip_col_pkg.skip_col(scott.dept, 'number');

DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

SCOTT.DEPT表からスタンドアロンskip_col_by_type PTFを起動して、型がNUMBERでない列のみをレポートすると、同じ結果が得られます。

SELECT * FROM skip_col_by_type(scott.dept, 'number');

DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

SCOTT.DEPT表から型がNUMBERの列のみをレポートするために、パッケージskip_col PTF (overload 2)を起動します。

SELECT * FROM skip_col_pkg.skip_col(scott.dept, 'number', flip => 'True');

   DEPTNO
----------
        10
        20
        30
        40

SCOTT.DEPT表からスタンドアロンskip_col_by_type PTFを起動して、型がNUMBERの列のみをレポートすると、同じ結果が得られます。

SELECT * FROM skip_col_by_type(scott.dept, 'number', flip => 'True');

   DEPTNO
----------
        10
        20
        30
        40

パッケージskip_col PTFを起動して、SCOTT.EMP表から部門20のCOMM、HIREDATEおよびMGRを除くすべての従業員をレポートします。

SELECT *
FROM skip_col_pkg.skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
WHERE deptno = 20;

    EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800         20
      7566 JONES      MANAGER         2975         20
      7788 SCOTT      ANALYST         3000         20
      7876 ADAMS      CLERK           1100         20
      7902 FORD       ANALYST         3000         20

12.6.7 To_doc多相テーブル・ファンクションの例

to_doc PTFの例では、指定された列のリストを1つのドキュメント列に結合します。

例12-37 To_doc多相テーブル・ファンクションの例

to_doc PTFは、列のリストをJSONオブジェクトのように構築されたドキュメント列に結合します。

Live SQL:

このサンプルは、18c To_doc多相テーブル・ファンクションでOracle Live SQLで表示および実行できます

多相テーブル・ファンクション(PTF)のDESCRIBEファンクションおよびFETCH_ROWSプロシージャを含む実装パッケージto_docを作成します。

PTFパラメータは、次のとおりです。

tab : 入力表(tabパラメータは表記述子レコード型のDBMS_TF.TABLE_T型です)
cols (オプション) : ドキュメントを変換する列のリスト。(colsパラメータは、列記述子レコード型のDBMS_TF.COLUMNS_Tです)

CREATE PACKAGE to_doc_p AS
   FUNCTION describe(tab      IN OUT DBMS_TF.TABLE_T,
                     cols     IN     DBMS_TF.COLUMNS_T DEFAULT NULL)
		       RETURN DBMS_TF.DESCRIBE_T;
   
   PROCEDURE fetch_rows;
END to_doc_p;

DESCRIBEファンクションおよびFETCH_ROWSプロシージャを含むパッケージを作成します。出力行セットにDOCUMENTという名前の新しい列を作成するには、FETCH_ROWSプロシージャが必要です。DESCRIBEファンクションは、入力表記述子TABLE_Tで注釈を付けることによって読取り列を指定します。指定された読取り列のみがフェッチされるため、FETCH_ROWS時の処理に使用できます。問合せでのPTFの起動は、COLUMNS疑似演算子を使用して、問合せがPTFに読み取らせる列を指定できます。この情報はDESCRIBEファンクションに渡され、このファンクションによってCOLUMN_T.FOR_READブール・フラグが設定されます。読取り列には、スカラーSQLデータ型のみを使用できます。COLUMN_T.PASS_THROUGHブール・フラグは、変更なしでPTFの入力表から出力に渡される列を示します。

CREATE PACKAGE BODY to_doc_p AS
   
FUNCTION describe(tab      IN OUT DBMS_TF.TABLE_T,
                  cols     IN     DBMS_TF.COLUMNS_T DEFAULT NULL)
		    RETURN DBMS_TF.DESCRIBE_T AS
BEGIN
  FOR i IN 1 .. tab.column.count LOOP 
	 CONTINUE WHEN NOT DBMS_TF.SUPPORTED_TYPE(tab.column(i).DESCRIPTION.TYPE);
	 
	  IF cols IS NULL THEN
	     tab.column(i).FOR_READ     := TRUE;
	     tab.column(i).PASS_THROUGH := FALSE;
	     CONTINUE;
	   END IF;
	 
	  FOR j IN 1 .. cols.count LOOP
	    IF (tab.column(i).DESCRIPTION.NAME = cols(j)) THEN
	        tab.column(i).FOR_READ     := TRUE;
	        tab.column(i).PASS_THROUGH := FALSE;
	    END IF;
	  END LOOP;
	 
  END LOOP;
      
  RETURN DBMS_TF.describe_t(new_columns => DBMS_TF.COLUMNS_NEW_T(1 =>
	                          DBMS_TF.COLUMN_METADATA_T(name =>'DOCUMENT')));   
END;
   
 PROCEDURE fetch_rows AS 
      rst DBMS_TF.ROW_SET_T;
      col DBMS_TF.TAB_VARCHAR2_T;
      rct PLS_INTEGER;
 BEGIN
      DBMS_TF.GET_ROW_SET(rst, row_count => rct);
      FOR rid IN 1 .. rct LOOP 
	       col(rid) := DBMS_TF.ROW_TO_CHAR(rst, rid); 
      END LOOP;
      DBMS_TF.PUT_COL(1, col);
 END; 
   
END to_doc_p;

スタンドアロンto_doc PTFを作成します。TABLE型の仮引数を正確に1つ指定し、PTFの戻り型をTABLEとして指定し、行セマンティクスPTF型を指定し、PTF実装パッケージがto_doc_pを使用するように指定します。

CREATE FUNCTION to_doc(
                 tab  TABLE, 
       			    cols  COLUMNS DEFAULT NULL) 
       			    RETURN TABLE
    PIPELINED ROW POLYMORPHIC USING to_doc_p;

to_doc PTFを起動して、表SCOTT.DEPTのすべての列を結合された1つのDOCUMENT列として表示します。

SELECT * FROM to_doc(scott.dept);

DOCUMENT
--------------------------------------------------
{"DEPTNO":10, "DNAME":"ACCOUNTING", "LOC":"NEW YORK"}
{"DEPTNO":20, "DNAME":"RESEARCH", "LOC":"DALLAS"}
{"DEPTNO":30, "DNAME":"SALES", "LOC":"CHICAGO"}
{"DEPTNO":40, "DNAME":"OPERATIONS", "LOC":"BOSTON"}

部門10および30のすべての従業員について、DEPTNO、ENAMEおよびDOCUMENT列をDEPTNOおよびENAMEの順で表示します。to_doc PTFをCOLUMNS擬似演算子で起動し、表SCOTT.EMPの列EMPNO、JOB、MGR、HIREDATE、SALおよびCOMMを選択します。PTFは、これらの列をDOCUMENT列に結合します。

SELECT deptno, ename, document 
FROM   to_doc(scott.emp, COLUMNS(empno,job,mgr,hiredate,sal,comm))
WHERE  deptno IN (10, 30) 
ORDER BY 1, 2;

DEPTNO ENAME      DOCUMENT
------ ---------- ---------------------------------------------------------------------------------
    10 CLARK      {"EMPNO":7782, "JOB":"MANAGER", "MGR":7839, "HIREDATE":"09-JUN-81", "SAL":2450}
    10 KING       {"EMPNO":7839, "JOB":"PRESIDENT", "HIREDATE":"17-NOV-81", "SAL":5000}
    10 MILLER     {"EMPNO":7934, "JOB":"CLERK", "MGR":7782, "HIREDATE":"23-JAN-82", "SAL":1300}
    30 ALLEN      {"EMPNO":7499, "JOB":"SALESMAN", "MGR":7698, "HIREDATE":"20-FEB-81", "SAL":1600, "COMM":300}
    30 BLAKE      {"EMPNO":7698, "JOB":"MANAGER", "MGR":7839, "HIREDATE":"01-MAY-81", "SAL":2850}
    30 JAMES      {"EMPNO":7900, "JOB":"CLERK", "MGR":7698, "HIREDATE":"03-DEC-81", "SAL":950}
    30 MARTIN     {"EMPNO":7654, "JOB":"SALESMAN", "MGR":7698, "HIREDATE":"28-SEP-81", "SAL":1250, "COMM":1400}
    30 TURNER     {"EMPNO":7844, "JOB":"SALESMAN", "MGR":7698, "HIREDATE":"08-SEP-81", "SAL":1500, "COMM":0}
    30 WARD       {"EMPNO":7521, "JOB":"SALESMAN", "MGR":7698, "HIREDATE":"22-FEB-81", "SAL":1250, "COMM":500}

Eという名前の副問合せを使用して、DOC_ID列およびDOCUMENT列を表示します。すべての事務員従業員、給与、部門および部門の場所をレポートします。to_doc PTFを使用して、NAME、SAL、DEPTNOおよびLOC列をDOCUMENT列に結合します。

WITH e AS (
       SELECT ename name, sal, deptno, loc 
	     FROM scott.emp NATURAL JOIN scott.dept 
        WHERE job = 'CLERK')
	  SELECT ROWNUM doc_id, t.*
	    FROM to_doc(e) t;

    DOC_ID DOCUMENT
---------- -------------------------------------------------------
         1 {"NAME":"MILLER", "SAL":1300, "DEPTNO":10, "LOC":"NEW YORK"}
         2 {"NAME":"SMITH", "SAL":800, "DEPTNO":20, "LOC":"DALLAS"}
         3 {"NAME":"ADAMS", "SAL":1100, "DEPTNO":20, "LOC":"DALLAS"}
         4 {"NAME":"JAMES", "SAL":950, "DEPTNO":30, "LOC":"CHICAGO"}

副問合せブロックを使用して、DOCUMENT列に変換されたc1、c2、c3列の値を表示します。

WITH t(c1,c2,c3)  AS (
    SELECT NULL, NULL, NULL FROM dual 
    UNION ALL
    SELECT    1, NULL, NULL FROM dual 
    UNION ALL
    SELECT NULL,    2, NULL FROM dual 
    UNION ALL
    SELECT    0, NULL,    3 FROM dual)
  SELECT * 
    FROM to_doc(t);

DOCUMENT
---------------
{}
{"C1":1}
{"C2":2}
{"C1":0, "C3":3}

部門30のすべての従業員について、プロパティ名ENAMEおよびCOMMを持つメンバーの値を表示します。SCOTT.EMP表のPTF起動レポートにより、JSON_VALUEファンクションへの入力として使用できるDOCUMENT列が生成されます。このファンクションは、一部のJSONデータからスカラー値を選択します。

SELECT JSON_VALUE(document, '$.ENAME') ename, 
       JSON_VALUE(document, '$.COMM')  comm 
FROM   to_doc(scott.emp)
WHERE  JSON_VALUE(document, '$.DEPTNO') = 30;

ENAME      COMM
---------- ----
ALLEN      300
WARD       500
MARTIN     1400
BLAKE
TURNER     0
JAMES

12.6.8 Implicit_echo多相表関数の例

implicit_echo PTFの例では、多相表関数とDESCRIBE関数が同じパッケージ内で定義されている場合、USING句がオプションであることを示しています。

例12-38 Implicit_echo多相表関数の例

implicit_echo PTFは表と列を受け入れ、同じ値で新しい列を作成します。

このPTFは、入力表タブの列を返し、colsにリストされた、列名に"ECHO_"という接頭辞が付いた列を追加します。

DESCRIBEファンクション、implicit_echo多相表関数(PTF)およびFETCH_ROWSプロシージャが含まれている実装パッケージimplicit_echo_packageを作成します。

CREATE PACKAGE implicit_echo_package AS
  prefix   DBMS_ID := '"ECHO_';

  FUNCTION DESCRIBE(tab   IN OUT DBMS_TF.TABLE_T,
                    cols  IN     DBMS_TF.COLUMNS_T)
           RETURN DBMS_TF.DESCRIBE_T;
  
  PROCEDURE FETCH_ROWS;

  -- PTF FUNCTION: WITHOUT USING CLAUSE --
  FUNCTION implicit_echo(tab TABLE, cols COLUMNS)
           RETURN TABLE PIPELINED ROW POLYMORPHIC;

END implicit_echo_package;

読み取る入力表パラメータと列パラメータを含むDESCRIBEファンクションが含まれているパッケージを作成します。このファンクションは、多相表関数によって作成される行のタイプを決定する場合に呼び出されます。このファンクションは、表DBMS_TF.DESCRIBE_Tを戻します。FETCH_ROWSプロシージャは、出力行セット内に"ECHO_"という接頭辞付いた新しい列とともに指定された読取り列を生成するために必要になります。implicit_echoはPTF関数であり、2つの引数(tabとcols)が含まれています。それらの値は、問合せによって取得され、この情報がDESCRIBEファンクションに渡されます。RowセマンティクスではPTFタイプを指定しますが、USING句は指定されていません。このファンクションは、SQL問合せから起動されます。

PTF定義を含める実装パッケージ本体implicit_echo_packageを作成します。

CREATE PACKAGE BODY implicit_echo_package AS

FUNCTION DESCRIBE(tab  IN  OUT DBMS_TF.TABLE_T,
                  cols IN      DBMS_TF.COLUMNS_T)
          RETURN DBMS_TF.DESCRIBE_T
AS
  new_cols DBMS_TF.COLUMNS_NEW_T;
  col_id   PLS_INTEGER := 1;

BEGIN
 FOR i in 1 .. tab.column.COUNT LOOP
 
   FOR j in 1 .. cols.COUNT LOOP
    
     IF (tab.column(i).description.name = cols(j)) THEN
       
       IF (NOT DBMS_TF.SUPPORTED_TYPE(tab.column(i).description.type)) THEN
            RAISE_APPLICATION_ERROR(-20102, 'Unsupported column type['||
                                    tab.column(i).description.type||']');
       END IF;

       tab.column(i).for_read := TRUE;
       new_cols(col_id)       := tab.column(i).description;
       new_cols(col_id).name  := prefix||
                                 REGEXP_REPLACE(tab.column(i).description.name,
                                                                      '^"|"$');
       col_id                 := col_id + 1;
       EXIT;

     END IF;

    END LOOP;

 END LOOP;

/* VERIFY ALL COLUMNS WERE FOUND */
 IF (col_id - 1 != cols.COUNT) then
    RAISE_APPLICATION_ERROR(-20101,'Column mismatch['||col_id-1||'],
                                                   ['||cols.COUNT||']');
 END IF;

 RETURN DBMS_TF.DESCRIBE_T(new_columns => new_cols);

END;

 PROCEDURE FETCH_ROWS AS
	 rowset DBMS_TF.ROW_SET_T;
 BEGIN
         DBMS_TF.GET_ROW_SET(rowset);
         DBMS_TF.PUT_ROW_SET(rowset);
 END;

END implicit_echo_package;

PTFを起動すると、表SCOTT.EMPのENAME列が表示されます。また、同じ値を持つ別の列ECHO_ENAMEも表示されます。

SELECT ENAME, ECHO_ENAME
FROM implicit_echo_package.implicit_echo(SCOTT.EMP, COLUMNS(SCOTT.ENAME));

ENAME      ECHO_ENAME
---------- ----------
SMITH      SMITH
ALLEN      ALLEN
WARD       WARD
JONES      JONES
MARTIN     MARTIN
BLAKE      BLAKE
CLARK      CLARK
SCOTT      SCOTT
KING       KING
TURNER     TURNER
ADAMS      ADAMS
JAMES      JAMES
FORD       FORD
MILLER     MILLER

12.7 大規模な表のパラレルでの更新

DBMS_PARALLEL_EXECUTEパッケージを使用すると、次の2つのステップで、大規模な表のデータをパラレルで増分更新できます。

表の行をグループ化して小さなチャンクに分けます。
必要なUPDATE文を並列でチャンクに適用し、1チャンクの処理が終了するたびにコミットします。

大量のデータを更新する場合は、常にこの方法をお薦めします。この方法には次のメリットがあります。

表全体のロックではなく、相対的に短い時間で一度に1セットの行のみをロックする。
操作全体が完了する前に問題が発生してもそれまでに処理した内容が失われない。
ロールバック用の領域の消費が削減されます。
パフォーマンスが向上します。

関連項目:

DBMS_PARALLEL_EXECUTEパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

12.8 ユーザー定義の識別子に関するデータの収集

PL/Scopeは、PL/SQLソース・テキストからPL/SQLおよびSQLの識別子ならびにSQL文に関するデータを抽出、編成および格納します。静的データ・ディクショナリ・ビュー*_IDENTIFIERSおよび*_STATEMENTSを使用して、識別子と文のデータを取り出すことができます。

関連項目:

PLSQL_SETTINGSパラメータの詳細は、PL/SQLユニットおよびコンパイル・パラメータを参照してください
PL/Scopeの使用に関する詳細は、Oracle Database開発ガイドを参照してください

12.9 PL/SQLプログラムのプロファイルおよびトレース

大規模なPL/SQLプログラムでのパフォーマンスの問題を切り出すことができるように、PL/SQLには、PL/SQLパッケージとして実装される次のツールが用意されています。

例12-1 プロファイル・ツールおよびトレース・ツールの概要

ツール	パッケージ	説明
プロファイラ・インタフェース	`DBMS_PROFILER`	PL/SQLプログラムが各行および各サブプログラムに費やす時間を計算します。プロファイル対象のユニットに対する`CREATE`権限が必要です。ランタイム統計をデータベース表に保存します。このデータベース表は、問い合せることができます。
トレース・インタフェース	`DBMS_TRACE`	サブプログラムの実行順序をトレースできます。トレースするサブプログラムおよびトレース・レベルを指定できます。ランタイム統計をデータベース表に保存します。このデータベース表は、問い合せることができます。
PL/SQL階層型プロファイラ	`DBMS_HPROF`	PL/SQLプログラムの動的な実行プログラム・プロファイルをサブプログラムの起動ごとにまとめてレポートします。SQL実行時間とPL/SQL実行時間を別々に示します。特別なソースまたはコンパイル時の準備は必要ありません。 HTML形式でレポートを生成します。カスタム・レポートの生成用にプロファイラ・データと結果をリレーショナル形式でデータベース表に格納する(サードパーティ・ツールで提供されるような)オプションを提供します。
SQLトレース	`DBMS_APPLICATION_INFO`	Oracle TraceおよびSQLトレース機能を持つ`DBMS_APPLICATION_INFO`パッケージを使用して、実行しているモジュール名またはトランザクションをデータベースに記録できます。この記録は、後で行う様々なモジュールおよびデバッグのパフォーマンスを追跡するときに使用されます。
PL/SQL基本ブロック・カバレッジ	`DBMS_PLSQL_CODE_COVERAGE`	基本ブロック・カバレッジ・データを収集して分析します。
コール・スタック・ユーティリティ	`UTL_CALL_STACK`	現在実行中のサブプログラムに関する情報(サブプログラム名、ユニット名、所有者名、エディション名、エラー・スタック情報など)を提供するため、これらの情報からより露出度の高いエラー・ログやアプリケーション実行トレースを作成できます。

12.10 システム固有の実行のためのPL/SQLユニットのコンパイル

通常、PL/SQLユニットをコンパイルして、システム固有のコード(プロセッサに依存するシステム・コード)にすると、PL/SQLユニットをスピードアップできます。システム固有のコードは、SYSTEM表領域に格納されます。

Oracle Databaseが提供するユニットを含め、すべてのタイプのPL/SQLユニットをネイティブ・コンパイルできます。

ネイティブ・コンパイルされたプログラム・ユニットは、共有サーバー構成(以前のマルチスレッド・サーバー)やOracle Real Application Clusters(Oracle RAC)などのすべてのサーバー環境で動作します。

ほとんどのプラットフォームで、PL/SQLネイティブ・コンパイルに特別な設定またはメンテナンスは必要ありません。一部のプラットフォームでは、DBAによるオプションの構成が必要な場合もあります。

関連項目:

データベースの構成の詳細は、『Oracle Database管理者ガイド』を参照してください。
ご使用のプラットフォームの詳細は、プラットフォーム固有の構成ドキュメントを参照してください。

PL/SQLのネイティブ・コンパイルを有効にしてパフォーマンスが改善される度合いは、テストによって確認できます。

PL/SQLのネイティブ・コンパイルを使用するとデータベース操作で大幅にパフォーマンスが改善されると判断した場合、ネイティブ・モードでデータベース全体をコンパイルすることをお薦めします(これを行うには、DBA権限が必要です)。これによって、独自のコードと、Oracle Database提供のPL/SQLパッケージへのコールの両方がスピードアップします。

ここでのトピック

* DBA権限が必要です。

12.10.1 PL/SQLのネイティブ・コンパイルを使用するかどうかの判断

PL/SQLユニットをネイティブ・モードでコンパイルするか、解釈済モードでコンパイルするかは、開発サイクルの段階およびプログラム・ユニットの内容によって決まります。

プログラム・ユニットをデバッグし、頻繁に再コンパイルしている場合は、解釈済モードに次のメリットがあります。

(ネイティブ・モードでコンパイルされたプログラム・ユニットではなく)解釈済モードでコンパイルされたプログラム・ユニットに対しては、PL/SQLデバッグ・ツールを使用できます。
解釈済モードのコンパイルは、ネイティブ・モードでのコンパイルより高速です。

開発のデバッグ・フェーズ後、ネイティブ・モードでPL/SQLユニットをコンパイルするかどうかを判断する際は次のことを考慮してください。

PL/SQLのネイティブ・コンパイルによって、計算集中型のプロシージャ操作のパフォーマンスは大幅に改善されます。この例には、データ・ウェアハウス・アプリケーションや、サーバー側でデータを大幅に変換して表示するアプリケーションなどがあります。
PL/SQLのネイティブ・コンパイルによって、SQLの実行にほとんどの時間を費やすPL/SQLサブプログラムのパフォーマンスはほとんど改善されません。
多数のプログラム・ユニット(通常は15,000以上)をシステム固有の実行用にコンパイルし、同時にアクティブにすると、大量の共有メモリーが必要になるため、システムのパフォーマンスに影響することがあります。

12.10.2 PL/SQLのネイティブ・コンパイルの動作

ネイティブ・コンパイルを使用しない場合、PL/SQLユニットのPL/SQL文は、中間形式のシステム・コードにコンパイルされます。このコードは、カタログに格納され、実行時に解釈されます。

PL/SQLのネイティブ・コンパイルを使用する場合、PL/SQLユニットのPL/SQL文はシステム固有のコードにコンパイルされ、カタログに格納されます。システム固有のコードは、実行時に解釈する必要がないため、高速で実行されます。

ネイティブ・コンパイルはPL/SQL文にのみ適用されるため、SQL文のみを使用するPL/SQLユニットの場合、ネイティブ・コンパイルしても高速に実行されない可能性がありますが、対応する解釈済コードより低速になることはありません。コンパイルされたコードと解釈済コードは同じライブラリをコールするため、アクションは同じです。

ネイティブ・コンパイルされたPL/SQLユニットを初めて実行すると、このユニットは、SYSTEM表領域から共有メモリーにフェッチされます。プログラム・ユニットを起動するセッションの数に関係なく、共有メモリーにはそのコピーが1つのみ含まれています。プログラム・ユニットが使用されていない場合、そのプログラム・ユニットが使用している共有メモリーを解放してメモリー・ロードを軽減できます。

ネイティブ・コンパイルされたサブプログラムと解釈済サブプログラムは相互に起動し合うことができます。

PL/SQLのネイティブ・コンパイルは、Oracle Real Application Clusters(Oracle RAC)環境では透過的に動作します。

PLSQL_CODE_TYPEコンパイル・パラメータによって、PL/SQLコードをネイティブ・コンパイルするか解釈済にするかが決まります。このコンパイル・パラメータの詳細は、「PL/SQLユニットおよびコンパイル・パラメータ」を参照してください。

12.10.3 依存性、無効化および再評価

無効化したPL/SQLモジュールでは、再コンパイルが自動的に行われます。たとえば、ネイティブ・コンパイルされたPL/SQLサブプログラムが依存するオブジェクトが変更されると、サブプログラムは無効になります。同じサブプログラムが次にコールされたとき、データベースはサブプログラムを自動的に再コンパイルします。PLSQL_CODE_TYPE設定はサブプログラムごとにライブラリ・ユニットに格納されるため、自動再コンパイルではこの格納された設定をコード型として使用します。

明示的な再コンパイルで、格納されているPLSQL_CODE_TYPE設定が使用されるとはかぎりません。格納されている設定を明示的な再コンパイルで使用する条件は、「PL/SQLユニットおよびコンパイル・パラメータ」を参照してください。

12.10.4 PL/SQLのネイティブ・コンパイルで使用する新しいデータベースの設定

DBA権限を持っている場合、コンパイル・パラメータPLSQL_CODE_TYPEをNATIVEに設定して、PL/SQLネイティブ・コンパイル用に新しいデータベースを設定できます。多くのデータベース操作で使用されるOracle Database提供のPL/SQLパッケージで、パフォーマンスが改善します。

ノート:

データベース全体をNATIVEでコンパイルする場合は、システム・レベルでPLSQL_CODE_TYPEを設定することをお薦めします。

12.10.5 PL/SQLネイティブ・コンパイルまたは解釈コンパイルで使用するデータベース全体のコンパイル

DBA権限を持っている場合、この項で説明するプロセスで、dbmsupgnv.sqlおよびdbmsupgin.sqlのスクリプトを使用して、既存のデータベースのすべてのPL/SQLモジュールをそれぞれNATIVEまたはINTERPRETEDに再コンパイルできます。この変換を実行する前に、「PL/SQLのネイティブ・コンパイルを使用するかどうかの判断」の内容に目を通してください。

ノート:

データベース全体をNATIVEでコンパイルする場合は、システム・レベルでPLSQL_CODE_TYPEを設定することをお薦めします。
Database Vaultが有効で、Database Vaultの管理者からDV_PATCH_ADMINロールを付与された場合にのみ、dbmsupgnv.sqlを実行できます。

ネイティブ・コンパイルへの変換で、dbmsupgnv.sqlによってTYPE仕様部をNATIVEに再コンパイルすることはできません。これらの仕様部には実行可能なコードが含まれていないためです。

パッケージ仕様部に実行可能なコードが含まれることはほとんどないため、NATIVEにコンパイルしても実行時の利点は得られません。dbmsupgnv.sqlスクリプトでTRUEコマンドライン・パラメータを使用すると、NATIVEへの再コンパイルからパッケージ仕様部を除外して、変換処理にかかる時間を節約できます。

dbmsupgin.sqlスクリプトで解釈コンパイルに変換するときは、パラメータは指定できず、PL/SQLユニットを除外することはできません。

ノート:

次の手順では、ネイティブ・コンパイルへの変換について説明します。すべてのPL/SQLモジュールを解釈コンパイルに再コンパイルする必要がある場合は、ステップを次のように変更してください。

1番目のステップをスキップします。
PLSQL_CODE_TYPEコンパイル・パラメータをNATIVEではなくINTERPRETEDに設定します。
dbmsupgnv.sqlスクリプトをdbmsupgin.sqlスクリプトに置き換えます。

PL/SQLテスト・ユニットをコンパイルできることを確認します。たとえば:
```
ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
```
アプリケーション・サービス、リスナーおよびデータベースを停止します。
- Forms Process、Web Server、Reports Server、Concurrent Manager Serverなど、すべてのアプリケーション・サービスを停止します。すべてのアプリケーション・サービスを停止した後で、データベースへのすべての接続が終了したことを確認します。
- データベースのTNSリスナーを停止して、新規接続が作成されないようにします。
- ユーザーSYSとして、通常モードまたは即時モードでデータベースを停止します。『Oracle Database管理者ガイド』を参照してください。
コンパイル・パラメータ・ファイルでPLSQL_CODE_TYPEをNATIVEに設定します。データベースでサーバー・パラメータ・ファイルを使用している場合は、データベースを起動してからこの設定を行ってください。

PLSQL_CODE_TYPEの値は、このステップのPL/SQLユニットの変換には影響を与えません。ただし、この手順以降にコンパイルしたユニットは影響を受けるため、ここで必要なコンパイル・タイプを明示的に設定してください。
UPGRADEオプションを使用して、データベースをアップグレード・モードで起動します。SQL*PlusのSTARTUPの詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください。
次のコードを実行して、無効なPL/SQLユニットを一覧表示します。SQL SPOOL文を使用すると、問合せの出力を今後の参照のために保存しておくことができます。
```
-- To save the output of the query to a file:
  SPOOL pre_update_invalid.log
SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE 
FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s 
WHERE o.OBJECT_NAME = s.NAME AND o.STATUS='INVALID';
-- To stop spooling the output: SPOOL OFF
```
Oracleが提供するユニットが無効な場合は、それらのユニットを再コンパイルして有効にします。たとえば:
```
ALTER PACKAGE SYS.DBMS_OUTPUT COMPILE BODY REUSE SETTINGS;
```
ユニットを有効にできない場合は、今後の解決のためにスプール・ログを保存してから処理を続行します。
次の問合せを実行して、NATIVEおよびINTERPRETEDでコンパイルされるオブジェクトの数を確認します(出力を保存するには、SQLのSPOOL文を使用します)。
```
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;
```
NULL plsql_code_typeのオブジェクトは特別な内部オブジェクトであり、無視してかまいません。
ユーザーSYSとして$ORACLE_HOME/rdbms/admin/dbmsupgnv.sqlスクリプトを実行して、すべてのPL/SQLユニットのディクショナリ表でplsql_code_type設定をNATIVEに更新します。この処理によってもユニットが無効化されます。パッケージ仕様部を除外するときは、スクリプトでTRUEを使用し、パッケージ仕様部を含めるときは、FALSEを使用します。

この更新は、データベースがUPGRADEモードの場合に実行する必要があります。スクリプトを使用すると、更新を正常に完了したり、すべての変更をロールバックすることもできます。
データベースを停止し、NORMALモードで再起動します。
問題の発生を回避するため、utlrp.sqlスクリプトを実行する前に他のセッションに接続しないことをお薦めします。このためには、次の文を実行します。
```
ALTER SYSTEM ENABLE RESTRICTED SESSION;
```
ユーザーSYSとして$ORACLE_HOME/rdbms/admin/utlrp.sqlスクリプトを実行します。このスクリプトでは、デフォルトの並列度を使用してすべてのPL/SQLモジュールを再コンパイルします。並列度を明示的に設定する方法については、スクリプトのコメントを参照してください。

スクリプトが異常終了した場合は、utlrp.sqlスクリプトを再実行して残りの無効なPL/SQLモジュールを再コンパイルします。
コンパイルが正常に完了したら、ステップ5の問合せを使用して、他に無効なPL/SQLユニットがないかどうかを確認します。問合せの出力をpost_upgrade_invalid.logファイルにスプールしておくと、以前に作成したpre_upgrade_invalid.logファイルがあれば、スプールした内容と比較できます。
ステップ6の問合せを再実行します。dbmsupgnv.sqlで再コンパイルした場合は、除外したTYPE仕様部およびパッケージ仕様部を除くすべてのPL/SQLユニットがNATIVEであることを確認します。dbmsupgin.sqlで再コンパイルした場合は、すべてのPL/SQLユニットがINTERPRETEDであることを確認します。
データベースの制限付きセッション・モードを無効にしてから、前に停止したサービスを開始します。制限付きセッション・モードを無効にするには、次の文を使用します。
```
ALTER SYSTEM DISABLE RESTRICTED SESSION;
```