11 PL/SQLアプリケーションのパフォーマンスのチューニング

この章では、効率的なPL/SQLコードを記述する方法および既存のコードを高速化する方法について説明します。

この章の項目は、次のとおりです。

• PL/SQLコンパイル用の初期化パラメータ

• PL/SQLでプログラムを最適化する方法

• PL/SQLのパフォーマンス問題を回避するためのガイドライン

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

• バルクSQLによる、DML文および問合せのループ・オーバーヘッドの削減

• PL/SQLでの計算集中型プログラムの記述

• EXECUTE IMMEDIATEおよびカーソル変数を使用した動的SQLのチューニング

• NOCOPYコンパイラ・ヒントを使用したPL/SQLプロシージャ・コールのチューニング

• システム固有の実行のためのPL/SQLコードのコンパイル

• パイプライン・ファンクションによる変換の設定

  参照: PL/SQLパフォーマンス・チューニングの詳細は、次のURLを参照してください。 http://www.oracle.com/technology/tech/pl_sql/htdocs/new_in_10gr1.htm PL/SQLアプリケーションのチューニングの詳細は、Oracle Technology Network（OTN）を参照してください。 http://www.oracle.com/technology/tech/pl_sql/ 「Best Practice PL/SQL」などの、OTNの特定の項目の情報にアクセスするには、次のURLでOTNメイン・ページの検索フィールドに適切な語句を入力してください。 http://www.oracle.com/technology/ Oracle 10g のPL/SQLコンパイラの変更については、次を参照してください。 「パフォーマンスの向上」

PL/SQLコンパイル用の初期化パラメータ

PL/SQLユニットのコンパイル時には、いくつかのOracle初期化パラメータが使用されます。このようなPL/SQLコンパイラ・パラメータには、PLSQL_CCFLAGS、PLSQL_CODE_TYPE、PLSQL_DEBUG、PLSQL_NATIVE_LIBRARY_DIR、PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT、PLSQL_OPTIMIZE_LEVEL、PLSQL_WARNINGS、NLS_LENGTH_SEMANTICSがあります。初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。

PLSQL_CCFLAGS、PLSQL_CODE_TYPE、PLSQL_DEBUG、PLSQL_OPTIMIZE_LEVEL、PLSQL_WARNINGS、NLS_LENGTH_SEMANTICSの各初期化パラメータのコンパイル時の値は、ユニットのメタデータとともに格納されます。これらのパラメータの設定に関する情報は、ALL_PLSQL_OBJECT_SETTINGSビューで参照できます。詳細は、『Oracle Databaseリファレンス』を参照してください。

SQLのALTER文でREUSE SETTINGSを指定すると、ユニットのメタデータに格納されている値を保持できます。ALTER文でのPL/SQLコンパイラ・パラメータの使用の詳細は、『Oracle Database SQLリファレンス』でALTER FUNCTION文、ALTER PACKAGE文、ALTER PROCEDURE文およびALTER SESSION文を参照してください。

PL/SQLでプログラムを最適化する方法

10g より前のOracleリリースでは、PL/SQLコンパイラは、パフォーマンスを最適化するための変更を行うことなく、作成されたコードを機械コードに変換していました。今回のリリースのPL/SQLでは、コードのパフォーマンスが向上するように再調整する機能を持つ最適化コンパイラが使用されます。

この新しいオプティマイザを利用するための特別な操作は必要ありません。このオプティマイザは、デフォルトで使用可能です。まれに、オプティマイザのオーバーヘッドによって、非常に大規模なアプリケーションをコンパイルするときに時間が長くかかる場合があります。この場合、初期化パラメータPLSQL_OPTIMIZE_LEVEL=1を設定して（デフォルト値は2）、最適化レベルを低くすることもできます。また、非常にまれですが、例外動作が変更されて、例外が発生しなくなったり、予測よりも早く例外が発生するようになる場合もあります。PLSQL_OPTIMIZE_LEVEL=0に設定すると、コードは再調整されません。PLSQL_OPTIMIZE_LEVEL初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。

ご使用の環境の最適化レベルおよびその他のPL/SQLコンパイラ設定については、ALL_PLSQL_OBJECT_SETTINGSビューで参照できます。詳細は、『Oracle Databaseリファレンス』を参照してください。

PL/SQLコードをチューニングする場合

この章では、特に次の条件に該当する場合に有効な情報について説明します。

• 数学の計算を大量に実行するプログラム。PLS_INTEGER、BINARY_FLOATおよびBINARY_DOUBLEデータ型について検討します。

• PL/SQL問合せからコールされ、数百万回実行される可能性があるファンクション。ファンクションをできるだけ効率的にするために、すべてのパフォーマンス機能を検討します。また、各行の結果を事前に計算して問合せ時間を短縮するファンクション索引について検討することをお薦めします。

• INSERT文、UPDATE文またはDELETE文の処理、あるいは問合せのループに長時間を費やすプログラム。DMLを発行する場合はFORALL文、問合せの場合はBULK COLLECT INTO句およびRETURNING BULK COLLECT INTO句について検討します。

• 最新のPL/SQL言語機能を利用していない古いコード。Oracle Database 10g では多くの点でパフォーマンスが改善されたため、以前のリリースのコードはすべてチューニングの対象になります。

• SQLに直接渡されるだけのDDL文（CREATE TABLEなど）を発行するのではなく、PL/SQL処理に長時間を費やすプログラム。ネイティブ・コンパイルについて検討します。PL/SQLは多くの組込みデータベース機能で使用されるため、このチューニング機能をデータベース全体に適用して、自分のコードのみでなく、多くの面でパフォーマンスを改善できます。

チューニングに取りかかる前に、現在のシステムで、個々のサブプログラムの実行時間を計測します。Oracle Database 10g のPL/SQLには、多くの自動最適化機能があるため、チューニングしなくても、パフォーマンスが改善する場合があります。

PL/SQLのパフォーマンス問題を回避するためのガイドライン

PL/SQLベースのアプリケーションのパフォーマンスが十分でない場合、その原因は通常、不適切なSQL文の記述、プログラミング慣習の不徹底、PL/SQLの基本に対する不注意、共有メモリーの間違った使用などが考えられます。

PL/SQLコードでのCPUオーバーヘッドの回避

この項では、PL/SQLコードが原因でCPUに過度にオーバーヘッドがかかるのを回避するための方法について説明します。

SQL文の最大限の効率化

PL/SQLプログラムは比較的単純に見えますが、これは、ほとんどの処理がSQL文で実行されるためです。処理が低速なSQL文は、実行が低速になる主な原因となります。

SQL文によってプログラムが低速になる場合は、次の手順を試します。

• 適切な索引を使用していることを確認します。状況に応じて、使用する索引の種類は異なります。索引の使用方法は、問合せでの様々な表のサイズ、各問合せでのデータの配布、およびWHERE句で使用される列によって異なる場合があります。

• DBMS_STATSパッケージのサブプログラムを使用して、すべての表について最新の統計が存在することを確認します。

• 次のものを使用して、実行計画およびSQL文のパフォーマンスを分析します。
  • EXPLAIN PLAN文

  • TKPROFユーティリティを持つSQLトレース機能

• 必要に応じて、SQL文を記述しなおします。たとえば、問合せヒントを使用すると、不要な全表スキャンなどの問題を回避できます。

これらの方法の詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。

次に示すように、PL/SQL機能を利用してSQL文のパフォーマンスを改善できる場合もあります。

• PL/SQLループ内でSQL文を実行している場合、INSERT文、UPDATE文およびDELETE文のループのかわりに、FORALL文を使用することを検討します。

• 問合せの結果セットをループしている場合、単一の操作で結果セット全体をメモリーに格納する方法として、SELECT INTO文のBULK COLLECT句を使用することを検討します。

ファンクション・コールの最大限の効率化

不適切に記述されたサブプログラム（低速のソートや検索機能など）によって、パフォーマンスが低下する場合があります。次に示すように、サブプログラムに対する不要なコールを回避して、コードを最適化します。

• SQL問合せ内でファンクションがコールされる場合、問合せ時に表にファンクション索引を作成することで、行ごとにファンクション値をキャッシュできます。CREATE INDEX文には少し時間がかかる場合もありますが、問合せは非常に高速になる可能性があります。

• SQL問合せ内で列がファンクションに渡される場合、問合せではその列上で通常の索引を使用できません。また、ファンクションが（非常に大きい可能性がある）表の行ごとにコールされる可能性があります。例11-1に示すように、問合せをネストして、内側の問合せで問合せ結果が少ない行数になるようにフィルタリングし、外側の問合せでファンクションが数回のみコールされるようにすることを検討します。

例11-1    問合せのネストによるパフォーマンス改善

BEGIN
-- Inefficient, calls function for every row
   FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees)
   LOOP
      DBMS_OUTPUT.PUT_LINE(item.col_alias);
   END LOOP;
