ヘッダーをスキップ

Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス
10g リリース2(10.2)

B19257-01
目次
目次
索引
索引

戻る 次へ

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

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

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

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

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

PLSQL_CCFLAGSPLSQL_CODE_TYPEPLSQL_DEBUGPLSQL_OPTIMIZE_LEVELPLSQL_WARNINGSNLS_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コードをチューニングする場合

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

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

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

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

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

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

SQL文の最大限の効率化

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

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

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

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

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

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

例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アプリケーションはループの周囲に構築されることが多いため、次に示すように、ループ自体およびループ内のコードを最適化することは重要です。

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

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

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

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

参照:

Oracle Technology Network(OTN)の「Oracle By Example - Using Regular Expressions」

http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm 

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

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変数の演算では、ライブラリ・ルーチンへのコールが必要です。

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

浮動小数点の算術計算での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パッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

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

トレース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つのカテゴリに分類されます。

バルク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-2FORALL文では、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%FOUNDSQL%ISOPENSQL%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属性は同じ添字を使用します。たとえば、FORALL5〜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-8FORALL文は、反復のたびに任意の数の行を挿入します。それぞれの反復後に、%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によって格納された値は常に、直前に実行されたFORALL文を参照します。例外の数は、%BULK_EXCEPTIONS.COUNTに保存されます。添字の範囲は1〜COUNTです。

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

例外が発生した反復で使用されたコレクション要素を特定するために、処理を逆にたどる必要がある場合があります。たとえば、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_FLOATBINARY_DOUBLENUMBERおよびPLS_INTEGER)の種類に合わせて最適化して、不要な変換を回避できます。

SQRTSINCOSなどの組込み数学ファンクションには、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ヒントを無視して、値によってパラメータを渡します。エラーは生成されません。

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

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

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

参照:

  • PL/SQLネイティブ・コンパイルの設定に関するBest Practicesおよびその他の情報は、Oracle Technology Network(OTN)を参照してください。

    http://www.oracle.com/technology/tech/pl_sql/

  • Note 269012.1など、ネイティブ・コンパイルの詳細は、Oracle Metalinkを参照してください。

    http://metalink.oracle.com

 

はじめに

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

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

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


注意:

PL/SQLコードをネイティブ・コンパイルして使用するための前提要件については、プラットフォームごとにOracleデータベースのインストレーション・ガイドで説明しています。ソフトウェア要件の項で、ご使用のプラットフォームのネイティブ・コンパイルに使用するための動作保証されたコンパイラを確認し、保証されたコンパイラを使用してください。 


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に指定したパスが、クラスタを構成するすべてのノードに等しく作成されていることを確認してください。

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

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

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ネイティブ・コンパイルの設定に使用される初期化パラメータについて説明します。

