| Oracle® Database開発ガイド 12c リリース1 (12.1) B71295-06 |
|
 前 |
 次 |
PL/SQLサブプログラムおよびパッケージは、Oracle Databaseアプリケーションのビルディング・ブロックです。『Oracle Database PL/SQL言語リファレンス』に記載されている理由により、アプリケーションはパッケージとして実装することをお薦めします。
内容は次のとおりです。
PL/SQLソース・プログラムの基本単位はブロックで、関連する宣言および文をグループ化します。ブロックには、オプションの宣言部、必須の実行可能部、オプションの例外処理部があります。ブロックには、匿名ブロックと名前付きブロックがあります。(PL/SQLブロックの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照)。
PL/SQLのサブプログラムは、繰り返し起動できる名前付きブロックです。サブプログラムにパラメータが含まれている場合は、起動のたびにパラメータ値を変えることができます。サブプログラムを使用する理由については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
サブプログラムは、プロシージャまたはファンクションのいずれかです。通常、プロシージャはアクションを実行するために使用し、ファンクションは計算を行って値を戻すために使用します。
また、サブプログラムは、ネストしたサブプログラム(PL/SQLブロック内に作成され、別のサブプログラムである場合があります)、パッケージ・サブプログラム(パッケージ仕様で宣言され、パッケージ本体で定義されます)、またはスタンドアロン・サブプログラム(スキーマ・レベルで作成されます)でもあります。パッケージ・サブプログラムおよびスタンドアロン・プログラムは、ストアド・サブプログラムです。ストアド・サブプログラムはデータベースでコンパイルおよび格納され、ここで多くのアプリケーションがストアド・サブプログラムを起動できます。
ストアド・サブプログラムは、AUTHIDおよびACCESSIBLE BY句の影響を受けます。AUTHID句は、実行時にサブプログラムによって発行されるSQL文の名前解決および権限チェックに影響を与えます。ACCESSIBLE BY句は、サブプログラムにアクセスできるPL/SQLユニットのホワイト・リストを指定します。PL/SQLユニットの詳細は、11.3項を参照してください。
Oracle Databaseのインスタンス上で実行するPL/SQLサブプログラムは、第三世代言語(3GL)で作成された外部サブプログラムを起動できます。3GLサブプログラムは、データベースのアドレス空間とは別のアドレス空間で実行されます。外部サブプログラムの詳細は、第18章「複数のプログラミング言語を使用したアプリケーションの開発」を参照してください。
PL/SQLでは、ネストしたサブプログラム、パッケージ・サブプログラムおよび型のメソッドをオーバーロードできます。オーバーロードされたサブプログラムは名前が同じですが、仮パラメータの名前、数、順序またはデータ型のファミリが異なります。オーバーロードされたサブプログラムの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
トリガーは、ストアド・プロシージャのように、データベースに格納して繰り返し起動できる名前付きPL/SQLユニットです。ストアド・プロシージャとは異なり、トリガーは、有効にしたり無効にすることができますが、明示的に起動することはできません。トリガーは、有効の場合、データベースによって自動的に起動されます(つまり、トリガーは、トリガーを起動するイベントが発生すると、常に起動されます)。トリガーは、無効の場合、起動されません。トリガーの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
|
参照: PL/SQLサブプログラムの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
PL/SQLパッケージとは、論理的に関連するPL/SQLの型、変数、定数、サブプログラム、カーソルおよび例外をグループにまとめたスキーマ・オブジェクトのことです。パッケージをコンパイルしてデータベースに格納し、その内容を複数のアプリケーションで共有できます。パッケージを使用する理由については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
パッケージには必ず仕様部があり、パッケージの外から参照できるパブリック項目はここで宣言します。パブリック項目を起動または使用できるのは、パッケージに対するEXECUTE権限を持つ外部ユーザーまたはEXECUTE ANY PROCEDURE権限を持つ外部ユーザーです。
パブリック項目にカーソルまたはサブプログラムが含まれる場合、パッケージには本体も必要です。本体には、パブリック・カーソルの問合せとパブリック・サブプログラムのコードを定義する必要があります。本体では、パッケージの外からは参照できなくてもパッケージの内部動作に必要なプライベート項目を宣言および定義することもできます。本体の最後には初期化部(ここには、変数を初期化する文と、その他の1回のみの設定手順を実行する文を入れます)、および例外処理部を配置することができます。本体は、仕様部またはパブリック項目への参照を変更せずに変更できるため、パッケージ本体はブラック・ボックスと考えることができます。
パッケージ仕様部またはパッケージ本体のいずれでも、パッケージ・サブプログラムを外部のJavaサブプログラムまたはCサブプログラムにマップできますが、その際にはコール仕様を使用して、外部サブプログラム名、パラメータ型および戻り型を対応するSQLにマップします。
パッケージ仕様部のAUTHID句は、パッケージのサブプログラムとカーソルをその定義者(デフォルト)と実行者のどちらの権限で実行するか、およびスキーマ・オブジェクトへの未修飾の参照が定義者と実行者のどちらのスキーマで解決されるかを決定します。
パッケージ仕様のACCESSIBLE BY句を使用すると、パッケージにアクセスできるPL/SQLユニットのホワイト・リストを指定できます。この句は次のような状況で使用します。
PL/SQLアプリケーションを複数のパッケージとして実装します。このうち、1つのパッケージがアプリケーション・プログラミング・インタフェース(API)を提供し、ヘルパー・パッケージが処理を実行します。このとき、クライアントがAPIにアクセスするが、ヘルパー・パッケージにはアクセスしないようにします。したがって、ACCESSIBLE BY句をAPIパッケージの仕様から省略し、ヘルパー・パッケージの仕様ごとに組み込み、ここで、APIパッケージのみがヘルパー・パッケージにアクセスできるように指定します。
同じスキーマ内のすべてではなく一部のPL/SQLユニットにサービスを提供するユーティリティ・パッケージを作成します。パッケージの使用対象を目的のユニットに制限するには、パッケージ仕様内のACCESSIBLE BY句にこれらをリストします。
PL/SQLユニットの詳細は、11.3項を参照してください。
|
注意: 独自のパッケージを作成する前に、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照し、必要な機能がパッケージに用意されているかどうかを確認してください。 |
|
参照: PL/SQLパッケージの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
無名PL/SQLブロック
FUNCTION
LIBRARY
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY
PL/SQLユニットは、PL/SQLコンパイル・パラメータ(データベース初期化パラメータのカテゴリ)によって影響を受けます。異なるPL/SQLユニット(パッケージの仕様部や本体など)に、異なるコンパイル・パラメータ設定を含めることができます。PL/SQLユニットおよびコンパイル・パラメータの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
PL/SQLユニットのAUTHIDプロパティは、実行時にユニットによって発行されるSQL文の名前解決および権限チェックに影響します。詳細は、「Oracle Database PL/SQL言語Lリファレンス」を参照してください。
PL/SQL最適化レベルは、PL/SQLオプティマイザがコードを再調整してパフォーマンス向上を図ることのできる程度を決定します。このレベルは、コンパイル・パラメータPLSQL_OPTIMIZE_LEVEL (デフォルト値は2)を使用して設定します。
セッションのPL/SQL最適化レベルを変更するには、SQLコマンドALTER SESSIONを使用します。セッションのレベルを変更しても、影響を受けるのは、その後で作成されたPL/SQLユニットのみです。既存のPL/SQLユニットのレベルを変更するには、ALTERコマンドをCOMPILE句とともに使用します。
1つ以上のPL/SQLユニットのPLSQL_OPTIMIZE_LEVELの現在の値を表示するには、静的データ・ディクショナリ・ビューALL_PLSQL_OBJECT_SETTINGSを使用します。
例11-1では、2つのプロシージャを作成し、これらの最適化レベルを表示し、セッションの最適化レベルを変更し、3番目のプロシージャを作成し、3つのプロシージャすべての最適化レベルを表示します。3番目のプロシージャのみが新しい最適化レベルを持ちます。このため、この例では、1つのプロシージャの最適化レベルのみを変更し、3つのプロシージャすべての最適化レベルを再表示します。
例11-1 PLSQL_OPTIMIZE_LEVELの変更
2つのプロシージャを作成します。
CREATE OR REPLACE PROCEDURE p1 AUTHID DEFINER AS BEGIN NULL; END; / CREATE OR REPLACE PROCEDURE p2 AUTHID DEFINER AS BEGIN NULL; END; /
2つのプロシージャの最適化レベルを表示します。
SELECT NAME, PLSQL_OPTIMIZE_LEVEL FROM USER_PLSQL_OBJECT_SETTINGS WHERE NAME LIKE 'P%' AND TYPE='PROCEDURE' ORDER BY NAME;
結果:
NAME PLSQL_OPTIMIZE_LEVEL ------------------------------ -------------------- P1 2 P2 2 2 rows selected.
セッションの最適化レベルを変更し、3番目のプロシージャを作成します。
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=1;
CREATE OR REPLACE PROCEDURE p3 AUTHID DEFINER AS
BEGIN
NULL;
END;
/
3つのプロシージャの最適化レベルを表示します。
SELECT NAME, PLSQL_OPTIMIZE_LEVEL FROM USER_PLSQL_OBJECT_SETTINGS WHERE NAME LIKE 'P%' AND TYPE='PROCEDURE' ORDER BY NAME;
結果:
NAME PLSQL_OPTIMIZE_LEVEL ------------------------------ -------------------- P1 2 P2 2 P3 1 3 rows selected.
プロシージャp1の最適化レベルを3に変更します。
ALTER PROCEDURE p1 COMPILE PLSQL_OPTIMIZE_LEVEL=3;
3つのプロシージャの最適化レベルを表示します。
SELECT NAME, PLSQL_OPTIMIZE_LEVEL FROM USER_PLSQL_OBJECT_SETTINGS WHERE NAME LIKE 'P%' AND TYPE='PROCEDURE' ORDER BY NAME;
結果:
NAME PLSQL_OPTIMIZE_LEVEL ------------------------------ -------------------- P1 3 P2 2 P3 1 3 rows selected.
|
参照:
|
内容は次のとおりです。
独自のスキーマでスタンドアロン・サブプログラムまたはパッケージを作成するには、CREATE PROCEDUREシステム権限が必要です。別のスキーマでスタンドアロン・サブプログラムまたはパッケージを作成するには、CREATE ANY PROCEDUREシステム権限が必要です。
作成するサブプログラムまたはパッケージがスキーマ・オブジェクトを参照する場合、これらのオブジェクトに必須のオブジェクト権限が必要です。これらの権限は、ロールを介してではなく、明示的に付与される必要があります。
サブプログラムまたはパッケージの所有者の権限が変更された場合、実行前にそのサブプログラムまたはパッケージを再認証する必要があります。参照オブジェクトに必要なオブジェクト権限が、そのサブプログラムまたはパッケージの所有者から取り消されている場合、そのサブプログラムは実行できません。
サブプログラムに対してEXECUTE権限を付与すると、ユーザーはサブプログラム所有者のセキュリティ・ドメインでサブプログラムを実行できるため、サブプログラムが参照するオブジェクトに対する権限をユーザーに付与する必要がなくなります。EXECUTE権限を使用すると、データベース・アプリケーションおよびそのユーザーに対してさらに統制のとれた効率的なセキュリティ計画が可能になります。また、サブプログラムおよびパッケージをデータ・ディクショナリ(SYSTEM表領域内)に格納できるようになります。ここでは、サブプログラムおよびパッケージを作成するユーザーが使用可能な領域の量が割当てによって制御されません。
このトピックでは、SQLデータ定義言語(DDL)文を使用してスタンドアロン・サブプログラムおよびスタンドアロン・パッケージを作成する方法について説明します。
スタンドアロン・サブプログラムおよびスタンドアロン・パッケージを作成するためのDDL文は、次のとおりです。
CREATE FUNCTION (詳細は、『Oracle Database PL/SQL言語リファレンス』を参照)
CREATE PROCEDURE (詳細は、『Oracle Database PL/SQL言語リファレンス』を参照)
CREATE PACKAGE (詳細は、『Oracle Database PL/SQL言語リファレンス』を参照)
CREATE PACKAGE BODY (詳細は、『Oracle Database PL/SQL言語リファレンス』を参照)
パッケージの名前およびパブリック・オブジェクトの名前は、パッケージ・スキーマ内で一意である必要があります。パッケージ仕様部およびその本体は、同じ名前である必要があります。パッケージ構成の名前は、オーバーロードされたサブプログラムを除いて、パッケージの範囲内で一意である必要があります。
前述のCREATE文には、それぞれオプションのOR REPLACE句を指定できます。OR REPLACE句は、既存のPL/SQLユニットを再作成する場合(つまり、既存のPL/SQLユニットの宣言または定義の削除または再作成およびそれらに事前に付与されているオブジェクト権限の再付与を行わずに、それらの宣言または定義を変更する場合)に指定します。PL/SQLユニットを再定義すると、そのPL/SQLユニットはデータベースによって再コンパイルされます。
|
注意: CREATE OR REPLACE文は、既存のPL/SQLユニットを置き換える前に警告を発行しません。 |
任意のテスト・エディタを使用して、任意の数のサブプログラムおよびパッケージを作成するためのDDL文が含まれるテキスト・ファイルを作成します。
DDL文を実行するには、SQL*Plusなどの対話形式のツールを使用します。SQL*PlusコマンドSTARTまたは@でスクリプトを実行します。たとえば、次のSQL*Plusコマンドで、スクリプトmy_app.sqlを選択します。
@my_app
(SQL*Plusでのスクリプトの実行の詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください。)
また、SQL Developerを使用してDDL文を作成および実行することもできます。Oracle SQL Developerの使用方法については、Oracle SQL Developerユーザーズ・ガイドを参照してください。
サブプログラム、トリガー、パッケージなどのPL/SQLストアド・データベース・オブジェクトのサイズは、共有プール内のDescriptive Intermediate Attributed Notation for Ada (DIANA)コードのサイズ(バイト単位)に制限されています。フラット化されたDIANA/codeのサイズの制限は、LinuxやUNIXでは64KBですが、デスクトップ・プラットフォームでは32KBに制限されている場合があります。
ユーザーがアクセスできるもので最も密接に関連する数値は、静的データ・ディクショナリ・ビュー*_OBJECT_SIZEの列、PARSED_SIZEです。列PARSED_SIZEには、SYS.IDL_xxx$表に格納されたDIANAのサイズがバイト単位で示されています。これは共有プールでのサイズではありません。(コンパイル中に使用される)PL/SQLコードのDIANA部分のサイズは、システム表内より共有プール内で非常に大きくなります。
|
参照:
|
このトピックでは、PL/SQLデータ型を紹介し、詳細な情報の参照先として他の章またはドキュメントを示します。
データベース・アプリケーション内のPL/SQL変数ごとに正確かつ最も固有のPL/SQLデータ型を使用してください。この理由については、7.1項「正確かつ最も固有のデータ型の使用」を参照してください。
内容は次のとおりです。
スカラー・データ型には、内部コンポーネントを持たない値が格納されます。
スカラー・データ型にはサブタイプを持たせることができます。サブタイプとは、別のデータ型のサブセットとなるデータ型のことで、その別のデータ型は、そのサブタイプのベース型となります。サブタイプには、そのベース型で有効な演算と同じ演算が含まれています。データ型とそのサブタイプでデータ型ファミリが構成されます。
PL/SQLには、多くの型やサブタイプがパッケージSTANDARDに事前定義されています。また、PL/SQLを使用して、独自のサブタイプを定義することもできます。
内容は次のとおりです。
|
参照:
|
PL/SQLデータ型にはSQLデータ型が含まれます。SQLデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください(記載されているデータ型およびサブタイプ、データ型の比較のルール、データ変換、リテラル、および書式モデルに関する情報はすべて、『Oracle Database PL/SQL言語リファレンス』に記載されているものを除き、SQLとPL/SQLの両方に適用されます)。
データベース・アプリケーションでSQLデータ型を使用する方法の詳細は、第7章「データベース・アプリケーションにおけるSQLデータ型の使用」を参照してください。
BOOLEANデータ型には、論理値(ブール値のTRUEとFALSE、およびNULL値)が格納されます。NULLは、不明な値を表します。BOOLEANデータ型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
PL/SQLのデータ型であるPLS_INTEGERとBINARY_INTEGERは同じです。わかりやすくするために、このマニュアルでは、PLS_INTEGERとBINARY_INTEGERの両方を表すためにPLS_INTEGERを使用します。
PLS_INTEGERデータ型は、32ビットで表される-2147483648から2147483647の範囲の符号付き整数を格納します。
PLS_INTEGERデータ型には、NUMBERデータ型およびNUMBERサブタイプと比べて、次のようなメリットがあります。
PLS_INTEGER値の方が、必要な記憶域が少なくなります。
PLS_INTEGER演算はハードウェア算術計算を使用するため、ライブラリ算術計算を使用するNUMBER演算より処理速度が速くなります。
効率のために、PLS_INTEGERの範囲内でのすべての計算にPLS_INTEGER値を使用してください。
PLS_INTEGERデータ型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
カーソル変数は、明示カーソルと似ていますが、次の点が異なります。
1つの問合せに限定されません。
問合せのカーソル変数をオープンし、結果セットを処理し、そのカーソル変数を別の問合せに使用できます。
値を代入できます。
式で使用できます。
サブプログラム・パラメータとして使用できます。
カーソル変数は、サブプログラム間で問合せ結果セットを渡すために使用できます。
ホスト変数として使用できます。
カーソル変数は、PL/SQLのストアド・サブプログラムとそれらのクライアントとの間で問合せ結果セットを渡すために使用できます。
パラメータを受け入れることはできません。
カーソル変数にパラメータを渡すことはできませんが、問合せ全体を渡すことができます。
カーソル変数がこのような柔軟性を持つ理由は、それがポインタであるため(その値が項目自体ではなく項目のアドレスを示すため)です。
REF CURSORデータ型とカーソル変数の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
コンポジット・データ型には、内部コンポーネントがあります。PL/SQLのコンポジット・データ型は、コレクションおよびレコードです。
コレクションの内部コンポーネントは、常に同じデータ型であり、要素と呼ばれます。コレクション変数の各要素には、一意の索引によってアクセスできます。PL/SQLには、連想配列、VARRAY (可変サイズの配列)およびネストした表の3つのコレクション型があります。
レコードの内部コンポーネントは、データ型が異なる場合があり、フィールドと呼ばれます。レコード変数の各フィールドには、名前によってアクセスできます。
レコードのコレクション、およびコレクションを含むレコードを作成できます。
PL/SQLコンポジット・データ型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
PL/SQLでは、従来のデータベース・プログラミングと同様に、カーソルを使用して問合せ結果セットを処理できます。カーソルとは、特定のSELECT文またはDML文の処理に関する情報を格納しておく、SQLのプライベート領域を指すポインタです。
PL/SQLで構築され管理されるカーソルは、暗黙カーソルです。ユーザーが構築および管理するセッション・カーソルは、明示カーソルです。暗黙カーソルに勝る明示カーソルの唯一のメリットは、明示カーソルの場合、フェッチされる行の数を制限できる点です(詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください)。カーソルの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。暗黙および明示カーソルを使用した問合せセット結果の処理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
カーソル変数とは、カーソルを指すポインタです。つまり、その値は、カーソル自体ではなく、カーソルのアドレスです。したがって、カーソル変数は明示カーソルより柔軟です。ただし、カーソル変数には、明示カーソルにはないデメリットもあります。
内容は次のとおりです。
|
参照:
|
カーソル変数は、明示カーソルと似ていますが、次の点が異なります。
1つの問合せに限定されません。
問合せのカーソル変数をオープンし、結果セットを処理し、そのカーソル変数を別の問合せに使用できます。
値を代入できます。
式で使用できます。
サブプログラム・パラメータとして使用できます。
カーソル変数は、サブプログラム間で問合せ結果セットを渡すために使用できます。
ホスト変数として使用できます。
カーソル変数は、PL/SQLのストアド・サブプログラムとそれらのクライアントとの間で問合せ結果セットを渡すために使用できます。
パラメータを受け入れることはできません。
カーソル変数にパラメータを渡すことはできませんが、問合せ全体を渡すことができます。問合せには変数を組み込むことができます。
前述の特性のため、カーソル変数には次のメリットがあります。
カプセル化
カーソル変数をオープンするストアド・サブプログラムに問合せを集中化できます。
メンテナンスの容易さ
カーソルの変更が必要な場合は、ストアド・サブプログラムの変更のみで済みます。ストアド・サブプログラムを起動するすべてのアプリケーションで変更を行う必要はありません。
セキュリティの利便性
アプリケーションは、アプリケーション・ユーザーのユーザー名を持つサーバーに接続します。アプリケーション・ユーザーには、カーソルをオープンするストアド・サブプログラムに対するEXECUTE権限が必要ですが、問い合せた表に対するREAD権限は必要ありません。
カーソル変数を使用する必要がない場合、パフォーマンスおよびプログラミングの容易性を向上するために、暗黙または明示カーソルを使用してください。
内容は次のとおりです。
|
注意: これらのトピックの例には、TKPROFレポートが含まれます。TKPROFレポートの作成の手順については、Oracle Database SQLチューニング・ガイドを参照してください。 |
明示カーソルを閉じた場合、カーソルはパースペクティブから閉じられますが(つまり、開いているカーソルが必要な場合にそのカーソルを使用できない)、PL/SQLは明示カーソルをオープン状態でキャッシュします。カーソルに関連付けられた文を再実行すると、PL/SQLはキャッシュされたカーソルを使用することにより、解析を回避します。
解析の回避により、CPUの使用率が大幅に削減され、明示カーソルのキャッシュが明白になります。この場合、プログラミングは影響を受けません。PL/SQLは使用可能なオープン・カーソルの供給を削減しません。プログラムが他のカーソルを開く必要があるが、開くとOPEN_CURSORSのinit.ora設定を超えるような場合、PL/SQLはキャッシュされたカーソルを閉じます。
PL/SQLはオープン状態のカーソル変数はキャッシュできません。したがって、カーソル変数には解析のペナルティがあります。
例11-2では、プロシージャは明示カーソルをオープンし、ここからフェッチし、これをクローズし、カーソル変数でも同じ処理を行います。匿名ブロックがプロシージャを10回コールします。TKPROFレポートには、両方の問合せが10回実行されたが、明示カーソルに関連付けられた問合せが解析されたのは1回のみで、カーソル変数に関連付けられた問合せが解析されたのが10回であることが示されます。
例11-2 カーソル変数の解析のペナルティ
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS CURSOR e_c IS SELECT * FROM DUAL d1; -- explicit cursor c_v SYS_REFCURSOR; -- cursor variable rec DUAL%ROWTYPE; BEGIN OPEN e_c; -- explicit cursor FETCH e_c INTO rec; CLOSE e_c; OPEN c_v FOR SELECT * FROM DUAL d2; -- cursor variable FETCH c_v INTO rec; CLOSE c_v; END; / BEGIN FOR i IN 1..10 LOOP -- execute p 10 times p; END LOOP;
TKPROFレポートは、次のようになります。
SELECT * FROM DUAL D1; call count ------- ------ Parse 1 Execute 10 Fetch 10 ------- ------ total 21 **************** SELECT * FROM DUAL D2; call count ------- ------ Parse 10 Execute 10 Fetch 10 ------- ------ total 30
例11-3では、7,000行以上の表を作成し、これらすべての行を2回フェッチします。この場合、初回は暗黙カーソル(配列のフェッチ)を使用し、2回目はカーソル変数(個別行のフェッチ)を使用します。暗黙カーソルのコードは、カーソル変数のコードより単純であり、TKPROFレポートには、暗黙カーソルのコードの方がパフォーマンスもよいことが示されます。
カーソル変数を使用して配列をフェッチすることもできますが、より多くのコードが必要になります。特に、次の処理を行うためのコードが必要になります。
配列のフェッチ先のコレクションのタイプの定義
コレクションに対する明示的な一括収集
フェッチしたデータを処理するためのコレクションのループ
明示的にオープンしたカーソル変数のクローズ
例11-3 カーソル変数の配列フェッチのペナルティ
問い合せる表を作成し、その行数を表示します。
CREATE TABLE t AS SELECT * FROM ALL_OBJECTS; SELECT COUNT(*) FROM t;
結果は次のようになります。
COUNT(*)
----------
70788
暗黙カーソルとカーソル変数で同等の操作を実行します。
DECLARE c_v SYS_REFCURSOR; rec t%ROWTYPE; BEGIN FOR x IN (SELECT * FROM t exp_cur) LOOP -- implicit cursor NULL; END LOOP; OPEN c_v FOR SELECT * FROM t cur_var; -- cursor variable LOOP FETCH c_v INTO rec; EXIT WHEN c_v%NOTFOUND; END LOOP; CLOSE c_v; END; /
TKPROFレポートは、次のようになります。
SELECT * FROM T EXP_CUR call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 722 0.23 0.23 0 1748 0 72198 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 724 0.23 0.23 0 1748 0 72198 ******************************************************************************** SELECT * FROM T CUR_VAR call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 72199 0.40 0.42 0 72203 0 72198 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 72201 0.40 0.42 0 72203 0 72198
データ・ウェアハウス環境では、大量のデータを変換するためにPL/SQLファンクションを使用します。データは、異なるファンクションによる一連の変換を経由して渡す場合があります。PL/SQL表ファンクションを使用すると、このような変換を、かなりのメモリー・オーバーヘッドを必要としたり、各変換の間でデータを表に格納する必要なく実行できます。これらのファンクションは、複数の行を受け入れて戻すことが可能で、一度ではなく準備できた順に行を戻すことができるだけでなく、パラレル化も可能です。
|
参照: パイプライン・テーブル・ファンクションを使用して複数の変換を実行する方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
PL/SQLファンクション結果キャッシュを使用すると、領域と時間を大幅に節約できます。結果キャッシュPL/SQLファンクションを、異なるパラメータ値を使用して起動するたびに、それらのパラメータおよびその結果がキャッシュに格納されます。それ以降、同じファンクションが同じパラメータ値で起動されると、結果は再計算されるのではなく、キャッシュから取り出されます。キャッシュは共有グローバル領域(SGA)に格納されるため、アプリケーションが実行されるすべてのセッションで使用可能です。
キャッシュ結果を計算する際に使用したデータベース・オブジェクトが更新されると、そのキャッシュ結果は無効になり、再計算が必要になります。
結果キャッシュの対象として最適のファンクションは、起動される頻度が高く、かつほとんど変更されない情報に依存するものです。
PL/SQLファンクション結果キャッシュの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
Oracle Databaseは2つのエンジンを使用して、PL/SQLユニットを実行します。PL/SQLエンジンは手続き型の文を実行し、SQLエンジンはSQL文を実行します。すべてのSQL文により、2つのエンジン間でコンテキストの切替えが行われます。PL/SQLユニットごとに行われるコンテキストの切替えの回数を最小限に抑えることにより、データベース・アプリケーションのパフォーマンスを大幅に向上させることができます。
バインド変数としてコレクション要素を使用するループ内でSQL文が実行される場合、必要とする多数のコンテキストのスイッチングによってパフォーマンスが低下することがあります。コレクションには次のものが含まれます。
連想配列
可変サイズの配列
ネストした表
ホスト配列
バインドとは、SQL文内のPL/SQL変数に対して値を代入することです。バルク・バインドとは、コレクション全体を一度にバインドすることです。バルク・バインドは1つの操作でコレクション全体を2つのエンジン間で受け渡すことができます。
通常、バルク・バインドによって、4つ以上のデータベース行に影響するSQL文のパフォーマンスが改善されます。SQL文によって影響される行数が多いほど、バルク・バインドによるパフォーマンスの向上率は高くなります。コレクションを参照するDMLおよびSELECT INTO文や、コレクションを参照してDMLを戻すFORループのパフォーマンスを向上させるためにバルク・バインドを使用することを検討してください。
|
注意: バルク・バインドを使用する場合、パラレルDML文は無効になります。パラレルDML文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
内容は次のとおりです。
|
参照: バルク・バインド操作中に発生する例外の処理方法を含むバルク・バインドの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください |
FORALLキーワードを使用するバルク・バインドによって、コレクション要素を参照するINSERT、UPDATEまたはDELETE文のパフォーマンスが向上します。
例11-4のPL/SQLブロックは、管理職のID番号が7902、7698または7839の従業員について昇給を行うものですが、バルク・バインドを使用する場合と使用しない場合があります。PL/SQLがバルク・バインドを使用しないで、更新する各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例11-4 コレクションを参照するDML文
DECLARE TYPE numlist IS VARRAY (100) OF NUMBER; id NUMLIST := NUMLIST(7902, 7698, 7839); BEGIN -- Efficient method, using bulk bind: FORALL i IN id.FIRST..id.LAST UPDATE EMPLOYEES SET SALARY = 1.1 * SALARY WHERE MANAGER_ID = id(i); -- Slower method: FOR i IN id.FIRST..id.LAST LOOP UPDATE EMPLOYEES SET SALARY = 1.1 * SALARY WHERE MANAGER_ID = id(i); END LOOP; END; /
|
参照: FORALL文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください |
BULK COLLECT句によって、コレクションを参照する問合せのパフォーマンスを改善できます。スカラー値表または%TYPE値表にBULK COLLECTを使用できます。
例11-5のPL/SQLブロックは、複数の値の問合せを行ってその値をPL/SQL表に格納するものですが、バルク・バインドを使用する場合と使用しない場合があります。PL/SQLがバルク・バインドを使用しないで、選択された各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例11-5 コレクションを参照するSELECT文
DECLARE
TYPE var_tab IS TABLE OF VARCHAR2(20)
INDEX BY PLS_INTEGER;
empno VAR_TAB;
ename VAR_TAB;
counter NUMBER;
CURSOR c IS
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE MANAGER_ID = 7698;
BEGIN
-- Efficient method, using bulk bind:
SELECT EMPLOYEE_ID, LAST_NAME BULK COLLECT
INTO empno, ename
FROM EMPLOYEES
WHERE MANAGER_ID = 7698;
-- Slower method:
counter := 1;
FOR rec IN c LOOP
empno(counter) := rec.EMPLOYEE_ID;
ename(counter) := rec.LAST_NAME;
counter := counter + 1;
END LOOP;
END;
/
|
参照: BULK COLLECT句の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください |
BULK COLLECTキーワードとともにFORALLキーワードを使用すると、コレクションを参照しDMLを戻すFORループのパフォーマンスを改善できます。
例11-6のPL/SQLブロックは、従業員コレクションのボーナスを計算し、EMPLOYEES表を更新します。さらに、ボーナスをbonus_list_inst列に戻します。この操作をバルク・バインドを使用して実行し、また使用せずに実行します。PL/SQLがバルク・バインドを使用しないで、更新する各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例11-6 コレクションを参照しDMLを返すFORループ
DECLARE TYPE emp_list IS VARRAY(100) OF EMPLOYEES.EMPLOYEE_ID%TYPE; empids emp_list := emp_list(182, 187, 193, 200, 204, 206); TYPE bonus_list IS TABLE OF EMPLOYEES.SALARY%TYPE; bonus_list_inst bonus_list; BEGIN -- Efficient method, using bulk bind: FORALL i IN empids.FIRST..empids.LAST UPDATE EMPLOYEES SET SALARY = 0.1 * SALARY WHERE EMPLOYEE_ID = empids(i) RETURNING SALARY BULK COLLECT INTO bonus_list_inst; -- Slower method: FOR i IN empids.FIRST..empids.LAST LOOP UPDATE EMPLOYEES SET SALARY = 0.1 * SALARY WHERE EMPLOYEE_ID = empids(i) RETURNING SALARY INTO bonus_list_inst(i); END LOOP; END; /
|
参照: BULK COLLECT句とともにRETURNING INTOを使用する方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください |
動的SQLは、実行時にSQL文を生成して実行するためのプログラミング方法です。この方法は、非定型の問合せシステムのような柔軟性がある汎用目的のプログラムを記述する場合、データベース定義言語(DDL)文を実行する必要があるプログラムを記述する場合、またはコンパイル時にSQL文のテキスト全体またはそのSQL文の入力変数および出力変数の数またはデータ型が不明な場合に有効です。
動的SQLが必要ない場合は、静的SQLを使用します。静的SQLには、次のようなメリットがあります。
コンパイルが正常に完了すると、静的SQL文が有効なデータベース・オブジェクトを参照していること、およびそれらのオブジェクトへのアクセスに必要な権限が存在していることが保証されます。
コンパイルが正常に完了すると、スキーマ・オブジェクトの依存性が作成されます。
スキーマ・オブジェクトの依存性の詳細は、第23章「スキーマ・オブジェクトの依存性の理解」を参照してください。
動的SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
静的SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
ストアド・スタンドアロン・サブプログラムまたはパッケージの名前を変更するには、これを削除してから(11.6項の手順どおり)、新しい名前で作成する必要があります。次に例を示します。
CREATE PROCEDURE p IS BEGIN NULL; END; / DROP PROCEDURE p / CREATE PROCEDURE p1 IS BEGIN NULL; END; /
ストアド・スタンドアロン・サブプログラムまたはパッケージを名前を変更せずに変更するには、CREATE文にOR REPLACEを組み込むことにより、同じ名前でこれを新しいバージョンに置き換えます。次に例を示します。
CREATE OR REPLACE PROCEDURE p1 IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello, world!'); END; /
|
注意: ALTER文(ALTER FUNCTION、ALTER PROCEDUREおよびALTER PACKAGEなど)の場合、既存のPL/SQLユニットの宣言または定義は変更されず、ユニットの再コンパイルのみが行われます。ALTER文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
ストアド・スタンドアロン・サブプログラムを削除するには、次の文を使用します。
DROP FUNCTION (詳細は、『Oracle Database PL/SQL言語リファレンス』を参照)
DROP PROCEDURE (詳細は、『Oracle Database PL/SQL言語リファレンス』を参照)
パッケージ(仕様部および本体)または本体のみを削除するには、『Oracle Database PL/SQL言語リファレンス』で説明されている、文DROP PACKAGEを使用します。
通常、PL/SQLユニットをコンパイルして、システム固有のコード(プロセッサに依存するシステム・コード)にすると、PL/SQLユニット(独自のユニットまたはOracleが提供するユニット)をスピードアップできます。システム固有のコードは、SYSTEM表領域に格納されます。
ネイティブ・コードにコンパイルされたPL/SQLユニットは、共有サーバー構成(以前のマルチスレッド・サーバー)やOracle Real Application Clusters (Oracle RAC)などのすべてのサーバー環境で動作します。
PL/SQLユニットをネイティブ・コードでコンパイルするかどうかは、開発サイクルの段階およびPL/SQLユニットの内容によって決まります。
システム固有の実行のためのPL/SQLユニットのコンパイルの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
|
注意: システム固有の実行のためにJavaパッケージおよびクラスをコンパイルするには、ncompツールを使用してください。詳細は、『Oracle Database Java開発者ガイド』を参照してください。 |
ストアドPL/SQLサブプログラムは、次のように様々な環境から起動できます。次に例を示します。
Oracle Databaseのツール製品を使用した対話形式による起動
別のサブプログラム本体からの起動
トリガー本体からの起動
アプリケーション(SQL*Formsやプリコンパイラなど)の内部からの起動
ストアドPL/SQLファンクション(プロシージャではない)は、SQL文から起動することもできます。詳細は、11.9項を参照してください。
別のユーザーが所有するサブプログラムを起動する場合は、次のようになります。
起動の際に所有者の名前を指定する必要があります。次に例を示します。
EXECUTE jdoe.Fire_emp (1043); EXECUTE jdoe.Hire_fire.Fire_emp (1043);
サブプログラムのAUTHIDプロパティは、実行時にサブプログラムによって発行されるSQL文の名前解決および権限チェックに影響します。詳細は、「Oracle Database PL/SQL言語Lリファレンス」を参照してください。
内容は次のとおりです。
|
参照:
|
次のようなサブプログラムを起動する場合、権限は必要ありません。
所有しているスタンドアロン・サブプログラム
所有しているパッケージ内のサブプログラム
パブリック・スタンドアロン・サブプログラム
パブリック・パッケージ内のサブプログラム
別のユーザーが所有するストアド・サブプログラムを起動するには、スタンドアロン・サブプログラムまたはパッケージ・サブプログラムを含むパッケージのEXECUTE権限、またはEXECUTE ANY PROCEDUREシステム権限が必要です。サブプログラムがリモートにある場合は、EXECUTE権限またはEXECUTE ANY PROCEDUREシステム権限が、ロールを介してではなく直接付与されている必要があります。
|
参照: システム権限およびオブジェクト権限の詳細は、『Oracle Database SQL言語リファレンス』を参照してください |
サブプログラムは、SQL*Plusなど、Oracle Databaseのツール製品から対話形式で起動できます。例11-7では、SQL*Plusを使用してプロシージャを作成し、2つの方法で起動します。
例11-7 SQL*Plusを使用した対話形式によるサブプログラムの起動
CREATE OR REPLACE PROCEDURE salary_raise ( employee EMPLOYEES.EMPLOYEE_ID%TYPE, increase EMPLOYEES.SALARY%TYPE ) IS BEGIN UPDATE EMPLOYEES SET SALARY = SALARY + increase WHERE EMPLOYEE_ID = employee; END; /
無名ブロックの内部からプロシージャを起動します。
BEGIN
salary_raise(205, 200);
END;
/
結果:
PL/SQL procedure successfully completed.
EXECUTE文でプロシージャを起動します。
EXECUTE salary_raise(205, 200);
結果:
PL/SQL procedure successfully completed.
一部の対話形式ツールでは、セッション中に使用できるセッション変数を作成することができます。例11-8では、SQL*Plusを使用してセッション変数の作成、使用および出力を行います。
例11-8 SQL*Plusを使用したセッション変数の作成と使用
-- Create function for later use: CREATE OR REPLACE FUNCTION get_job_id ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) RETURN EMPLOYEES.JOB_ID%TYPE IS job_id EMPLOYEES.JOB_ID%TYPE; BEGIN SELECT JOB_ID INTO job_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN job_id; END; / -- Create session variable: VARIABLE job VARCHAR2(10); -- Run function and store returned value in session variable: EXECUTE :job := get_job_id(204); PL/SQL procedure successfully completed.
SQL*Plusコマンド:
PRINT job;
結果:
JOB -------------------------------- PR_REP
|
参照:
|
ストアド・サブプログラムは、別のサブプログラムやトリガーから起動できます。例11-9では、プロシージャprint_mgr_nameがプロシージャprint_emp_nameを起動します。
サブプログラムの再帰的な起動が可能です(つまり、サブプログラムがサブプログラム自体を起動できます)。
例11-9 別のサブプログラムからのサブプログラムの起動
-- Create procedure that takes employee's ID and prints employee's name: CREATE OR REPLACE PROCEDURE print_emp_name ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) IS fname EMPLOYEES.FIRST_NAME%TYPE; lname EMPLOYEES.LAST_NAME%TYPE; BEGIN SELECT FIRST_NAME, LAST_NAME INTO fname, lname FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; DBMS_OUTPUT.PUT_LINE ( 'Employee #' || emp_id || ': ' || fname || ' ' || lname ); END; / -- Create procedure that takes employee's ID and prints manager's name: CREATE OR REPLACE PROCEDURE print_mgr_name ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) IS mgr_id EMPLOYEES.MANAGER_ID%TYPE; BEGIN SELECT MANAGER_ID INTO mgr_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; DBMS_OUTPUT.PUT_LINE ( 'Manager of employee #' || emp_id || ' is: ' ); print_emp_name(mgr_id); END; /
プロシージャを起動します。
BEGIN print_emp_name(200); print_mgr_name(200); END; /
結果:
Employee #200: Jennifer Whalen Manager of employee #200 is: Employee #101: Neena Kochhar
リモート・サブプログラムは、起動者とは別のデータベースに格納されます。リモート・サブプログラムの起動には、サブプログラム名、サブプログラムが格納されているデータベースへのデータベース・リンク、およびすべての仮パラメータの実パラメータ(仮パラメータにデフォルト値がある場合でも)が含まれる必要があります。
たとえば、次のSQL*Plus文はストアド・スタンドアロン・プロシージャfire_emp1を起動します。このプロシージャは、boston_serverという名前のローカル・データベース・リンクによって参照されています。
EXECUTE fire_emp1@boston_server(1043);
|
注意: リモート・パッケージ・サブプログラムは起動できますが、リモート・パッケージの変数および定数には直接アクセスできません。 |
|
注意:
|
内容は次のとおりです。
|
参照:
|
シノニムは、スキーマ・オブジェクトの別名です。リモート・サブプログラム名とデータベース・リンクのシノニムを作成し、シノニムを使用してサブプログラムを起動することができます。次に例を示します。
CREATE SYNONYM synonym1 for fire_emp1@boston_server; EXECUTE synonym1(1043);
|
注意: パッケージ・サブプログラムのシノニムを作成することはできません。これは、パッケージ・サブプログラムはスキーマ・オブジェクトではないためです(そのパッケージはスキーマ・オブジェクトです)。 |
シノニムによってデータの独立性と場所の透過性の両方が実現されます。シノニムを使用すると、サブプログラムの所有者や場所を知らなくてもサブプログラムを起動できます。ただし、シノニムは権限の代替物ではありません。シノニムを使用してサブプログラムを起動するには、サブプログラムに必須の権限が必要です。
シノニムに権限を付与することは、基本オブジェクトに権限を付与することと同じです。同様に、基本オブジェクトに対して権限を付与することは、オブジェクトのすべてのシノニムに権限を付与することと同じです。
プライベート・シノニムとパブリック・シノニムの両方を作成できます。プライベート・シノニムは自分のスキーマ内にあり、他のユーザーによるシノニムの使用を自分で制御できます。パブリック・シノニムはユーザー・グループPUBLICに属しており、すべてのデータベース・ユーザーがアクセスできます。
パブリック・シノニムではデータベースの統合が困難になるため、パブリック・シノニムは慎重に使用してください(例については、『Oracle Database概要』を参照)。
シノニムを使用しない場合は、リモート・サブプログラムを起動するローカル・サブプログラムを作成することもできます。次に例を示します。
CREATE OR REPLACE PROCEDURE local_procedure (arg IN NUMBER) AS BEGIN fire_emp1@boston_server(arg); END; / DECLARE arg NUMBER; BEGIN local_procedure(arg); END; /
|
参照:
|
リモート・サブプログラムの起動により、データベースが更新されることが想定されます。したがって、リモート・サブプログラムを起動するトランザクションには、(リモート・サブプログラムによってデータベースが更新されない場合でも) 2フェーズ・コミットが必要です。トランザクションがロールバックされると、リモート・サブプログラムによって実行された処理もロールバックされます。
文COMMIT、ROLLBACKおよびSAVEPOINTに関しては、リモート・サブプログラムは次の点においてローカル・サブプログラムと異なります。
トランザクションがOracle Database以外のデータベースで開始された場合、リモート・サブプログラムでこれらの文は実行できません。
このような状況になるのはOracle XAアプリケーションなどの場合であり、このアプリケーションを使用することはお薦めしません。詳細は、第19章「Oracle XAを使用したアプリケーションの開発」を参照してください。
これらの文の1つを実行した後、リモート・サブプログラムは独自の分散トランザクションを開始できなくなります。
分散トランザクションでは複数のデータベースが更新されます。トランザクション内の文は別のデータベースに送信され、トランザクションはユニットとして成功または失敗します。トランザクションが任意のトランザクションで失敗した場合、このトランザクションをすべてのデータベースで(セーブポイントまで、または完全に)ロールバックする必要があります。分散更新を実行するサブプログラムを作成する場合は、この点を考慮する必要があります。
リモート・サブプログラムがその作業をコミットまたはロールバックしない場合、データベース・リンクがクローズした時点で作業が暗黙的にコミットされます。それまでは、リモート・サブプログラムがトランザクションを実行しているとみなされます。したがって、リモート・サブプログラムに対するこれ以上の起動は許可されません。
PL/SQLストアド・ファンクションを起動できるSQL文は次のとおりです。
INSERT
UPDATE
DELETE
SELECT
(SELECTは、WITH句で宣言および定義されているPL/SQLファンクションを起動することもできます。詳細は、『Oracle Database SQL言語リファレンス』を参照してください。)
CALL
(CALLは、PL/SQLストアド・プロシージャを起動することもできます。)
PL/SQLファンクションをSQL文から起動するには、ファンクションに対するEXECUTE権限を所有している必要があります。PL/SQLファンクションで定義されたビューを検索するには、そのビューのREADまたはSELECT権限が必要です。ビューを検索するには個々のEXECUTE権限は必要ありません。
パラメータを渡す方法など、サブプログラムを起動する方法の概要は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
内容は次のとおりです。
SQL文でPL/SQLファンクションを起動することにより、次の効果があります。
SQLの拡張によって、ユーザーの生産性が向上します。
実行する内容がSQL文のみで表現するには複雑すぎたり、非常に扱いにくかったり、不可能な場合に、SQL文の表現機能が強化されます。
問合せの効率を向上します。
問合せのWHERE句にファンクションを指定すると、条件を使用してデータをフィルタできます。ファンクションを使用できない場合は、アプリケーションで評価する必要があります。
特殊なデータ型(緯度、経度、温度など)を表すための文字列を操作できます
パラレル問合せを実行できます。
問合せがパラレル化されると、PL/SQLサブプログラム内のSQL文も(パラレル問合せオプションを使用して)パラレルに実行できます。
SQL文では、SQLファンクションや式を使用できる任意の箇所でPL/SQLファンクションを使用できます。次に例を示します。
SELECT文の選択リスト
WHERE句またはHAVING句の条件
CONNECT BY句、START WITH句、ORDER BY句またはGROUP BY句
INSERT文のVALUES句
UPDATE文のSET句
SELECT文では、次のかわりにPL/SQL表ファンクション(複数の行をまとめて戻す)を使用できます。
SELECTリストの列名
FROM句の表名
不変の定義が必要な次のようなコンテキストには、PL/SQLファンクションは使用できません。
CREATE文またはALTER TABLE文のCHECK制約句
列に対するデフォルト値の指定
PL/SQLファンクションをSQL式から起動するには、そのファンクションが次の要件を満たしている必要があります。
ユーザー定義の集計ファンクションまたは行ファンクションであること。
仮パラメータが、OUTパラメータやIN OUTパラメータではなく、INパラメータであること。
例11-10のファンクションは、これらの要件を満たしています。
例11-10 SQL式でのPL/SQLファンクション(規則に従った場合)
DROP TABLE payroll; -- in case it exists CREATE TABLE payroll ( srate NUMBER, orate NUMBER, acctno NUMBER ); CREATE OR REPLACE FUNCTION gross_pay ( emp_id IN NUMBER, st_hrs IN NUMBER := 40, ot_hrs IN NUMBER := 0 ) RETURN NUMBER IS st_rate NUMBER; ot_rate NUMBER; BEGIN SELECT srate, orate INTO st_rate, ot_rate FROM payroll WHERE acctno = emp_id; RETURN st_hrs * st_rate + ot_hrs * ot_rate; END gross_pay; /
サブプログラムは、独自のローカル変数の値以外のものを変更する場合、副作用を伴います。たとえば、次のいずれかを変更するサブプログラムには、副作用があります。
独自のOUTまたはIN OUTパラメータ
グローバル変数
パッケージ内のパブリック変数
データベース表
データベース
外部の状態(たとえば、DBMS_OUTPUTの起動や電子メールの送信などによる変更)
副作用によって、問合せのパラレル処理が妨害されたり、処理順序に依存する(したがって、不確定な)結果が発生したり、ユーザー・セッションにまたがったパッケージ状態のメンテナンスが必要になります。
ファンクションがSQL問合せまたはDML文から起動される場合は、一部の副作用は受け入れられません。
Oracle Database 8gリリース1 (8.1)より前では、アプリケーション開発者はPRAGMA RESTRICT_REFERENCESを使用してファンクションの純粋度(副作用からの自由度)をアサートしていました。このプラグマは、下位互換用として使用可能のままですが、新しいアプリケーションでは使用しないでください。かわりに、ファンクションの作成時にはオプティマイザ・ヒントDETERMINISTICおよびPARALLEL_ENABLEを指定してください。DETERMINISTICおよびPARALLEL_ENABLEの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
内容は次のとおりです。
|
注意: SQL文から起動したファンクションの制限は、SQL文によって起動されるトリガーにも適用されます。 |
SQL文がファンクションを起動し、ファンクションが新規SQL文を実行すると、新しい文の実行は、ファンクションを起動した文のコンテキストに論理的に埋め込まれます。このコンテキストで新しい文が安全であるようにするために、Oracle Databaseはファンクションに対して次の制限を課します。
ファンクションを起動するSQL文が問合せまたはDML文である場合、ファンクションは、現行のトランザクションの終了、セーブポイントの作成またはセーブポイントまでのロールバック、あるいはシステムまたはセッションの変更(ALTER)を実行できません。
ファンクションを起動するSQL文が問合せまたはパラレル化されたパラレルDML文である場合、ファンクションは、DML文を実行できないか、またはデータベースを変更できません。
ファンクションを起動するSQL文がDML文である場合、ファンクションは、ファンクションを起動したSQL文によって変更される表の読取りまたは変更を実行できません。
これらの制限は、ファンクションが新規SQL文を実行する方法とは関係なく適用されます。たとえば、これらは、ファンクションが次の処理を行う新規SQL文に適用されます。
ファンクションの本体に直接埋め込まれているかどうかにかかわらず、PL/SQLから起動し、EXECUTE IMMEDIATEを使用して実行するか、またはDBMS_SQLパッケージを使用して実行
SQLJ構文を使用してJavaに埋め込むか、JDBCを使用して実行
外部Cファンクション内からコールバック・コンテキストを使用してOCIで実行
これらの制約を回避するには、新しいSQL文の実行が、ファンクションを起動するSQL文のコンテキストに論理的に埋め込まれていない場合ことを確認してください。たとえば、新規SQL文を自律型トランザクションに配置するか、OCIで、OCIExtProcContext引数によって提供されるハンドルを使用するかわりに外部Cファンクションに対する新規接続を作成します。自律型トランザクションの詳細は、6.8項を参照してください。
Oracle Databaseがパラレル化されたSQL文を実行する場合、同時に多数のプロセスが連携して1つのSQL文を実行します。パラレル化されたSQL文がファンクションを起動すると、各プロセスにより、プロセスが処理する行のサブセットのみに対して、そのファンクションのコピーが起動される場合があります。
各プロセスには、そのプロセス専用のパッケージ変数のコピーがあります。パラレル実行が開始されると、ユーザーがシステムにログインしたときのように、プロセスごとにパッケージ変数が初期化されます。パッケージ変数内の値は、元のログイン・セッションからはコピーされません。1つのプロセスがパッケージ変数に対して行う変更が他のプロセスまたは元のログイン・セッションに自動的に伝播されることはありません。JavaのSTATICクラス属性は、同様に、各プロセス内で独立して初期化され変更されます。ファンクションは、パッケージおよびJava STATIC変数を使用して、発生する様々な行にわたって値を累積できます。このため、Oracle Databaseはデフォルトでは、ユーザー定義ファンクションの実行をパラレル化しません。
Oracle Database 8gリリース1 (8.1)より前:
パラレル化された問合せによってユーザー定義ファンクションが起動されたときに、PRAGMA RESTRICT_REFERENCESによってファンクションに対してRNPSとWNPSの両方がアサートされた(つまり、ファンクションがパッケージ変数を参照、またはその値を変更しなかった)場合、ファンクションの実行をパラレル化できました。
このアサーションがなく、ファンクションがパッケージ変数を参照、またはその値を変更しなかったことをOracle Databaseが確認した場合、スタンドアロンPL/SQLファンクション(ただし、CまたはJava関数ではありません)の実行をパラレル化できました。
パラレル化されたDML文によってユーザー定義ファンクションが起動されたときに、PRAGMA RESTRICT_REFERENCESによってファンクションに対してRNDS、WNDS、RNPSおよびWNPSがアサートされた(つまり、ファンクションがパッケージ変数またはデータベース表を参照、またはその値を変更しなかった)場合、ファンクションの実行をパラレル化できました。
このアサーションがなく、ファンクションがパッケージ変数またはデータベース表を参照、またはその値を変更しなかったことをOracle Databaseが確認した場合、スタンドアロンPL/SQLファンクション(ただし、CまたはJava関数ではありません)の実行をパラレル化できました。
Oracle Database 8gリリース1 (8.1)以降、パラレル化されたSQL文によってユーザー定義ファンクションが起動された場合、ファンクションの実行は次の状況下でパラレル化できます。
ファンクションがPARALLEL_ENABLEを使用して作成された場合。
Oracle Database 8gリリース1 (8.1)より前では、データベースがファンクションをパラレル化可能として識別した場合。
既存のPL/SQLアプリケーションでは、PRAGMA RESTRICT_REFERENCESを削除することも、新しいファンクションでも既存コードとの統合を容易にするために、これを引き続き使用することもできます。次に例を示します。
既存コードからPRAGMA RESTRICT_REFERENCESを完全に削除することが不可能かつ非現実的な場合。
たとえば、サブプログラムS1がサブプログラムS2に依存し、S1からプラグマを削除しない場合、S1のコンパイルにS2のプラグマが必要になる場合があります。
既存コードでPRAGMA RESTRICT_REFERENCESをPARALLEL_ENABLEおよびDETERMINISTICに置き換えると、新しい依存コードのアクションに悪影響を与える可能性がある場合。
PRAGMA RESTRICT_REFERENCESを使用してファンクションの純粋度をアサートする場合、(パッケージ本体ではなく)パッケージ仕様で、ファンクション宣言後の任意の場所で、次の構文を使用します。
PRAGMA RESTRICT_REFERENCES (function_name, assertion [, assertion]... );
この場合、assertionは次のいずれかです。
| アサーション | 意味 |
|---|---|
RNPS |
ファンクションはパッケージ状態を読み込みません(パッケージ変数の値を参照しないということです) |
WNPS |
ファンクションはパッケージ状態を書き込みません(パッケージ変数の値を変更しないということです)。 |
RNDS |
ファンクションはデータベース状態を読み込みません(データベース表を問い合せないということです)。 |
WNDS |
ファンクションはデータベース状態を書き込みません(データベース表を変更しないということです)。 |
TRUST |
ファンクションに対して行われた任意のアサーションにファンクション本体内のSQL文が違反していないことを信頼します。詳細は、第11.9.4.3.1項を参照してください。 |
TRUSTを指定せず、指定したアサーションにファンクション本体内のSQL文が違反すると、PL/SQLコンパイラは、違反している文の解析中にエラー・メッセージを表示します。
PL/SQLコンパイラによってファンクションが不要に拒否されることがないように、ファンクションで許可される最高の純粋度レベル(最上位アサーション)をアサートしてください。
|
注意: ファンクションがサブプログラムを起動する場合、これらのサブプログラムに対してもPRAGMA RESTRICT_REFERENCESを指定するか、起動する側のファンクションまたは起動される側のサブプログラムにTRUSTを指定してください。 |
|
参照: PRAGMA RESTRICT_REFERENCESの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください |
例11-11では、データベースまたはパッケージ状態の読取りおよび書込みを行わず、それが最大の純粋度レベルであることをアサートするファンクションを作成します。
例11-11 PRAGMA RESTRICT_REFERENCES
DROP TABLE accounts; -- in case it exists CREATE TABLE accounts ( acctno INTEGER, balance NUMBER ); INSERT INTO accounts (acctno, balance) VALUES (12345, 1000.00); CREATE OR REPLACE PACKAGE finance AS FUNCTION compound_ ( years IN NUMBER, amount IN NUMBER, rate IN NUMBER ) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES (compound_, WNDS, WNPS, RNDS, RNPS); END finance; / CREATE PACKAGE BODY finance AS FUNCTION compound_ ( years IN NUMBER, amount IN NUMBER, rate IN NUMBER ) RETURN NUMBER IS BEGIN RETURN amount * POWER((rate / 100) + 1, years); END compound_; -- No pragma in package body END finance; / DECLARE interest NUMBER; BEGIN SELECT finance.compound_(5, 1000, 6) INTO interest FROM accounts WHERE acctno = 12345; END; /
内容は次のとおりです。
PRAGMA RESTRICT REFERENCESによってTRUSTが指定されると、PL/SQLコンパイラはサブプログラム本体に違反があるかどうかを確認しません。
TRUSTを使用すると、PRAGMA RESTRICT REFERENCESを使用するサブプログラムが、これを使用しないサブプログラムを起動しやすくなります。
PL/SQLサブプログラムがCまたはJavaサブプログラムを起動する場合、PL/SQLサブプログラム(例11-12を参照)またはCまたはJavaサブプログラム(例11-13を参照)に対してTRUSTを指定する必要があります。これは、PL/SQLコンパイラは実行時にCまたはJavaサブプログラムに違反があるかどうかを確認できないためです。
例11-12 起動する側にTRUSTが指定されたPRAGMA RESTRICT REFERENCES
CREATE OR REPLACE PACKAGE p IS PROCEDURE java_sleep (milli_seconds IN NUMBER) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; FUNCTION f (n NUMBER) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES(f,WNDS,TRUST); END p; / CREATE OR REPLACE PACKAGE BODY p IS FUNCTION f ( n NUMBER ) RETURN NUMBER IS BEGIN java_sleep(n); RETURN n; END f; END p; /
例11-13 起動される側にTRUSTが指定されたPRAGMA RESTRICT REFERENCES
CREATE OR REPLACE PACKAGE p IS PROCEDURE java_sleep (milli_seconds IN NUMBER) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; PRAGMA RESTRICT_REFERENCES(java_sleep,WNDS,TRUST); FUNCTION f (n NUMBER) RETURN NUMBER; END p; / CREATE OR REPLACE PACKAGE BODY p IS FUNCTION f ( n NUMBER ) RETURN NUMBER IS BEGIN java_sleep(n); RETURN n; END f; END p; /
静的なINSERT、UPDATEまたはDELETE文は、データベース状態(表の列など)を明示的に読み込まない場合は、RNDSには違反しません。動的なINSERT、UPDATEまたはDELETE文の場合は、データベース状態を明示的に読み込むかどうかにかかわらず、常にRNDSに違反します。
次のINSERT文は、動的に実行される場合はRNDSに違反しますが、静的に実行される場合は違反しません。
INSERT INTO my_table values(3, 'BOB');
次のUPDATE文は、my_tableの列nameを明示的に読み込むため、静的に実行された場合も動的に実行された場合もRNDSに違反します。
UPDATE my_table SET id=777 WHERE name='BOB';
ストアド・サブプログラムをコンパイルするには、コードの構文エラーを修正する必要があります。サブプログラムが正常に実行され、パフォーマンスも最適であり、エラーが修正されていることを確認するには、追加のデバッグを行う必要があります。次のようなデバッグが考えられます。
出力文を追加して、実行処理の検証およびサブプログラム内の任意の点でのデータ値のチェックをする。
変数または式の値を出力するには、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』で説明するように、OracleパッケージDBMS_OUTPUT内のPUTおよびPUT_LINEサブプログラムを使用します。
PL/Scope、PL/SQL階層プロファイラまたはデバッガの実行によって実行をより詳細に分析する
内容は次のとおりです。
|
参照:
|
PL/Scopeはコンパイラ駆動方式のツールであり、PL/SQLソース・コードからユーザー定義の識別子に関するデータを収集して構成します。PL/Scopeはコンパイラ駆動方式のツールであるため、直接使用するのではなく、対話型の開発環境(SQL DeveloperやJDeveloperなど)を介して使用します。
PL/Scopeによって、強力で効率的なPL/Scopeソース・コード・ブラウザの開発が可能になります。このブラウザは、ソース・コードの参照および理解に費やされる時間を最小限にすることによって、PL/SQL開発者の生産性を向上させます。
PL/Scopeの詳細は、第12章「PL/Scopeの使用」を参照してください。
PL/SQL階層プロファイラは、サブプログラム・コール別に編成されたPL/SQLプログラムの動的実行プロファイルをレポートします。SQL実行時間およびPL/SQL実行時間が個別に説明されます。動的実行プロファイルにおけるサブプログラム・レベルの各サマリーには、サブプログラムへのコール数、サブプログラム自体に要した時間、サブプログラムのサブツリー(つまり依存サブプログラム)に要した時間、詳細な親子情報などが表示されます。
生成されたHTMLレポートは任意のブラウザで参照できます。ブラウザのナビゲーション機能と厳選したリンクを組み合せた効率的な手段により、大規模なアプリケーションのパフォーマンスを分析し、アプリケーションのパフォーマンスを向上させ、開発コストを削減できます。
PL/SQL階層プロファイラの詳細は、第13章「PL/SQL階層プロファイラの使用」を参照してください。
デバッガは、生成されたデバッグ情報とともにコンパイルされたコードでのみ、個々のコード行で停止したり、変数にアクセスできます。
生成されたデバッグ情報とともにPL/SQLユニットをコンパイルするには、コンパイル・パラメータPLSQL_OPTIMIZE_LEVEL (デフォルト値は2)を1に設定します。PLSQL_OPTIMIZE_LEVELの詳細は、『Oracle Databaseリファレンス』を参照してください。PL/SQLユニットの詳細は、11.3項を参照してください。
Oracle Database 10gから、データベース内で実行されるPL/SQLおよびJavaコードのデバッグに新しい権限モデルが適用されるようになりました。このモデルは、すべての開発環境に適用されます。
デバッガに接続するセッションでは、DEBUG CONNECT SESSIONシステム権限を所有するユーザーが接続操作を実行する必要があります。接続コールに関係するDRサブプログラムの所有者であるユーザーも、この操作を実行できます。
セッションがデバッガに接続されると、セッション・ログイン・ユーザーおよび有効なセッション・レベルのロールが、このデバッグ用の接続の権限環境として決定されます。デバッグに必要なすべての権限を、関連コードでこのユーザーとロールの組合せに付与する必要があります。これらの権限は、次のとおりです。
PL/SQLパッケージ仕様で宣言された変数またはJavaパブリック変数の表示および変更: EXECUTEまたはDEBUG。
プライベート変数の表示および変更、またはコード行の段階的なブレークポイントおよび実行: DEBUG
|
注意: DEBUG権限を付与することにより、デバッグ対象のサブプログラムが実行するようにコーディングされている処理をデバッグ・セッションで実行することが可能になります。 |
DEBUG ANY PROCEDUREシステム権限の付与は、データベース内のすべてのオブジェクトにDEBUG権限を付与することと同じです。O7_DICTIONARY_ACCESSIBILITYパラメータの値がTRUEである場合は、SYSが所有しているオブジェクトにも付与します。
|
注意: SYSが所有するオブジェクトにDEBUG ANY PROCEDURE権限またはDEBUG権限を付与すると、すべての権限がデータベースに付与されます。 |
|
参照:
|
DBMS_DEBUGパッケージは、OracleサーバーにおけるPL/SQLデバッガ・レイヤー、プローブに対するPL/SQLインタフェースです。このAPIは、主にサーバー側のデバッガを実装することを目的としており、サーバー側のPL/SQLプログラム・ユニットをデバッグする方法を提供します。
Oracle Procedure Builderや様々なサード・パーティ・ベンダーが提供するソリューションなどデバッガのいくつかでは、このAPIが使用されています。
低レベルのデバッグ・コード(デバッガの一部のためのコードなど)を作成する場合、DBMS_DEBUGの使用が必要な場合があります。
|
参照: DBMS_DEBUGの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください |
パッケージ・オブジェクトを参照する各セッションには、対応するパッケージの独自のインスタンスがあり、この中には、パブリック変数、プライベート変数、カーソルおよび定数に対する持続状態が含まれます。セッションのインスタンス化されたパッケージ(仕様部または本体)のいずれかが無効化されると、そのセッションのすべてのパッケージ・インスタンスが無効になり、再コンパイルされます。これにより、そのセッションのすべてのパッケージ・インスタンスに関するセッション状態は失われます。
セッションのパッケージが無効である場合、セッションが無効なパッケージ・インスタンスのオブジェクトを最初に使用しようとしたときに、ORA-04068が戻されます。2度目にセッションがこのようなパッケージ・コールを行うと、エラーは発生せずに、パッケージはセッションに対して再インスタンス化されます。ただし、このエラーをアプリケーションで処理する場合は、次の点に注意してください。
パフォーマンスを最適な状態に保つため、Oracle Databaseがこのエラー・メッセージを戻すのは、パッケージ状態が破棄されるとき1回のみです。あるパッケージ内のサブプログラムにより別のパッケージ内のサブプログラムが起動される場合、両方のパッケージに対するセッション状態が失われます。
サーバー・セッションがORA-04068をトラップした場合、クライアント・セッションに対してORA-04068は発生しません。したがって、このクライアント・セッションがパッケージ内のオブジェクトを使用しようとすると、このパッケージは再インスタンス化されません。パッケージを再インスタンス化するには、クライアント・セッションをデータベースに再接続するか、またはクライアント・セッションでパッケージを再コンパイルする必要があります。
例11-14では、RAISE文により、処理中の例外ORA-06508の原因である現在の例外ORA-04068が呼び出されます。ORA-04068は検出されません。
例11-14 ORA-04068の呼出し
PROCEDURE p IS
package_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (package_exception, -6508);
BEGIN
...
EXCEPTION
WHEN package_exception THEN
RAISE;
END;
/
例11-15では、RAISE文により、ORA-06508に応えて、現在の例外ORA-04068ではなく、例外ORA-20001が呼び出されます。ORA-04068が検出されます。このような場合、ORA-04068エラーはマスクされ、これにより、パッケージの再インスタンス化が停止します。
例11-15 ORA-04068の検出
PROCEDURE p IS
package_exception EXCEPTION;
other_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (package_exception, -6508);
PRAGMA EXCEPTION_INIT (other_exception, -20001);
BEGIN
...
EXCEPTION
WHEN package_exception THEN
...
RAISE other_exception;
END;
/
本番環境の多くでは、パッケージが無効になるDDL操作は、通常、業務時間外に行われるため、エンドユーザー・アプリケーションでは、このような状況は問題にならない可能性もあります。しかし、パッケージが業務時間中に無効になることがよくある場合は、パッケージ・コールが行われたときにこのエラーを処理するように、アプリケーションを作成することが必要になります。