-- Efficient, only calls function once for each distinct value.
   FOR item IN
   ( SELECT SQRT(department_id) col_alias FROM
     ( SELECT DISTINCT department_id FROM employees)
   )
   LOOP
      DBMS_OUTPUT.PUT_LINE(item.col_alias);
   END LOOP;
END;
/

OUTパラメータまたはIN OUTパラメータを使用する場合、PL/SQLは、例外が発生した場合に正しく動作するように、パフォーマンスのオーバーヘッドを追加します（OUTパラメータの元の値が保たれるように、OUTパラメータに値を割り当てた後で、未処理例外のためにサブプログラムを終了します）。

作成するプログラムにおいて、このような状況でOUTパラメータの値を保持する必要がない場合、NOCOPYキーワードをパラメータ宣言に追加して、パラメータをOUT NOCOPYまたはIN OUT NOCOPYと宣言できます。

この手法を使用すると、コレクション、大きいVARCHAR2値、LOBなどの大量のデータをOUTパラメータに渡す場合の処理を、大幅に高速化することができます。

また、この手法は、オブジェクト型のメンバー・メソッドにも適用できます。このメソッドがオブジェクト型の属性を変更する場合、すべての属性は、サブプログラムが終了するときにコピーされます。このオーバーヘッドを回避するには、PL/SQLの暗黙的な宣言であるSELF IN OUTを使用するのではなく、メンバー・メソッドの最初のパラメータをSELF IN OUT NOCOPYとして明示的に宣言します。オブジェクト・メソッドの設計上の考慮事項の詳細は、『Oracle Databaseアプリケーション開発者ガイド-オブジェクト・リレーショナル機能』を参照してください。

ループの最大限の効率化

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

• 一連のDML文を発行するために、ループ構造をFORALL文で置換します。

• 結果セットをループして値を格納するかわりに、問合せでBULK COLLECT句を使用して、単一の操作で問合せ結果をメモリーに格納します。

• 結果セットを2回以上ループしたり、結果セットのループ時に他の問合せを発行する必要がある場合、元の問合せを改善して、目的の結果を正確に取得することが可能な場合があります。UNION、INTERSECT、MINUSおよびCONNECT BY問合せ演算子の使用を検討します。

• 問合せを他の問合せ内にネストして（副問合せという）、複数の段階でフィルタリングおよびソートを行うこともできます。たとえば、内側のWHERE句でPL/SQLファンクションをコールすると、表の各行でファンクションが毎回コールされる可能性があります。かわりに、内側の問合せで結果セットが少ない行数になるようにフィルタリングして、外側の問合せでファンクションをコールすることができます。

組込み文字列ファンクションの使用

PL/SQLには、REPLACE、TRANSLATE、SUBSTR、INSTR、RPAD、LTRIMなどの高度に最適化された文字列ファンクションが多数用意されています。これらの組込みファンクションは、通常のPL/SQLよりも効率的な低レベルのコードを使用します。

正規表現の検索にPL/SQLの文字列ファンクションを使用する場合、REGEXP_SUBSTRなどの組込み正規表現ファンクションを使用することを検討します。

• 正規表現による検索には、SQL演算子REGEXP_LIKEを使用します。例6-10を参照してください。

• 文字列のテストまたは操作には、組込みファンクションREGEXP_INSTR、REGEXP_REPLACEおよびREGEXP_SUBSTRを使用します。

Oracleの正規表現機能では、UNIXやPerlプログラミングでも使用される「.」、「*」、「^」、「$」などの文字を使用します。多言語用のプログラミングの場合、その他の拡張機能も使用できます。たとえば、小文字の検索で、アクセント記号付きの小文字には一致しない[a-z]のかわりに、[:lower:]を使用できます。

条件テストにおける最低コストの条件の先頭への配置

PL/SQLは、結果が判別できた時点でただちに論理式の評価を停止します。この機能は、短絡評価と呼ばれるものです。「短絡評価」を参照してください。

ANDまたはORで指定された複数の条件を評価する場合、最低コストの条件を最初に配置します。たとえば、ファンクションの戻り値をテストする前に、PL/SQL変数の値を確認します。これは、PL/SQLがファンクションのコールをスキップできる場合があるためです。

データ型変換の回数の最少化

PL/SQLは実行時に、異なるデータ型を自動的に変換します。たとえば、PLS_INTEGER変数をNUMBER変数に代入すると、両者の内部表現は異なるため、変換が実行されます。

データ型は、暗黙的な変換が最小限に抑えられるように慎重に選択してください。文字式では文字リテラル、数式では小数など、適切な型のリテラルを使用します。

変換の回数を可能なかぎり減らすには、変数の型を変更したり、異なるデータ型で表を設計しなおすことが必要となる場合があります。また、データを一度変換（INTEGER列からPLS_INTEGER変数への変換など）した後は、一貫してそのPL/SQL型を使用することもできます。INTEGERからPLS_INTEGERデータ型に変換すると、より効率の良いハードウェア算術計算が使用されるため、実際のパフォーマンスを向上できる場合があります。「整数の算術計算でのPLS_INTEGERの使用」を参照してください。

整数の算術計算でのPLS_INTEGERの使用

ローカル整変数を宣言する必要がある場合は、PLS_INTEGERデータ型を使用します。PLS_INTEGERは、最も効率的な整数型です。PLS_INTEGER変数は、INTEGER値またはNUMBER値より格納サイズが小さく、PLS_INTEGER演算ではマシン算術計算が使用されます。BINARY_INTEGERデータ型は、PLS_INTEGERと同じです。

NUMBER型とそのサブタイプは特殊な内部形式で表され、パフォーマンスよりも移植性および任意の位取りと精度に重点を置いて設計されています。INTEGERサブタイプの場合も、小数点以下がない浮動小数点数として扱われます。NUMBER変数またはINTEGER変数の演算では、ライブラリ・ルーチンへのコールが必要です。

パフォーマンスを重視するコードでは、INTEGER、NATURAL、NATURALN、POSITIVE、POSITIVEN、SIGNTYPEなどの、制約付きのサブタイプを使用しないようにします。この型の変数を計算で使用すると、実行時に余分なチェックが必要となります。

浮動小数点の算術計算でのBINARY_FLOATおよびBINARY_DOUBLEの使用

NUMBER型とそのサブタイプは特殊な内部形式で表され、パフォーマンスよりも移植性および任意の位取りと精度に重点を置いて設計されています。NUMBER変数またはINTEGER変数の演算では、ライブラリ・ルーチンへのコールが必要です。

BINARY_FLOAT型およびBINARY_DOUBLE型では、システム固有のマシン算術計算命令を使用することができ、科学的な処理など、大量の演算を行うアプリケーションではより適しています。また、これらの型の方が、データベースで必要な領域が少なくて済みます。

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

PL/SQLコードでのメモリー・オーバーヘッドの回避

この項では、PL/SQLコードが原因でメモリーに過度にオーバーヘッドがかかるのを回避するための方法について説明します。

VARCHAR2変数宣言時の大きいサイズの指定

式の結果がどの程度のサイズになるかが不明な場合、VARCHAR2変数に大きいサイズを割り当てる必要がある場合があります。VARCHAR2変数を宣言する際に、256や1000などのやや大きいサイズを予測して指定するよりも、32000などの大きいサイズを指定する方が、実際にはメモリーを節約できます。PL/SQLの最適化機能によって、簡単にオーバーフローの問題を回避し、同時にメモリーも節約できます。VARCHAR2型変数には、4000文字以上のサイズを指定します。PL/SQLは、変数が割り当てられるまで待機し、必要な量の記憶域のみを割り当てます。

関連するサブプログラムのパッケージへのグループ化

パッケージ・サブプログラムを初めてコールすると、パッケージ全体が共有メモリー・プールにロードされます。パッケージ内の関連するサブプログラムに対する2度目以降のコールでは、ディスクI/Oが必要ないため実行速度が向上します。パッケージがメモリーからエージ・アウトされた場合は、再参照する前に再ロードする必要があります。

共有メモリー・プールのサイズを適切に設定すると、パフォーマンスを改善できます。頻繁に使用するパッケージを十分に保持でき、しかもメモリーが浪費されないサイズになるように設定してください。

共有メモリー・プールでのパッケージの確保

オラクル社が提供するパッケージDBMS_SHARED_POOLを使用すると、頻繁にアクセスするパッケージを共有メモリー・プールに確保できます。パッケージを確保すると、Oracleで通常使用されるLRU（Least Recently Used）アルゴリズムでもエージ・アウトされることはありません。パッケージは、プールの占有状態やパッケージへのアクセス頻度に関係なく、メモリーに残ります。

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

コンパイラの警告を回避するためのコードの改善

PL/SQLのコンパイラは、プログラムは動作しても、パフォーマンスが低くなる可能性がある場合に、警告を発行します。このような警告を受け取った場合、パフォーマンスが重要なコードでは、警告のアドバイスに従ってコードを変更し、コードをより効率的にします。

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