これらのパラメータの設定を確認するには、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 PROCEDUREALTER 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();

  • 処理が正常に実行されたかどうかを確認するために、データ・ディクショナリを問い合せて、プロシージャがシステム固有の実行用にコンパイルされたかどうかを調べることができます。既存のプロシージャがシステム固有の実行用にコンパイルされたかどうかを確認するには、ALL_PLSQL_OBJECT_SETTINGSビューを問い合せます。PLSQL_CODE_TYPE列の値は、プロシージャがシステム固有の実行用にコンパイルされていればNATIVE、それ以外の場合はINTERPRETEDとなります。詳細は、『Oracle Databaseリファレンス』を参照してください。

  • たとえば、プロシージャhello_nativeのステータスを確認するには、例11-19のように問合せを使用します。

    例11-19    コンパイルされたプロシージャに対するplsql_code_typeの確認

    SELECT PLSQL_CODE_TYPE FROM USER_PLSQL_OBJECT_SETTINGS
       WHERE NAME = 'HELLO_NATIVE';
    
    

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

    この項で示す手順を使用して、PL/SQLネイティブ・コンパイル用に新しいデータベースを設定します。多くのデータベース操作で使用されるすべての組込みPL/SQLパッケージで、パフォーマンスが改善します。Real Application Clusters環境を使用している場合は、「Real Application ClustersおよびPL/SQLのネイティブ・コンパイル」を参照してください。

    1. spnc_commandsファイルのコマンド・テンプレートが正しいことを確認します。システム管理者に連絡し、必要なCコンパイラがオペレーティング・システムに存在することを確認します。「spnc_commandsファイル」を参照してください。

    2. PL/SQLのシステム固有ライブラリのディレクトリをOracleデータベースごとに作成します。「PLSQL_NATIVE_LIBRARY_DIR初期化パラメータ」を参照してください。

      • OracleデータベースごとにPL/SQLライブラリを設定する必要があります。共有ライブラリ(.soおよび.dllファイル)は、各データベースへ論理的に接続されます。データベース間で共有することはできません。PL/SQLライブラリを設定して共有すると、データベースは破損します。

      • .soおよび.dllファイルに対する不正アクセスを防ぐために、OFA規則に従って、保護された場所にディレクトリを作成します。

      • PL/SQLのネイティブ・コンパイルに使用されるコンパイラの実行可能ファイルが、適切な権限を持つユーザーによってのみ書込み可能であることを確認します。

      • 共有ライブラリの元のコピーが、データベース内に格納されていることを確認します。これによって元のコピーがデータベースとともに自動的にバックアップされます。

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

      データベース・コンフィギュレーション・アシスタントを使用する場合は、これを使用してPL/SQLネイティブ・コンパイルに必要な初期化パラメータを設定します。PLSQL_NATIVE_LIBRARY_DIRをアクセス可能なディレクトリに設定し、PLSQL_CODE_TYPENATIVEに設定していることを確認します。

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

    この項に説明するプロセスで、dbmsupgnv.sqlおよびdbmsupgin.sqlのスクリプトを使用して、既存のデータベースのすべてのPL/SQLモジュールをそれぞれNATIVEまたはINTERPRETEDに再コンパイルできます。この変換を実行する前に、「PL/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スクリプトに置き換えます。

     

    1. ネイティブ・コンパイルをサポートするように、次の点が正しく構成されていることを確認します。

    ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE
        REUSE SETTINGS;
  • アプリケーション・サービス、リスナーおよびデータベースを停止します。

    • Forms Process、Web Server、Reports Server、Concurrent Manager Serverなど、すべてのアプリケーション・サービスを停止します。すべてのアプリケーション・サービスを停止したら、データベースへのすべての接続が終了していることを確認します。

    • データベースのTNSリスナーを停止して、新規接続が作成されないようにします。

    • ユーザーSYSとして、通常モードまたは即時モードでデータベースを停止します。『Oracle Database管理者ガイド』の起動と終了に関する項を参照してください。

  • 初期化パラメータ・ファイルでPLSQL_CODE_TYPENATIVEに設定します。データベースでサーバー・パラメータ・ファイルを使用している場合は、データベースを起動してからこの設定を行ってください。「PLSQL_CODE_TYPE初期化パラメータ」を参照してください。

    PLSQL_CODE_TYPEの値は、この手順のPL/SQLユニットの変換には影響を与えません。ただし、この手順以降にコンパイルしたユニットは影響を受けるため、ここで必要なコンパイル・タイプを明示的に設定する必要があります。

  • UPGRADEオプションを使用して、データベースをアップグレード・モードで起動します。SQL*PlusのSTARTUPの詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください。

  • 次のコードを実行して、無効なPL/SQLユニットを一覧表示します。SQL SPOOLコマンドを使用すると、問合せの出力を今後の参照のために保存しておくことができます。

  • 例11-20    無効なPL/SQLユニットの確認

    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
    
    

    Oracleが提供するユニットが無効化されている場合は、有効化します。次に例を示します。

    ALTER PACKAGE OLAPSYS.DBMS_AWM COMPILE BODY REUSE SETTINGS;

    『Oracle Database SQLリファレンス』のALTER FUNCTIONALTER PACKAGEおよびALTER PROCEDUREを参照してください。ユニットを有効化できない場合は、今後の解決のためスプール・ログを保存してから処理を続けます。

  • 次の問合せを実行して、NATIVEおよびINTERPRETEDにコンパイルされるオブジェクトの数を確認します。問合せの出力を保存する場合は、SQLのSPOOLコマンドを使用します。

    例11-21    PL/SQLのコンパイル・タイプの確認

    SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS
      WHERE PLSQL_CODE_TYPE IS NOT NULL
      GROUP BY TYPE, PLSQL_CODE_TYPE
      ORDER BY TYPE, PLSQL_CODE_TYPE;
    
    

    NULL plsql_code_typeのオブジェクトは特別な内部オブジェクトであり、無視してかまいません。

  • ユーザーSYSとして$ORACLE_HOME/rdbms/admin/dbmsupgnv.sqlスクリプトを実行して、すべてのPL/SQLユニットのディクショナリ表でplsql_code_type設定をNATIVEに更新します。この処理によってもユニットが無効化されます。パッケージ仕様部を除外するときは、スクリプトでTRUEを使用します。パッケージ仕様部を含めるときは、FALSEを使用します。

    この更新は、データベースがUPGRADEモードの場合に実行する必要があります。スクリプトを使用すると、更新を正常に完了できます。また、すべての変更をロールバックすることもできます。

  • データベースを停止し、NORMALモードで再起動します。

  • 問題の発生を回避するため、utlrp.sqlスクリプトを実行する前に他のセッションに接続しないことをお薦めします。このためには、次の文を実行します。

    ALTER SYSTEM ENABLE RESTRICTED SESSION;

  • ユーザーSYSとして$ORACLE_HOME/rdbms/admin/utlrp.sqlスクリプトを実行します。このスクリプトでは、デフォルトの並列度を使用してすべてのPL/SQLモジュールを再コンパイルします。並列度を明示的に設定する方法については、スクリプトのコメントを参照してください。

    スクリプトが異常終了した場合は、utlrp.sqlスクリプトを再実行して残りの無効なPL/SQLモジュールを再コンパイルします。

  • コンパイルが正常に完了したら、例11-20の問合せを使用して、他に無効なPL/SQLユニットがないかを確認します。問合せの出力をpost_upgrade_invalid.logファイルにスプーリングしておくと、以前に作成したpre_upgrade_invalid.logファイルがあれば、スプーリングした内容と比較できます。

  • 例11-21の問合せを再実行します。dbmsupgnv.sqlで再コンパイルした場合は、除外したTYPE仕様部およびパッケージ仕様部を除くすべてのPL/SQLユニットがNATIVEであることを確認します。dbmsupgin.sqlで再コンパイルした場合は、すべてのPL/SQLユニットがINTERPRETEDであることを確認します。

  • データベースの制限付きセッション・モードを無効にしてから、前に停止したサービスを開始します。制限付きセッション・モードを無効にするには次の文を実行します。

    ALTER SYSTEM DISABLE RESTRICTED SESSION;

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

    この項では、パイプライン・テーブル・ファンクションと呼ばれる特殊なファンクションを連鎖する方法について説明します。テーブル・ファンクションは、データ・ウェアハウスなどで複数の変換をデータに適用する場合に使用します。

    参照:

    • 次のURLで「テーブル・ファンクションとカーソル式」を参照してください。

      http://www.oracle.com/technology/tech/pl_sql/pdf/PLSQL_9i_New_Features_Doc.pdf

    • 「テーブル・ファンクションとカーソル式」の例は、次のURLにアクセスしてください。

      http://www.oracle.com/technology/sample_code/tech/pl_sql/index.html

    • パイプラインと並列テーブル・ファンクションの詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。

     

    パイプライン・テーブル・ファンクションの概要

    パイプライン・テーブル・ファンクションは、物理データベース表と同様に問合せできるように、またはPL/SQLコレクション変数に代入できるように、行のコレクション(ネストした表またはVARRAY)を生成するファンクションです。テーブル・ファンクションは、問合せのFROM句にあるデータベース表の名前のかわりに、または問合せのSELECTリストにある列名のかわりに使用できます。

    テーブル・ファンクションは、入力として行のコレクションを使用することができます。入力コレクション・パラメータには、コレクション型(VARRAYやPL/SQL表など)またはREF CURSORを使用できます。

    テーブル・ファンクションの実行はパラレル化でき、戻される行は中間のステージングなしで次のプロセスに直接送ることができます。テーブル・ファンクションから戻されるコレクションの行は、パイプライン化することもできます。つまり、テーブル・ファンクションの入力の処理がすべて完了してからバッチで戻されるのではなく、生成された時点で反復的に戻されます。

    テーブル・ファンクションのストリーム、パイプラインおよびパラレル実行によって、次のようにパフォーマンスを改善できます。

    パイプライン・テーブル・ファンクションの記述

    パイプライン・テーブル・ファンクションを宣言するには、PIPELINEDキーワードを指定します。パイプライン・ファンクションは、CREATE FUNCTIONを使用してスキーマ・レベルで定義することも、パッケージに定義することもできます。PIPELINEDキーワードは、ファンクションが行を反復的に戻すことを示します。パイプライン・テーブル・ファンクションの戻り型は、ネストした表やVARRAYなど、サポートされているコレクション型である必要があります。このコレクション型は、スキーマ・レベルまたはパッケージ内で宣言できます。ファンクション内では、コレクション型の個々の要素を戻します。コレクション型の要素は、NUMBERVARCHAR2など、サポートされているSQLデータ型である必要があります。パイプライン・ファンクションでは、PLS_INTEGERBOOLEANなどのPL/SQLデータ型はコレクション要素としてサポートされません。

    例11-22に、パイプライン・テーブル・ファンクションの結果をPL/SQLコレクション変数に代入し、そのファンクションをSELECT文で使用する方法を示します。

    例11-22    テーブル・ファンクションの結果の代入

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

    変換へのパイプライン・テーブル・ファンクションの使用

    パイプライン・テーブル・ファンクションには、通常のファンクションに使用できる引数であれば、すべて使用できます。引数としてREF CURSORを受け入れるテーブル・ファンクションは、変換ファンクションとして使用できます。つまり、REF CURSORを使用して入力行をフェッチし、その変換を実行し、結果をパイプラインで出力できます。

    例11-23では、f_transファンクションがemployees表の1行を2行に変換しています。

    例11-23    変換へのパイプライン・テーブル・ファンクションの使用

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

    この問合せでは、パイプライン・テーブル・ファンクションf_transCURSOR副問合せSELECT * FROM employees ...から行をフェッチし、変換を実行して、結果をパイプラインでユーザーに表として戻します。このファンクションでは、入力行ごとに出力行(コレクション要素)が2行ずつ生成されます。

    例11-23のように、CURSOR副問合せがSQLからREF CURSORファンクションの引数に渡される場合、ファンクションの実行中には参照先のカーソルがすでにオープンされています。

    パイプライン・テーブル・ファンクションからの結果の戻し

    PL/SQLでは、PIPE ROW文によってパイプライン・テーブル・ファンクションで行がパイプされ、処理が継続します。この文を使用すると、PL/SQLのテーブル・ファンクションで生成直後に行を戻すことができます。パフォーマンス上の理由から、PL/SQLランタイム・システムでは、行はコンシューマにバッチで与えられます。

    例11-23PIPE ROW(out_rec)文では、データをパイプラインでPL/SQLテーブル・ファンクションから戻しています。out_recはレコードで、その型は出力コレクションの要素の型と一致します。

    PIPE ROW文を使用できるのはパイプライン・テーブル・ファンクションの本体内のみで、他の場所で使用するとエラーが呼び出されます。行を戻さないパイプライン・テーブル・ファンクションの場合は、PIPE ROW文を省略できます。

    パイプライン・テーブル・ファンクションでは、値を戻さないRETURN文を含めることもできます。このRETURN文は、制御をコンシューマに移し、次回のフェッチでNO_DATA_FOUND例外が確実に呼び出されるようにします。

    テーブル・ファンクションとコール元のルーチンは、行の生成に伴って制御をやり取りするため、テーブル・ファンクションとPRAGMA AUTONOMOUS_TRANSACTIONの組合せに関する制限があります。テーブル・ファンクションが自律型トランザクションの一部である場合、コール元のサブプログラムでエラーが発生しないように、各PIPE ROW文の前にCOMMITまたはROLLBACKを実行する必要があります。

    Oracleには、オブジェクトやコレクション型など、他のSQL型の型記述、データ・インスタンスおよびデータ・インスタンス・セットを動的にカプセル化してアクセスできるように、3つの特別なデータ型が用意されています。また、この3つの型を使用すると、匿名コレクション型のように匿名の(つまり、名前を持たない)型を作成できます。この3つの型は、SYS.ANYTYPESYS.ANYDATAおよびSYS.ANYDATASETです。SYS.ANYDATA型は、テーブル・ファンクションからの戻り値として役立つ場合があります。

    参照:

    ANYTYPEANYDATAおよびANYDATASET型へのインタフェースと、この3つの型で使用するDBMS_TYPESパッケージについては、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 

    PL/SQLテーブル・ファンクション間のデータのパイプライン

    シリアル実行では、コルーチン実行に似たアプローチを使用して、結果があるPL/SQLテーブル・ファンクションから別のPL/SQLテーブル・ファンクションへとパイプラインされます。たとえば、次の文では、ファンクションgからファンクションfへと結果がパイプラインされます。

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

    パラレル実行の場合も同様ですが、各ファンクションは異なるプロセス(またはプロセス・セット)で実行されます。

    パイプライン・テーブル・ファンクションに対する複数コールの最適化

    パイプライン・テーブル・ファンクションを同じ問合せまたは別の問合せで複数回コールすると、基礎となる実装が複数回実行されます。デフォルトでは、行のバッファリングや再利用は行われません。次に例を示します。

    SELECT * FROM TABLE(f(...)) t1, TABLE(f(...)) t2
      WHERE t1.id = t2.id;
    SELECT * FROM TABLE(f());
    SELECT * FROM TABLE(f());

    ファンクションが、渡される値の各組合せに対して常に同じ結果の値を生成する場合、ファンクションDETERMINISTICを宣言すると、Oracleによって行が自動的にバッファリングされます。ファンクションが実際は非決定的である場合、予測できない結果になります。

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

    PL/SQLのCURSORとREF CURSORは、テーブル・ファンクションに対する問合せ用に定義できます。次に例を示します。

    OPEN c FOR SELECT * FROM TABLE(f(...));

    テーブル・ファンクションのカーソルと通常のカーソルでは、フェッチの意味は同じです。テーブル・ファンクションに基づくREF CURSORの代入に特別な意味はありません。

    ただし、SQLオプティマイザでは、PL/SQL文にまたがる最適化は行われません。次に例を示します。

    DECLARE
      r SYS_REFCURSOR;
    BEGIN
      OPEN r FOR SELECT *
        FROM TABLE(f(CURSOR(SELECT * FROM tab)));
      SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));
    END;
    /

    前述の例は、次の例と同様には実行されません。

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

    これは、2つのSQL文の実行に関連するオーバーヘッドを無視して、2つの文の間で結果をパイプラインできると想定した場合も同様です。

    カーソル変数によるデータの引渡し

    PL/SQLファンクションにREF CURSORパラメータで行セットを渡すことができます。たとえば、このファンクションが事前定義された弱い型指定のREF CURSORを持つSYS_REFCURSOR型の引数を受け入れるように宣言されているとします。

    FUNCTION f(p1 IN SYS_REFCURSOR) RETURN ... ;

    副問合せの結果をファンクションに直接渡すことができます。

    SELECT * FROM TABLE(f(CURSOR(SELECT empid FROM tab)));

    この例では、副問合せの結果をREF CURSORパラメータとして渡す必要があることを示すために、CURSORキーワードが必要です。

    事前定義の弱いREF CURSOR型のSYS_REFCURSORもサポートされます。SYS_REFCURSORを使用すると、パッケージ内でREF CURSOR型を使用前に作成する必要はありません。

    強いREF CURSOR型を使用する場合は、PL/SQLパッケージを作成し、その中で宣言する必要があります。また、強いREF CURSOR型をテーブル・ファンクションの引数として使用する場合は、REF CURSOR引数の実際の型が列の型と一致する必要があります。一致しない場合は、エラーが生成されます。テーブル・ファンクションの弱いREF CURSOR引数をパーティション化するには、PARTITION BY ANY句を使用する必要があります。弱いREF CURSOR引数には、レンジ・パーティション化もハッシュ・パーティション化も使用できません。

    例11-24に示すように、PL/SQLファンクションは複数のREF CURSOR入力変数を受け入れることができます。

    例11-24    複数のREF CURSOR入力変数の使用

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

    戻されたデータを反復するREF CURSORを作成すると、テーブル・ファンクションの戻り値を他のテーブル・ファンクションに渡すことができます。

    SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g(...)))));

    問合せに対してREF CURSORを明示的にオープンし、それをテーブル・ファンクションにパラメータとして渡すことができます。

    DECLARE
      r SYS_REFCURSOR;
      rec ...;
    BEGIN
      OPEN r FOR SELECT * FROM TABLE(f(...));
      -- Must return a single row result set.
      SELECT * INTO rec FROM TABLE(g(r));
    END;
    /

    この場合、テーブル・ファンクションは完了時にカーソルをクローズするため、プログラムではカーソルを明示的にクローズしないようにする必要があります。

    テーブル・ファンクションでは、入力REF CURSORを使用して集計結果を計算できます。例11-25では、一連の入力行を反復することで、加重平均を計算しています。

    例11-25    集計関数としてのパイプライン・テーブル・ファンクションの使用

    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;
    
    

    パイプライン・テーブル・ファンクション内でのDML操作の実行

    DML文を実行するには、AUTONOMOUS_TRANSACTIONプラグマを使用してパイプライン・テーブル・ファンクションを宣言します。これによって、ファンクションは、他のプロセスに共有されない新しいトランザクションで実行されます。

    CREATE FUNCTION f(p SYS_REFCURSOR)
      RETURN CollType PIPELINED IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN NULL; END;
    /

    パラレル実行中に、テーブル・ファンクションの各インスタンスが独立したトランザクションを作成します。

    パイプライン・テーブル・ファンクションへのDML操作の実行

    パイプライン・テーブル・ファンクションをUPDATEINSERTまたはDELETE文のターゲット表にすることはできません。たとえば、次の文ではエラーが呼び出されます。

    UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;
      INSERT INTO f(...) VALUES ('any', 'thing');

    ただし、テーブル・ファンクションのビューを作成し、INSTEAD OFトリガーを使用して更新できます。次に例を示します。

    CREATE VIEW BookTable AS SELECT x.Name, x.Author
      FROM TABLE(GetBooks('data.txt')) x;

    次のINSTEAD OFトリガーは、ユーザーがBookTableビューに行を挿入すると起動します。

    CREATE TRIGGER BookTable_insert
    INSTEAD OF INSERT ON BookTable
    REFERENCING NEW AS n
    FOR EACH ROW
    BEGIN
    ...
    END;
    /
    INSERT INTO BookTable VALUES (...);

    INSTEAD OFトリガーは、テーブル・ファンクションに作成されたビューでのすべてのDML操作について定義できます。

    パイプライン・テーブル・ファンクションの例外処理

    パイプライン・テーブル・ファンクションの例外処理は、通常のファンクションの場合と同じです。

    CやJavaなど、一部の言語には、ユーザー指定の例外処理のためのメカニズムが用意されています。テーブル・ファンクション内で呼び出された例外が処理される場合に、テーブル・ファンクションは例外ハンドラを実行して処理を継続します。例外ハンドラを終了すると、制御が外側の有効範囲に移ります。例外が解消されると、実行は通常どおり進行します。

    テーブル・ファンクションに未処理の例外があると、親トランザクションがロールバックされます。


  • 戻る 次へ
    Oracle
    Copyright © 2005 Oracle Corporation.

    All Rights Reserved.
    目次
    目次
    索引
    索引