この章では、PL/SQLコンパイラでコードがどのように最適化されるかを説明し、効率的なPL/SQLコードを記述する方法と既存のPL/SQLコードを改善する方法について説明します。
ここでのトピック
関連項目:
カーソル変数のデメリットの詳細は、『Oracle Database開発ガイド』を参照してください。
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リファレンス』を参照してください。
コンパイラが実行できる最適化の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 サブプログラムのインライン化の指定
この例では、INLINE
プラグマは、PLSQL_OPTIMIZE_LEVEL=2
の場合、プロシージャの起動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 オーバーロードされたサブプログラムのインライン化の指定
この例では、INLINE
プラグマは、PLSQL_OPTIMIZE_LEVEL=2
の場合、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 ...
次のような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ユニットのコンパイル」を参照してください。
ここでのトピック
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およびバルク・バインド」を参照してください。
問合せで起動されるファンクションは、数百万回実行される可能性があります。問合せで不必要にファンクションを起動しないようにして、起動はできるだけ効率的にします。
問合せの表にファンクション索引を作成してください。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
サブプログラムに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; /
PL/SQLアプリケーションはループの周囲に構築されることが多いため、ループ自体とループ内のコードの両方を最適化することが重要です。
結果セットを2回以上ループしたり、結果セットのループ時に他の問合せを発行する必要がある場合、元の問合せを変更して、目的の結果を正確に取得することが可能な場合があります。複数の問合せを結合できるSQLの集合演算子を調査してください(『Oracle Database SQL言語リファレンス』を参照)。
副問合せを使用して、複数の段階でフィルタリングおよびソートを行うこともできます(「副問合せによる問合せ結果セットの処理」を参照)。
関連項目:
ここでの推奨事項は、特に計算集中型PL/SQLコードに適用されます(ただし、それらのコードに限定されません)。
ここでのトピック
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言語リファレンス』を参照)を起動することによって、計算集中型コードをスピードアップできます。
パフォーマンスを重視するコードでは、制約付きサブタイプ(「制約付きサブタイプ」を参照)を使用することは避けてください。制約付きサブタイプの変数またはパラメータに代入を行うたびに、代入される値が制約に違反していないことを保証するために実行時に余分なチェックが必要になります。
関連項目:
「PL/SQLの事前定義のデータ型」(事前定義の制約付きサブタイプを含む)
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言語リファレンス』を参照してください。
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を参照してください。
バルク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バインド操作は、次のカテゴリに分類されます。
バインド・カテゴリ | このバインドが実行される場合 |
---|---|
インバインド |
|
アウトバインド |
|
|
|
インバインドおよびアウトバインドでは、バルクSQLはバルク・バインドを使用します(つまり、値のコレクション全体を一度にバインドします)。n個の要素があるコレクションの場合、バルクSQLは単一の操作で、n回分のSELECT
INTO
文またはDML文に相当する処理を実行できます。バルクSQLを使用する問合せでは、行ごとにFETCH
文を使用することなく、任意の数の行を戻すことができます。
注意:
パラレルDMLは、バルクSQLを使用すると無効になります。
ここでのトピック
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
文がコレクションのサブセットに適用されます。
ここでのトピック
例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;
/
FORALL
文の境界句で疎コレクションを参照する場合、INDICES
OF
句またはVALUES
OF
句を使用して既存の索引値のみを指定します。
INDICES
OF
は、文字列で索引付けされている結合配列を除く任意のコレクションに使用できます。VALUES
OF
は、PLS_INTEGER
で索引付けされているPLS_INTEGER
要素のコレクションにのみ使用できます。
PLS_INTEGER
で索引付けされているPLS_INTEGER
要素のコレクションには、索引コレクションを使用できます。索引コレクションは、他のコレクションの要素に対するポインタのコレクションです(索引付けされたコレクション)。
索引コレクションは、異なるFORALL
文で同じコレクションの異なるサブセットを処理する場合に便利です。時間とメモリーを大量に費やす可能性のあるサブセットを表す新しいコレクションに元のコレクションの要素をコピーするかわりに、索引コレクションを使用して各サブセットを表し、各索引コレクションを異なるFORALL
文のVALUES
OF
句で使用します。
例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.
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例外の処理」を参照)
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.
一部の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.
FORALL
文の完了後、各DML文によって影響を受けた行の数を、暗黙カーソル属性SQL%BULK_ROWCOUNT
から取得できます。
FORALL
文の影響を受けた行の合計数を取得するには、「SQL%ROWCOUNT属性: 影響を受けた行数」で説明されている暗黙カーソル属性SQL%ROWCOUNT
を使用します。
SQL%BULK_ROWCOUNT
は、i番目の要素が、直前に完了したFORALL
文のi番目のDML文によって影響を受けた行の数に相当する結合配列とほぼ同じです。要素のデータ型はINTEGER
です。
注意:
サーバーがOracle Database 12c以降で、そのクライアントがOracle Database 11g2以前(またはその逆)の場合、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
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に示すように、ターゲット・コレクションが空かどうかを確認する必要があります。
ここでのトピック
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
次の書式の文について説明します。
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
大量の行を戻す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; /
結果セットをコレクションに格納する場合、行をループして異なる列を参照することは簡単です。この方法では非常に高速に処理されますが、メモリーも集中的に使用されます。この方法を頻繁に使用する場合、次のことに注意してください。
結果セットを1回ループするには、カーソルFOR
LOOP
を使用します(「カーソルFOR LOOP文による問合せ結果セットの処理」を参照)。
この方法を使用すると、結果セットのコピーを格納する際のメモリーのオーバーヘッドを回避できます。
結果セットをループして特定の値を検索したり、結果をフィルタリングしてより小さい結果セットにするかわりに、SELECT
INTO
文の問合せで検索またはフィルタリングを行います。
たとえば、単純な問合せではWHERE
句を使用し、複数の結果セットを比較する問合せでは、INTERSECT
やMINUS
などの集合演算子を使用します。集合演算子の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
結果セットをループして各結果行に対して別の問合せを実行するかわりに、SELECT
INTO
文の問合せで副問合せを使用します(「副問合せによる問合せ結果セットの処理」を参照)。
結果セットをループして各結果行に対して別のDML文を実行するかわりに、FORALL
文を使用します(「FORALL文」を参照)。
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
大量の行を戻すFETCH
BULK
COLLECT
文では、大規模なコレクションが生成されます。行数およびコレクション・サイズを制限するには、LIMIT
句を使用します。
例12-24では、LOOP
文が繰り返されるたびに、FETCH
文によって10行(またはそれ以下)が結合配列empids
にフェッチされます(前の値は上書きされます)。LOOP
文の終了条件に注意してください。
例12-24 LIMITによるバルク・フェッチの制限
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
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
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-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
クライアント・プログラム(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; /
パイプライン・テーブル・ファンクションの連鎖は、複数のデータ変換を実行する場合に効率的な方法です。
注意:
パイプライン・テーブル・ファンクションは、データベース・リンクを介して実行することはできません。パイプライン・テーブル・ファンクションの戻り型がユーザー定義のSQL型であり、単一データベース内でのみ使用可能であるためです(『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照)。パイプライン・テーブル・ファンクションの戻り型がPL/SQL型のように見える場合でも、そのPL/SQL型は実際にはデータベースによって対応するユーザー定義のSQL型に変換されます。
ここでのトピック
テーブル・ファンクションは、行のコレクション(連想配列、ネストした表またはVARRAY)を戻すユーザー定義のPL/SQLファンクションです。SELECT
文のTABLE
句の内部でテーブル・ファンクションを起動することで、データベース表のようにこのコレクションから要素を選択できます。
次に例を示します。
SELECT * FROM TABLE(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 Streamsの詳細は、『Oracle Streams概要および管理』を参照してください
パイプライン・テーブル・ファンクションおよびパラレル・テーブル・ファンクションを使用する方法の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
パイプライン・テーブル・ファンクションは、スタンドアロン・ファンクションまたはパッケージ・ファンクションのいずれかである必要があります。
PIPELINEDオプション(必須)
スタンドアロン・ファンクションでは、CREATE
FUNCTION
文にPIPELINED
オプションを指定します(構文は、「CREATE FUNCTION文」を参照してください)。パッケージ・ファンクションでは、ファンクション宣言とファンクション定義の両方にPIPELINED
オプションを指定します(構文は、「ファンクションの宣言および定義」を参照してください)。
PARALLEL_ENABLEオプション(推奨)
パフォーマンスを向上するには、PARALLEL_ENABLE
オプションを指定して、パイプライン・テーブル・ファンクションのパラレル実行を有効にします。
AUTONOMOUS_TRANSACTIONプラグマ
パイプライン・テーブル・ファンクションでDML文を実行する場合、AUTONOMOUS_TRANSACTION
プラグマ(「AUTONOMOUS_TRANSACTIONプラグマ」を参照)を使用すると、ファンクションが自律型になります。その後、パラレル実行中に、ファンクションの各インスタンスが独立したトランザクションを作成します。
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
が戻す行のコレクションから要素を選択します。
例12-29 パイプライン・テーブル・ファンクションの作成および起動
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; / CREATE OR REPLACE PACKAGE BODY pkg1 AS -- FUNCTION f1 returns a collection of elements (1,2,3,... x) 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.
カーソル変数パラメータを持つパイプライン・テーブル・ファンクションは、変換ファンクションとして使用できます。ファンクションは、カーソル変数を使用して入力行をフェッチします。ファンクションは、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.
パイプライン・テーブル・ファンクションのtf1とtf2
を連鎖
するには、tf1
の出力をtf2
の入力にします。次に例を示します。
SELECT * FROM TABLE(tf2(CURSOR(SELECT * FROM TABLE(tf1()))));
tf1
によってパイプ出力される行は、tf2
の仮入力パラメータと互換性のある実パラメータである必要があります。
連鎖されたパイプライン・テーブル・ファンクションでパラレル実行が有効化されていると、各ファンクションは異なるプロセス(またはプロセスのセット)で実行されます。
名前付きカーソルは、パイプライン・テーブル・ファンクションを起動する問合せに関連付けることができます。このようなカーソルに特別なフェッチ・セマンティクスはなく、また、このようなカーソル変数に特別な代入セマンティクスはありません。
ただし、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-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; / SELECT w.column_value "weighted result" FROM TABLE ( pkg_gpa.weighted_average ( CURSOR (SELECT weight, grade FROM gradereport) ) ) w;
結果:
weighted result --------------- 3.5 1 row selected.
パイプライン・テーブル・ファンクションが戻す表を、DELETE
文、INSERT
文、UPDATE
文またはMERGE
のターゲット表にすることはできません。ただし、このような表のビューを作成し、そのビューに対してINSTEAD
OF
トリガーを作成できます。INSTEAD
OF
トリガーの詳細は、「INSTEAD OF DMLトリガー」を参照してください。
関連項目:
CREATE
VIEW
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
事前定義の例外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
パッケージに次の3つのパブリック項目が含まれると想定します。
プロシージャ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; /
DBMS_PARALLEL_EXECUTE
パッケージを使用すると、次の2つの手順で、大規模な表のデータをパラレルで増分更新できます。
UPDATE
文をパラレルでチャンクに適用し、1つのチャンクの処理が終わるたびにコミットします。大量のデータを更新する場合は、常にこの方法をお薦めします。この方法には次のメリットがあります。
表全体をロックするのではなく、一度に1つの行セットのみをロックするため、比較的短時間で済みます。
操作全体が完了する前になんらかの障害が発生した場合でも、その時点までに終了していた作業の結果は失われません。
ロールバック用の領域の消費が削減されます。
パフォーマンスが向上します。
関連項目:
DBMS_PARALLEL_EXECUTE
パッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
PL/Scopeは、PL/SQLソース・テキストからユーザー定義の識別子に関するデータを抽出、編成および格納します。静的データ・ディクショナリ・ビュー*_IDENTIFIERS
を使用してソース・テキストの識別子データを取り出すことができます。詳細は、『Oracle Database開発ガイド』を参照してください。
大規模なPL/SQLプログラムでのパフォーマンスの問題を切り出すことができるように、PL/SQLには、PL/SQLパッケージとして実装される次のツールが用意されています。
ツール | パッケージ | 説明 |
---|---|---|
プロファイラAPI |
|
PL/SQLプログラムが各行および各サブプログラムに費やす時間を計算します。 プロファイル対象のユニットに対する ランタイム統計をデータベース表に保存します。このデータベース表は、問い合せることができます。 |
トレースAPI |
|
サブプログラムの実行順序をトレースできます。 トレースするサブプログラムおよびトレース・レベルを指定できます。 ランタイム統計をデータベース表に保存します。このデータベース表は、問い合せることができます。 |
PL/SQL階層型プロファイラ |
|
PL/SQLプログラムの動的な実行プログラム・プロファイルをサブプログラムの起動ごとにまとめてレポートします。SQL実行時間とPL/SQL実行時間を別々に示します。 特別なソースまたはコンパイル時の準備は必要ありません。 HTML形式でレポートを生成します。カスタム・レポートの生成用に結果をリレーショナル形式でデータベース表に格納する(サードパーティ・ツールで提供されるような)オプションを提供します。 |
ここでのトピック
PL/SQL階層型プロファイラの詳細は、『Oracle Database開発ガイド』を参照してください。
プロファイラAPI(プロファイラ)は、PL/SQLパッケージDBMS_PROFILER
として実装されます。PL/SQLパッケージDBMS_PROFILERのサービスによって、PL/SQLプログラムが各行および各サブプログラムに費やす時間が計算され、それらの統計がデータベース表に保存されます。このデータベース表は、問い合せることができます。
注意:
プロファイラは、CREATE権限を持つユニットに対してのみ使用できます。PL/SQL階層型プロファイラの使用にCREATE権限は必要ありません(『Oracle Database開発ガイド』を参照)。
プロファイラを使用するには、次の手順を実行します。
プロファイル・セッションを開始します。
十分な範囲のコードを取得できるまでPL/SQLプログラムを実行します。
収集されたデータをデータベースにフラッシュします。
プロファイル・セッションを停止します。
プロファイラでデータを収集したら、次の操作を実行できます。
パフォーマンス・データを含むデータベース表を問い合せます。
ほとんどの実行時間を使用するサブプログラムおよびパッケージを識別します。
特定のデータ構造へのアクセスおよび特定のコード・セグメントの実行に、プログラムでより時間がかかっている原因を判断します。
SQL文、ループ、再帰ファンクションなどの考えられるパフォーマンスのボトルネックを調べます。
分析結果に基づいて、不適切なデータ構造を置き換え、低速のアルゴリズムを書きなおします。
たとえば、データが急増したために、線形検索をバイナリ検索に置き換える必要が出てくることがあります。
DBMS_PROFILER
サブプログラムの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
トレースAPI(トレース)は、PL/SQLパッケージDBMS_TRACE
として実装されます。PL/SQLパッケージDBMS_TRACEのサービスによって、サブプログラムまたは例外ごとに実行がトレースされ、それらの統計がデータベース表に保存されます。このデータベース表は、問い合せることができます。
トレースを使用するには、次の手順を実行します。
(オプション)トレースを特定のサブプログラムに制限し、トレース・レベルを選択します。
大規模なプログラムのすべてのサブプログラムおよび例外をトレースすると、大量のデータが生成されて管理が困難になることがあります。
トレース・セッションを開始します。
PL/SQLプログラムを実行します。
トレース・セッションを停止します。
トレースでデータを収集した後で、パフォーマンス・データが含まれているデータベース表を問い合せて、プロファイラからパフォーマンス・データを分析する方法と同じ方法で分析できます(「プロファイラAPI: DBMS_PROFILERパッケージ」を参照)。
DBMS_TRACE
サブプログラムの詳細は、『Oracle Database 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パッケージへのコールの両方がスピードアップします。
ここでのトピック
PL/SQLユニットをネイティブ・モードでコンパイルするか、解釈済モードでコンパイルするかは、開発サイクルの段階およびプログラム・ユニットの内容によって決まります。
プログラム・ユニットをデバッグし、頻繁に再コンパイルしている場合は、解釈済モードに次のメリットがあります。
(ネイティブ・モードでコンパイルされたプログラム・ユニットではなく)解釈済モードでコンパイルされたプログラム・ユニットに対しては、PL/SQLデバッグ・ツールを使用できます。
解釈済モードのコンパイルは、ネイティブ・モードでのコンパイルより高速です。
開発のデバッグ・フェーズ後、ネイティブ・モードでPL/SQLユニットをコンパイルするかどうかを判断する際は次のことを考慮してください。
PL/SQLのネイティブ・コンパイルによって、計算集中型のプロシージャ操作のパフォーマンスは大幅に改善されます。この例には、データ・ウェアハウス・アプリケーションや、サーバー側でデータを大幅に変換して表示するアプリケーションなどがあります。
PL/SQLのネイティブ・コンパイルによって、SQLの実行にほとんどの時間を費やすPL/SQLサブプログラムのパフォーマンスはほとんど改善されません。
多数のプログラム・ユニット(通常は15,000以上)をシステム固有の実行用にコンパイルし、同時にアクティブにすると、大量の共有メモリーが必要になるため、システムのパフォーマンスに影響することがあります。
ネイティブ・コンパイルを使用しない場合、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ユニットおよびコンパイル・パラメータ」を参照してください。
無効化したPL/SQLモジュールでは、再コンパイルが自動的に行われます。たとえば、ネイティブ・コンパイルされたPL/SQLサブプログラムが依存するオブジェクトが変更されると、サブプログラムは無効になります。同じサブプログラムが次にコールされたとき、データベースはサブプログラムを自動的に再コンパイルします。PLSQL_CODE_TYPE
設定はサブプログラムごとにライブラリ・ユニットに格納されるため、自動再コンパイルではこの格納された設定をコード型として使用します。
明示的な再コンパイルで、格納されているPLSQL_CODE_TYPE
設定が使用されるとはかぎりません。格納されている設定を明示的な再コンパイルで使用する条件は、「PL/SQLユニットおよびコンパイル・パラメータ」を参照してください。
DBA権限を持っている場合、コンパイル・パラメータPLSQL_CODE_TYPE
をNATIVE
に設定して、PL/SQLネイティブ・コンパイル用に新しいデータベースを設定できます。多くのデータベース操作で使用されるOracle Database提供のPL/SQLパッケージで、パフォーマンスが改善します。
注意:
データベース全体をNATIVE
でコンパイルする場合は、システム・レベルでPLSQL_CODE_TYPE
を設定することをお薦めします。
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
スクリプトに置き換えます。