開発するPL/SQLアプリケーションが大きくなるほど、パフォーマンスの問題を切り出すのは困難になります。PL/SQLには、実行時の動作をプロファイルし、パフォーマンスのボトルネックを識別できるように、プロファイラAPIが用意されています。また、サーバー上でプログラムの実行をトレースするためのトレースAPIもあります。Oracle Traceを使用すると、サブプログラム別または例外ごとに実行をトレースできます。

プロファイラAPIの使用: DBMS_PROFILERパッケージ

プロファイラAPIは、PL/SQLパッケージDBMS_PROFILERとして実装され、ランタイム統計を収集して保存するサービスを提供します。情報はデータベース表に格納され、後で問合せできます。たとえば、PL/SQLの各行とサブプログラムの実行にかかる所要時間を知ることができます。

プロファイラを使用するには、プロファイル・セッションを開始し、十分な範囲のコードを取得できるまでアプリケーションを実行し、収集されたデータをデータベースにフラッシュし、プロファイル・セッションを停止します。

プロファイラによって、プログラムの実行がトレースされ、各行および各サブプログラムの所要時間が計算されます。収集されたデータを使用してパフォーマンスを改善できます。たとえば、低速のサブプログラムに改善の重点を置くことができます。DBMS_PROFILERサブプログラムの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

プロファイラでデータを収集したら、次の操作を実行できます。

• 収集したパフォーマンス・データの分析

  特定のコード・セグメントの実行や特定のデータ構造へのアクセスに、他よりも時間がかかっている原因を判断します。パフォーマンス・データを問い合せて問題を探します。ほとんどの実行時間を占めているサブプログラムやパッケージに重点を置いて、SQL文、ループ、再帰ファンクションなど、考えられるパフォーマンスのボトルネックを調べてください。

• トレース・データを使用したパフォーマンス改善

  分析結果に基づいて低速のアルゴリズムを書き直します。たとえば、データが急増したために、線形検索をバイナリ検索に置き換える必要が出てくることがあります。また、不適切なデータ構造によって生じた非効率な部分を探し、必要に応じてそのデータ構造を置き換えてください。

トレースAPIの使用: DBMS_TRACEパッケージ

大規模で複雑なアプリケーションの場合は、サブプログラム間のコールを追跡するのは困難です。トレースAPIでコードをトレースすると、サブプログラムの実行順序を確認できます。トレースAPIはPL/SQLパッケージDBMS_TRACEとして実装され、サブプログラムまたは例外ごとに実行をトレースするサービスを提供します。

トレースを使用するには、トレース・セッションを開始し、アプリケーションを実行してから、トレース・セッションを停止します。プログラムを実行すると、トレース・データが収集され、データベース表に格納されます。

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

トレースの制御

大規模なアプリケーションをトレースすると、大量のデータが生成されて管理が困難になることがあります。必要であれば、トレースを起動する前に、トレース・データ収集用の特定のサブプログラムを選択し、収集されるデータ量を制限できます。

また、トレース・レベルも選択できます。たとえば、すべてのサブプログラムと例外をトレースするか、選択したサブプログラムと例外をトレースするように指定できます。

バルクSQLによる、DML文および問合せのループ・オーバーヘッドの削減

PL/SQLは、DMLや問合せなどのSQL文をSQLエンジンへ送信して実行します。SQLは、結果データをPL/SQLに戻します。バルクSQLと総称されるPL/SQL言語機能を使用することによって、このPL/SQLとSQL間の通信において、パフォーマンスのオーバーヘッドを最小化できます。FORALL文を使用すると、INSERT文、UPDATE文またはDELETE文が、1文ずつではなく、バッチで送信されます。BULK COLLECT句を使用すると、結果がSQLからバッチで戻されます。DML文が4つ以上のデータベース行に影響する場合は、バルクSQLを使用するとパフォーマンスが向上します。

値をPL/SQL変数にSQL文で代入することを、バインドと呼びます。PL/SQLバインド操作は、3つのカテゴリに分類されます。

• インバインド: PL/SQL変数またはホスト変数が、INSERT文またはUPDATE文によってデータベースに格納される場合。

• アウトバインド: データベースの値が、INSERT文、UPDATE文またはDELETE文のRETURNING句によってPL/SQL変数またはホスト変数に代入される場合。

• 定義: データベースの値が、SELECT文またはFETCH文によってPL/SQL変数またはホスト変数に代入される場合。

バルクSQLは、VARRAYやネストした表などのPL/SQLコレクションを使用して、大量のデータを単一の操作でやり取りします。この処理をバルク・バインドと呼びます。コレクションに20個の要素がある場合、バルク・バインドは単一の操作で、20回分のSELECT文、INSERT文、UPDATE文またはDELETE文に相当する処理を実行できます。問合せでは、任意の数の結果を戻すことができ、行ごとにFETCH文を指定する必要はありません。

INSERT文、UPDATE文およびDELETE文をスピードアップするには、ループ構造ではなくPL/SQLのFORALL文内にSQL文を記述します。

SELECT文をスピードアップするには、SELECT文で、INTOを使用するかわりにBULK COLLECT INTO句を指定します。

これらの文に関する構文と制限の詳細は、「FORALL文」および「SELECT INTO文」を参照してください。

FORALL文の使用

キーワードFORALLを使用すると、複数のDML文を非常に効率的に実行できます。汎用目的のFORループとは異なり、1つのDML文のみを繰り返すことができます。完全な構文と制約については、「FORALL文」を参照してください。

このSQL文では複数のコレクションを参照できますが、索引値が添字として使用される場合のみ、FORALLによってパフォーマンスが改善されます。

通常、境界には連続した索引番号の範囲を指定します。コレクション要素を削除した後などに索引番号が連続していない場合、INDICES OF句またはVALUES OF句を使用して、実際に存在する索引値のみを反復できます。

INDICES OF句は、指定したコレクションの索引値全体、または下限から上限の境界内の索引値のみを反復します。

VALUES OF句は、BINARY_INTEGER型またはPLS_INTEGER型によって索引付けされ、要素がBINARY_INTEGER型またはPLS_INTEGER型であるコレクションを参照します。FORALL文は、このコレクションの要素によって指定される索引値を反復します。

例11-2のFORALL文では、3つのDELETE文を一度にまとめてSQLエンジンに送信しています。

例11-2    ループでのDELETE文の発行

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);
   COMMIT;
END;
/

例11-3では、一部のデータをPL/SQLコレクションにロードします。次に、コレクション要素をデータベース表に2回挿入しています。1回目はFORループを使用し、2回目はFORALL文を使用します。FORALLを使用する方が高速です。

例11-3    ループでのINSERT文の発行

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
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 := 500;
  t1 INTEGER;
  t2 INTEGER;
  t3 INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- load index-by tables
     pnums(j) := j;
     pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;
  t1 := DBMS_UTILITY.get_time;
  FOR i IN 1..iterations LOOP  -- use FOR loop
     INSERT INTO parts1 VALUES (pnums(i), pnames(i));
  END LOOP;
  t2 := DBMS_UTILITY.get_time;
  FORALL i IN 1..iterations  -- use FORALL statement
     INSERT INTO parts2 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;
/

このブロックを実行すると、FORALLを使用するループの方が高速であることがわかります。

例11-4に示すように、FORALLループの境界は、必ずしもすべての要素に適用する必要はなく、コレクションの一部に適用できます。

例11-4    コレクションの一部でのFORALLの使用

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  -- use only part of varray
      DELETE FROM employees_temp WHERE department_id = depts(j);
   COMMIT;
END;
/

FORALL文でコレクションを使用する前に、コレクションから一部の要素を削除する必要がある場合があります。INDICES OF句は、残りの要素のみを反復して、疎コレクションを処理します。

元のコレクションを変更せずに、一部の要素のみを処理したり、要素を異なる順序で処理したり、または一部の要素を複数回処理する場合もあります。VALUES OF句を使用すると、要素全体を新しいコレクションにコピーする（その結果、場合によっては大量のメモリーが消費される）かわりに、元のコレクションの要素へのポインタとして機能する要素を持つ単純なコレクションを設定できます。

例11-5では、任意のデータ（表名のセット）を保持するコレクションを作成します。要素の一部を削除すると、デフォルトのFORALL文では動作しない疎コレクションになります。プログラムは、INDICES OF句を指定したFORALL文を使用して、データを表に挿入します。次に、元のコレクションの特定の要素を指す2つのコレクションを新しく設定します。VALUES OF句を指定したFORALL文を使用して、名前の各セットを別のデータベース表に格納します。

例11-5    連続していない索引値でのFORALLの使用

-- Create empty tables to hold order details
CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
DECLARE
-- Make collections to hold a set of customer names and order amounts.
   SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
   TYPE cust_typ IS TABLe OF cust_name;
   cust_tab cust_typ;
   SUBTYPE order_amount IS valid_orders.amount%TYPE;
   TYPE amount_typ IS TABLE OF NUMBER;
   amount_tab amount_typ;
