13 PL/SQLサブプログラムおよびパッケージのコード化
PL/SQLサブプログラムおよびパッケージは、Oracle Databaseアプリケーションのビルディング・ブロックです。『Oracle Database PL/SQL言語リファレンス』に記載されている理由により、アプリケーションはパッケージとして実装することをお薦めします。
トピック:
13.1 PL/SQLサブプログラムの概要
PL/SQLソース・プログラムの基本単位はブロックで、関連する宣言および文をグループ化します。ブロックには、オプションの宣言部、必須の実行可能部、オプションの例外処理部があります。ブロックには、匿名ブロックと名前付きブロックがあります。
PL/SQLのサブプログラムは、繰り返し起動できる名前付きブロックです。サブプログラムにパラメータが含まれている場合は、起動のたびにパラメータ値を変えることができます。
サブプログラムは、プロシージャまたはファンクションのいずれかです。通常、プロシージャはアクションを実行するために使用し、ファンクションは計算を行って値を戻すために使用します。
また、サブプログラムは、ネストしたサブプログラム(PL/SQLブロック内に作成され、別のサブプログラムである場合があります)、パッケージ・サブプログラム(パッケージ仕様で宣言され、パッケージ本体で定義されます)、またはスタンドアロン・サブプログラム(スキーマ・レベルで作成されます)でもあります。パッケージ・サブプログラムおよびスタンドアロン・プログラムは、ストアド・サブプログラムです。ストアド・サブプログラムはデータベースでコンパイルおよび格納され、ここで多くのアプリケーションがストアド・サブプログラムを起動できます。
ストアド・サブプログラムは、AUTHID
およびACCESSIBLE
BY
句の影響を受けます。AUTHID
句は、実行時にサブプログラムによって発行されるSQL文の名前解決および権限チェックに影響を与えます。ACCESSIBLE
BY
句は、サブプログラムにアクセスできるPL/SQLユニットのホワイト・リストを指定します。
Oracle Databaseのインスタンス上で実行するPL/SQLサブプログラムは、第三世代言語(3GL)で作成された外部サブプログラムを起動できます。3GLサブプログラムは、データベースのアドレス空間とは別のアドレス空間で実行されます。
PL/SQLでは、ネストしたサブプログラム、パッケージ・サブプログラムおよび型のメソッドをオーバーロードできます。オーバーロードされたサブプログラムは名前が同じですが、仮パラメータの名前、数、順序またはデータ型のファミリが異なります。
トリガーは、ストアド・プロシージャのように、データベースに格納して繰り返し起動できる名前付きPL/SQLユニットです。ストアド・プロシージャとは異なり、トリガーは、有効にしたり無効にすることができますが、明示的に起動することはできません。トリガーは、有効の場合、データベースによって自動的に起動されます(つまり、トリガーは、トリガーを起動するイベントが発生すると、常に起動されます)。トリガーは、無効の場合、起動されません。
BEFORE UPDATE
トリガーは、内部再試行によって複数回起動できます。アプリケーションを設計する際は、これを考慮してください。通常のトリガーを使用すると、トリガーによって行われたすべての作業は、再試行時にロールバックされます。ただし、自律型トランザクションを使用してトリガーを定義した場合、トリガーによって実行された作業はロールバックされません。
関連項目:
-
PL/SQLサブプログラムの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQLブロックの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
サブプログラムの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQLユニットの詳細は、「PL/SQLユニットの概要」を参照してください。
-
外部サブプログラムの詳細は、「複数のプログラミング言語を使用したアプリケーションの開発」を参照してください。
-
オーバーロードされたサブプログラムの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.2 PL/SQLパッケージの概要
PL/SQLパッケージとは、論理的に関連する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
句にこれらをリストします。
ノート:
独自のパッケージを作成する前に、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照し、必要な機能がパッケージに用意されているかどうかを確認してください。
関連項目:
-
PL/SQLユニットの詳細は、PL/SQLユニットの概要を参照してください。
-
PL/SQLパッケージの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
パッケージを使用する理由は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.3 PL/SQLユニットの概要
PL/SQLユニットは、次のいずれかです。
-
無名PL/SQLブロック
-
FUNCTION
-
LIBRARY
-
PACKAGE
-
PACKAGE
BODY
-
PROCEDURE
-
TRIGGER
-
TYPE
-
TYPE
BODY
PL/SQLユニットは、PL/SQLコンパイル・パラメータ(データベース初期化パラメータのカテゴリ)によって影響を受けます。異なるPL/SQLユニット(パッケージの仕様部や本体など)に、異なるコンパイル・パラメータ設定を含めることができます。
PL/SQLユニットのAUTHID
プロパティは、実行時にユニットによって発行されるSQL文の名前解決および権限チェックに影響します。
関連項目:
-
PL/SQLユニットおよびコンパイル・パラメータの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
AUTHID
プロパティの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.3.1 PLSQL_OPTIMIZE_LEVELコンパイル・パラメータ
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
を使用します。
例13-1 では、2つのプロシージャを作成し、これらの最適化レベルを表示し、セッションの最適化レベルを変更し、3番目のプロシージャを作成し、3つのプロシージャすべての最適化レベルを表示します。3番目のプロシージャのみが新しい最適化レベルを持ちます。このため、この例では、1つのプロシージャの最適化レベルのみを変更し、3つのプロシージャすべての最適化レベルを再表示します。
関連項目:
例13-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.
13.4 PL/SQLサブプログラムおよびパッケージの作成
トピック:
13.4.1 サブプログラムおよびパッケージの作成に必要な権限
独自のスキーマでスタンドアロン・サブプログラムまたはパッケージを作成するには、CREATE
PROCEDURE
システム権限が必要です。別のスキーマでスタンドアロン・サブプログラムまたはパッケージを作成するには、CREATE
ANY
PROCEDURE
システム権限が必要です。
作成するサブプログラムまたはパッケージがスキーマ・オブジェクトを参照する場合、これらのオブジェクトに必須のオブジェクト権限が必要です。これらの権限は、ロールを介してではなく、明示的に付与される必要があります。
サブプログラムまたはパッケージの所有者の権限が変更された場合、実行前にそのサブプログラムまたはパッケージを再認証する必要があります。参照オブジェクトに必要なオブジェクト権限が、そのサブプログラムまたはパッケージの所有者から取り消されている場合、そのサブプログラムは実行できません。
サブプログラムに対してEXECUTE
権限を付与すると、ユーザーはサブプログラム所有者のセキュリティ・ドメインでサブプログラムを実行できるため、サブプログラムが参照するオブジェクトに対する権限をユーザーに付与する必要がなくなります。EXECUTE
権限を使用すると、データベース・アプリケーションおよびそのユーザーに対してさらに統制のとれた効率的なセキュリティ計画が可能になります。また、サブプログラムおよびパッケージをデータ・ディクショナリ(SYSTEM
表領域内)に格納できるようになります。ここでは、サブプログラムおよびパッケージを作成するユーザーが使用可能な領域の量が割当てによって制御されません。
13.4.2 サブプログラムおよびパッケージの作成
このトピックでは、SQLデータ定義言語(DDL)文を使用してスタンドアロン・サブプログラムおよびスタンドアロン・パッケージを作成する方法について説明します。
スタンドアロン・サブプログラムおよびスタンドアロン・パッケージを作成するためのDDL文は、次のとおりです。
-
CREATE
FUNCTION
-
CREATE
PROCEDURE
-
CREATE
PACKAGE
-
CREATE
PACKAGE
BODY
パッケージの名前およびパブリック・オブジェクトの名前は、パッケージ・スキーマ内で一意である必要があります。パッケージ仕様部およびその本体は、同じ名前である必要があります。パッケージ構成の名前は、オーバーロードされたサブプログラムを除いて、パッケージの範囲内で一意である必要があります。
前述の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 Developerを使用してDDL文を作成および実行することもできます。
関連項目:
-
SQL*Plusにおけるスクリプトの実行の詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください。
-
SQL Developerの詳細は、『Oracle SQL Developerユーザーズ・ガイド』を参照してください。
-
次の関数の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
CREATE
FUNCTION
-
CREATE
PROCEDURE
-
CREATE
PACKAGE
-
CREATE
PACKAGE
BODY
-
13.4.3 PL/SQLオブジェクト・サイズの制限
サブプログラム、トリガー、パッケージなどの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部分のサイズは、システム表内より共有プール内で非常に大きくなります。
13.4.4 PL/SQLのデータ型
このトピックでは、PL/SQLデータ型を紹介し、詳細な情報の参照先として他の章またはドキュメントを示します。
データベース・アプリケーション内のPL/SQL変数ごとに正確かつ最も固有のPL/SQLデータ型を使用してください。
関連項目:
トピック:
13.4.4.1 PL/SQLのスカラー・データ型
スカラー・データ型には、内部コンポーネントを持たない値が格納されます。
スカラー・データ型にはサブタイプを持たせることができます。サブタイプとは、別のデータ型のサブセットとなるデータ型のことで、その別のデータ型は、そのサブタイプのベース型となります。サブタイプには、そのベース型で有効な演算と同じ演算が含まれています。データ型とそのサブタイプでデータ型ファミリが構成されます。
PL/SQLには、多くの型やサブタイプがパッケージSTANDARD
に事前定義されています。また、PL/SQLを使用して、独自のサブタイプを定義することもできます。
トピック:
13.4.4.1.1 SQLデータ型
PL/SQLデータ型にはSQLデータ型が含まれます。
関連項目:
-
データベース・アプリケーションでSQLデータ型を使用する方法の詳細は、「データベース・アプリケーションにおけるSQLデータ型の使用」を参照してください。
-
SQLデータ型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.4.1.2 BOOLEANデータ型
BOOLEAN
データ型には、論理値(ブール値のTRUE
とFALSE
、およびNULL
値)が格納されます。NULL
は、不明な値を表します。
関連項目:
BOOLEAN
データ型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.4.1.3 PLS_INTEGERおよびBINARY_INTEGERデータ型
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言語リファレンス』を参照してください。
13.4.4.1.4 REF CURSORデータ型
REF
CURSOR
は、カーソル変数のデータ型です。
カーソル変数は、明示カーソルと似ていますが、次の点が異なります。
-
1つの問合せに限定されません。
カーソル変数を問合せに対してオープンし、結果セットを処理した後、カーソル変数を別の問合せのために使用できます。
-
値を代入できます。
-
式で使用できます。
-
サブプログラム・パラメータとして使用できます。
カーソル変数は、サブプログラム間で問合せ結果セットを渡すために使用できます。
-
ホスト変数として使用できます。
カーソル変数は、PL/SQLのストアド・サブプログラムとそれらのクライアントとの間で問合せ結果セットを渡すために使用できます。
-
パラメータを受け入れることはできません。
カーソル変数にパラメータを渡すことはできませんが、問合せ全体を渡すことができます。
カーソル変数がこのような柔軟性を持つ理由は、それがポインタであるため(その値が項目自体ではなく項目のアドレスを示すため)です。
関連項目:
REF
CURSOR
データ型およびカーソル変数の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.4.1.5 ユーザー定義のPL/SQLサブタイプ
PL/SQLではユーザー独自のサブタイプを定義できます。ベース型には、事前に定義したユーザー定義のサブタイプを含む、任意のPL/SQLスカラー型を指定できます。
サブタイプの役割は次のとおりです。
-
ANSI/ISOデータ型との互換性の提供
-
その型のデータ項目の使用意図の提示
-
範囲外の値の検出
関連項目:
ユーザー定義のPL/SQLサブタイプの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.4.2 PL/SQLのコンポジット・データ型
コンポジット・データ型には、内部コンポーネントがあります。PL/SQLのコンポジット・データ型は、コレクションおよびレコードです。
collectionの内部コンポーネントは、常に同じデータ型であり、要素と呼ばれます。コレクション変数の各要素には、一意の索引によってアクセスできます。PL/SQLには、連想配列、VARRAY
(可変サイズの配列)およびネストした表の3つのコレクション型があります。
レコードの内部コンポーネントは、データ型が異なる場合があり、フィールドと呼ばれます。レコード変数の各フィールドには、名前によってアクセスできます。
レコードのコレクション、およびコレクションを含むレコードを作成できます。
関連項目:
PL/SQLコンポジット・データ型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.4.3 抽象データ型
抽象データ型(ADT)は、データ構造と、データを操作するサブプログラムで構成されています。静的データ・ディクショナリ・ビュー*_OBJECTS
では、ADTのOBJECT_TYPE
はTYPE
です。静的データ・ディクショナリ・ビュー*_TYPES
では、ADTのTYPECODE
はOBJECT
です。
関連項目:
ADTの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.5 クライアントへの結果セットの戻し
PL/SQLでは、従来のデータベース・プログラミングと同様に、カーソルを使用して問合せ結果セットを処理できます。カーソルとは、特定のSELECT
文またはDML文の処理に関する情報を格納しておく、SQLのプライベート領域を指すポインタです。
ノート:
この項で説明するカーソルは、セッション・カーソルです。セッション・カーソルはセッション・メモリーに存在し、セッションが終了すると消滅します。セッション・カーソルは、プログラム・グローバル領域(PGA)のSQLプライベート領域内のカーソルとは異なります。
PL/SQLで構築され管理されるカーソルは、暗黙カーソルです。ユーザーが構築および管理するカーソルは、明示カーソルです。暗黙カーソルに勝る明示カーソルの唯一のメリットは、明示カーソルを使用すると、フェッチされる行の数を制限できる点です。
カーソル変数とは、カーソルを指すポインタです。つまり、その値は、カーソル自体ではなく、カーソルのアドレスです。したがって、カーソル変数は明示カーソルより柔軟です。ただし、カーソル変数には、明示カーソルにはないデメリットもあります。
トピック:
関連項目:
-
カーソル変数、および暗黙カーソルと明示カーソルを使用した問合せセット結果処理の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
一括収集文での行数および収集サイズを制限する方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
OCIでのカーソル変数の使用方法の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください
-
PGAの詳細は、『Oracle Database概要』を参照してください。
13.4.5.1 カーソル変数のメリット
カーソル変数は、明示カーソルと似ていますが、次の点が異なります。
-
1つの問合せに限定されません。
カーソル変数を問合せに対してオープンし、結果セットを処理した後、カーソル変数を別の問合せのために使用できます。
-
値を代入できます。
-
式で使用できます。
-
サブプログラム・パラメータとして使用できます。
カーソル変数は、サブプログラム間で問合せ結果セットを渡すために使用できます。
-
ホスト変数として使用できます。
カーソル変数は、PL/SQLのストアド・サブプログラムとそれらのクライアントとの間で問合せ結果セットを渡すために使用できます。
-
パラメータを受け入れることはできません。
カーソル変数にパラメータを渡すことはできませんが、問合せ全体を渡すことができます。問合せには変数を組み込むことができます。
前述の特性のため、カーソル変数には次のメリットがあります。
-
カプセル化
カーソル変数をオープンするストアド・サブプログラムに問合せを集中化できます。
-
メンテナンスの容易さ
カーソルの変更が必要な場合は、ストアド・サブプログラムの変更のみで済みます。ストアド・サブプログラムを起動するすべてのアプリケーションで変更を行う必要はありません。
-
セキュリティの利便性
アプリケーションは、アプリケーション・ユーザーのユーザー名を持つサーバーに接続します。アプリケーション・ユーザーには、カーソルをオープンするストアド・サブプログラムに対する
EXECUTE
権限が必要ですが、問い合せた表に対するREAD
権限は必要ありません。
13.4.5.2 カーソル変数のデメリット
カーソル変数を使用する必要がない場合、パフォーマンスおよびプログラミングの容易性を向上するために、暗黙または明示カーソルを使用してください。
トピック:
ノート:
これらのトピックの例には、TKPROF
レポートが含まれます。
関連項目:
TKPROF
レポートの生成手順は、『Oracle Database SQLチューニング・ガイド』を参照してください。
13.4.5.2.1 カーソル変数の解析のペナルティ
明示カーソルを閉じた場合、カーソルはパースペクティブから閉じられますが(つまり、開いているカーソルが必要な場合にそのカーソルを使用できない)、PL/SQLは明示カーソルをオープン状態でキャッシュします。カーソルに関連付けられた文を再実行すると、PL/SQLはキャッシュされたカーソルを使用することにより、解析を回避します。
解析の回避により、CPUの使用率が大幅に削減され、明示カーソルのキャッシュが明白になります。この場合、プログラミングは影響を受けません。PL/SQLは使用可能なオープン・カーソルの供給を削減しません。プログラムが他のカーソルを開く必要があるが、開くとOPEN_CURSORS
のinit.ora設定を超えるような場合、PL/SQLはキャッシュされたカーソルを閉じます。
PL/SQLはオープン状態のカーソル変数はキャッシュできません。したがって、カーソル変数には解析のペナルティがあります。
例13-2では、プロシージャは明示カーソルをオープンし、ここからフェッチし、これをクローズし、カーソル変数でも同じ処理を行います。匿名ブロックがプロシージャを10回コールします。TKPROF
レポートには、両方の問合せが10回実行されたが、明示カーソルに関連付けられた問合せが解析されたのは1回のみで、カーソル変数に関連付けられた問合せが解析されたのが10回であることが示されます。
例13-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
13.4.5.2.2 カーソル変数の複数行フェッチのペナルティ
例13-3では、7,000行以上の表を作成し、これらすべての行を2回フェッチします。この場合、初回は暗黙カーソル(配列のフェッチ)を使用し、2回目はカーソル変数(個別行のフェッチ)を使用します。暗黙カーソルのコードは、カーソル変数のコードより単純であり、TKPROF
レポートには、暗黙カーソルのコードの方がパフォーマンスもよいことが示されます。
カーソル変数を使用して配列をフェッチすることもできますが、より多くのコードが必要になります。特に、次の処理を行うためのコードが必要になります。
-
配列のフェッチ先のコレクションのタイプの定義
-
コレクションに対する明示的な一括収集
-
フェッチしたデータを処理するためのコレクションのループ
-
明示的にオープンしたカーソル変数のクローズ
例13-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
13.4.5.3 問合せ結果の暗黙的戻し
ストアド・サブプログラムは、DBMS_SQL
.RETURN_RESULT
プロシージャを起動することにより、問合せ結果を暗黙的にクライアント・プログラムまたはサブプログラムの直接のコール元に戻すことができます。DBMS_SQL
.RETURN_RESULT
によって結果が戻された後、受信者のみがこれにアクセスできます。
ノート:
動的SQLを使用して実行された問合せの結果を暗黙的に戻すには、サブプログラムは、EXECUTE
IMMEDIATE
文ではなくDBMS_SQL
プロシージャを使用して問合せを実行する必要があります。この理由は、EXECUTE
IMMEDIATE
文がサブプログラムに戻すカーソルは、EXECUTE
IMMEDIATE
文が完了するときにクローズされるためです。
関連項目:
-
DBMS_SQL
.RETURN_RESULT
プロシージャの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 -
動的SQLの
DBMS_SQL
プロシージャの使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.6 ファンクションからの大量のデータの戻し
データ・ウェアハウス環境では、大量のデータを変換するためにPL/SQLファンクションを使用します。データは、異なるファンクションによる一連の変換を経由して渡す場合があります。PL/SQL表ファンクションを使用すると、このような変換を、かなりのメモリー・オーバーヘッドを必要としたり、各変換の間でデータを表に格納する必要なく実行できます。これらのファンクションは、複数の行を受け入れて戻すことが可能で、一度ではなく準備できた順に行を戻すことができるだけでなく、パラレル化も可能です。
関連項目:
パイプライン・テーブル・ファンクションを使用して複数の変換を実行する方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.7 PL/SQLファンクション結果キャッシュ
PL/SQLファンクション結果キャッシュを使用すると、領域と時間を大幅に節約できます。結果キャッシュPL/SQLファンクションを、異なるパラメータ値を使用して起動するたびに、それらのパラメータおよびその結果がキャッシュに格納されます。それ以降、同じファンクションが同じパラメータ値で起動されると、結果は再計算されるのではなく、キャッシュから取り出されます。キャッシュは共有グローバル領域(SGA)に格納されるため、アプリケーションが実行されるすべてのセッションで使用可能です。
キャッシュ結果を計算する際に使用したデータベース・オブジェクトが更新されると、そのキャッシュ結果は無効になり、再計算が必要になります。
結果キャッシュの対象として最良のファンクションは、頻繁に起動され、ほとんどまたはまったく変更されない情報に依存するファンクションです。
関連項目:
PL/SQLファンクション結果キャッシュの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.8 バルク・バインドの概要
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文は無効になります。
トピック:
関連項目:
-
バルク・バインド操作中に発生する例外の処理方法を含むバルク・バインドの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください
-
並列DML文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.4.8.1 コレクションを参照するDML文
FORALL
キーワードを使用するバルク・バインドによって、コレクション要素を参照するINSERT
、UPDATE
またはDELETE
文のパフォーマンスが向上します。
例13-4のPL/SQLブロックは、管理職のID番号が7902、7698または7839の従業員について昇給を行うものですが、バルク・バインドを使用する場合と使用しない場合があります。PL/SQLがバルク・バインドを使用しないで、更新する各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例13-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; /
13.4.8.2 コレクションを参照するSELECT文
BULK
COLLECT
句によって、コレクションを参照する問合せのパフォーマンスを改善できます。スカラー値表または%TYPE
値表にBULK
COLLECT
を使用できます。
例13-5のPL/SQLブロックは、複数の値の問合せを行ってその値をPL/SQL表に格納するものですが、バルク・バインドを使用する場合と使用しない場合があります。PL/SQLがバルク・バインドを使用しないで、選択された各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例13-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; /
13.4.8.3 コレクションを参照しDMLを戻すFORループ
BULK
COLLECT
キーワードとともにFORALL
キーワードを使用すると、コレクションを参照しDMLを戻すFOR
ループのパフォーマンスを改善できます。
例13-6のPL/SQLブロックは、従業員コレクションのボーナスを計算し、EMPLOYEES
表を更新します。さらに、ボーナスをbonus_list_inst
列に戻します。この操作をバルク・バインドを使用して実行し、また使用せずに実行します。PL/SQLがバルク・バインドを使用しないで、更新する各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例13-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; /
13.4.9 PL/SQLの動的SQL
動的SQLは、実行時にSQL文を生成して実行するためのプログラミング方法です。この方法は、非定型の問合せシステムのような柔軟性がある汎用目的のプログラムを記述する場合、データベース定義言語(DDL)文を実行する必要があるプログラムを記述する場合、またはコンパイル時にSQL文のテキスト全体またはそのSQL文の入力変数および出力変数の数またはデータ型が不明な場合に有効です。
動的SQLが必要ない場合は、静的SQLを使用します。静的SQLには、次のようなメリットがあります。
-
コンパイルが正常に完了すると、静的SQL文が有効なデータベース・オブジェクトを参照していること、およびそれらのオブジェクトへのアクセスに必要な権限が存在していることが保証されます。
-
コンパイルが正常に完了すると、スキーマ・オブジェクトの依存性が作成されます。
関連項目:
-
スキーマ・オブジェクトの依存性の詳細は、「スキーマ・オブジェクトの依存性の理解」を参照してください。
-
動的SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
静的SQLの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.5 PL/SQLサブプログラムおよびパッケージの変更
ストアド・スタンドアロン・サブプログラムまたはパッケージの名前を変更するには、これを削除してから、新しい名前で作成する必要があります。例:
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言語リファレンス』を参照してください。
13.6 推奨されないパッケージ、サブプログラムおよびタイプ
DEPRECATE
プラグマを使用して、新しいインタフェースで非推奨または置き換えられたパッケージ、サブプログラムまたはタイプと通信できます。非推奨の要素を参照するユニットがコンパイルされると、コンパイルの警告が発行されます。PL/SQLユニットを非推奨としてマークするには、DEPRECATE
プラグマを使用します。
関連項目:
DEPRECATE
プラグマの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.7 PL/SQLサブプログラムおよびパッケージの削除
ストアド・スタンドアロン・サブプログラムを削除するには、次の文を使用します。
-
DROP
FUNCTION
-
DROP
PROCEDURE
パッケージ(仕様部および本体)、またはその本体のみを削除するには、DROP
PACKAGE
文を使用します。
関連項目:
-
DROP
FUNCTION
の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 -
DROP
PROCEDURE
の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 -
Oracle Database PL/SQL言語リファレンス
DROP
PACKAGE
13.8 システム固有の実行のためのPL/SQLユニットのコンパイル
通常、PL/SQLユニットをコンパイルして、システム固有のコード(プロセッサに依存するシステム・コード)にすると、PL/SQLユニット(独自のユニットまたはOracleが提供するユニット)をスピードアップできます。システム固有のコードは、SYSTEM表領域に格納されます。
ネイティブ・コードにコンパイルされたPL/SQLユニットは、共有サーバー構成(以前のマルチスレッド・サーバー)やOracle Real Application Clusters (Oracle RAC)などのすべてのサーバー環境で動作します。
PL/SQLユニットをネイティブ・コードでコンパイルするかどうかは、開発サイクルの段階およびPL/SQLユニットの内容によって決まります。
ノート:
システム固有の実行のためにJavaパッケージおよびクラスをコンパイルするには、ncomp
ツールを使用してください。
関連項目:
-
システム固有の実行のためのPL/SQLユニットのコンパイルの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.9 ストアドPL/SQLサブプログラムの起動
ストアドPL/SQLサブプログラムは、次のように様々な環境から起動できます。例:
-
Oracle Databaseのツール製品を使用した対話形式による起動
-
別のサブプログラム本体からの起動
-
トリガー本体からの起動
-
アプリケーション(SQL*Formsやプリコンパイラなど)の内部からの起動
ストアドPL/SQLファンクション(プロシージャではない)は、SQL文から起動することもできます。
別のユーザーが所有するサブプログラムを起動する場合は、次のようになります。
-
起動の際に所有者の名前を指定する必要があります。例:
EXECUTE jdoe.Fire_emp (1043); EXECUTE jdoe.Hire_fire.Fire_emp (1043);
-
サブプログラムの
AUTHID
プロパティは、実行時にサブプログラムによって発行されるSQL文の名前解決および権限チェックに影響します。
トピック:
関連項目:
-
AUTHID
プロパティ、サブプログラムの起動、パラメータ、および定義者と起動者の権限の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 -
トリガー本体のコーディングの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
OCIアプリケーションからのPL/SQLサブプログラムの起動方法の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください
-
Pro*C/C++からのPL/SQLサブプログラムの起動の詳細は、『Pro*C/C++プログラマーズ・ガイド』を参照してください
-
Pro*COBOLからのPL/SQLサブプログラムの起動の詳細は、『Pro*COBOLプログラマーズ・ガイド』を参照してください
-
JDBCアプリケーションからのPL/SQLサブプログラムの起動方法の詳細は、『Oracle Database JDBC開発者ガイド』を参照してください
13.9.1 ストアド・サブプログラムの起動に必要な権限
次のようなサブプログラムを起動する場合、権限は必要ありません。
-
所有しているスタンドアロン・サブプログラム
-
所有しているパッケージ内のサブプログラム
-
パブリック・スタンドアロン・サブプログラム
-
パブリック・パッケージ内のサブプログラム
別のユーザーが所有するストアド・サブプログラムを起動するには、スタンドアロン・サブプログラムまたはパッケージ・サブプログラムを含むパッケージのEXECUTE
権限、またはEXECUTE
ANY
PROCEDURE
システム権限が必要です。サブプログラムがリモートにある場合は、EXECUTE
権限またはEXECUTE
ANY
PROCEDURE
システム権限が、ロールを介してではなく直接付与されている必要があります。
13.9.2 Oracleツールを使用した対話形式によるサブプログラムの起動
サブプログラムは、SQL*Plusなど、Oracle Databaseのツール製品から対話形式で起動できます。
関連項目:
-
EXECUTE
コマンドの詳細は、『SQL*Plusユーザーズ・ガイドおよびリファレンス』を参照してください。 -
開発ツールを使用して同様の操作を実行する詳細は、ご使用のツール製品のドキュメントを参照してください。
例13-7では、SQL*Plusを使用してプロシージャを作成し、2つの方法で起動します。
一部の対話形式ツールでは、セッション中に使用できるセッション変数を作成することができます。例13-8では、SQL*Plusを使用してセッション変数の作成、使用および出力を行います。
例13-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.
例13-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
13.9.3 別のサブプログラムからのサブプログラムの起動
ストアド・サブプログラムは、別のサブプログラムやトリガーから起動できます。例13-9では、プロシージャprint_mgr_name
がプロシージャprint_emp_name
を起動します。
サブプログラムの再帰的な起動が可能です(つまり、サブプログラムがサブプログラム自体を起動できます)。
例13-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
13.9.4 リモート・サブプログラムの起動
リモート・サブプログラムは、起動者とは別のデータベースに格納されます。リモート・サブプログラムの起動には、サブプログラム名、サブプログラムが格納されているデータベースへのデータベース・リンク、およびすべての仮パラメータの実パラメータ(仮パラメータにデフォルト値がある場合でも)が含まれる必要があります。
たとえば、次のSQL*Plus文はストアド・スタンドアロン・プロシージャfire_emp1
を起動します。このプロシージャは、boston_server
という名前のローカル・データベース・リンクによって参照されています。
EXECUTE fire_emp1@boston_server(1043);
ノート:
リモート・パッケージ・サブプログラムは起動できますが、リモート・パッケージの変数および定数には直接アクセスできません。
注意:
-
リモート・サブプログラムの起動では、実行時バインディングが使用されます。接続するユーザー・アカウントは、データベース・リンクに依存します。(ストアド・サブプログラムではコンパイル時バインディングが使用されます。)
-
ローカル・サブプログラムがリモート・サブプログラムを起動する場合、ローカル・サブプログラムの実行中にタイムスタンプの不一致が検出されると、リモート・サブプログラムは実行されず、ローカル・サブプログラムが無効になります。詳細は、「ローカル・データベース・プロシージャとリモート・データベース・プロシージャの間の依存性」を参照してください。
トピック:
関連項目:
13.9.4.1 リモート・サブプログラムのシノニム
シノニムとは、スキーマ・オブジェクトの別名です。リモート・サブプログラム名とデータベース・リンクのシノニムを作成し、シノニムを使用してサブプログラムを起動することができます。例:
CREATE SYNONYM synonym1 for fire_emp1@boston_server; EXECUTE synonym1(1043);
ノート:
パッケージ・サブプログラムのシノニムを作成することはできません。これは、パッケージ・サブプログラムはスキーマ・オブジェクトではないためです(そのパッケージはスキーマ・オブジェクトです)。
シノニムによってデータの独立性と場所の透過性の両方が実現されます。シノニムを使用すると、サブプログラムの所有者や場所を知らなくてもサブプログラムを起動できます。ただし、シノニムは権限の代替物ではありません。シノニムを使用してサブプログラムを起動するには、サブプログラムに必須の権限が必要です。
シノニムに権限を付与することは、基本オブジェクトに権限を付与することと同じです。同様に、基本オブジェクトに対して権限を付与することは、オブジェクトのすべてのシノニムに権限を付与することと同じです。
プライベート・シノニムとパブリック・シノニムの両方を作成できます。プライベート・シノニムは自分のスキーマ内にあり、他のユーザーによるシノニムの使用を自分で制御できます。パブリック・シノニムはユーザー・グループPUBLIC
に属しており、すべてのデータベース・ユーザーがアクセスできます。
パブリック・シノニムではデータベースの統合が困難になるため、パブリック・シノニムは慎重に使用してください。
シノニムを使用しない場合は、リモート・サブプログラムを起動するローカル・サブプログラムを作成することもできます。例:
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; /
関連項目:
-
シノニムの詳細は、『Oracle Database概要』を参照してください。
-
パブリック・シノニムの例は、『Oracle Database概要』を参照してください。
-
CREATE
SYNONYM
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
GRANT
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
13.9.4.2 リモート・サブプログラムを起動するトランザクション
リモート・サブプログラムの起動により、データベースが更新されることが想定されます。したがって、リモート・サブプログラムを起動するトランザクションには、(リモート・サブプログラムによってデータベースが更新されない場合でも) 2フェーズ・コミットが必要です。トランザクションがロールバックされると、リモート・サブプログラムによって実行された処理もロールバックされます。
文COMMIT
、ROLLBACK
およびSAVEPOINT
に関しては、リモート・サブプログラムは次の点においてローカル・サブプログラムと異なります。
-
トランザクションがOracle Database以外のデータベースで開始された場合、リモート・サブプログラムでこれらの文は実行できません。
このような状況になるのはOracle XAアプリケーションなどの場合であり、このアプリケーションを使用することはお薦めしません。詳細は、「Oracle XAを使用したアプリケーションの開発」を参照してください。
-
これらの文の1つを実行した後、リモート・サブプログラムは独自の分散トランザクションを開始できなくなります。
分散トランザクションでは複数のデータベースが更新されます。トランザクション内の文は別のデータベースに送信され、トランザクションはユニットとして成功または失敗します。トランザクションが任意のトランザクションで失敗した場合、このトランザクションをすべてのデータベースで(セーブポイントまで、または完全に)ロールバックする必要があります。分散更新を実行するサブプログラムを作成する場合は、この点を考慮する必要があります。
-
リモート・サブプログラムがその作業をコミットまたはロールバックしない場合、データベース・リンクがクローズした時点で作業が暗黙的にコミットされます。それまでは、リモート・サブプログラムがトランザクションを実行しているとみなされます。したがって、リモート・サブプログラムに対するこれ以上の起動は許可されません。
13.10 SQL文からのストアドPL/SQLファンクションの起動
注意:
SQLは命令型(または手続き型)言語ではなく宣言型言語であるため、ファンクションが命令型言語のPL/SQLで記述されていても、SQL文によって起動されるファンクションが何回実行されるかはわかりません。
アプリケーションでファンクションが特定の回数実行されることが必要な場合には、SQL文からファンクションを起動しないでください。かわりにカーソルを使用します。
たとえば、選択した行ごとにファンクションをコールすることがアプリケーションで必要な場合は、カーソルをオープンして、カーソルから行を選択し、各行に対してファンクションをコールします。この技術により、ファンクションをコールする回数が、カーソルからフェッチされる行数と同じになります。
PL/SQLストアド・ファンクションを起動できるSQL文は次のとおりです。
-
INSERT
-
UPDATE
-
DELETE
-
SELECT
(
SELECT
は、WITH
句で宣言および定義されているPL/SQLファンクションを起動することもできます。 -
CALL
(
CALL
は、PL/SQLストアド・プロシージャを起動することもできます。)
PL/SQLファンクションをSQL文から起動するには、ファンクションに対するEXECUTE
権限を所有している必要があります。PL/SQLファンクションで定義されたビューを検索するには、そのビューのREAD
またはSELECT
権限が必要です。ビューを検索するには個々のEXECUTE
権限は必要ありません。
ノート:
AUTHID
はユニットが実行時に発行するSQL文の名前の解決と権限チェックに影響するため、PL/SQLファンクションのAUTHID
プロパティは、SQL文からファンクションを呼び出すのに必要な権限にも影響します。詳細は、「Oracle Database PL/SQ言語Lリファレンス」を参照してください。
トピック:
関連項目:
-
SELECT文の詳細は、『Oracle Database SQL言語リファレンス』
を参照してください。
-
パラメータの受渡しも含めた、サブプログラムの起動の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
13.10.1 SQL文からPL/SQLファンクションを起動する理由
SQL文でPL/SQLファンクションを起動することにより、次の効果があります。
-
SQLの拡張によって、ユーザーの生産性が向上します。
実行する内容がSQL文のみで表現するには複雑すぎたり、非常に扱いにくかったり、不可能な場合に、SQL文の表現機能が強化されます。
-
問合せの効率を向上します。
問合せの
WHERE
句にファンクションを指定すると、条件を使用してデータをフィルタできます。ファンクションを使用できない場合は、アプリケーションで評価する必要があります。 -
特殊なデータ型(緯度、経度、温度など)を表すための文字列を操作できます
-
パラレル問合せを実行できます。
問合せがパラレル化されると、PL/SQLサブプログラム内のSQL文も(パラレル問合せオプションを使用して)パラレルに実行できます。
13.10.2 SQL文の中でPL/SQLファンクションを使用できる箇所
SQL文では、PL/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
制約句 -
列に対するデフォルト値の指定
13.10.3 SQL式の中でPL/SQLファンクションを使用できる条件
PL/SQLファンクションをSQL式から起動するには、そのファンクションが次の要件を満たしている必要があります。
-
ユーザー定義の集計ファンクションまたは行ファンクションであること。
-
仮パラメータが、
OUT
パラメータやIN
OUT
パラメータではなく、IN
パラメータであること。
例13-10のファンクションは、これらの要件を満たしています。
例13-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; /
13.10.4 SQL文から起動したPL/SQLファンクションの副作用の制御
サブプログラムは、独自のローカル変数の値以外のものを変更する場合、副作用を伴います。たとえば、次のいずれかを変更するサブプログラムには、副作用があります。
-
独自の
OUT
またはIN
OUT
パラメータ -
グローバル変数
-
パッケージ内のパブリック変数
-
データベース表
-
データベース
-
外部の状態(たとえば、
DBMS_OUTPUT
の起動や電子メールの送信などによる変更)
副作用によって、問合せのパラレル処理が妨害されたり、処理順序に依存する(したがって、不確定な)結果が発生したり、ユーザー・セッションにまたがったパッケージ状態のメンテナンスが必要になります。
ファンクションがSQL問合せまたはDML文から起動される場合は、一部の副作用は受け入れられません。
Oracle Database 8gリリース1 (8.1)より前では、アプリケーション開発者はPRAGMA
RESTRICT_REFERENCES
を使用してファンクションの純粋度(副作用からの自由度)をアサートしていました。このプラグマは、下位互換用として使用可能のままですが、新しいアプリケーションでは使用しないでください。かわりに、ファンクションの作成時にはオプティマイザ・ヒントDETERMINISTIC
およびPARALLEL_ENABLE
を指定してください。
トピック:
13.10.4.1 SQL文から起動したファンクションの制限
ノート:
SQL文から起動したファンクションの制限は、SQL文によって起動されるトリガーにも適用されます。
SQL文がファンクションを起動し、ファンクションが新規SQL文を実行すると、新しい文の実行は、ファンクションを起動した文のコンテキストに論理的に埋め込まれます。このコンテキストで新しい文が安全であるようにするために、Oracle Databaseはファンクションに対して次の制限を課します。
-
ファンクションを起動するSQL文が問合せまたはDML文である場合、ファンクションは、現行のトランザクションの終了、セーブポイントの作成またはセーブポイントまでのロールバック、あるいはシステムまたはセッションの変更(
ALTER
)を実行できません。 -
ファンクションを起動するSQL文が問合せまたはパラレル化されたパラレルDML文である場合、ファンクションは、DML文を実行できないか、またはデータベースを変更できません。
-
ファンクションを起動するSQL文がDML文である場合、ファンクションは、ファンクションを起動したSQL文によって変更される表の読取りまたは変更を実行できません。
これらの制限は、ファンクションが新規SQL文を実行する方法とは関係なく適用されます。たとえば、これらは、ファンクションが次の処理を行う新規SQL文に適用されます。
-
ファンクションの本体に直接埋め込まれているかどうかにかかわらず、PL/SQLから起動し、
EXECUTE
IMMEDIATE
を使用して実行するか、またはDBMS_SQL
パッケージを使用して実行 -
JDBCを使用した実行
-
外部Cファンクション内からコールバック・コンテキストを使用してOCIで実行
これらの制約を回避するには、新しいSQL文の実行が、ファンクションを起動するSQL文のコンテキストに論理的に埋め込まれていない場合ことを確認してください。たとえば、新規SQL文を自律型トランザクションに配置するか、OCIで、OCIExtProcContext
引数によって提供されるハンドルを使用するかわりに外部Cファンクションに対する新規接続を作成します。
関連項目:
13.10.4.2 パラレルSQL文から起動したPL/SQLファンクション
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)より前では、データベースがファンクションをパラレル化可能として識別した場合。
13.10.4.3 PRAGMA RESTRICT_REFERENCES
ノート:
PRAGMA
RESTRICT_REFERENCES
は非推奨です。新規アプリケーションでは、RESTRICT_REFERENCES
のかわりにDETERMINISTIC
およびPARALLEL_ENABLE
(『Oracle Database SQL言語リファレンス』を参照)を使用することをお薦めします。
既存の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
は次のいずれかです。
アサーション | 意味 |
---|---|
|
ファンクションはパッケージ状態を読み込みません(パッケージ変数の値を参照しないということです) |
|
ファンクションはパッケージ状態を書き込みません(パッケージ変数の値を変更しないということです)。 |
|
ファンクションはデータベース状態を読み込みません(データベース表を問い合せないということです)。 |
|
ファンクションはデータベース状態を書き込みません(データベース表を変更しないということです)。 |
|
ファンクションに対して行われた任意のアサーションにファンクション本体内のSQL文が違反していないことを信頼します。詳細は、「アサーションTRUSTの指定」を参照してください。 |
TRUST
を指定せず、指定したアサーションにファンクション本体内のSQL文が違反すると、PL/SQLコンパイラは、違反している文の解析中にエラー・メッセージを表示します。
PL/SQLコンパイラによってファンクションが不要に拒否されることがないように、ファンクションで許可される最高の純粋度レベル(最上位アサーション)をアサートしてください。
ノート:
ファンクションがサブプログラムを起動する場合、これらのサブプログラムに対してもPRAGMA
RESTRICT_REFERENCES
を指定するか、起動する側のファンクションまたは起動される側のサブプログラムにTRUST
を指定してください。
トピック:
13.10.4.3.1 例: PRAGMA RESTRICT_REFERENCES
PL/SQLパッケージを作成する際には、PRAGMA RESTRICT_REFERENCES
句を使用できます。
例13-11では、データベースまたはパッケージ状態の読取りおよび書込みを行わず、それが最大の純粋度レベルであることをアサートするファンクションを作成します。
例13-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; /
13.10.4.3.2 アサーションTRUSTの指定
PRAGMA
RESTRICT
REFERENCES
によってTRUST
が指定されると、PL/SQLコンパイラはサブプログラム本体に違反があるかどうかを確認しません。
TRUST
を使用すると、PRAGMA
RESTRICT
REFERENCES
を使用するサブプログラムが、これを使用しないサブプログラムを起動しやすくなります。
PL/SQLサブプログラムがCまたはJavaサブプログラムを起動する場合、PL/SQLサブプログラム(例13-12を参照)またはCまたはJavaサブプログラム(例13-13を参照)に対してTRUST
を指定する必要があります。これは、PL/SQLコンパイラは実行時にCまたはJavaサブプログラムに違反があるかどうかを確認できないためです。
例13-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; /
例13-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; /
13.10.4.3.3 静的SQL文と動的SQL文の違い
静的な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';
13.11 ストアド・サブプログラムの分析とデバッグ
ストアド・サブプログラムをコンパイルするには、コードの構文エラーを修正する必要があります。サブプログラムが正常に実行され、パフォーマンスも最適であり、エラーが修正されていることを確認するには、追加のデバッグを行う必要があります。次のようなデバッグが考えられます。
-
出力文を追加して、実行処理の検証およびサブプログラム内の任意の点でのデータ値のチェックをする。
変数または式の値を出力するには、Oracleパッケージ
DBMS_OUTPUT
内のPUT
およびPUT_LINE
サブプログラムを使用します。 -
PL/Scope、PL/SQL階層プロファイラまたはデバッガの実行によって、プログラムとその実行をより詳細に分析する
トピック:
関連項目:
-
PL/SQLサブプログラムおよびパッケージのエラー処理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください
-
DBMS_OUTPUT
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
13.11.1 PL/Scope
PL/Scopeによって、強力で効率的なPL/Scopeソース・コード・ツールの開発が可能になります。このツールは、ソース・コードの参照および理解に費やされる時間を最小限にすることによって、PL/SQL開発者の生産性を向上させます。
PL/Scopeの詳細は、「PL/Scopeの使用」を参照してください。
13.11.2 PL/SQL階層プロファイラ
PL/SQL階層プロファイラは、サブプログラム・コール別に編成されたPL/SQLプログラムの動的実行プロファイルをレポートします。SQL実行時間およびPL/SQL実行時間が個別に説明されます。動的実行プロファイルにおけるサブプログラム・レベルの各サマリーには、サブプログラムへのコール数、サブプログラム自体に要した時間、サブプログラムのサブツリー(つまり依存サブプログラム)に要した時間、詳細な親子情報などが表示されます。
生成されたHTMLレポートは任意のブラウザで参照できます。ブラウザのナビゲーション機能と厳選したリンクを組み合せた効率的な手段により、大規模なアプリケーションのパフォーマンスを分析し、アプリケーションのパフォーマンスを向上させ、開発コストを削減できます。
関連項目:
PL/SQL階層プロファイラの詳細は、「PL/SQL階層プロファイラの使用」を参照してください。
13.11.3 PL/SQLおよびJavaのデバッグ
データベース内のPL/SQLとJavaコードは、Oracle SQL Developer、Oracle JDeveloperおよび各種のサード・パーティ・ツールを使用してデバッグできます。DBMS_DEBUG_JDWPパッケージを使用すると、データベース・セッションとこれらのデバッガ・プログラム間の接続を確立できます。
セッションに接続して別のセッションからデバッグすることで、長時間実行しているテストや運用環境で発生する問題を調査できます。セッションのデバッグ中に、有効範囲内変数の状態を調べることができます。また、デバッグされるセッションでコミットされていないトランザクション中にデータベースの状態を参照する際に、データベースの状態を調べることができます。ブレークポイントで停止すると、デバッグを実行しているユーザーはSQLコマンドを発行して、必要に応じて匿名ブロックのストアドPL/SQLサブプログラムを呼び出すPL/SQLコードを実行できます。
関連項目:
-
関数とプロシージャの実行とデバッグの詳細は、『Oracle SQL Developerユーザーズ・ガイド』を参照してください。
-
Java Debug Wire Protocol (JDWP) PL/SQLデバッガの使用の詳細は、『Oracle Database Java開発者ガイド』を参照してください。
-
DBMS_DEBUG_JDWP
パッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。 -
V$PLSQL_DEBUGGABLE_SESSIONS
ビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
13.11.3.1 デバッグ用のコードのコンパイル
デバッガは、生成されたデバッグ情報とともにコンパイルされたコードでのみ、個々のコード行で停止したり、変数にアクセスできます。
生成されたデバッグ情報とともにPL/SQLユニットをコンパイルするには、コンパイル・パラメータPLSQL_OPTIMIZE_LEVEL
(デフォルト値は2)を1に設定します。
ノート:
PL/SQLコンパイラでは、PL/SQLのwrap
ユーティリティにより非表示になっているコードに対しては、デバッグ情報は生成されません。
関連項目:
-
wrapユーティリティの詳細は、『Oracle Database PL/SQL言語リファレンス』
を参照してください。
-
PL/SQLユニットの詳細は、PL/SQLユニットの概要を参照してください。
13.11.3.2 PL/SQLストアド・サブプログラムおよびJavaストアド・サブプログラムのデバッグ権限
デバッガに接続するセッションでは、DEBUG CONNECT SESSION
、DEBUG CONNECT ANY
または適切なDEBUG CONNECT ON USER
権限を所有するユーザーが接続操作を実行する必要があります。接続コールに関係するDRサブプログラムの所有者であるユーザーも、この操作を実行できます。
セッションがデバッガに接続されると、セッション・ログイン・ユーザーおよび有効なセッション・レベルのロールが、このデバッグ用の接続の権限環境として決定されます。デバッグに必要なすべての権限を、関連コードでこのユーザーとロールの組合せに付与する必要があります。これらの権限は、次のとおりです。
-
PL/SQLパッケージ仕様で宣言された変数またはJavaパブリック変数の表示および変更:
EXECUTE
またはDEBUG
。 -
プライベート変数の表示および変更、またはコード行をブレークポイントまでおよびステップごとに実行するには:
DEBUG
注意:
DEBUG
権限を付与することにより、デバッグ対象のサブプログラムが実行するようにコーディングされている処理をデバッグ・セッションで実行することが可能になります。
DEBUG
ANY
PROCEDURE
システム権限の付与は、データベース内のすべてのオブジェクトにDEBUG
権限を付与することと同じです。O7_DICTIONARY_ACCESSIBILITY
パラメータの値がTRUE
である場合は、SYS
が所有しているオブジェクトにも付与します。
注意:
SYS
が所有するオブジェクトにDEBUG
ANY
PROCEDURE
権限またはDEBUG
権限を付与すると、すべての権限がデータベースに付与されます。
関連項目:
-
システム権限およびオブジェクト権限の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
-
Javaサブプログラムのデバッグ権限の詳細は、『Oracle Database Java開発者ガイド』を参照してください
-
DBMS_DEBUG_JDWPパッケージのセキュリティ・モデルの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
13.12 パッケージの無効化とセッションの状態
パッケージ・オブジェクトを参照する各セッションには、対応するパッケージの独自のインスタンスがあり、この中には、パブリック変数、プライベート変数、カーソルおよび定数に対する持続状態が含まれます。セッションのインスタンス化されたパッケージ(仕様部または本体)のいずれかが無効化されると、そのセッションのすべてのパッケージ・インスタンスが無効になり、再コンパイルされます。これにより、そのセッションのすべてのパッケージ・インスタンスに関するセッション状態は失われます。
セッションのパッケージが無効である場合、セッションが無効なパッケージ・インスタンスのオブジェクトを最初に使用しようとしたときに、ORA-04068が戻されます。2度目にセッションがこのようなパッケージ・コールを行うと、エラーは発生せずに、パッケージはセッションに対して再インスタンス化されます。ただし、このエラーをアプリケーションで処理する場合は、次の点に注意してください。
-
パフォーマンスを最適な状態に保つため、Oracle Databaseがこのエラー・メッセージを戻すのは、パッケージ状態が破棄されるとき1回のみです。あるパッケージ内のサブプログラムにより別のパッケージ内のサブプログラムが起動される場合、両方のパッケージに対するセッション状態が失われます。
-
サーバー・セッションがORA-04068をトラップした場合、クライアント・セッションに対してORA-04068は発生しません。したがって、このクライアント・セッションがパッケージ内のオブジェクトを使用しようとすると、このパッケージは再インスタンス化されません。パッケージを再インスタンス化するには、クライアント・セッションをデータベースに再接続するか、またはクライアント・セッションでパッケージを再コンパイルする必要があります。
本番環境の多くでは、パッケージが無効になるDDL操作は、通常、業務時間外に行われるため、エンドユーザー・アプリケーションでは、このような状況は問題にならない可能性もあります。しかし、パッケージが業務時間中に無効になることがよくある場合は、パッケージ・コールが行われたときにこのエラーを処理するように、アプリケーションを作成することが必要になります。
13.13 例: ORA-04068エラーの呼出し
RAISE
句を使用して例外を呼び出すことができます。
例13-14では、RAISE
文により、処理中の例外ORA-06508の原因である現在の例外ORA-04068が呼び出されます。ORA-04068は検出されません。
例13-14 ORA-04068の呼出し
PROCEDURE p IS
package_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (package_exception, -6508);
BEGIN
...
EXCEPTION
WHEN package_exception THEN
RAISE;
END;
/
13.14 例: ORA-04068の検出
パッケージ定義でRAISE
文を使用してエラーを検出できます。
例13-15では、RAISE
文により、ORA-06508に応えて、現在の例外ORA-04068ではなく、例外ORA-20001が呼び出されます。ORA-04068が検出されます。このような場合、ORA-04068エラーはマスクされ、これにより、パッケージの再インスタンス化が停止します。
例13-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;
/