-- Make other collections to point into the CUST_TAB collection.
   TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
   big_order_tab index_pointer_t := index_pointer_t();
   rejected_order_tab index_pointer_t := index_pointer_t();
   PROCEDURE setup_data IS BEGIN
 -- Set up sample order data, including some invalid orders and some 'big' orders.
     cust_tab := cust_typ('Company1','Company2','Company3','Company4','Company5');
     amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
   END;
BEGIN
   setup_data();
   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 (where amount is null or 0).
   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;
   DBMS_OUTPUT.PUT_LINE('--- 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;
-- Because the subscripts of the collections are not consecutive, use
-- FORALL...INDICES OF to iterate through the actual subscripts, 
-- rather than 1..COUNT
   FORALL i IN INDICES OF cust_tab
     INSERT INTO valid_orders(cust_name, amount) 
        VALUES(cust_tab(i), amount_tab(i));
-- Now process the order data differently
-- Extract 2 subsets and store each subset in a different table
   setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
   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; -- Add a new element to this collection
-- Record the subscript from the original collection
       rejected_order_tab(rejected_order_tab.LAST) := i; 
     END IF;
     IF amount_tab(i) > 2000 THEN
        big_order_tab.EXTEND; -- Add a new element to this collection
-- Record the subscript from the original collection
        big_order_tab(big_order_tab.LAST) := i;
     END IF;
   END LOOP;
-- Now it's easy to run one DML statement on one subset of elements, 
-- and another DML statement on a different subset.
   FORALL i IN VALUES OF rejected_order_tab
     INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
   FORALL i IN VALUES OF big_order_tab
     INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
   COMMIT;
END;
/
-- Verify that the correct order details were stored
SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;
SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;
SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;

FORALLがロールバックに与える影響

FORALL文では、SQL文の実行によって未処理例外が発生した場合、前回の実行中に行われたすべてのデータベース変更はロールバックされます。ただし、呼び出された例外が捕捉され処理されると、変更は、各SQL文の実行の前にマークされた暗黙的なセーブポイントまでロールバックされます。前の実行の間に行われた変更は、ロールバックされません。たとえば、例11-6に示すように、部門番号と肩書きを格納するデータベース表を作成するとします。次に、肩書きを、より長い肩書きに変更します。この新しい値が長すぎてその列では使用できないため、2番目のUPDATEは失敗します。この例外に対する処理は行われるため、最初のUPDATEはロールバックされず、変更はコミットされます。

例11-6    FORALLでのロールバックの使用

CREATE TABLE emp_temp (deptno NUMBER(2), job VARCHAR2(18));
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 30);
BEGIN
  INSERT INTO emp_temp VALUES(10, 'Clerk');
-- Lengthening this job title causes an exception
  INSERT INTO emp_temp VALUES(20, 'Bookkeeper');
  INSERT INTO emp_temp VALUES(30, 'Analyst');
  COMMIT;
  FORALL j IN depts.FIRST..depts.LAST -- Run 3 UPDATE statements.
    UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j);
-- raises a "value too large" exception
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Problem in the FORALL statement.');
    COMMIT; -- Commit results of successful updates.
END;
/

%BULK_ROWCOUNT属性を持つFORALLによる影響を受ける行カウント

カーソル属性のSQL%FOUND、SQL%ISOPEN、SQL%NOTFOUNDおよびSQL%ROWCOUNTは、直前に実行されたDML文についての役に立つ情報を戻します。カーソル属性の詳細は、「暗黙カーソル」を参照してください。

SQLカーソルは、FORALLで使用するための複合属性%BULK_ROWCOUNTを持ちます。この属性は、結合配列のように機能します。SQL%BULK_ROWCOUNT(i)には、INSERT文、UPDATE文またはDELETE文のi番目の実行によって処理された行数が格納されます。次に例を示します。

例11-7    FORALL文での%BULK_ROWCOUNTの使用

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);
-- How many rows were affected by each DELETE statement?
   FOR i IN depts.FIRST..depts.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' ||
         SQL%BULK_ROWCOUNT(i) || ' rows.');
   END LOOP;
END;
/

FORALL文と%BULK_ROWCOUNT属性は同じ添字を使用します。たとえば、FORALLが5〜10の範囲を使用した場合は、%BULK_ROWCOUNTでも同じ範囲が使用されます。FORALL文でINDICES OF句を使用して疎コレクションを処理する場合、%BULK_ROWCOUNTは、対応する疎の添字を持ちます。FORALL文でVALUES OF句を使用して要素のサブセットを処理する場合、%BULK_ROWCOUNTは、索引コレクションの要素の値に対応する添字を持ちます。索引コレクションの要素が重複しているために一部のDML文が同じ添字を使用して複数回発行される場合、%BULK_ROWCOUNTの対応する要素は、その添字を使用するDML文によって影響を受けたすべての行の合計です。INDICES OF句およびVALUES OF句を使用する場合の%BULK_ROWCOUNTの解釈例については、http://www.oracle.com/technology/sample_code/tech/pl_sqlのPL/SQLサンプル・プログラムを参照してください。

典型的な挿入操作は1行にのみ影響するため、通常、挿入の場合の%BULK_ROWCOUNTは1です。INSERT ...SELECT構造の場合は、%BULK_ROWCOUNTが1よりも大きくなることがあります。たとえば、例11-8のFORALL文は、反復のたびに任意の数の行を挿入します。それぞれの反復後に、%BULK_ROWCOUNTは挿入された行数を戻します。

例11-8    FORALLで%BULK_ROWCOUNTを指定した場合の行カウント

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
        SELECT employee_id, department_id FROM employees
           WHERE department_id = deptnums(i);
  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;
/

FORALL文を実行した後で、スカラー属性の%FOUND、%NOTFOUNDおよび%ROWCOUNTも使用できます。たとえば、%ROWCOUNTは、SQL文のすべての実行によって処理された行の総数を戻します。

%FOUNDと%NOTFOUNDは、SQL文の最後の実行のみを参照します。%BULK_ROWCOUNTを使用すると、個々の実行に対する値を推論できます。たとえば、%BULK_ROWCOUNT(i)がゼロの場合、%FOUNDと%NOTFOUNDはそれぞれ、FALSEおよびTRUEになります。

%BULK_EXCEPTIONS属性を持つFORALL例外の処理

PL/SQLには、FORALL文の実行中に呼び出される例外を処理するメカニズムが用意されています。このメカニズムによって、バルク・バインド操作では、例外に関する情報を保存して処理を継続できます。

エラーが発生した場合もバルク・バインドを完了させるには、FORALL文にキーワードSAVE EXCEPTIONSを（境界の後、DML文の前に）追加します。バルク操作中に発生した例外を追跡する場合は、例外ハンドラも用意してください。

例11-9では、DML操作でエラーが発生しても停止せずに、多数のDML操作を実行する方法の例を示します。この例では、EXCEPTION_INITを使用して、dml_errors例外をORA-24381エラーに関連付けています。ORA-24381エラーは、バルク操作後に例外が捕捉され、保存された場合に発生します。

実行中に呼び出されたすべての例外は、レコードのコレクションを格納するカーソル属性%BULK_EXCEPTIONSに保存されます。各レコードには次の2つのフィールドがあります。

• %BULK_EXCEPTIONS(i).ERROR_INDEXには、例外が呼び出されたときに実行中だったFORALL文の反復が保持されます。

• %BULK_EXCEPTIONS(i).ERROR_CODEには、対応するOracleエラー・コードが保持されます。

%BULK_EXCEPTIONSによって格納された値は常に、直前に実行されたFORALL文を参照します。例外の数は、%BULK_EXCEPTIONS.COUNTに保存されます。添字の範囲は1〜COUNTです。

個々のエラー・メッセージは、置換される引数も含めて保存されませんが、例11-9に示すように、ERROR_CODEをSQLERRMとともに使用すると、エラー・メッセージの本文を検索できます。

例外が発生した反復で使用されたコレクション要素を特定するために、処理を逆にたどる必要がある場合があります。たとえば、INDICES OF句を使用して疎コレクションを処理する場合、要素を1つずつ確認して、%BULK_EXCEPTIONS(i).ERROR_INDEXに対応する要素を検出する必要があります。VALUES OF句を使用して要素のサブセットを処理する場合、索引コレクション内で添字が%BULK_EXCEPTIONS(i).ERROR_INDEXに一致する要素を検出し、次にその要素の値を添字として使用して、元のコレクション内の誤った要素を検出する必要があります。INDICES OF句およびVALUES OF句を使用する場合に誤った要素を検出する方法の例については、http://www.oracle.com/technology/tech/pl_sql/のPL/SQLサンプル・プログラムを参照してください。

キーワードSAVE EXCEPTIONSを省略すると、例外が呼び出された時点でFORALL文の実行が停止します。その場合、SQL%BULK_EXCEPTIONS.COUNTは1を戻し、SQL%BULK_EXCEPTIONSにはレコードが1つのみ含まれます。実行中に例外が呼び出されなければ、SQL%BULK_EXCEPTIONS.COUNTは0（ゼロ）を戻します。

例11-9    例外が発生しても継続するバルク操作

-- create a temporary table for this example
CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE
   TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
   emp_sr empid_tab;
-- create an exception handler for ORA-24381
   errors NUMBER;
   dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
   SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp 
         WHERE hire_date < '30-DEC-94';
-- add '_SR' to the job_id of the most senior employees
     FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
       UPDATE emp_temp SET job_id = job_id || '_SR' 
          WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.

EXCEPTION
  WHEN dml_errors THEN -- Now we figure out what failed and why.
   errors := SQL%BULK_EXCEPTIONS.COUNT;
   DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || errors);
   FOR i IN 1..errors LOOP
      DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '|| 
         'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 
          DBMS_OUTPUT.PUT_LINE('Error message is ' ||
          SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
   END LOOP;
END;
/
DROP TABLE emp_temp;

この例の出力は次のようになります。

Number of statements that failed: 2

Error #1 occurred during iteration #7

Error message is ORA-12899: value too large for column

Error #2 occurred during iteration #13

Error message is ORA-12899: value too large for column

例11-9のPL/SQLでは、更新後の値が大きすぎてjob_id列に挿入できないため、事前定義済例外が発生します。FORALL文の後、SQL%BULK_EXCEPTIONS.COUNTが2を返し、SQL%BULK_EXCEPTIONSの内容が(7,12899)および(13,12899)になりました。

Oracleエラー・メッセージを（コードを含めて）取得するために、SQL%BULK_EXCEPTIONS(i).ERROR_CODEの値を無効にして、エラー・レポート・ファンクションSQLERRMに渡しています。このファンクションでは負の数値が予測されています。

BULK COLLECT句を使用した、問合せ結果のコレクションへの取出し

問合せでキーワードBULK COLLECTを使用すると、結果セットを非常に効率的に取り出すことができます。各行をループするかわりに、単一の操作で、結果を1つ以上のコレクションに格納できます。このキーワードは、SELECT INTO文、FETCH INTO文およびRETURNING INTO句で使用できます。

BULK COLLECT句では、INTOリスト内のすべての変数はコレクションである必要があります。表の列には、スカラー値または複合値（オブジェクト型を含む）を格納できます。例11-10では、ネストした表に、データベースの2つの列全体をロードします。

例11-10    BULK COLLECTによる問合せ結果の取得

DECLARE
   TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
   TYPE NameTab IS TABLE OF employees.last_name%TYPE;
   enums NumTab;   -- No need to initialize the collections.
   names NameTab;  -- Values will be filled in by the SELECT INTO.
   PROCEDURE print_results IS
   BEGIN
     IF enums.COUNT = 0 THEN 
       DBMS_OUTPUT.PUT_LINE('No results!');
     ELSE
       DBMS_OUTPUT.PUT_LINE('Results:');
       FOR i IN enums.FIRST .. enums.LAST
       LOOP
         DBMS_OUTPUT.PUT_LINE('  Employee #' || enums(i) || ': ' || names(i));
       END LOOP;
     END IF;
   END;
BEGIN
 -- Retrieve data for employees with Ids greater than 1000
   SELECT employee_id, last_name
     BULK COLLECT INTO enums, names FROM employees WHERE employee_id > 1000;
-- The data has all been brought into memory by BULK COLLECT
-- No need to FETCH each row from the result set
   print_results();
 -- Retrieve approximately 20% of all rows
   SELECT employee_id, last_name
      BULK COLLECT INTO enums, names FROM employees SAMPLE (20);
   print_results();
END;
/

コレクションは自動的に初期化されます。ネストした表および結合配列は、必要な数の要素を保持できるように拡張されます。VARRAYを使用する場合、すべての戻り値は、宣言したVARRAYのサイズに格納できる必要があります。要素は、索引1から、既存の要素を上書きしながら挿入されます。

BULK COLLECT INTO句の処理はFETCHループに類似しており、問合せに一致する行がない場合にNO_DATA_FOUND例外は発生しません。例11-10に示すように、処理後のネストした表またはVARRAYがNULLかどうか、または処理後の結合配列に要素が含まれているかどうかを確認する必要があります。

結果のコレクションが無制限に拡大するのを防ぐため、LIMIT句または疑似列ROWNUMを使用して、処理される行の数を制限できます。また、SAMPLE句を使用して、行のランダムなサンプルを取り出すこともできます。

例11-11    疑似列ROWNUMによる問合せ結果の制限

DECLARE
   TYPE SalList IS TABLE OF employees.salary%TYPE;
   sals SalList;
BEGIN
-- Limit the number of rows to 50
   SELECT salary BULK COLLECT INTO sals FROM employees
      WHERE ROWNUM <= 50;
-- Retrieve 10% (approximately) of the rows in the table
   SELECT salary BULK COLLECT INTO sals FROM employees SAMPLE (10);
END;
/

次の項に示すように、指定した数の行をカーソルから一度にフェッチすることで、非常にサイズが大きい結果セットを処理できます。

カーソルからのバルク・フェッチの例

例11-12に示すように、1つのカーソルから1つ以上のコレクションにフェッチできます。

例11-12    カーソルから1つ以上のコレクションへのバルク・フェッチ

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;
  names NameList;
  sals  SalList;
  TYPE RecList IS TABLE OF c1%ROWTYPE;
  recs RecList;
  v_limit PLS_INTEGER := 10;
  PROCEDURE print_results IS
  BEGIN
    IF names IS NULL OR names.COUNT = 0 THEN  -- check if collections are empty
       DBMS_OUTPUT.PUT_LINE('No results!');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Results: ');
      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 at once ---');
  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 the columns from the result set come from a single record
    DBMS_OUTPUT.PUT_LINE('  Employee ' || recs(i).last_name || ': $'
         || recs(i).salary);
  END LOOP;
END;
/

例11-13では、1つのカーソルから複数レコードのコレクションにフェッチする方法を示します。

例11-13    カーソルからレコードのコレクションへのバルク・フェッチ

DECLARE
  TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
  dept_recs DeptRecTab;
  CURSOR c1 IS
    SELECT department_id, department_name, manager_id, location_id 
      FROM departments WHERE department_id > 70;
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT INTO dept_recs;
END;
/

LIMIT句を使用したバルク・フェッチ操作の対象行の制限

バルクFETCH文でのみ使用可能なオプションのLIMIT句を使用すると、データベースからフェッチされる行の数を制限できます。例11-14では、ループが繰り返されるたびに、FETCH文によって10行（またはそれ以下）が索引付き表empidsにフェッチされます。前の値は上書きされます。ループから抜けるタイミングを判断するために、empids.COUNTを使用しています。

例11-14    LIMITによるBULK COLLECTの行数の制御

DECLARE
   TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   CURSOR c1 IS SELECT employee_id FROM employees WHERE department_id = 80;
   empids    numtab;
   rows      PLS_INTEGER := 10;
BEGIN
  OPEN c1;
  LOOP -- the following statement fetches 10 rows or less in each iteration
    FETCH c1 BULK COLLECT INTO empids LIMIT rows;
    EXIT WHEN empids.COUNT = 0;
--  EXIT WHEN c1%NOTFOUND; -- incorrect, can omit some data
    DBMS_OUTPUT.PUT_LINE('------- Results from Each Bulk Fetch --------');
    FOR i IN 1..empids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE( 'Employee Id: ' || empids(i));
    END LOOP;
  END LOOP;
  CLOSE c1;
END;
/

RETURNING INTO句を使用したコレクションへのDML結果の取出し

次に示すように、INSERT文、UPDATE文またはDELETE文のRETURNING INTO句にBULK COLLECT句を使用できます。

例11-15    RETURNING INTO句でのBULK COLLECTの使用

CREATE TABLE emp_temp AS SELECT * FROM employees;
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;
/

FORALLとBULK COLLECTの併用

FORALL文とBULK COLLECT句を組み合せることができます。出力コレクションは、FORALL文が反復されるとともに構築されます。

例11-16では、削除対象の各行のemployee_id値をコレクションe_idsに格納しています。コレクションdeptsには3つの要素が存在するため、FORALL文は3回反復します。FORALL文によって発行される各DELETEで5行ずつ削除される場合、削除された行の値を格納するコレクションe_idsには、文が完了すると15の要素が保持されます。

例11-16    BULK COLLECTでのFORALLの使用

CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10,20,30);
   TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
   TYPE dept_t IS TABLE OF employees.department_id%TYPE;
   e_ids enum_t;
   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;
/

各実行によって戻された列の値は、前に戻された値に追加されます。FORALL文のかわりにFORループを使用する場合、戻り値のセットは、各DELETE文によって上書きされます。

FORALL文では、SELECT ...BULK COLLECT文は使用できません。

バルク・バインドとホスト配列の併用

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

ホスト配列はOCIやPro*Cプログラムなどのホスト環境で宣言され、PL/SQLコレクションと区別するためのコロンを接頭辞として付ける必要があります。次の例では、DELETE文に入力ホスト配列が使用されています。実行時に、無名PL/SQLブロックがデータベース・サーバーに送信されて、実行されます。

DECLARE

  ...

BEGIN

  -- assume that values were assigned to the host array

  -- and host variables in the host environment

  FORALL i IN :lower..:upper

    DELETE FROM employees WHERE department_id = :depts(i);

  COMMIT;

END;

/

PL/SQLでの計算集中型プログラムの記述

BINARY_FLOATおよびBINARY_DOUBLEデータ型を使用すると、浮動小数点計算を伴う科学アプリケーションなど、大量の演算を行う実用的なPL/SQLプログラムを記述できます。これらのデータ型は、多くのハードウェア・システム上でシステム固有の浮動小数点型とほぼ同様に動作し、IEEE-754浮動小数点標準で示されるセマンティックを持ちます。

これらのデータ型で小数データを表す方法は、財務処理アプリケーションにはあまり適していません。財務処理アプリケーションの場合、小数部分が正確に表されることの方が、単にパフォーマンスが向上することよりも重要です。

PLS_INTEGERおよびBINARY_INTEGERデータ型はPL/SQL専用のデータ型で、整数の算術計算の場合、SQLデータ型のNUMBERまたはINTEGERより効率的です。PLS_INTEGERまたはBINARY_INTEGERを使用して、整数の算術計算用に純粋なPL/SQLコードを記述したり、PL/SQLで操作できるようにNUMBERまたはINTEGER値をPLS_INTEGERまたはBINARY_INTEGERに変換できます。BINARY_INTEGERデータ型は、PLS_INTEGERと同じです。その他の考慮事項については、「BINARY_INTEGERデータ型への変更」を参照してください。

パッケージ内で、異なる数値パラメータを受け入れるための、オーバーロードされるプロシージャおよびファンクションを記述できます。数学ルーチンは、パラメータ（BINARY_FLOAT、BINARY_DOUBLE、NUMBERおよびPLS_INTEGER）の種類に合わせて最適化して、不要な変換を回避できます。

SQRT、SIN、COSなどの組込み数学ファンクションには、BINARY_FLOATパラメータおよびBINARY_DOUBLEパラメータを受け入れる、オーバーロードされる高速なファンクションがすでに用意されています。BINARY_FLOATおよびBINARY_DOUBLE型の変数をこのようなファンクションに渡したり、このようなファンクションに式を渡すときにTO_BINARY_FLOATまたはTO_BINARY_DOUBLEファンクションをコールすることによって、計算集中型コードをスピードアップできます。

EXECUTE IMMEDIATEおよびカーソル変数を使用した動的SQLのチューニング

たとえば汎用目的のレポート・ライターなど、プログラムによっては、文の正確なテキストが実行時まで判明しない場合、様々なSQL文を構築および処理する必要があります。多くの場合、このような文は実行ごとに変わります。このような文は動的SQL文と呼ばれます。

以前は、動的SQL文を実行するには、提供されているパッケージDBMS_SQLを使用する必要がありました。現在、PL/SQL内では、どの種類の動的SQL文でも、システム固有の動的SQLと呼ばれるインタフェースを使用して実行できます。これに関連する主なPL/SQL機能は、EXECUTE IMMEDIATE文およびカーソル変数（REF CURSORともいう）です。

システム固有の動的SQLコードは、DBMS_SQLパッケージをコールする場合よりもサイズが小さくなり、処理速度も高速になります。次の例では、カーソル変数を宣言し、そのカーソル変数を動的SELECT文に関連付けます。

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   v_ename VARCHAR2(15);
   v_sal   NUMBER := 1000;
   table_name VARCHAR2(30) := 'employees';
BEGIN
   OPEN emp_cv FOR 'SELECT last_name, salary FROM ' || table_name ||
      ' WHERE salary > :s' USING v_sal;
   CLOSE emp_cv;
END;
/

詳細は、第7章「システム固有の動的SQLを使用したSQL操作の実行」を参照してください。

NOCOPYコンパイラ・ヒントを使用したPL/SQLプロシージャ・コールのチューニング

デフォルトでは、OUTパラメータとIN OUTパラメータは値によって渡されます。すべてのIN OUTパラメータの値は、サブプログラムの実行前にコピーされます。サブプログラムの実行中は、一時変数に出力パラメータ値が保持されます。サブプログラムが正常に終了した場合、この値は実パラメータにコピーされます。サブプログラムが未処理例外で終了した場合、元のパラメータは変更されません。

パラメータが、コレクション、レコード、オブジェクト型のインスタンスなどの大規模なデータ構造を表す場合、このコピー作業によって実行速度が遅くなり、メモリーが消費されます。特に、このオーバーヘッドは、オブジェクト・メソッドに対する各コールで発生します。メソッドが正常に終了した場合のみメソッドによる変更が適用されるように、一時コピーがすべての属性に対して作成されます。

このオーバーヘッドを回避するには、NOCOPYヒントを指定します。これによって、PL/SQLコンパイラはOUTおよびIN OUTパラメータを参照によって渡すことができます。サブプログラムが正常に終了した場合、動作は通常の場合と同じです。サブプログラムが例外によって終了した場合も、OUTおよびIN OUTパラメータ（またはオブジェクト属性）の値が変更されることがあります。この手法を使用する場合、サブプログラムですべての例外が処理されるようにします。

次の例では、IN OUTパラメータv_staffを参照によって渡すように、コンパイラに指示します。これによって、エントリのVARRAYをサブプログラムにコピーしてサブプログラムが終了することを回避できます。

DECLARE

  TYPE Staff IS VARRAY(200) OF Employee;

  PROCEDURE reorganize (v_staff IN OUT NOCOPY Staff) IS ...

例11-17では、ローカルのネストした表に25,000レコードがロードされます。この表は、何も実行しない2つのローカル・プロシージャに渡されます。プロシージャへのコールでNOCOPYを使用する方が、時間がかかりません。

例11-17    パラメータでのNOCOPYの使用

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;
/

NOCOPYの制限

NOCOPYを使用することによって、パラメータのエイリアシングの可能性が高くなります。詳細は、「サブプログラムのパラメータのエイリアシングの理解」を参照してください。

NOCOPYは、ディレクティブではなく、ヒントです。次の場合には、PL/SQLコンパイラはNOCOPYヒントを無視して、値によってパラメータを渡します。エラーは生成されません。

• 実パラメータが結合配列の要素である場合。この制限は、パラメータが結合配列全体の場合は適用されません。

• 実パラメータに、位取りやNOT NULLなどの制約がある場合。この制限は、サイズ制約付きの文字列には適用されません。この制限は、制約付きの要素またはコンポジット型の属性には拡張されません。

• 実パラメータと仮パラメータがレコードであり、いずれかまたは両方のレコードが%ROWTYPEまたは%TYPEを使用して宣言されており、レコード内の対応するフィールドの制約が異なる場合。

• 実パラメータと仮パラメータがレコードであり、実パラメータはカーソルFORループの索引として（暗黙的に）宣言されており、レコード内の対応するフィールドの制約が異なる場合。

• 実パラメータを渡すために、暗黙的なデータ型の変換が必要となる場合。

• サブプログラムが、データベース・リンクによって、または外部プロシージャとしてコールされる場合。

システム固有の実行のためのPL/SQLコードのコンパイル

PL/SQLプロシージャをコンパイルして、共有ライブラリに常駐するシステム固有のコードにすると、プロシージャをスピードアップできます。プロシージャはC言語のコードに変換されてから、通常のCコンパイラでコンパイルされ、Oracleプロセスにリンクされます。

ネイティブ・コンパイルは、提供されているOracleパッケージと独自に記述したプロシージャの両方に使用できます。この方法でコンパイルしたプロシージャは、共有サーバー構成（以前のマルチスレッド・サーバー）やOracle Real Application Clustersなどのすべてのサーバー環境で動作します。

はじめに

PL/SQLのネイティブ・コンパイルを初めて実行する場合は、本番環境の前に、まずテスト・データベースで試します。

データベースをPL/SQLのネイティブ・コンパイル用に構成する前に、常にデータベースをバックアップします。パフォーマンス上のメリットに比べて、余分なコンパイル時間のデメリットの方が大きい場合、バックアップからリストアする方が、解釈済モードで全体を再コンパイルするよりも、必要な時間が短い場合があります。

設定手順の一部では、DBAの認可レベルが必要です。一部の初期化パラメータの値を変更して、データベース・サーバーで（可能であればインスタンスのデータ・ファイルの近くに）新しいディレクトリを作成する必要があります。また、データベース・サーバーにはCコンパイラも必要です。クラスタの場合、コンパイラはノードごとに必要です。この手順を開発用マシン上でテストできる場合でも、通常、ネイティブ・コンパイルを本番サーバーで実行するには、DBAに協力を依頼する必要があります。

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

PL/SQLのネイティブ・コンパイルによって、計算集中型のプロシージャ操作のパフォーマンスは大幅に改善されます。このような操作の例には、データ・ウェアハウス・アプリケーションや、サーバー側でデータを大幅に変換して表示するアプリケーションなどがあります。このような場合、実行速度が最高30%向上する場合があります。

この手法では、PL/SQLからコールされるSQL文は高速にならないため、SQLの実行に費やす時間の少ない計算集中型のPL/SQLプロシージャで最も高い効果が得られます。PL/SQLのネイティブ・コンパイルを有効にしてパフォーマンスが改善される度合いは、テストによって確認できます。

ネイティブ・コンパイルを使用してプログラム・ユニットをコンパイルすると、デフォルトの解釈済モードを使用するよりも時間がかかります。コードを頻繁に再コンパイルする、開発サイクルの繁忙期には、ネイティブ・コンパイルを無効にしてもかまいません。

PL/SQLのネイティブ・コンパイルを使用するとデータベース操作で大幅にパフォーマンスが改善すると判断した場合、NATIVE設定を使用してデータベース全体をコンパイルすることをお薦めします。データベース内のすべてのPL/SQLコードをコンパイルすると、作成したコードおよびすべての組込みPL/SQLパッケージへのコールがスピードアップします。

ご使用の環境で解釈コンパイルが必要な場合は、すべてのPL/SQLユニットをINTERPRETEDでコンパイルできます。たとえば、NATIVE PL/SQLユニットが含まれているデータベース全体を、Cコンパイラが使用できない環境にインポートする場合などです。

データベース全体をNATIVEまたはINTERPRETEDコンパイルに変換する場合は、「PL/SQLネイティブ・コンパイルまたは解釈コンパイルで使用するデータベース全体の変更」を参照してください。

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

ネイティブ・コンパイルを使用しない場合、各PL/SQLプログラム・ユニットは、中間形式の機械可読コード（mコード）にコンパイルされます。mコードはデータベース・ディクショナリに格納され、実行時に解釈されます。PL/SQLのネイティブ・コンパイルを使用する場合、PL/SQL文は実行時に解釈が不要なCコードへ変換され、実行時のパフォーマンスが改善されます。

PLSQL_初期化パラメータでは、PL/SQLネイティブ・コンパイルの環境を設定します。詳細は、「PL/SQLネイティブ・コンパイルの初期化パラメータの設定」を参照してください。

PL/SQLは、コマンド・ファイル$ORACLE_HOME/plsql/spnc_commands、およびオペレーティング・システムでサポートされているCコンパイラとリンカーを使用し、変換されたCコードをコンパイルして共有ライブラリにリンクします。「spnc_commandsファイル」を参照してください。

共有ライブラリは、データ・ディクショナリ内に格納されるため、自動的にパックアップすることができ、削除されないように保護されます。共有ライブラリ・ファイルは、ファイル・システムにコピーされ、PL/SQLのサブプログラムの起動時にロードされて実行されます。このファイルがデータベースの停止中にファイル・システムから削除された場合、またはライブラリを格納するディレクトリを変更した場合、ファイルは自動的に再度抽出されます。

SQL文をコールするだけのPL/SQLプログラム・ユニットの場合、あまり（またはまったく）高速化しないこともありますが、ネイティブ・コンパイルされたPL/SQLが、対応する解釈済コードより低速になることはありません。コンパイルされたコードは解釈済コードと同じライブラリをコールするため、動作は同じです。

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

プロシージャをコンパイルして共有ライブラリに入れると、Oracleプロセスに自動的にリンクされます。データベースを再起動したり、共有ライブラリを別の場所に移動する必要はありません。ストアド・プロシージャがすべて解釈されたか、すべてシステム固有の実行用にコンパイルされたか、または両者を組み合せてコンパイルされたかにかかわらず、ストアド・プロシージャを任意にコールできます。

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

格納された設定は、再評価の一部として再コンパイルが行われるときにのみ使用されます。SQLコマンドのCREATE OR REPLACEまたはALTER...COMPILEを使用してPL/SQLサブプログラムを明示的にコンパイルする場合、現在のセッション設定が使用されます。「PL/SQLコンパイル用の初期化パラメータ」を参照してください。

生成された共有ライブラリは、SYSTEM表領域のデータベースに格納されます。ネイティブ・コンパイルされたプロシージャを初めて実行すると、対応する共有ライブラリが、データベースからPLSQL_NATIVE_LIBRARY_DIR初期化パラメータで指定されたディレクトリにコピーされます。

Real Application ClustersおよびPL/SQLのネイティブ・コンパイル

クラスタではすべてのノードでPL/SQLのサブプログラムをコンパイルすることが必要な場合があるため、クラスタの各ノードでCコンパイラが必要です。また、各ノードの$ORACLE_HOME/plsql/spnc_commandsファイルで、設定およびパスを適切に指定する必要があります。

Real Application Clusters（RAC）環境でPL/SQLのネイティブ・コンパイルを使用する場合、共有ライブラリ・ファイルの元のコピーはデータベースに格納され、自動的にクラスタのすべてのノードに伝播されます。この機能を使用するためにライブラリをコピーする必要はありません。

RACクラスタのすべてのノードで、PL/SQLネイティブ・コンパイルを制御する初期化パラメータの設定が同じであることを確認してください。また、共有ライブラリを配置する、PLSQL_NATIVE_LIBRARY_DIRに指定したパスが、クラスタを構成するすべてのノードに等しく作成されていることを確認してください。

ネイティブ・コンパイルの制限

ネイティブ・コンパイルには次のような制限があります。

• PL/SQL用のデバッグ・ツールでは、システム固有の実行用にコンパイルされたプロシージャは処理されません。

• 多数のプロシージャとパッケージ（通常は15000以上）をシステム固有の実行用にコンパイルすると、単一のディレクトリに多数の共有オブジェクトができるため、システムのパフォーマンスに影響することがあります。回避策の詳細は、「PL/SQLのシステム固有のライブラリ用のサブディレクトリの設定」を参照してください。

spnc_commandsファイル

$ORACLE_HOME/plsqlディレクトリのspnc_commandsファイルには、各プログラムをコンパイルおよびリンクするコマンドのテンプレートが含まれています。%(src)などの一部の特殊な名前は事前定義されており、対応するファイル名で置換されます。$(ORACLE_HOME)変数は、Oracleホーム・ディレクトリの位置で置換されます。コメント行は、#文字で始まります。このファイルには、すべての特殊な表記法について説明するコメントが記載されています。

spnc_commandsファイルには、デフォルトのCコンパイラの事前定義されたパスが記述されています。パスはオペレーティング・システムによって異なります。オペレーティング・システムごとに特定のコンパイラがサポートされます。PL/SQLモジュールのコンパイルに使用できるコンパイラは1つのみです。データベース内のPL/SQLモジュールを異なるコンパイラでコンパイルしないでください。

spnc_commandsファイルを参照して、コマンド・テンプレートが正しいことを確認できます。このファイルは、システム管理者がコンパイラを別の場所にインストールした場合、またはサポートされている別のCコンパイラを使用する場合を除き、変更する必要はありません。詳細は、ご使用のプラットフォームのOracleデータベースのインストレーション・ガイドを参照してください。また、Oracle Metalink（http://metalink.oracle.com）でspnc_commandsを検索して情報を参照することもできます。

PL/SQLネイティブ・コンパイルの初期化パラメータの設定

この項では、PL/SQLネイティブ・コンパイルの設定に使用される初期化パラメータについて説明します。

• PLSQL_NATIVE_LIBRARY_DIR

• PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

• PLSQL_CODE_TYPE

これらのパラメータの設定を確認するには、SQL*Plusで次のように入力します。

SHOW PARAMETERS PLSQL

「PL/SQLコンパイル用の初期化パラメータ」を参照してください。

PLSQL_NATIVE_LIBRARY_DIR初期化パラメータ

システム・レベル専用の必須パラメータで、ネイティブ・コンパイル済のPL/SQLコードを格納する共有ライブラリの格納場所のフルパスおよびディレクトリ名を指定します。この値は、既存のアクセス可能なディレクトリを明示的に示す必要があります。このパスには、ORACLE_HOMEなどの変数を含めることはできません。パラメータ値を設定するには、ALTER SYSTEMコマンドを使用するか、または初期化ファイルを更新します。

たとえば、PL/SQLのシステム固有のライブラリに使用するディレクトリのパスが/oracle/oradata/db1/natlibの場合、初期化ファイルの設定は次のようになります。

PLSQL_NATIVE_LIBRARY_DIR='/oracle/oradata/db1/natlib'

Optimal Flexible Architecture（OFA）規則に従って、共有ライブラリのディレクトリを、データ・ファイルの格納場所のサブディレクトリとして作成することをお薦めします。セキュリティ上の理由から、このディレクトリに対する書込み権限を持つユーザーは、oracleおよびrootのみにする必要があります。

Real Application Clusters環境を使用している場合は、「Real Application ClustersおよびPL/SQLのネイティブ・コンパイル」を参照してください。PLSQL_NATIVE_LIBRARY_DIR初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT初期化パラメータ

システム・レベル専用のオプションのパラメータで、PLSQL_NATIVE_LIBRARY_DIRパラメータで指定したディレクトリ内のサブディレクトリの数を指定します。パラメータ値を設定するには、ALTER SYSTEMコマンドを使用するか、または初期化ファイルを更新します。

たとえば、1,000個のサブディレクトリに対してパラメータを設定する場合は、初期化パラメータ・ファイルに次のように設定します。

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT=1000;

ネイティブ・コンパイルされたプログラム・ユニットの数が膨大である場合は、このパラメータを設定してください。「PL/SQLのシステム固有のライブラリ用のサブディレクトリの設定」を参照してください。

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。

PLSQL_CODE_TYPE初期化パラメータ

PLSQL_CODE_TYPE初期化パラメータによって、PL/SQLコードをネイティブ・コンパイルするか解釈済にするかが決まります。デフォルト設定はINTERPRETEDです。PL/SQLのネイティブ・コンパイルを有効にするには、PLSQL_CODE_TYPEの値をNATIVEに設定します。

このパラメータは、システム・レベル、セッション・レベルまたは特定のPL/SQLモジュール用に設定できます。パラメータ値を設定するには、ALTER SYSTEMコマンドまたはALTER SESSIONコマンドを使用するか、初期化ファイルを更新してください。データベース全体をNATIVEでコンパイルする場合は、システム・レベルでPLSQL_CODE_TYPEを設定することをお薦めします。

次のSQL*Plus構文では、セッション・レベルでパラメータを設定しています。

ALTER SESSION SET PLSQL_CODE_TYPE='NATIVE';

ALTER SESSION SET PLSQL_CODE_TYPE='INTERPRETED';

PLSQL_CODE_TYPE = NATIVE句をALTER ..COMPILE文とともに特定のPL/SQLモジュールに対して使用することもできます。詳細は、例11-18を参照してください。この影響を受けるのは、指定したモジュールのみです。セッション全体の初期化パラメータは変更されません。パッケージ仕様部とその本体は、同じネイティブ・コンパイル設定でコンパイルする必要はありません。

PLSQL_CODE_TYPE初期化パラメータの詳細は、『Oracle Databaseリファレンス』を参照してください。

PL/SQLのシステム固有のライブラリ用のサブディレクトリの設定

デフォルトでは、PL/SQLプログラム・ユニットは1つのディレクトリに保持されます。ただし、プログラム・ユニットの数が膨大である場合は、多数のファイルを1つのディレクトリで処理するのが難しいこともあります。この問題を回避するため、PLSQL_NATIVE_LIBRARY_DIR初期化パラメータで指定したディレクトリ下の複数のサブディレクトリにPL/SQLプログラム・ユニットを分散させることをお薦めします。

既存のデータベースを新しいデータベースへ移植する場合、またはテスト・データベースを設定した場合、次のSQL問合せによって、使用しているPL/SQLプログラム・ユニットの数を判断します。

SELECT COUNT (*) from DBA_PLSQL_OBJECT_SETTINGS

パッケージなど一部のユニットを除外する場合は、前述の問合せで次の構文を使用します。

    WHERE TYPE NOT IN ('TYPE', 'PACKAGE')

PL/SQLのシステム固有のライブラリ用のサブディレクトリを設定する必要がある場合は、まずサブディレクトリをd0、d1、d2、d3...dxと順番に作成します。ここで、xはディレクトリの総数です。このタスクには、スクリプトを使用することをお薦めします。たとえば、次のようなPL/SQLブロックを実行して、出力をファイルに保存してから、そのファイルをシェル・スクリプトとして実行することができます。

SPOOL make_dirs

BEGIN

  FOR j IN 0..1000 -- change to the number of directories needed

  LOOP

    DBMS_OUTPUT.PUT_LINE ( 'mkdir d' || TO_CHAR(j) );

  END LOOP;

END;

/

SPOOL OFF

次に、PLSQL_NATIVE_LIBARY_SUBDIR_COUNT初期化パラメータに、作成したサブディレクトリの数を設定します。たとえば、1,000個のサブディレクトリを作成した場合は、SQL*Plusを使用して次のSQL文を入力できます。

ALTER SYSTEM SET PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT=1000;

「PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT初期化パラメータ」を参照してください。

PL/SQLネイティブ・コンパイルの設定方法およびテスト方法

ネイティブ・コンパイルを使用して1つ以上のサブプログラムを設定しテストする手順は、次のとおりです。

1. 必要な初期化パラメータを設定します。「PL/SQLネイティブ・コンパイルの初期化パラメータの設定」を参照してください。

2. 次のいずれかの方法を使用して1つ以上のサブプログラムをコンパイルします。
   • CREATE OR REPLACEを使用してサブプログラムを作成または再コンパイルします。

   • 例11-18に示すとおり、ALTER PROCEDURE、ALTER FUNCTIONまたはALTER PACKAGEコマンドにCOMPILEオプションを指定して、特定のサブプログラムまたはパッケージ全体を再コンパイルします。

   • サブプログラムを削除して再作成します。

   • オラクル社が提供するパッケージ・セットを作成するSQL*Plusスクリプトの1つを実行します。

   • PLSQL_CODE_TYPE=NATIVEを指定し、構成済の初期化ファイルを使用してデータベースを作成します。データベースの作成中に、utlirpスクリプトが実行され、オラクル社が提供するパッケージがすべてコンパイルされます。

   例11-18に、ネイティブ・コンパイルを使用してプロシージャを変更しテストするまでのプロセスを示します。プロシージャはただちにコールできるようになり、Oracleプロセス内で共有ライブラリとして直接実行されます。コンパイル時にエラーが発生した場合、USER_ERRORSビューまたはSQL*PlusのSHOW ERRORSコマンドを使用して、エラーを確認できます。

例11-18    システム固有の実行のためのPL/SQLプロシージャのコンパイル

-- PLSQL_NATIVE_LIBRARY_DIR must be set to an existing, accessible directory
SET SERVEROUTPUT ON FORMAT WRAPPED

CREATE OR REPLACE PROCEDURE hello_native AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello world. Today is ' || TO_CHAR(SYSDATE) || '.');
END hello_native;
/

ALTER PROCEDURE hello_native COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
SHOW ERRORS
-- check for a file HELLO_NATIVE... in the PLSQL_NATIVE_LIBRARY_DIR directory
CALL hello_native();

SELECT PLSQL_CODE_TYPE FROM USER_PLSQL_OBJECT_SETTINGS
   WHERE NAME = 'HELLO_NATIVE';

REM 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';
REM To stop spooling the output: SPOOL OFF

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;

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

CREATE 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;
END pkg1;
/

-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));

-- Define the ref cursor types and function
CREATE OR REPLACE PACKAGE refcur_pkg 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;
    EXIT WHEN p%NOTFOUND;
    -- first row
    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);
    -- second row
    out_rec.var_char1 := in_rec.email;
    out_rec.var_char2 := in_rec.phone_number;
    PIPE ROW(out_rec);
  END LOOP;
  CLOSE p;
  RETURN;
  END;
END refcur_pkg;
/
-- SELECT query using the f_transc table function
SELECT * FROM TABLE(
   refcur_pkg.f_trans(CURSOR(SELECT * FROM employees WHERE department_id = 60)));

-- Define the ref cursor types
CREATE PACKAGE refcur_pkg 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;
END refcur_pkg;
/

-- SELECT query using the g_trans table function
SELECT * FROM TABLE(refcur_pkg.g_trans(
  CURSOR(SELECT * FROM employees WHERE department_id = 60),
  CURSOR(SELECT * FROM departments WHERE department_id = 60)));

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

CREATE PACKAGE pkg_gpa 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
-- The function accepts a ref cursor and loops through all the input rows
  LOOP
     FETCH input_values INTO weight, grade;
     EXIT WHEN input_values%NOTFOUND;
-- Accumulate the weighted average
     total_weight := total_weight + weight;
     total := total + grade*weight;
  END LOOP;
  PIPE ROW (total / total_weight);
  RETURN; -- the function returns a single result
END;
END pkg_gpa;
/
-- the query result comes back as a nested table with a single row
-- COLUMN_VALUE is a keyword that returns the contents of a nested table
SELECT w.column_value "weighted result" FROM TABLE(
       pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;