8 PL/SQLサブプログラム
PL/SQLのサブプログラムは、繰り返し起動できる名前付きPL/SQLブロックです。サブプログラムにパラメータが含まれている場合は、起動のたびにパラメータ値を変えることができます。
サブプログラムは、プロシージャまたはファンクションのいずれかです。通常、プロシージャはアクションを実行するために使用し、ファンクションは計算を行って値を戻すために使用します。
ここでのトピック
8.1 サブプログラムを使用する理由
サブプログラムでは、次の特長に基づく信頼性の高い再利用可能なコードの開発およびメンテナンスがサポートされます。
-
モジュール性
サブプログラムを使用すると、プログラムを管理の容易な、正しく定義されたモジュールに分けることができます。
-
アプリケーションの設計の容易さ
アプリケーションを設計する場合、メイン・プログラムのテストが終了するまでサブプログラムの実装の詳細を遅らせ、段階的に各サブプログラムを改良できます。(実装の詳細なしでサブプログラムを定義するには、例4-35に示すとおり、
NULL
文を使用します。) -
メンテナンス性
起動元を変更することなく、サブプログラムの実装の詳細を変更できます。
-
パッケージ性
サブプログラムは、パッケージにグループ化できます。そのメリットの詳細は、「パッケージを使用する理由」を参照してください。
-
再利用性
多くの異なる環境において、任意の数のアプリケーションで、同じパッケージ・サブプログラムまたはスタンドアロン・サブプログラムを使用できます。
-
より高いパフォーマンス
各サブプログラムはコンパイルされてから実行可能な形式で格納され、繰り返し起動することができます。ストアド・サブプログラムはデータベース・サーバー内で実行されるため、ネットワークを介した1回の起動で大規模なジョブを開始できます。この作業の分割によってネットワークの通信量が軽減され、応答時間が短縮されます。ストアド・サブプログラムはキャッシュされ、ユーザー間で共有されるため、メモリー要件と起動のオーバーヘッドが低減します。
サブプログラムは、パッケージ(「PL/SQLパッケージ」を参照)や抽象データ型(「抽象データ型」を参照)などのメンテナンス性を向上させる他の機能の重要なコンポーネントです。
8.2 ネストしたサブプログラム、パッケージ・サブプログラムおよびスタンドアロン・サブプログラム
サブプログラムは、PL/SQLブロック内(別のサブプログラムの可能性もあります)、パッケージ内またはスキーマ・レベルで作成できます。
PL/SQLブロック内で作成されるサブプログラムは、ネストしたサブプログラムです。サブプログラムは、宣言と定義を同時に行うか、または最初に宣言して、後で同じブロック内で定義することができます(「前方宣言」を参照)。ネストしたサブプログラムは、スタンドアロン・サブプログラムまたはパッケージ・サブプログラム内でネストした場合にのみ、データベースに格納されます。
パッケージ内で作成されるサブプログラムは、パッケージ・サブプログラムです。パッケージ仕様部で宣言し、パッケージ本体で定義します。パッケージを削除するまでデータベースに格納されます。(パッケージについては、「PL/SQLパッケージ」を参照してください。)
スキーマ・レベルで作成されるサブプログラムは、スタンドアロン・サブプログラムです。CREATE
FUNCTION
文またはCREATE
PROCEDURE
文を使用して作成できます。DROP
FUNCTION
文またはDROP
PROCEDURE
文を使用して削除するまでデータベースに格納されます。(これらの文の詳細は、「ストアドPL/SQLユニット用のSQL文」を参照してください。)
ストアド・サブプログラムは、パッケージ・サブプログラムまたはスタンドアロン・サブプログラムのいずれかです。ストアド・サブプログラムはAUTHID
およびACCESSIBLE
BY
句の影響を受けます。これらはCREATE
FUNCTION
、CREATE
PROCEDURE
およびCREATE
PACKAGE
文で使用されます。AUTHID
句はサブプログラムが実行時に発行するSQL文の名前解決と権限チェックに影響します(詳細は「実行者権限および定義者権限(AUTHIDプロパティ)」)を参照してください。ACCESSIBLE
BY
句は、サブプログラムにアクセスできるPL/SQLユニットのホワイト・リストを指定します。
8.3 サブプログラムの起動
サブプログラムの起動の形式は次のとおりです。
subprogram_name [ ( [ parameter [, parameter]... ] ) ]
サブプログラムにパラメータがない場合、またはサブプログラムによってすべてのパラメータにデフォルト値が指定される場合は、パラメータ・リストを省略するかまたは空のパラメータ・リストを指定できます。
プロシージャの起動はPL/SQL文です。次に例を示します。
raise_salary(employee_id, amount);
ファンクションの起動は式です。次に例を示します。
new_salary := get_salary(employee_id); IF salary_ok(new_salary, new_title) THEN ...
関連項目:
サブプログラムの起動でパラメータを指定する方法の詳細は、「サブプログラムのパラメータ」を参照してください
8.4 サブプログラムのプロパティ
サブプログラムの各プロパティは、サブプログラムの宣言で1回のみ記述できます。プロパティは任意の順序で記述できます。プロパティは、サブプログラムのヘッダー内のIS
またはAS
キーワードの前に記述します。プロパティは、ネストしたサブプログラムには記述できません。
パッケージ・サブプログラムに記述できるのは、ACCESSIBLE BY
プロパティのみです。スタンドアロン・サブプログラムの宣言には次のプロパティを使用できます。
8.5 サブプログラムの各部
サブプログラムの先頭には、その名前およびオプションのパラメータ・リストを指定するサブプログラムのヘッダーがあります。
無名ブロックと同様に、サブプログラムには次の部分があります。
-
宣言部(オプション)
この部分では、ローカル型、カーソル、定数、変数、例外およびネストしたサブプログラムを宣言および定義します。これらの項目は、サブプログラムの実行が完了すると消滅します。
この部分は、プラグマを指定することもできます。
注意:
無名ブロックの宣言部とは異なり、サブプログラムの宣言部は、キーワード
DECLARE
では始まりません。 -
実行部(必須)
この部分には、値の代入、実行の制御およびデータの操作を実行する1つ以上の文が含まれています。(アプリケーション設計プロセスの初期段階では、例4-35に示すとおり、この部分に
NULL
文のみを含めることも可能です。) -
例外処理部(オプション)
この部分には、ランタイム・エラーを処理するコードが含まれています。
ここでのトピック
関連項目:
-
プロシージャ宣言と定義の構文は、「プロシージャの宣言および定義」を参照してください
-
サブプログラムのパラメータの詳細は、「サブプログラムのパラメータ」を参照してください
例8-1 単純なPL/SQLプロシージャの宣言、定義および起動
この例では、無名ブロックでプロシージャの宣言と定義を同時に行い、そのプロシージャを3回起動しています。3回目の起動では、プロシージャの例外処理部で処理される例外が呼び出されています。
DECLARE first_name employees.first_name%TYPE; last_name employees.last_name%TYPE; email employees.email%TYPE; employer VARCHAR2(8) := 'AcmeCorp'; -- Declare and define procedure PROCEDURE create_email ( -- Subprogram heading begins name1 VARCHAR2, name2 VARCHAR2, company VARCHAR2 ) -- Subprogram heading ends IS -- Declarative part begins error_message VARCHAR2(30) := 'Email address is too long.'; BEGIN -- Executable part begins email := name1 || '.' || name2 || '@' || company; EXCEPTION -- Exception-handling part begins WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE(error_message); END create_email; BEGIN first_name := 'John'; last_name := 'Doe'; create_email(first_name, last_name, employer); -- invocation DBMS_OUTPUT.PUT_LINE ('With first name first, email is: ' || email); create_email(last_name, first_name, employer); -- invocation DBMS_OUTPUT.PUT_LINE ('With last name first, email is: ' || email); first_name := 'Elizabeth'; last_name := 'MacDonald'; create_email(first_name, last_name, employer); -- invocation END; /
結果:
With first name first, email is: John.Doe@AcmeCorp With last name first, email is: Doe.John@AcmeCorp Email address is too long.
8.5.1 ファンクションの追加部分
ファンクションの構造は、プロシージャと同じですが、次の点が異なります。
-
ファンクションのヘッダーには、ファンクションが戻す値のデータ型を指定する
RETURN
句が含まれている必要があります。(プロシージャのヘッダーには、RETURN
句を含めることはできません。) -
ファンクションの実行部では、すべての実行パスが
RETURN
文へ導かれる必要があります。そうではない場合、PL/SQLコンパイラによってコンパイル時に警告が発行されます。(プロシージャでは、RETURN
文はオプションであり、推奨されません。詳細は、「RETURN文」を参照してください。) -
ファンクションの宣言には、次に示すオプションを含めることができます。
オプション | 説明 |
---|---|
|
オプティマイザが冗長なファンクションの起動を回避するために役立ちます。 |
|
ファンクションのパラレル実行を可能にし、パラレルDML評価のスレーブ・セッションで安全に使用できるようにします。 |
|
行ソースとして使用するためにテーブル・ファンクションをパイプライン化します。 |
|
PL/SQLファンクション結果キャッシュに、ファンクションの結果を格納します。 |
関連項目:
-
前述の表内の項目の説明を含む、ファンクション宣言と定義の構文は、「ファンクションの宣言および定義」を参照してください
-
RESULT_CACHE
オプションの詳細は、「PL/SQLファンクション結果キャッシュ」を参照してください
例8-2 単純なPL/SQLファンクションの宣言、定義および起動
この例では、無名ブロックでファンクションの宣言と定義を同時に行い、そのファンクションを起動しています。
DECLARE -- Declare and define function FUNCTION square (original NUMBER) -- parameter list RETURN NUMBER -- RETURN clause AS -- Declarative part begins original_squared NUMBER; BEGIN -- Executable part begins original_squared := original * original; RETURN original_squared; -- RETURN statement END; BEGIN DBMS_OUTPUT.PUT_LINE(square(100)); -- invocation END; /
結果:
10000
8.5.2 RETURN文
RETURN
文は、サブプログラムまたはこの文を含む無名ブロックの実行を即座に終了させます。サブプログラムまたは無名ブロックでは、複数のRETURN
文を使用できます。
ここでのトピック
関連項目:
RETURN
文の構文は、「RETURN文」を参照してください
8.5.2.1 ファンクションのRETURN文
ファンクションでは、すべての実行パスがRETURN
文に続き、すべてのRETURN
文が式を指定している必要があります。RETURN
文は、ファンクション識別子に式の値を代入し、起動元に制御を戻します(ここで、起動直後に実行が再開します)。
注意:
パイプライン・テーブル・ファンクションでは、RETURN
文で式を指定する必要はありません。パイプライン・テーブル・ファンクションの各部の詳細は、「パイプライン・テーブル・ファンクションの作成」を参照してください。
例8-3では、無名ブロックで同じファンクションを2回起動しています。1回目では、RETURN
文は起動元の文の内部に制御を戻します。2回目では、RETURN
文は起動元の文の直後の文に制御を戻します。
例8-4では、ファンクションに複数のRETURN
文が含まれますが、パラメータが0または1でない場合、実行パスがRETURN
文へ導かれません。このファンクションは、コンパイル時に「PLW-05005: サブプログラムFは、行11に値なしで戻ります」という警告が発生します。
例8-5は、例8-4と同様ですが、ELSE
句が追加されています。すべての実行パスがRETURN
文へ導かれるため、ファンクションのコンパイル時に警告PLW-05005は発生しません。
例8-3 ファンクション内にあるRETURN文の後での実行の再開
DECLARE x INTEGER; FUNCTION f (n INTEGER) RETURN INTEGER IS BEGIN RETURN (n*n); END; BEGIN DBMS_OUTPUT.PUT_LINE ( 'f returns ' || f(2) || '. Execution returns here (1).' ); x := f(2); DBMS_OUTPUT.PUT_LINE('Execution returns here (2).'); END; /
結果:
f returns 4. Execution returns here (1).Execution returns here (2).
例8-4 RETURN文へ導かれない実行パスを含むファンクション
CREATE OR REPLACE FUNCTION f (n INTEGER) RETURN INTEGER AUTHID DEFINER IS BEGIN IF n = 0 THEN RETURN 1; ELSIF n = 1 THEN RETURN n; END IF; END; /
例8-5 すべての実行パスがRETURN文へ導かれるファンクション
CREATE OR REPLACE FUNCTION f (n INTEGER)
RETURN INTEGER
AUTHID DEFINER
IS
BEGIN
IF n = 0 THEN
RETURN 1;
ELSIF n = 1 THEN
RETURN n;
ELSE
RETURN n*n;
END IF;
END;
/
BEGIN
FOR i IN 0 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE('f(' || i || ') = ' || f(i));
END LOOP;
END;
/
結果:
f(0) = 1 f(1) = 1 f(2) = 4 f(3) = 9
8.5.2.2 プロシージャのRETURN文
プロシージャでは、RETURN
文は起動元に制御を戻します。ここで、起動すると実行が即座に再開します。RETURN
文では、式を指定できません。
例8-6では、RETURN
文は、起動元の文の直後の文に制御を戻しています。
例8-6 プロシージャ内にあるRETURN文の後での実行の再開
DECLARE PROCEDURE p IS BEGIN DBMS_OUTPUT.PUT_LINE('Inside p'); RETURN; DBMS_OUTPUT.PUT_LINE('Unreachable statement.'); END; BEGIN p; DBMS_OUTPUT.PUT_LINE('Control returns here.'); END; /
結果:
Inside p Control returns here.
8.5.2.3 無名ブロックのRETURN文
無名ブロックでは、RETURN
文によってそのブロックおよびすべての外側のブロックが終了されます。RETURN
文では、式を指定できません。
例8-7では、RETURN
文によって、内側と外側の両方のブロックが終了されています。
例8-7 無名ブロック内にあるRETURN文の後での実行の再開
BEGIN BEGIN DBMS_OUTPUT.PUT_LINE('Inside inner block.'); RETURN; DBMS_OUTPUT.PUT_LINE('Unreachable statement.'); END; DBMS_OUTPUT.PUT_LINE('Inside outer block. Unreachable statement.'); END; /
結果:
Inside inner block.
8.6 前方宣言
サブプログラムは起動の前に宣言する必要があるため、同じPL/SQLブロック内のネストしたサブプログラムが相互に起動し合う場合は、そのサブプログラムに前方宣言が必要です。
前方宣言では、ネストしたサブプログラムを宣言しますが、定義はしません。同じブロックで後から定義する必要があります。前方宣言および定義は、同じサブプログラム・ヘッダーを持つ必要があります。
例8-8では、無名ブロックによって、相互に起動し合う2つのプロシージャを作成しています。
例8-8 相互に起動し合うネストしたサブプログラム
DECLARE -- Declare proc1 (forward declaration): PROCEDURE proc1(number1 NUMBER); -- Declare and define proc2: PROCEDURE proc2(number2 NUMBER) IS BEGIN proc1(number2); END; -- Define proc 1: PROCEDURE proc1(number1 NUMBER) IS BEGIN proc2 (number1); END; BEGIN NULL; END; /
8.7 サブプログラムのパラメータ
サブプログラムにパラメータが含まれている場合は、起動のたびにパラメータ値を変えることができます。
ここでのトピック
8.7.1 サブプログラムの仮パラメータと実パラメータ
サブプログラムにパラメータを保持する場合、サブプログラムのヘッダーで仮パラメータを宣言します。仮パラメータの宣言ごとに、パラメータの名前とデータ型を指定し、オプションでそのモードとデフォルト値を指定します。サブプログラムの実行部で、仮パラメータをその名前によって参照します。
サブプログラムを起動する際に、仮パラメータに代入する値が含まれる実パラメータを指定します。対応する実パラメータと仮パラメータのデータ型には、互換性が必要です。
注意:
次のように、制約付きサブタイプの仮パラメータを宣言できます。
DECLARE SUBTYPE n1 IS NUMBER(1); SUBTYPE v1 IS VARCHAR2(1); PROCEDURE p (n n1, v v1) IS ...
ただし、次のように、仮パラメータの宣言内に制約を含めることはできません。
DECLARE PROCEDURE p (n NUMBER(1), v VARCHAR2(1)) IS ...
ヒント:
混同を避けるため、仮パラメータと実パラメータには異なる名前を使用してください。
注意:
-
実際のパラメータ(仮パラメータのデフォルト値を含む)はどのような順序でも評価できます。プログラムにより評価の順序が決定される場合は、プログラムで決定が行われる時点で動作は定義されません。
-
サーバー間リモート・プロシージャ・コール(RPC)ではLOBパラメータは使用できません。
例8-9では、プロシージャに仮パラメータのemp_id
およびamount
が含まれます。プロシージャの最初の起動では、対応する実パラメータはemp_num
(値120)およびbonus
(値100)です。プロシージャの2回目の起動では、実パラメータはemp_num
(値120)およびmerit
+ bonus
(値150)です。
トピック:
関連項目:
-
仮パラメータの宣言の構文およびセマンティクスは、「仮パラメータの宣言」を参照してください
-
ファンクションの起動の構文およびセマンティクスは、「function_call ::=」および「function_call」を参照してください
-
プロシージャの起動の構文およびセマンティクスは、procedure_call ::=およびprocedureを参照してください
例8-9 仮パラメータと実パラメータ
DECLARE emp_num NUMBER(6) := 120; bonus NUMBER(6) := 100; merit NUMBER(4) := 50; PROCEDURE raise_salary ( emp_id NUMBER, -- formal parameter amount NUMBER -- formal parameter ) IS BEGIN UPDATE employees SET salary = salary + amount -- reference to formal parameter WHERE employee_id = emp_id; -- reference to formal parameter END raise_salary; BEGIN raise_salary(emp_num, bonus); -- actual parameters /* raise_salary runs this statement: UPDATE employees SET salary = salary + 100 WHERE employee_id = 120; */ raise_salary(emp_num, merit + bonus); -- actual parameters /* raise_salary runs this statement: UPDATE employees SET salary = salary + 150 WHERE employee_id = 120; */ END; /
8.7.1.1 制約付きサブタイプの仮パラメータ
仮パラメータのデータ型が制約付きサブタイプの場合、次のことに注意してください。
-
サブタイプに
NOT
NULL
制約が含まれる場合、実パラメータはその制約を継承します。 -
サブタイプにベース型
VARCHAR2
が含まれる場合、実パラメータはサブタイプのサイズを継承しません。 -
サブタイプに数値ベース型が含まれる場合、実パラメータはサブタイプの範囲を継承しますが、精度または位取りは継承しません。
注意:
ファンクションでは、RETURN
datatype
句で非表示仮パラメータを宣言し、RETURN
value
文で対応する実パラメータを指定します。したがって、datatype
が制約付きデータ型の場合は、前述の規則がvalue
に適用されます(例8-11を参照)。
例8-10で、サブプログラムの実パラメータは、NOT
NULL
制約を継承していますが、VARCHAR2
サブタイプのサイズは継承していません。
「PL/SQLの事前定義のデータ型」に示されているように、PL/SQLには、他のデータ型の制約付きサブタイプである多くの事前定義のデータ型が用意されています。たとえば、INTEGER
はNUMBER
の制約付きサブタイプです。
SUBTYPE INTEGER IS NUMBER(38,0);
例8-11では、ファンクションに仮パラメータINTEGER
と戻り値INTEGER
の両方が含まれています。無名ブロックは、整数でない実パラメータを使用してファンクションを起動します。実パラメータはINTEGER
の範囲を継承しますが、精度と位取りは継承せず、また実パラメータはINTEGER
の範囲内にあるため、起動が成功します。同じ理由で、RETURN
文は正常に非整数値を戻します。
例8-12では、ファンクションは仮パラメータを戻す前に、その仮パラメータを制約付きサブタイプINTEGER
に暗黙的に変換しています。
関連項目:
制約付きサブタイプの一般情報は、「制約付きサブタイプ」を参照してください
例8-10 サブタイプからNOT NULLのみを継承する実パラメータ
DECLARE SUBTYPE License IS VARCHAR2(7) NOT NULL; n License := 'DLLLDDD'; PROCEDURE p (x License) IS BEGIN DBMS_OUTPUT.PUT_LINE(x); END; BEGIN p('1ABC123456789'); -- Succeeds; size is not inherited p(NULL); -- Raises error; NOT NULL is inherited END; /
結果:
p(NULL); -- Raises error; NOT NULL is inherited * ERROR at line 12: ORA-06550: line 12, column 5: PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter ORA-06550: line 12, column 3: PL/SQL: Statement ignored
例8-11 サブタイプから範囲のみを継承する実パラメータと戻り値
DECLARE
FUNCTION test (p INTEGER) RETURN INTEGER IS
BEGIN
DBMS_OUTPUT.PUT_LINE('p = ' || p);
RETURN p;
END test;
BEGIN
DBMS_OUTPUT.PUT_LINE('test(p) = ' || test(0.66));
END;
/
結果:
p = .66 test(p) = .66 PL/SQL procedure successfully completed.
例8-12 ファンクションによる仮パラメータから制約付きサブタイプへの暗黙的な変換
DECLARE FUNCTION test (p NUMBER) RETURN NUMBER IS q INTEGER := p; -- Implicitly converts p to INTEGER BEGIN DBMS_OUTPUT.PUT_LINE('p = ' || q); -- Display q, not p RETURN q; -- Return q, not p END test; BEGIN DBMS_OUTPUT.PUT_LINE('test(p) = ' || test(0.66)); END; /
結果:
p = 1 test(p) = 1 PL/SQL procedure successfully completed.
8.7.2 サブプログラムのパラメータの引渡し方法
PL/SQLコンパイラには、実パラメータをサブプログラムに引き渡す方法として次の2つがあります。
-
参照渡し
コンパイラは、実パラメータを指すポインタをサブプログラムに渡します。実パラメータと仮パラメータは、同じメモリー位置を参照します。
-
値渡し
コンパイラは、実パラメータの値を対応する仮パラメータに代入します。実パラメータと仮パラメータは、異なるメモリー位置を参照します。
コンパイラは、必要に応じて実パラメータのデータ型を仮パラメータのデータ型に暗黙的に変換します。暗黙的なデータ変換の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
ヒント:
(『Oracle Database SQL言語リファレンス』で説明されている理由のために)暗黙的なデータ変換を回避するには、次のいずれかの方法を使用します。
-
実パラメータとして使用する予定の変数を、それに対応する仮パラメータと同じデータ型で宣言します(例8-13の変数
x
の宣言を参照)。 -
『Oracle Database SQL言語リファレンス』で説明されているSQL変換ファンクションを使用して、実パラメータをそれに対応する仮パラメータのデータ型に明示的に変換します(例8-13の3回目のプロシージャの起動を参照)。
-
例8-13では、プロシージャp
に、値によって渡される1つのパラメータn
が含まれています。無名ブロックは、p
を3回起動していますが、そのうち2回は暗黙的な変換を回避しています。
コンパイラで特定の実パラメータが渡される方法は、そのモードによって異なります(「サブプログラムのパラメータ・モード」を参照)。
例8-13 実パラメータの暗黙的な変換の回避
CREATE OR REPLACE PROCEDURE p ( n NUMBER ) AUTHID DEFINER IS BEGIN NULL; END; / DECLARE x NUMBER := 1; y VARCHAR2(1) := '1'; BEGIN p(x); -- No conversion needed p(y); -- z implicitly converted from VARCHAR2 to NUMBER p(TO_NUMBER(y)); -- z explicitly converted from VARCHAR2 to NUMBER END; /
8.7.3 サブプログラムのパラメータ・モード
仮パラメータのモードによって、その動作が決定されます。
表8-1に、サブプログラムのパラメータ・モードの特性の概要および比較を示します。
表8-1 PL/SQLサブプログラムのパラメータ・モード
パラメータ・モード | デフォルト | ロール |
---|---|---|
IN |
デフォルト・モード |
サブプログラムに値を渡します。 |
OUT |
指定する必要があります。 |
起動元に値を戻します。 |
IN OUT |
指定する必要があります。 |
サブプログラムに初期値を渡し、更新された値を起動元に戻します。 |
表8-2 PL/SQLサブプログラムのパラメータ・モードの特性
パラメータ・モード | 仮パラメータ | 実パラメータ | 参照渡し |
---|---|---|---|
IN |
仮パラメータは定数のように動作します。サブプログラムが起動すると、その値は実パラメータまたはデフォルト値のいずれかの値となり、サブプログラムではその値を変更できません。 |
実パラメータには定数、リテラル、初期化された変数または式が使用できます。 |
実パラメータは参照によって渡されます。 |
OUT |
仮パラメータはその型のデフォルト値に初期化されます。 サブプログラムが起動すると、実パラメータの値にかかわらず、仮パラメータの値は初期値になります。サブプログラムで仮パラメータに値を割り当てることをお薦めします。 |
仮パラメータの型のデフォルト値が |
デフォルトでは、実パラメータは値によって渡されます。 |
IN OUT |
仮パラメータは初期化された変数のように動作します(サブプログラムが起動すると、その値は実パラメータの値になります)。サブプログラムで値を更新することをお薦めします。 |
実パラメータは、変数である必要があります(通常、文字列バッファまたは数値アキュムレータです)。 |
デフォルトでは、実パラメータは値によって渡されます(どちらの向きに対しても)。 |
ヒント:
ファンクションのパラメータには、OUT
およびIN
OUT
を使用しないでください。ファンクションは、0(ゼロ)個以上のパラメータを取り、単一の値を戻すことが理想です。IN
OUT
パラメータを持つファンクションは、複数の値を戻すため、副作用が発生します。
注意:
Oracle Database提供の多くのパッケージと型の仕様部では、次の表記法を使用して仮パラメータを宣言します。
i1 IN VARCHAR2 CHARACTER SET ANY_CS i2 IN VARCHAR2 CHARACTER SET i1%CHARSET
独自の仮パラメータまたは実パラメータを宣言する場合は、この表記法を使用しないでください。これは、提供されているパッケージ・タイプのOracle実装のために予約されています。
OUT
またはIN
OUT
パラメータが渡される方法にかかわらず、次のようになります。
-
サブプログラムが正常に終了した場合、実パラメータの値が、仮パラメータに代入される最終的な値になります。(仮パラメータには、少なくとも1つの値(初期値)が代入されます。)
-
サブプログラムが例外によって終了した場合、実パラメータの値は定義されません。
-
OUT
およびIN
OUT
仮パラメータは任意の順序で返されます。次の例では、x
およびy
の最終的な値は定義されていません。CREATE OR REPLACE PROCEDURE p (x OUT INTEGER, y OUT INTEGER) AS BEGIN x := 17; y := 93; END; /
OUT
またはIN
OUT
パラメータが参照によって渡される場合、実パラメータと仮パラメータは同じメモリー位置を参照します。そのため、サブプログラムによって仮パラメータの値が変更されると、その変更は実パラメータに即座に反映されます(「参照渡しパラメータによるサブプログラムのパラメータのエイリアシング」を参照)。
例8-14では、プロシージャp
に2つのIN
パラメータ、1つのOUT
パラメータおよび1つのIN
OUT
パラメータが含まれます。OUT
パラメータとIN
OUT
パラメータは、値によって渡されます(デフォルト)。無名ブロックは、異なる実パラメータを使用してp
を2回起動しています。各起動の前に、無名ブロックは実パラメータの値を出力します。プロシージャp
は、仮パラメータの初期値を出力します。各起動の後にも、無名ブロックは実パラメータの値を出力します。
例8-15では、無名ブロックがプロシージャp
(例8-14を参照)を起動していますが、その実パラメータによってp
で事前定義の例外ZERO_DIVIDE
が呼び出されます(p
はこの例外を処理しません)。例外は無名ブロックに伝播され、無名ブロックは、ZERO_DIVIDE
を処理し、p
のIN
およびIN
OUT
パラメータの実パラメータが起動前に保持していた値を現在も維持していることを示します。(例外の伝播の詳細は、「例外の伝播」を参照してください。)
例8-16では、プロシージャp
に3つのOUT
仮パラメータがあります(NULL
以外のデフォルト値を持つレコード型のx
、NULL
以外のデフォルト値を持たないレコード型のy
、およびレコードではないz
)。
x
、y
およびz
の対応する実パラメータはそれぞれ、r1
、r2
およびs
です。s
は初期値を使用して宣言されます。ただし、p
が起動されると、s
の値はNULL
に初期化されます。r1
およびr2
の値は、それらのレコード型のデフォルト値(それぞれabcde
、NULL
)に初期化されます。
例8-14 プロシージャの起動前、起動中および起動後のパラメータ値
CREATE OR REPLACE PROCEDURE p ( a PLS_INTEGER, -- IN by default b IN PLS_INTEGER, c OUT PLS_INTEGER, d IN OUT BINARY_FLOAT ) AUTHID DEFINER IS BEGIN -- Print values of parameters: DBMS_OUTPUT.PUT_LINE('Inside procedure p:'); DBMS_OUTPUT.PUT('IN a = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(a), 'NULL')); DBMS_OUTPUT.PUT('IN b = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(b), 'NULL')); DBMS_OUTPUT.PUT('OUT c = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(c), 'NULL')); DBMS_OUTPUT.PUT_LINE('IN OUT d = ' || TO_CHAR(d)); -- Can reference IN parameters a and b, -- but cannot assign values to them. c := a+10; -- Assign value to OUT parameter d := 10/b; -- Assign value to IN OUT parameter END; / DECLARE aa CONSTANT PLS_INTEGER := 1; bb PLS_INTEGER := 2; cc PLS_INTEGER := 3; dd BINARY_FLOAT := 4; ee PLS_INTEGER; ff BINARY_FLOAT := 5; BEGIN DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:'); DBMS_OUTPUT.PUT('aa = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), 'NULL')); DBMS_OUTPUT.PUT('bb = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), 'NULL')); DBMS_OUTPUT.PUT('cc = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), 'NULL')); DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd)); p (aa, -- constant bb, -- initialized variable cc, -- initialized variable dd -- initialized variable ); DBMS_OUTPUT.PUT_LINE('After invoking procedure p:'); DBMS_OUTPUT.PUT('aa = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), 'NULL')); DBMS_OUTPUT.PUT('bb = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), 'NULL')); DBMS_OUTPUT.PUT('cc = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), 'NULL')); DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd)); DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:'); DBMS_OUTPUT.PUT('ee = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ee), 'NULL')); DBMS_OUTPUT.PUT_LINE('ff = ' || TO_CHAR(ff)); p (1, -- literal (bb+3)*4, -- expression ee, -- uninitialized variable ff -- initialized variable ); DBMS_OUTPUT.PUT_LINE('After invoking procedure p:'); DBMS_OUTPUT.PUT('ee = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(ee), 'NULL')); DBMS_OUTPUT.PUT_LINE('ff = ' || TO_CHAR(ff)); END; /
結果:
Before invoking procedure p: aa = 1 bb = 2 cc = 3 dd = 4.0E+000 Inside procedure p: IN a = 1 IN b = 2 OUT c = NULL IN OUT d = 4.0E+000 After invoking procedure p: aa = 1 bb = 2 cc = 11 dd = 5.0E+000 Before invoking procedure p: ee = NULL ff = 5.0E+000 Inside procedure p: IN a = 1 IN b = 20 OUT c = NULL IN OUT d = 5.0E+000 After invoking procedure p: ee = 11 ff = 5.0E-001 PL/SQL procedure successfully completed.
例8-15 例外処理後のOUTおよびIN OUTパラメータの値
DECLARE j PLS_INTEGER := 10; k BINARY_FLOAT := 15; BEGIN DBMS_OUTPUT.PUT_LINE('Before invoking procedure p:'); DBMS_OUTPUT.PUT('j = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(j), 'NULL')); DBMS_OUTPUT.PUT_LINE('k = ' || TO_CHAR(k)); p(4, 0, j, k); -- causes p to exit with exception ZERO_DIVIDE EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('After invoking procedure p:'); DBMS_OUTPUT.PUT('j = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(j), 'NULL')); DBMS_OUTPUT.PUT_LINE('k = ' || TO_CHAR(k)); END; /
結果:
Before invoking procedure p: j = 10 k = 1.5E+001 Inside procedure p: IN a = 4 IN b = 0 OUT c = NULL IN OUT d = 1.5E+001 After invoking procedure p: j = 10 k = 1.5E+001 PL/SQL procedure successfully completed.
例8-16 NULL以外のデフォルト値を持つレコード型のOUT仮パラメータ
CREATE OR REPLACE PACKAGE r_types AUTHID DEFINER IS TYPE r_type_1 IS RECORD (f VARCHAR2(5) := 'abcde'); TYPE r_type_2 IS RECORD (f VARCHAR2(5)); END; / CREATE OR REPLACE PROCEDURE p ( x OUT r_types.r_type_1, y OUT r_types.r_type_2, z OUT VARCHAR2) AUTHID CURRENT_USER IS BEGIN DBMS_OUTPUT.PUT_LINE('x.f is ' || NVL(x.f,'NULL')); DBMS_OUTPUT.PUT_LINE('y.f is ' || NVL(y.f,'NULL')); DBMS_OUTPUT.PUT_LINE('z is ' || NVL(z,'NULL')); END; / DECLARE r1 r_types.r_type_1; r2 r_types.r_type_2; s VARCHAR2(5) := 'fghij'; BEGIN p (r1, r2, s); END; /
結果:
x.f is abcde y.f is NULL z is NULL PL/SQL procedure successfully completed.
8.7.4 サブプログラムのパラメータのエイリアシング
エイリアシングとは、同じメモリー位置に対して2つの異なる名前が存在することです。格納されている項目が複数のパスで表示可能な場合は、1つのパスでその項目を変更してからすべてのパスで変更を確認できます。
サブプログラムのパラメータのエイリアシングは、コンパイラが参照によって実パラメータを渡す場合に常に発生し、サブプログラムにカーソル変数パラメータが含まれる場合にも発生する可能性があります。
ここでのトピック
8.7.4.1 参照渡しパラメータによるサブプログラムのパラメータのエイリアシング
コンパイラが参照によって実パラメータを渡す場合、実パラメータと仮パラメータは同じメモリー位置を参照します。そのため、サブプログラムによって仮パラメータの値が変更されると、その変更は実パラメータに即座に反映されます。
コンパイラは常に参照によってIN
パラメータを渡しますが、サブプログラムはIN
パラメータに値を代入できないため、この場合のエイリアシングでは問題は発生しません。
コンパイラは、OUT
またはIN
OUT
パラメータにNOCOPY
が指定されると、そのパラメータを参照によって渡す可能性があります。NOCOPY
は単なるヒントで、コンパイラは、サブプログラムが起動されるたびに、NOCOPY
に従うか無視するかを暗黙的に決定します。したがって、エイリアシングの発生の有無は起動ごとに異なる可能性があるため、サブプログラムの結果は予測不能です。次に例を示します。
-
実パラメータがグローバル変数である場合、仮パラメータに対する代入は、グローバル・パラメータに反映される可能性があります(例8-17を参照)。
-
同じ変数が2つの仮パラメータの実パラメータである場合、いずれかの仮パラメータに対する代入は、両方の仮パラメータに即座に反映される可能性があります(例8-18を参照)。
-
実パラメータがパッケージ変数である場合、仮パラメータまたはパッケージ変数に対する代入は、仮パラメータとパッケージ変数の両方に即座に反映される可能性があります。
-
サブプログラムが未処理例外を戻して終了した場合、仮パラメータに対する代入は、実パラメータに反映される可能性があります。
関連項目:
コンパイラが常にNOCOPY
を無視する事例は、「NOCOPY」を参照してください
例8-17では、プロシージャにIN
OUT
NOCOPY
仮パラメータが含まれており、プロシージャはそのパラメータに値'aardvark'
を代入します。無名ブロックは、グローバル変数に値'aardwolf'
を代入し、そのグローバル変数をプロシージャに渡します。コンパイラがNOCOPY
ヒントに従う場合、グローバル変数の最終的な値は、'aardvark'
です。コンパイラがNOCOPY
ヒントを無視する場合、グローバル変数の最終的な値は、'aardwolf'
です。
例8-18では、プロシージャにIN
パラメータ、IN
OUT
パラメータおよびIN
OUT
NOCOPY
パラメータが含まれます。無名ブロックは、3つのすべての仮パラメータに同じ実パラメータ(グローバル変数)を使用してプロシージャを起動します。プロシージャは、IN
OUT
パラメータの値を変更してからIN
OUT
NOCOPY
パラメータの値を変更します。ただし、コンパイラがNOCOPY
ヒントに従う場合、後者の変更は、実パラメータに即座に反映されます。前者の変更は、プロシージャが正常に終了して制御が無名ブロックに戻された後に実パラメータに反映されます。
例8-17 実パラメータとしてのグローバル変数によるエイリアシング
DECLARE
TYPE Definition IS RECORD (
word VARCHAR2(20),
meaning VARCHAR2(200)
);
TYPE Dictionary IS VARRAY(2000) OF Definition;
lexicon Dictionary := Dictionary(); -- global variable
PROCEDURE add_entry (
word_list IN OUT NOCOPY Dictionary -- formal NOCOPY parameter
) IS
BEGIN
word_list(1).word := 'aardvark';
END;
BEGIN
lexicon.EXTEND;
lexicon(1).word := 'aardwolf';
add_entry(lexicon); -- global variable is actual parameter
DBMS_OUTPUT.PUT_LINE(lexicon(1).word);
END;
/
結果:
aardvark
例8-18 複数の仮パラメータに対する同じ実パラメータによるエイリアシング
DECLARE n NUMBER := 10; PROCEDURE p ( n1 IN NUMBER, n2 IN OUT NUMBER, n3 IN OUT NOCOPY NUMBER ) IS BEGIN n2 := 20; -- actual parameter is 20 only after procedure succeeds DBMS_OUTPUT.put_line(n1); -- actual parameter value is still 10 n3 := 30; -- might change actual parameter immediately DBMS_OUTPUT.put_line(n1); -- actual parameter value is either 10 or 30 END; BEGIN p(n, n, n); DBMS_OUTPUT.put_line(n); END; /
コンパイラがNOCOPY
ヒントに従う場合の結果:
10 30 20
コンパイラがNOCOPY
ヒントを無視する場合の結果:
10 10 30
8.7.4.2 カーソル変数パラメータによるサブプログラムのパラメータのエイリアシング
カーソル変数パラメータは、ポインタです。したがって、サブプログラムがあるカーソル変数パラメータを別のカーソル変数パラメータに代入すると、それらのパラメータは同じメモリー位置を参照します。このエイリアシングでは、意図しない結果が発生することがあります。
例8-19では、プロシージャに2つのカーソル変数パラメータ(emp_cv1
およびemp_cv2
)が含まれます。プロシージャは、emp_cv1
をオープンし、その値(ポインタ)をemp_cv2
に代入します。この場合、emp_cv1
とemp_cv2
は、同じメモリー位置を参照します。プロシージャがemp_cv1
をクローズすると、emp_cv2
もクローズされます。そのため、プロシージャがemp_cv2
からフェッチしようとすると、PL/SQLによって例外が呼び出されます。
例8-19 カーソル変数サブプログラム・パラメータによるエイリアシング
DECLARE TYPE EmpCurTyp IS REF CURSOR; c1 EmpCurTyp; c2 EmpCurTyp; PROCEDURE get_emp_data ( emp_cv1 IN OUT EmpCurTyp, emp_cv2 IN OUT EmpCurTyp ) IS emp_rec employees%ROWTYPE; BEGIN OPEN emp_cv1 FOR SELECT * FROM employees; emp_cv2 := emp_cv1; -- now both variables refer to same location FETCH emp_cv1 INTO emp_rec; -- fetches first row of employees FETCH emp_cv1 INTO emp_rec; -- fetches second row of employees FETCH emp_cv2 INTO emp_rec; -- fetches third row of employees CLOSE emp_cv1; -- closes both variables FETCH emp_cv2 INTO emp_rec; -- causes error when get_emp_data is invoked END; BEGIN get_emp_data(c1, c2); END; /
結果:
DECLARE * ERROR at line 1: ORA-01001: invalid cursor ORA-06512: at line 19 ORA-06512: at line 22
8.7.5 サブプログラムのINパラメータのデフォルト値
IN
仮パラメータを宣言する場合、そのパラメータにデフォルト値を指定できます。デフォルト値を持つ仮パラメータは、その対応する実パラメータがサブプログラムの起動においてオプションであるため、オプション・パラメータと呼ばれます。実パラメータが省略されると、起動によって仮パラメータにデフォルト値が代入されます。デフォルト値を持たない仮パラメータは、その対応する実パラメータがサブプログラムの起動において必須であるため、必須パラメータと呼ばれます。
実パラメータを省略しても、対応する仮パラメータの値はNULL
になりません。仮パラメータの値をNULL
にするには、デフォルト値または実パラメータとしてNULL
を指定します。
例8-20では、プロシージャに1つの必須パラメータと2つのオプション・パラメータが含まれます。
例8-20では、プロシージャの起動によって、実パラメータが、それと対応する仮パラメータの宣言と同じ順序で指定されます(起動では位置表記法が使用されます)。位置表記法では、raise_salary
の2番目のパラメータを省略せずに、3番目のパラメータを指定します(途中のパラメータを省略するには、名前表記法または混合表記法を使用する必要があります)。詳細は、実パラメータの位置表記法、名前表記法および混合表記法を参照してください。
仮パラメータのデフォルト値には、パラメータに値を代入できる任意の式を指定できます(値およびパラメータのデータ型には互換性が必要です)。サブプログラムの起動で仮パラメータに実パラメータが指定されると、その起動ではデフォルト値が評価されません。
例8-21では、プロシージャp
に、ファンクションf
を起動するデフォルト値を持つパラメータが含まれます。ファンクションf
は、グローバル変数の値を増分します。p
が実パラメータなしで起動されると、p
はf
を起動し、f
はグローバル変数を増分します。p
が実パラメータ付きで起動されると、p
はf
を起動せず、グローバル変数の値は変化しません。
例8-22では、2つの必須パラメータが含まれるプロシージャを作成してそれを起動し、その後、3番目のオプション・パラメータを追加しています。3番目のパラメータはオプションであるため、元の起動も引き続き有効です。
例8-20 デフォルトのパラメータ値を使用したプロシージャ
DECLARE PROCEDURE raise_salary ( emp_id IN employees.employee_id%TYPE, amount IN employees.salary%TYPE := 100, extra IN employees.salary%TYPE := 50 ) IS BEGIN UPDATE employees SET salary = salary + amount + extra WHERE employee_id = emp_id; END raise_salary; BEGIN raise_salary(120); -- same as raise_salary(120, 100, 50) raise_salary(121, 200); -- same as raise_salary(121, 200, 50) END; /
例8-21 ファンクションによるデフォルトのパラメータ値の提供
DECLARE global PLS_INTEGER := 0; FUNCTION f RETURN PLS_INTEGER IS BEGIN DBMS_OUTPUT.PUT_LINE('Inside f.'); global := global + 1; RETURN global * 2; END f; PROCEDURE p ( x IN PLS_INTEGER := f() ) IS BEGIN DBMS_OUTPUT.PUT_LINE ( 'Inside p. ' || ' global = ' || global || ', x = ' || x || '.' ); DBMS_OUTPUT.PUT_LINE('--------------------------------'); END p; PROCEDURE pre_p IS BEGIN DBMS_OUTPUT.PUT_LINE ( 'Before invoking p, global = ' || global || '.' ); DBMS_OUTPUT.PUT_LINE('Invoking p.'); END pre_p; BEGIN pre_p; p(); -- default expression is evaluated pre_p; p(100); -- default expression is not evaluated pre_p; p(); -- default expression is evaluated END; /
結果:
Before invoking p, global = 0. Invoking p. Inside f. Inside p. global = 1, x = 2. -------------------------------- Before invoking p, global = 1. Invoking p. Inside p. global = 1, x = 100. -------------------------------- Before invoking p, global = 1. Invoking p. Inside f. Inside p. global = 2, x = 4. --------------------------------
例8-22 既存の起動を変更しないサブプログラム・パラメータの追加
プロシージャの作成:
CREATE OR REPLACE PROCEDURE print_name ( first VARCHAR2, last VARCHAR2 ) AUTHID DEFINER IS BEGIN DBMS_OUTPUT.PUT_LINE(first || ' ' || last); END print_name; /
プロシージャの起動:
BEGIN print_name('John', 'Doe'); END; /
結果:
John Doe
デフォルト値を持つ3番目のパラメータの追加:
CREATE OR REPLACE PROCEDURE print_name (
first VARCHAR2,
last VARCHAR2,
mi VARCHAR2 := NULL
) AUTHID DEFINER IS
BEGIN
IF mi IS NULL THEN
DBMS_OUTPUT.PUT_LINE(first || ' ' || last);
ELSE
DBMS_OUTPUT.PUT_LINE(first || ' ' || mi || '. ' || last);
END IF;
END print_name;
/
プロシージャの起動:
BEGIN print_name('John', 'Doe'); -- original invocation print_name('John', 'Public', 'Q'); -- new invocation END; /
結果:
John Doe John Q. Public
8.7.6 実パラメータの位置表記法、名前表記法および混合表記法
サブプログラムの起動時に、位置表記法、名前表記法または混合表記法のいずれかを使用して実パラメータを指定できます。表8-3に、これらの表記法の概要および比較を示します。
表8-3 PL/SQLの実パラメータの表記法
表記法 | 構文 | オプションのパラメータ | メリット | デメリット |
---|---|---|---|---|
位置 |
仮パラメータが宣言されている順序と同じ順序で実パラメータを指定します。 |
後続のオプション・パラメータを省略できます。 |
実パラメータを間違った順序で指定すると(特に実パラメータがリテラルの場合)、検出が困難な問題が発生する可能性があります。 仮パラメータのリストが変更される場合、そのリストで新しい後続のオプション・パラメータのみが取得されるのでなければ、サブプログラムの起動を変更する必要があります(例8-22を参照)。 コードの明瞭性とメンテナンス性が下がります。サブプログラムに多数のパラメータがある場合はお薦めしません。 |
|
名前 |
次の構文を使用して、任意の順序で実パラメータを指定します。 formal => actual
|
任意のオプション・パラメータを省略できます。 |
実パラメータを指定する場合の間違った順序はありません。 仮パラメータのリストで新しい必須パラメータが取得される場合にのみ、サブプログラムの起動を変更する必要があります。 自分以外のユーザーによって定義または管理されているサブプログラムを起動する場合に推奨されます。 |
|
混合 |
まず位置表記法を使用し、その後、残りのパラメータに名前表記法を使用します。 |
位置表記法では、後続のオプション・パラメータを省略できます。名前表記法では、任意のオプション・パラメータを省略できます。 |
必須パラメータの後にオプション・パラメータが続くサブプログラムを起動し、オプション・パラメータを数個のみ指定する必要がある場合に便利です。 |
位置表記法では、間違った順序で指定すると(特に実パラメータがリテラルの場合)、検出が困難な問題が発生する可能性があります。 仮パラメータのリストが変更される場合、位置表記法の変更が必要になる可能性があります。 |
例8-23では、プロシージャの起動で異なる表記法を使用していますが、同じ意味です。
例8-24では、異なる表記法による同等の起動を使用して、SQLのSELECT
文でPL/SQLファンクションcompute_bonus
を起動しています。
例8-23 無名ブロックでの異なる表記法を使用した同等な起動
DECLARE emp_num NUMBER(6) := 120; bonus NUMBER(6) := 50; PROCEDURE raise_salary ( emp_id NUMBER, amount NUMBER ) IS BEGIN UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; END raise_salary; BEGIN -- Equivalent invocations: raise_salary(emp_num, bonus); -- positional notation raise_salary(amount => bonus, emp_id => emp_num); -- named notation raise_salary(emp_id => emp_num, amount => bonus); -- named notation raise_salary(emp_num, amount => bonus); -- mixed notation END; /
例8-24 SELECT文での異なる表記法を使用した同等な起動
CREATE OR REPLACE FUNCTION compute_bonus ( emp_id NUMBER, bonus NUMBER ) RETURN NUMBER AUTHID DEFINER IS emp_sal NUMBER; BEGIN SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; RETURN emp_sal + bonus; END compute_bonus; / SELECT compute_bonus(120, 50) FROM DUAL; -- positional SELECT compute_bonus(bonus => 50, emp_id => 120) FROM DUAL; -- named SELECT compute_bonus(120, bonus => 50) FROM DUAL; -- mixed
8.8 サブプログラムの起動の解決
PL/SQLコンパイラは、サブプログラムの起動を検出すると、まず現在の有効範囲内で一致するサブプログラムの宣言を検索し、次に必要に応じて連続する外側の有効範囲内を検索します。
サブプログラムの名前およびパラメータ・リストが一致した場合に、宣言と起動は一致します。パラメータ・リストは、宣言内の必須仮パラメータがそれぞれ起動内で対応する実パラメータを持つ場合に一致します。
コンパイラが起動に対して一致する宣言を見つけられない場合、セマンティック・エラーが生成されます。
図8-1に、PL/SQLコンパイラがサブプログラムの起動を解決する方法を示します。
例8-25では、ファンクションbalance
が、適切な実パラメータを使用して、外側のプロシージャswap
を起動しようとしています。ただし、balance
には、swap
という2つのネストしたプロシージャがありますが、どちらにも外側のプロシージャswap
と同じ型のパラメータが存在しません。このため、この起動によって、コンパイル・エラーPLS-00306が発生します。
例8-25 PL/SQLプロシージャ名の解決
DECLARE PROCEDURE swap ( n1 NUMBER, n2 NUMBER ) IS num1 NUMBER; num2 NUMBER; FUNCTION balance (bal NUMBER) RETURN NUMBER IS x NUMBER := 10; PROCEDURE swap ( d1 DATE, d2 DATE ) IS BEGIN NULL; END; PROCEDURE swap ( b1 BOOLEAN, b2 BOOLEAN ) IS BEGIN NULL; END; BEGIN -- balance swap(num1, num2); RETURN x; END balance; BEGIN -- enclosing procedure swap NULL; END swap; BEGIN -- anonymous block NULL; END; -- anonymous block /
結果:
swap(num1, num2); * ERROR at line 33: ORA-06550: line 33, column 7: PLS-00306: wrong number or types of arguments in call to 'SWAP' ORA-06550: line 33, column 7: PL/SQL: Statement ignored
8.9 オーバーロードされたサブプログラム
PL/SQLでは、ネストしたサブプログラム、パッケージ・サブプログラムおよび型のメソッドをオーバーロードできます。仮パラメータの名前、数、順序またはデータ型のファミリが異なっている場合、同じ名前を複数のサブプログラムで使用できます。(データ型ファミリは、データ型とそのサブタイプです。事前定義のPL/SQLデータ型のデータ型ファミリは、「PL/SQLの事前定義のデータ型」を参照してください。ユーザー定義のPL/SQLサブタイプの詳細は、「ユーザー定義のPL/SQLサブタイプ」を参照してください。)仮パラメータの違いが名前のみである場合、名前表記法を使用して対応する実パラメータを指定する必要があります。(名前表記法の詳細は、実パラメータの位置表記法、名前表記法および混合表記法を参照してください。)
例8-26では、同じ名前(initialize
)を持つ2つのサブプログラムを定義しています。プロシージャでは様々な型のコレクションを初期化します。これらのプロシージャは同じ処理を実行しているため、同じ名前を与えるのが論理的です。
この2つのinitialize
プロシージャは、同じブロック、サブプログラム、パッケージまたは型本体の中に置くことができます。PL/SQLは仮パラメータをチェックして、どちらのプロシージャを起動するかを判断します。PL/SQLが使用するinitialize
のバージョンは、プロシージャをdate_tab_typ
パラメータまたはnum_tab_typ
パラメータのどちらで起動するかによって異なります。
パッケージ内のオーバーロードされたプロシージャの例は、例10-9を参照してください。
ここでのトピック
例8-26 オーバーロードされたサブプログラム
DECLARE TYPE date_tab_typ IS TABLE OF DATE INDEX BY PLS_INTEGER; TYPE num_tab_typ IS TABLE OF NUMBER INDEX BY PLS_INTEGER; hiredate_tab date_tab_typ; sal_tab num_tab_typ; PROCEDURE initialize (tab OUT date_tab_typ, n INTEGER) IS BEGIN DBMS_OUTPUT.PUT_LINE('Invoked first version'); FOR i IN 1..n LOOP tab(i) := SYSDATE; END LOOP; END initialize; PROCEDURE initialize (tab OUT num_tab_typ, n INTEGER) IS BEGIN DBMS_OUTPUT.PUT_LINE('Invoked second version'); FOR i IN 1..n LOOP tab(i) := 0.0; END LOOP; END initialize; BEGIN initialize(hiredate_tab, 50); initialize(sal_tab, 100); END; /
結果:
Invoked first version Invoked second version
8.9.1 数値データ型のみが異なる仮パラメータ
サブプログラムの仮パラメータの違いが数値データ型のみの場合、それらのサブプログラムはオーバーロードできます。ファンクションの複数のバージョンが同じ名前を使用でき、それぞれが異なる数値型を受け取ることができるため、この手法は数値演算Application Programming Interface(API)を記述する場合に有効です。たとえば、BINARY_FLOAT
を受け取るファンクションはより高速で、BINARY_DOUBLE
を受け取るファンクションは精度がより高くなる場合があります。
オーバーロードされたサブプログラムにパラメータを渡す場合、次のことに注意して、問題または予期しない結果を回避します。
-
予期されるパラメータ・セットごとに、目的のバージョンのサブプログラムが起動されることを確認します。
たとえば、オーバーロードされるファンクションが
BINARY_FLOAT
およびBINARY_DOUBLE
を受け取る場合、「5.0」
のようなVARCHAR2
リテラルを渡すと、どちらのファンクションが起動されるかを確認します。 -
数値リテラルを修飾したり、変換ファンクションを使用して、目的のパラメータ型を明示します。
たとえば、
5.0f
(BINARY_FLOAT
)、5.0d
(BINARY_DOUBLE
)のようなリテラル、またはTO_BINARY_FLOAT
、TO_BINARY_DOUBLE
、TO_NUMBER
のような変換ファンクションを使用します。
PL/SQLは、次の順序で、一致する数値パラメータを検索します。
-
PLS_INTEGER
(または同じデータ型であるBINARY_INTEGER
) -
NUMBER
-
BINARY_FLOAT
-
BINARY_DOUBLE
VARCHAR2
値は、NUMBER
、BINARY_FLOAT
またはBINARY_DOUBLE
パラメータに一致します。
PL/SQLは、指定されたパラメータに最初に一致してオーバーロードされるサブプログラムを使用します。たとえば、SQRT
ファンクションは1つのパラメータを受け取ります。NUMBER
、BINARY_FLOAT
またはBINARY_DOUBLE
パラメータを受け取るオーバーロードされるファンクションがあるとします。PLS_INTEGER
パラメータを渡した場合、最初に一致してオーバーロードされるファンクションは、NUMBER
パラメータを持つファンクションです。
NUMBER
パラメータを受け取るSQRT
ファンクションは、最も低速となる可能性が高くなります。より高速なバージョンを使用するには、パラメータをSQRT
ファンクションに渡す前に、TO_BINARY_FLOAT
またはTO_BINARY_DOUBLE
ファンクションを使用して、そのパラメータを別のデータ型に変換します。
PL/SQLは、パラメータを別のデータ型に変換する必要がある場合、まずそのパラメータをより上位のデータ型に変換しようとします。次に例を示します。
-
ATAN2
ファンクションは、同じ型の2つのパラメータを受け取ります。異なる型のパラメータを渡した場合(たとえば、1つがPLS_INTEGER
で、1つがBINARY_FLOAT
の場合)、PL/SQLは、両方のパラメータでより高度な型が使用されている場合に一致するものを検索します。この例では、2つのBINARY_FLOAT
パラメータを受け取るバージョンのATAN2
が使用され、PLS_INTEGER
パラメータは、上位変換されます。 -
あるファンクションは、異なる型の2つのパラメータを受け取ります。オーバーロードされるバージョンの1つは、
PLS_INTEGER
パラメータおよびBINARY_FLOAT
パラメータを受け取ります。別のオーバーロードされるバージョンは、NUMBER
パラメータおよびBINARY_DOUBLE
パラメータを受け取ります。このファンクションを起動して、2つのNUMBER
パラメータを渡した場合、PL/SQLは、まず2つ目のパラメータがBINARY_FLOAT
のオーバーロードされるバージョンを検出します。このパラメータは、他方のオーバーロードされるバージョンのBINARY_DOUBLE
パラメータよりも一致度が高いため、次にPL/SQLは下位検索し、1つ目のNUMBER
パラメータをPLS_INTEGER
に変換します。
8.9.2 オーバーロードできないサブプログラム
次のサブプログラムはオーバーロードできません。
-
スタンドアロン・サブプログラム
-
仮パラメータの違いがモードのみのサブプログラム。次に例を示します。
PROCEDURE s (p IN VARCHAR2) IS ... PROCEDURE s (p OUT VARCHAR2) IS ...
-
仮パラメータの違いがサブタイプのみのサブプログラム。次に例を示します。
PROCEDURE s (p INTEGER) IS ... PROCEDURE s (p REAL) IS ...
INTEGER
およびREAL
は、NUMBER
のサブタイプであるため、同じデータ型のファミリに属しています。 -
戻り値のデータ型のみが異なるファンクション(そのデータ型のファミリが異なっている場合でも)。次に例を示します。
FUNCTION f (p INTEGER) RETURN BOOLEAN IS ... FUNCTION f (p INTEGER) RETURN INTEGER IS ...
8.9.3 サブプログラムのオーバーロード・エラー
PL/SQLコンパイラは、起動されたサブプログラムを判別できないと判断するとすぐに、オーバーロード・エラーを捕捉します。複数のサブプログラムに同一のヘッダーがある場合、サブプログラム自体のコンパイル(サブプログラムがネストされている場合)、またはサブプログラムを宣言しているパッケージ仕様部のコンパイルを試行すると、コンパイラはオーバーロード・エラーを捕捉します。それ以外の場合、サブプログラムの曖昧な起動のコンパイルを試行すると、コンパイラはエラーを捕捉します。
例8-27に示す、同一のヘッダーを持つ複数のサブプログラムを宣言しているパッケージ仕様部をコンパイルしようとすると、コンパイル時エラーPLS-00305が発生します。
例8-28に示すパッケージ仕様部は、仮パラメータの違いがサブタイプのみのサブプログラムはオーバーロードできないという規則に違反していますが、エラーを生成せずにコンパイルできます。
ただし、例8-29に示すようにpkg2
.s
の起動をコンパイルしようとすると、コンパイル時エラーPLS-00307が発生します。
例8-30のように、オーバーロードされたサブプログラムの仮パラメータに別の名前を付けることによって、例8-28に示されているオーバーロード・エラーを修正するとします。
これによって、名前表記法を使用して実パラメータを指定する場合に、エラーを生成せずにpkg2
.s
の起動をコンパイルできます(例8-31を参照)。(例8-29に示されているように、位置表記法を使用して実パラメータを指定すると、コンパイル時エラーPLS-00307が発生します。)
例8-32に示すパッケージ仕様部は、オーバーロード規則に違反していないため、エラーを生成せずにコンパイルできます。ただし、オーバーロードされたプロシージャを起動すると、例8-33の2つ目の起動に示すように、コンパイル時エラーPLS-00307が発生します。
どのサブプログラムが起動されたかを特定しようとしたとき、PL/SQLコンパイラは、暗黙的にあるパラメータを一致するタイプに変換すると、暗黙的に一致するタイプに変換できる別のパラメータを検索します。複数の一致がある場合、例8-34に示すように、コンパイル時エラーPLS-00307が発生します。
例8-27 コンパイル時エラーの原因となるオーバーロード・エラー
CREATE OR REPLACE PACKAGE pkg1 AUTHID DEFINER IS PROCEDURE s (p VARCHAR2); PROCEDURE s (p VARCHAR2); END pkg1; /
例8-28 正常にコンパイルが行われるオーバーロード・エラー
CREATE OR REPLACE PACKAGE pkg2 AUTHID DEFINER IS SUBTYPE t1 IS VARCHAR2(10); SUBTYPE t2 IS VARCHAR2(10); PROCEDURE s (p t1); PROCEDURE s (p t2); END pkg2; /
例8-29 コンパイル時エラーの原因となる例8-28のサブプログラムの起動
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS
a pkg2.t1 := 'a';
BEGIN
pkg2.s(a); -- Causes compile-time error PLS-00307
END p;
/
例8-30 例8-28のオーバーロード・エラーの修正
CREATE OR REPLACE PACKAGE pkg2 AUTHID DEFINER IS SUBTYPE t1 IS VARCHAR2(10); SUBTYPE t2 IS VARCHAR2(10); PROCEDURE s (p1 t1); PROCEDURE s (p2 t2); END pkg2; /
例8-31 例8-30のサブプログラムの起動
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS
a pkg2.t1 := 'a';
BEGIN
pkg2.s(p1=>a); -- Compiles without error
END p;
/
例8-32 オーバーロード・エラーが含まれていないパッケージ仕様部
CREATE OR REPLACE PACKAGE pkg3 AUTHID DEFINER IS PROCEDURE s (p1 VARCHAR2); PROCEDURE s (p1 VARCHAR2, p2 VARCHAR2 := 'p2'); END pkg3; /
例8-33 適切にオーバーロードされたサブプログラムの不適切な起動
CREATE OR REPLACE PROCEDURE p AUTHID DEFINER IS a1 VARCHAR2(10) := 'a1'; a2 VARCHAR2(10) := 'a2'; BEGIN pkg3.s(p1=>a1, p2=>a2); -- Compiles without error pkg3.s(p1=>a1); -- Causes compile-time error PLS-00307 END p; /
例8-34 パラメータの暗黙的な変換によるオーバーロード・エラー
CREATE OR REPLACE PACKAGE pack1 AUTHID DEFINER AS PROCEDURE proc1 (a NUMBER, b VARCHAR2); PROCEDURE proc1 (a NUMBER, b NUMBER); END; / CREATE OR REPLACE PACKAGE BODY pack1 AS PROCEDURE proc1 (a NUMBER, b VARCHAR2) IS BEGIN NULL; END; PROCEDURE proc1 (a NUMBER, b NUMBER) IS BEGIN NULL; END; END; / BEGIN pack1.proc1(1,'2'); -- Compiles without error pack1.proc1(1,2); -- Compiles without error pack1.proc1('1','2'); -- Causes compile-time error PLS-00307 pack1.proc1('1',2); -- Causes compile-time error PLS-00307 END; /
8.10 再帰的サブプログラム
再帰的サブプログラムは、自分自身を起動します。再帰はアルゴリズムを単純化する強力な手法です。
再帰的サブプログラムには、2つ以上の実行パス(再帰的起動に導く1つのパスと終了条件に導く1つのパス)が必要です。後者が存在しない場合、メモリーが足りなくなり、PL/SQLによって事前定義の例外STORAGE_ERROR
が呼び出されるまで、再帰が続きます。
例8-35では、ファンクションを使用してnの階乗(n!、1からnまでのすべての整数の積)に関する次の再帰定義を実装しています。
n! = n * (n - 1)!
例8-36では、ファンクションを使用してn番目のフィボナッチ数(n-1番目とn-2番目のフィボナッチ数の和)を戻しています。1番目と2番目のフィボナッチ数は、それぞれ0(ゼロ)と1です。
注意:
例8-36のファンクションは、結果キャッシュの対象に適したファンクションです。詳細は、「結果がキャッシュされる再帰ファンクション」を参照してください。
サブプログラムの再帰的起動が行われるたびに、サブプログラムで宣言されている各項目のインスタンスと、実行される各SQL文が作成されます。
カーソルFOR
LOOP
文の内側か、OPEN
またはOPEN
FOR
文とCLOSE
文の間にある再帰的起動は、起動ごとに新しいカーソルをオープンします。そのため、オープン・カーソルの数が、データベース初期化パラメータOPEN_CURSORS
で設定されている制限を超える可能性があります。
例8-35 nの階乗(n!)を戻す再帰ファンクション
CREATE OR REPLACE FUNCTION factorial ( n POSITIVE ) RETURN POSITIVE AUTHID DEFINER IS BEGIN IF n = 1 THEN -- terminating condition RETURN n; ELSE RETURN n * factorial(n-1); -- recursive invocation END IF; END; / BEGIN FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i || '! = ' || factorial(i)); END LOOP; END; /
結果:
1! = 1 2! = 2 3! = 6 4! = 24 5! = 120
例8-36 n番目のフィボナッチ数を戻す再帰ファンクション
CREATE OR REPLACE FUNCTION fibonacci ( n PLS_INTEGER ) RETURN PLS_INTEGER AUTHID DEFINER IS fib_1 PLS_INTEGER := 0; fib_2 PLS_INTEGER := 1; BEGIN IF n = 1 THEN -- terminating condition RETURN fib_1; ELSIF n = 2 THEN RETURN fib_2; -- terminating condition ELSE RETURN fibonacci(n-2) + fibonacci(n-1); -- recursive invocations END IF; END; / BEGIN FOR i IN 1..10 LOOP DBMS_OUTPUT.PUT(fibonacci(i)); IF i < 10 THEN DBMS_OUTPUT.PUT(', '); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(' ...'); END; /
結果:
0, 1, 1, 2, 3, 5, 8, 13, 21, 34 ...
8.11 サブプログラムの副作用
サブプログラムは、独自のローカル変数の値以外のものを変更する場合、副作用を伴います。たとえば、次のいずれかを変更するサブプログラムには、副作用があります。
-
独自の
OUT
またはIN
OUT
パラメータ -
グローバル変数
-
パッケージ内のパブリック変数
-
データベース表
-
データベース
-
外部の状態(たとえば、
DBMS_OUTPUT
の起動や電子メールの送信などによる変更)
副作用によって、問合せのパラレル処理が妨害されたり、処理順序に依存する(したがって、不確定な)結果が発生したり、ユーザー・セッションにまたがったパッケージ状態のメンテナンスが必要になります。
副作用を最小限に抑えることは、結果がキャッシュされるファンクションまたはストアド・ファンクションを定義してSQL文を起動する場合に特に重要です。
関連項目:
SQL文から起動されたPL/SQLファンクションでの副作用の制御の詳細は、『Oracle Database開発ガイド』を参照してください。
8.12 PL/SQLファンクション結果キャッシュ
PL/SQLファンクションにRESULT_CACHE
オプションが指定されていると、その結果は共有グローバル領域(SGA)にキャッシュされるようになります。これにより、同じインスタンスに接続しているセッションは、キャッシュされた結果を再使用できるようになります(結果が利用可能な場合)。
Oracle Databaseでは、結果がキャッシュされるファンクションの実行中に問合せが行われるすべてのデータ・ソース(表およびビュー)が自動的に検出されます。これらのデータ・ソースのいずれかに対する変更がコミットされると、キャッシュされた結果は、すべてのインスタンスに渡って無効になります。結果キャッシュの対象として最良のファンクションは、頻繁に起動され、ほとんどまたはまったく変更されない情報に依存するファンクションです。
ここでのトピック
8.12.1 ファンクションの結果キャッシュの有効化
ファンクションの結果がキャッシュされるようにするには、ファンクションの宣言と定義にRESULT_CACHE
句を含めます。構文の詳細は、「ファンクションの宣言および定義」を参照してください。
注意:
データベース・サーバーの結果キャッシュの構成と管理の詳細は、『Oracle Databaseリファレンス』および『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
例8-37では、パッケージdepartment_pkg
が、結果がキャッシュされるファンクションget_dept_info
を宣言し、定義しています(このファンクションによって、指定した部門に関する情報のレコードが戻されます)。このファンクションは、データベース表のDEPARTMENTS
およびEMPLOYEES
に依存します。
ファンクションget_dept_info
は、他のファンクションを起動する場合と同様に起動します。たとえば、次の起動では、部門番号10に関する情報のレコードが戻されます。
department_pkg.get_dept_info(10);
次の起動では、部門番号10の名前のみが戻されます。
department_pkg.get_dept_info(10).dept_name;
get_dept_info(10)
の結果が結果キャッシュに含まれている場合、結果はこのキャッシュから戻されますが、そうでない場合は、結果は計算されてキャッシュに追加されます。get_dept_info
はDEPARTMENTS
表およびEMPLOYEES
表に依存するため、DEPARTMENTS
またはEMPLOYEES
への変更がコミットされると、get_dept_info
のキャッシュされた結果はすべて無効になり、DEPARTMENTS
またはEMPLOYEES
が変更される可能性のあるすべての場所で、キャッシュ無効化ロジックをプログラミングする必要がなくなります。
例8-37 結果がキャッシュされるファンクションの宣言および定義
CREATE OR REPLACE PACKAGE department_pkg AUTHID DEFINER IS TYPE dept_info_record IS RECORD ( dept_name departments.department_name%TYPE, mgr_name employees.last_name%TYPE, dept_size PLS_INTEGER ); -- Function declaration FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record RESULT_CACHE; END department_pkg; / CREATE OR REPLACE PACKAGE BODY department_pkg IS -- Function definition FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record RESULT_CACHE IS rec dept_info_record; BEGIN SELECT department_name INTO rec.dept_name FROM departments WHERE department_id = dept_id; SELECT e.last_name INTO rec.mgr_name FROM departments d, employees e WHERE d.department_id = dept_id AND d.manager_id = e.employee_id; SELECT COUNT(*) INTO rec.dept_size FROM EMPLOYEES WHERE department_id = dept_id; RETURN rec; END get_dept_info; END department_pkg; /
8.12.2 結果がキャッシュされるファンクションを使用するアプリケーションの開発
結果がキャッシュされるファンクションを使用するアプリケーションを開発する場合、指定したパラメータ値のセットに対してそのファンクションの本体が実行される回数については何も想定しないでください。
結果がキャッシュされるファンクションの本体が実行される状況をいくつか次に示します。
-
このデータベース・インスタンスでのセッションが、これらのパラメータ値を使用してファンクションを初めて起動したとき
-
これらのパラメータ値のキャッシュされた結果が無効である場合
ファンクションが依存するいずれかのデータソースに対する変更がコミットされると、キャッシュされている結果が無効になります。
-
これらのパラメータ値のキャッシュされた結果がエージ・アウトされた場合
システムでは、必要なメモリーが不足すると、キャッシュされた値で最も古いものが破棄されます。
-
ファンクションがキャッシュをバイパスする場合(「結果キャッシュのバイパス」を参照)
8.12.3 結果がキャッシュされるファンクションの要件
結果がキャッシュされるPL/SQLファンクションは、あらゆる入力に対して生成される出力が、RESULT_CACHE
のマークが付けられていなかった場合に生成される出力と常に同じになるときに安全です。この安全性は、次に示す条件が満たされている場合にのみ保証されます。
-
ファンクションの実行時に、副作用がないこと。
副作用の詳細は、「サブプログラムの副作用」を参照してください。
-
ファンクションがアクセスするすべての表が、そのファンクションと同じデータベース内に存在する、通常の非
SYS
所有の永続的な表であること。 -
ファンクションの結果は、常に、そのファンクションが参照する表の現在の
SCN
でコミットされた内容とあわせた、入力実績のベクトルによってのみ決定されること。
結果がキャッシュされるファンクションでは、次の条件も満たされていることがお薦めされています。
-
セッション固有の設定に依存しない。
詳細は、「結果がキャッシュされるファンクションによるセッション固有の設定の処理」を参照してください。
-
セッション固有のアプリケーション・コンテキストに依存しない。
詳細は、「結果がキャッシュされるファンクションによるセッション固有のアプリケーション・コンテキストの処理」を参照してください。
詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
8.12.4 結果がキャッシュされるファンクションの例
結果キャッシュの対象として最良のファンクションは、(最初の例がこれに該当している可能性がありますが)頻繁に起動され、ほとんど変更されない情報に依存するファンクションです。結果キャッシュを行うことによって、再帰ファンクションでの冗長計算が回避されます。
例:
8.12.4.1 結果がキャッシュされるアプリケーション構成パラメータ
グローバル・レベル、アプリケーション・レベルまたはロール・レベルのいずれのレベルで設定できる構成パラメータを持つアプリケーションについて考えてみます。このアプリケーションは、構成情報を次の表に格納します。
-- Global Configuration Settings DROP TABLE global_config_params; CREATE TABLE global_config_params (name VARCHAR2(20), -- parameter NAME val VARCHAR2(20), -- parameter VALUE PRIMARY KEY (name) ); -- Application-Level Configuration Settings CREATE TABLE app_level_config_params (app_id VARCHAR2(20), -- application ID name VARCHAR2(20), -- parameter NAME val VARCHAR2(20), -- parameter VALUE PRIMARY KEY (app_id, name) ); -- Role-Level Configuration Settings CREATE TABLE role_level_config_params (role_id VARCHAR2(20), -- application (role) ID name VARCHAR2(20), -- parameter NAME val VARCHAR2(20), -- parameter VALUE PRIMARY KEY (role_id, name) );
各構成パラメータで、ロール・レベルの設定はアプリケーション・レベルの設定をオーバーライドし、アプリケーション・レベルの設定はグローバル設定をオーバーライドします。パラメータに適用される設定を決定するために、このアプリケーションはPL/SQLファンクションget_value
を定義します。パラメータ名、アプリケーションIDおよびロールIDを指定すると、get_value
はそのパラメータに適用される設定を戻します。
ファンクションget_value
が頻繁に起動され、構成情報はほとんど変更されない場合、このファンクションは結果キャッシュの対象として最良のファンクションとなります。
例8-38に、get_value
に指定可能な定義を示します。あるパラメータ値のセットに対して、グローバル設定によってget_value
の結果が決まるとします。get_value
の実行中に、データベースでは3つの表role_level_config_params
、app_level_config_params
およびglobal_config_params
の問合せが検出されます。これらの3つの表のいずれに対する変更がコミットされても、このパラメータ値のセットについてキャッシュされた結果は無効となり、再計算する必要があります。
次に、2番目のパラメータ値セットに対して、ロール・レベルの設定によってget_value
の結果が決定されるとします。get_value
の実行中に、データベースでは表role_level_config_params
の問合せのみが検出されます。role_level_config_params
に対する変更がコミットされると、2番目のパラメータ値セットについてキャッシュされた結果は無効となりますが、app_level_config_params
またはglobal_config_params
に対する変更がコミットされてもキャッシュされた結果には影響がありません。
例8-38 構成パラメータの設定を戻す、結果がキャッシュされるファンクション
CREATE OR REPLACE FUNCTION get_value (p_param VARCHAR2, p_app_id NUMBER, p_role_id NUMBER ) RETURN VARCHAR2 RESULT_CACHE AUTHID DEFINER IS answer VARCHAR2(20); BEGIN -- Is parameter set at role level? BEGIN SELECT val INTO answer FROM role_level_config_params WHERE role_id = p_role_id AND name = p_param; RETURN answer; -- Found EXCEPTION WHEN no_data_found THEN NULL; -- Fall through to following code END; -- Is parameter set at application level? BEGIN SELECT val INTO answer FROM app_level_config_params WHERE app_id = p_app_id AND name = p_param; RETURN answer; -- Found EXCEPTION WHEN no_data_found THEN NULL; -- Fall through to following code END; -- Is parameter set at global level? SELECT val INTO answer FROM global_config_params WHERE name = p_param; RETURN answer; END;
8.12.4.2 結果がキャッシュされる再帰ファンクション
フィボナッチ数列の数学的定義を模倣した、フィボナッチ数列のn番目の項を検索するための再帰ファンクションは、多くの冗長計算を実行する可能性があります。たとえば、fibonacci(7)
を評価するために、このファンクションはfibonacci(6)
およびfibonacci(5)
を計算する必要があります。fibonacci(6)
を計算するために、このファンクションはfibonacci(5)
およびfibonacci(4)
を計算する必要があります。このため、fibonacci(5)
などのいくつかの項は、重複して計算されます。結果キャッシュを行うことによって、これらの冗長計算が回避されます。
注意:
キャッシュされる再帰的起動の数は、最大で128です。
CREATE OR REPLACE FUNCTION fibonacci (n NUMBER)
RETURN NUMBER
RESULT_CACHE
AUTHID DEFINER
IS
BEGIN
IF (n =0) OR (n =1) THEN
RETURN 1;
ELSE
RETURN fibonacci(n - 1) + fibonacci(n - 2);
END IF;
END;
/
8.12.5 結果がキャッシュされるファンクションの高度なトピック
ここでのトピック
8.12.5.1 キャッシュ・ヒットの規則
結果がキャッシュされるファンクションが異なるパラメータ値で起動されるたびに、それらのパラメータおよびそれぞれの結果がキャッシュに格納されます。それ以降、同じファンクションが同じパラメータ値で起動されると(つまり、キャッシュ・ヒットがある場合)、結果は再計算されるのではなく、キャッシュから取り出されます。
キャッシュ・ヒット用のパラメータ比較の規則は、次に示すように、PL/SQLの「等号」(=)演算子の規則とは異なります。
カテゴリ | キャッシュ・ヒットの規則 | 「等号」演算子の規則 |
---|---|---|
NULLの比較 |
|
|
NULLでないスカラーの比較 |
NULLでないスカラーは、それぞれの値が同一である場合にのみ同じとなります(つまり、指定されたプラットフォームでそれぞれの値が同一のビット・パターンを持っている場合にのみ同じとなります)。たとえば、 |
NULLでないスカラーは、指定されたプラットフォームでそれぞれの値が同一のビット・パターンを持っていない場合でも、等しくなる可能性があります。たとえば、 |
8.12.5.2 結果キャッシュのバイパス
場合によって、キャッシュはバイパスされます。キャッシュがバイパスされる場合を次に示します。
-
ファンクションが結果をキャッシュから取り出すのではなく、計算する場合。
-
ファンクションが計算する結果がキャッシュに追加されない場合。
キャッシュがバイパスされる場合の例を次にいくつか示します。
-
すべてのセッションでキャッシュを使用できない場合。
たとえば、データベース管理者がアプリケーションへのパッチの適用中に、結果キャッシュを使用できない状態にした場合などです(「結果がキャッシュされるファンクションが依存するPL/SQLユニットへのホット・パッチの適用」を参照)。
-
結果がキャッシュされるファンクションが依存する表またはビューに対して、セッションがDML文を実行している場合。
このセッションは、そのDML文が完了するまで(コミットまたはロールバックされるまで)そのファンクションの結果キャッシュをバイパスします。その文がロールバックされると、このセッションは、そのファンクションのキャッシュの使用を再開します。
キャッシュをバイパスすると、次のことが保証されます。
-
各セッションのユーザーは、コミットされていないユーザー独自の変更を参照できます。
-
PL/SQLファンクションの結果キャッシュには、すべてのセッションで参照可能なコミットされた変更のみが含まれます。このため、あるセッションでコミットされていない変更は、他のセッションでは参照できません。
-
8.12.5.3 結果がキャッシュされるファンクションによるセッション固有の設定の処理
セッションによって異なる可能性がある設定(NLS_DATE_FORMAT
やTIME ZONE
など)にファンクションが依存している場合は、様々な設定を処理できるようにそのファンクションを変更できる場合にのみ、そのファンクションの結果がキャッシュされるようにします。
例8–39のファンクションget_hire_date
は、TO_CHAR
ファンクションを使用してDATE
項目をVARCHAR
項目に変換しています。ファンクションget_hire_date
に書式マスクが指定されていないため、書式マスクは、デフォルトでNLS_DATE_FORMAT
に指定されている書式マスクになります。get_hire_date
を起動するセッションのNLS_DATE_FORMAT
設定が異なっている場合、キャッシュされた結果の書式も異なる可能性があります。あるセッションで計算されてキャッシュされた結果がエージ・アウトされ、別のセッションで再計算された場合、同じパラメータ値に対する場合でも、書式が異なる可能性があります。キャッシュされた結果がセッションで取得され、結果の書式がセッションの書式とは異なる場合、その結果は不適切である可能性があります。
この問題の解決方法をいくつか次に示します。
-
get_hire_date
の戻り型をDATE
に変更し、各セッションがTO_CHAR
ファンクションを起動するようにします。 -
ある共通の書式がすべてのセッションで受入れ可能である場合は、書式マスクを指定して、
NLS_DATE_FORMAT
への依存性を削除します。次に例を示します。TO_CHAR(date_hired, 'mm/dd/yy');
-
書式マスクのパラメータを
get_hire_date
に追加します。次に例を示します。CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER, fmt VARCHAR) RETURN VARCHAR RESULT_CACHE AUTHID DEFINER IS date_hired DATE; BEGIN SELECT hire_date INTO date_hired FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN TO_CHAR(date_hired, fmt); END; /
例8-39 結果がキャッシュされるファンクションによるセッション固有の設定の処理
CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR RESULT_CACHE AUTHID DEFINER IS date_hired DATE; BEGIN SELECT hire_date INTO date_hired FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN TO_CHAR(date_hired); END; /
8.12.5.4 結果がキャッシュされるファンクションによるセッション固有のアプリケーション・コンテキストの処理
アプリケーション・コンテキストは、グローバルまたはセッション固有のいずれかで、属性とそれらの値の集合のことです。PL/SQLファンクションは、次の1つ以上の項目を実行する場合、セッション固有のアプリケーション・コンテキストに依存します。
-
指定したコンテキストで指定した属性の値を戻すSQLファンクション
SYS_CONTEXT
の直接起動 -
ファイングレイン・セキュリティのための仮想プライベート・データベース(VPD)・メカニズムを使用した
SYS_CONTEXT
の間接起動(VPDの詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください)
PL/SQLファンクションの結果キャッシュ機能は、セッション固有のアプリケーション・コンテキストへの依存性を自動的には処理しません。セッション固有のアプリケーション・コンテキストに依存しているファンクションの結果をキャッシュする必要がある場合は、アプリケーション・コンテキストをパラメータとしてファンクションに渡す必要があります。このパラメータにはデフォルト値を指定できるため、すべてのユーザーがこのパラメータを指定する必要があるわけではありません。
例8-40では、表config_tab
に、次に示す問合せを変換するVPDポリシーがあると想定しています。
SELECT value FROM config_tab WHERE name = param_name;
前述の問合せが、次の問合せに変換されます。
SELECT value FROM config_tab
WHERE name = param_name
AND app_id = SYS_CONTEXT('Config', 'App_ID');
例8-40 結果がキャッシュされるファンクションによるセッション固有のアプリケーション・コンテキストの処理
CREATE OR REPLACE FUNCTION get_param_value ( param_name VARCHAR, appctx VARCHAR DEFAULT SYS_CONTEXT('Config', 'App_ID') ) RETURN VARCHAR RESULT_CACHE AUTHID DEFINER IS rec VARCHAR(2000); BEGIN SELECT val INTO rec FROM config_tab WHERE name = param_name; RETURN rec; END; /
8.12.5.5 結果キャッシュの粒度の選択
PL/SQLにはファンクション結果キャッシュが用意されていますが、キャッシュの粒度はユーザーが選択します。粒度の概念を理解するために、Order Entry(OE
)サンプル・スキーマ内のProduct_Descriptions
表について考えてみます。
NAME NULL? TYPE ---------------------- -------- --------------- PRODUCT_ID NOT NULL NUMBER(6) LANGUAGE_ID NOT NULL VARCHAR2(3) TRANSLATED_NAME NOT NULL NVARCHAR2(50) TRANSLATED_DESCRIPTION NOT NULL NVARCHAR2(2000)
この表には、各製品の名前と説明が複数の言語で記載されています。各行の一意のキーは、PRODUCT_ID,LANGUAGE_ID
です。
PRODUCT_ID
およびLANGUAGE_ID
を受け取って、関連付けられたTRANSLATED_NAME
を戻すファンクションを定義する必要があるとします。また、変換された名前をキャッシュする必要もあるとします。これらの名前をキャッシュする場合の粒度の選択肢の一部を次に示します。
-
一度に1つの名前(粒度が細かい)
-
一度に1つの言語(粒度が粗い)
表8-4 粒度が細かいキャッシュと粗いキャッシュ
粒度 | メリット |
---|---|
細かい |
各ファンクション結果は、1つの論理結果に対応しています。 1回以上必要とされるデータのみを格納します。 各データ項目は、個別にエージ・アウトされます。 バルク・ロードは最適化できません。 |
粗い |
各ファンクション結果には、多数の論理的部分結果が含まれています。 使用されることのないデータを格納する場合もあります。 1つのデータ項目がエージ・アウトされると、全体がエージ・アウトされます。 バルク・ロードを最適化できます。 |
例8-41および例8-42では、ファンクションproductName
がPRODUCT_ID
およびLANGUAGE_ID
を取り、関連付けられたTRANSLATED_NAME
を戻しています。productName
の各バージョンは、変換された名前をキャッシュしますが、キャッシュする際の粒度はそれぞれ異なっています。
例8-41では、get_product_name_1
は結果がキャッシュされるファンクションです。get_product_name_1
は、別のPRODUCT_ID
およびLANGUAGE_ID
で起動されると、常に関連付けられたTRANSLATED_NAME
をキャッシュします。get_product_name_1
が起動されるたびに、最大1つのTRANSLATED_NAME
がキャッシュに追加されます。
例8-42では、get_product_name_2
は、結果がキャッシュされるファンクションall_product_names
を定義します。get_product_name_2
が別のLANGUAGE_ID
でall_product_names
を起動すると、常にall_product_names
はそのLANGUAGE_ID
に関連付けられたすべてのTRANSLATED_NAME
をキャッシュします。all_product_names
が起動されるたびに、最大1つのLANGUAGE_ID
のすべてのTRANSLATED_NAME
がキャッシュに追加されます。
例8-41 一度に1つの名前のキャッシュ(粒度が細かい)
CREATE OR REPLACE FUNCTION get_product_name_1 (
prod_id NUMBER,
lang_id VARCHAR2
)
RETURN NVARCHAR2
RESULT_CACHE
AUTHID DEFINER
IS
result_ VARCHAR2(50);
BEGIN
SELECT translated_name INTO result_
FROM OE.Product_Descriptions
WHERE PRODUCT_ID = prod_id
AND LANGUAGE_ID = lang_id;
RETURN result_;
END;
/
例8-42 一度に1つの言語の変換された名前のキャッシュ(粒度が粗い)
CREATE OR REPLACE FUNCTION get_product_name_2 (
prod_id NUMBER,
lang_id VARCHAR2
)
RETURN NVARCHAR2
AUTHID DEFINER
IS
TYPE product_names IS TABLE OF NVARCHAR2(50) INDEX BY PLS_INTEGER;
FUNCTION all_product_names (lang_id VARCHAR2)
RETURN product_names
RESULT_CACHE
IS
all_names product_names;
BEGIN
FOR c IN (SELECT * FROM OE.Product_Descriptions
WHERE LANGUAGE_ID = lang_id) LOOP
all_names(c.PRODUCT_ID) := c.TRANSLATED_NAME;
END LOOP;
RETURN all_names;
END;
BEGIN
RETURN all_product_names(lang_id)(prod_id);
END;
/
8.12.5.6 Oracle RAC環境での結果キャッシュ
キャッシュされた結果はシステム・グローバル領域(SGA)に格納されます。Oracle RAC環境では、各データベース・インスタンスによって、そのインスタンス独自のローカルなファンクション結果キャッシュが管理されます。ただし、ローカルな結果キャッシュの内容は、他のOracle RACインスタンスに付随するセッションからアクセスできます。必要な結果がローカル・インスタンスの結果キャッシュから欠落している場合、ローカルで計算するのではなく、他のインスタンスのローカル・キャッシュから結果が取り出される場合があります。
インスタンスのアクセス・パターンおよびワークロードによって、そのインスタンスのローカル・キャッシュに格納される結果セットが決まります。このため、インスタンスが異なると、そのローカル・キャッシュに格納される結果セットも異なります。
各データベース・インスタンスには、そのインスタンス独自のキャッシュされた結果セットが含まれている可能性がありますが、無効な結果を処理するメカニズムはOracle RAC環境全体にわたります。ローカル・インスタンスの結果キャッシュでのみ結果が無効にされた場合、他のインスタンスで無効な結果が使用される可能性があります。たとえば、データベース表内のデータから計算される品目の価格の結果キャッシュについて考えてみます。ある品目の価格に影響を与える方法でこれらのデータベース表のいずれかが更新された場合、キャッシュされたその品目の価格をOracle RAC環境内のすべてのデータベース・インスタンスで無効にする必要があります。
8.12.5.7 結果キャッシュの管理
PL/SQLファンクション結果キャッシュは、その管理および管理性インフラストラクチャを結果キャッシュと共有します。
データベース管理者は、初期化パラメータのRESULT_CACHE_MAX_SIZE
、RESULT_CACHE_MAX_RESULT
およびRESULT_CACHE_REMOTE_EXPIRATION
を指定することで、サーバーの結果キャッシュを管理します。
DBMS_RESULT_CACHE
パッケージでは、DBAが、SQL結果キャッシュおよびPL/SQLファンクション結果キャッシュによって使用される共有プールのその部分を管理できるインタフェースが提供されます。
動的パフォーマンスのビューには、サーバーとクライアントの結果キャッシュを監視するための情報が示されます。
関連項目:
-
RESULT_CACHE_MAX_SIZE
の詳細は、『Oracle Databaseリファレンス』を参照してください。 -
RESULT_CACHE_MAX_RESULT
の詳細は、『Oracle Databaseリファレンス』を参照してください。 -
結果キャッシュの概念の詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
-
DBMS_RESULT_CACHE
パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください -
V$RESULT_CACHE_STATISTICS
の詳細は、『Oracle Databaseリファレンス』を参照してください -
V$RESULT_CACHE_MEMORY
の詳細は、『Oracle Databaseリファレンス』を参照してください -
V$RESULT_CACHE_OBJECTS
の詳細は、『Oracle Databaseリファレンス』を参照してください -
V$RESULT_CACHE_DEPENDENCY
の詳細は、『Oracle Databaseリファレンス』を参照してください
8.12.5.8 結果がキャッシュされるファンクションが依存するPL/SQLユニットへのホット・パッチの適用
結果がキャッシュされるファンクションが依存するPL/SQLユニットに(直接または間接的に)ホット・パッチを適用する際、結果がキャッシュされるファンクションに関連付けられているキャッシュされた結果がすべての場合に自動的にフラッシュされるとはかぎりません。
たとえば、結果がキャッシュされるファンクションP1
.foo()
がパッケージ・サブプログラムP2
.bar()
に依存しているとします。パッケージP2
の本体の新しいバージョンがロードされた場合、P1
.foo()
に関連付けられているキャッシュされた結果は、自動的にはフラッシュされません。
このため、PL/SQLユニットへのホット・パッチの適用には、次の手順を実行することをお薦めします。
注意:
これらの手順に従うには、DBMS_RESULT_CACHE
パッケージに対するEXECUTE
権限が必要です。
8.13 SQL文によって起動できるPL/SQLファンクション
ストアド・ファンクション(およびそのファンクションによって起動されるサブプログラム)は、次に示す副作用を制御するための純正規則に従っている場合にのみ、SQL文から起動できます。
-
SELECT
文またはパラレル化INSERT
文、UPDATE
文、DELETE
文またはMERGE
文から起動された場合、サブプログラムはデータベース表を変更できません。 -
INSERT
文、UPDATE
文、DELETE
文またはMERGE
文から起動された場合、サブプログラムは、その文によって変更されたデータベース表の問合せまたは変更を実行できません。ファンクションによって表の問合せまたは変更を実行する場合に、その表に対するDML文でファンクションを起動すると、ORA-04091(変更表エラー)が発生します。ただし、これには例外が1つあり、
FORALL
文に存在しない単一行のINSERT
文によってVALUES
句のファンクションを起動する場合、ORA-04091は発生しません。 -
SELECT
文、INSERT
文、UPDATE
文、DELETE
文またはMERGE
文から起動された場合、サブプログラムは、次のどのSQL文も実行できません(PRAGMA
AUTONOMOUS_TRANSACTION
が指定されていない場合)。-
トランザクション制御文(
COMMIT
など) -
セッション制御文(
SET
ROLE
など) -
システム制御文(
ALTER
SYSTEM
など) -
データベース定義言語(DDL)文(
CREATE
など)。これらの文は自動的にコミットされます。
(
PRAGMA
AUTONOMOUS_TRANSACTION
の詳細は、「AUTONOMOUS_TRANSACTIONプラグマ」を参照してください。) -
ファンクション実行部のいずれかのSQL文が規則に違反すると、文の解析時にランタイム・エラーが発生します。
特にファンクションがDETERMINISTIC
またはPARALLEL_ENABLE
オプション(これらのオプションの詳細は、DETERMINISTIC句およびPARALLEL_ENABLE句を参照)を使用して宣言されている場合、ファンクションの副作用が少なくなるほど、そのファンクションはSELECT
文でより効率的に最適化されます。
関連項目:
-
SQL文によって起動できるPL/SQLファンクションの制限の詳細は、『Oracle Database開発ガイド』を参照してください。
8.14 実行者権限および定義者権限(AUTHIDプロパティ)
ストアドPL/SQLユニットのAUTHID
プロパティは、実行時にユニットによって発行されるSQL文の名前解決および権限チェックに影響します。AUTHID
プロパティはコンパイルには影響せず、コレクション型などのコードのないユニットに対しては意味を持ちません。
AUTHID
プロパティ値は、静的データ・ディクショナリ・ビュー*_PROCEDURES
に公開されます。AUTHID
が意味を持つユニットの場合はビューに値CURRENT_USER
またはDEFINER
が表示され、他のユニットの場合はビューにNULL
が表示されます。
次の文で作成または変更するストアドPL/SQLユニットについては、オプションのAUTHID
句を使用して、DEFINER
(デフォルト、下位互換性用)またはCURRENT_USER
(優先用途)を指定できます。
AUTHID
値がCURRENT_USER
のユニットは、実行者権限ユニットまたはIRユニットと呼ばれます。AUTHID
値がDEFINER
(デフォルト)のユニットは、定義者権限ユニットまたはDRユニットと呼ばれます。AUTHID
値を指定できないPL/SQLユニットおよびスキーマ・オブジェクトは次のように動作します。
PL/SQLユニットまたはスキーマ・オブジェクト | 動作 |
---|---|
無名ブロック |
IRユニット |
|
IRユニットに類似(『Oracle Databaseセキュリティ・ガイド』を参照) |
|
DRユニット |
トリガー |
DRユニット |
ユニットのAUTHID
プロパティによって、そのユニットがIRであるかDRであるかが決まります。このプロパティは実行時の名前解決および権限チェックの両方に影響します。
-
名前解決のコンテキストは
CURRENT_SCHEMA
です。 -
権限チェックのコンテキストは
CURRENT_USER
および有効になっているロールです。
セッションが開始されると、CURRENT_SCHEMA
の値はSESSION_USER
が所有するスキーマの値となり、CURRENT_USER
の値はSESSION_USER
の値と同じになります。(CURRENT_SCHEMA
、CURRENT_USER
またはSESSION_USER
の現在の値を取得するには、『Oracle Database SQL言語リファレンス』で説明されているSYS_CONTEXT
ファンクションを使用します。)
CURRENT_SCHEMA
は、SQL文ALTER
SESSION
SET
CURRENT_SCHEMA
を使用してセッション中に変更できます。CURRENT_USER
は、プログラムでは変更できませんが、PL/SQLユニットまたはビューがコール・スタックに格納されたり、コール・スタックから取り出されると、変更される場合があります。
注意:
ALTER
SESSION
SET
CURRENT_SCHEMA
は、ストアドPL/SQLユニット内から発行しないことをお薦めします。
サーバー・コール中にDRユニットがコール・スタックに格納されると、データベースによって、現在有効になっているロール、およびCURRENT_USER
とCURRENT_SCHEMA
の現在の値が格納されます。次に、CURRENT_USER
とCURRENT_SCHEMA
の両方がDRユニットの所有者に変更され、ロールPUBLIC
のみが有効にされます。(格納されたロールおよび値と新しいロールおよび値は、異なるとはかぎりません。)DRユニットがコール・スタックから取り出されると、データベースによって、格納されたロールおよび値がリストアされます。これに対し、IRユニットがコール・スタックに格納されたり、コール・スタックから取り出されても、CURRENT_USER
とCURRENT_SCHEMA
の値および現在有効になっているロールは変更されません(ロールがIRユニット自体に付与されていないかぎり。「PL/SQLパッケージおよびスタンドアロン・プログラムへのロールの付与」を参照)。
PL/SQLユニットによって発行される動的SQL文の場合、名前解決および権限チェックは実行時に1回行われます。静的SQL文の場合、名前解決および権限チェックは、PL/SQLユニットのコンパイル時、およびその後の実行時の2回行われます。コンパイル時に、AUTHID
プロパティは影響しません(DRユニットとIRユニットの両方がDRユニットのように処理されます)。ただし、実行時には、AUTHID
プロパティによってユニットがIRであるかDRであるかが決まり、その結果に従ってユニットは処理されます。
IRユニットの開始時に、初期化またはなんらかのコードの実行前に、ランタイム・システムにより権限がチェックされます。ユニット所有者に起動者に対するINHERIT
PRIVILEGES
権限またはINHERIT
ANY
PRIVILEGES
権限のいずれもなければ、ランタイム・システムによりエラーORA-06598が発行されます。
注意:
ユニット所有者に必要な権限がある場合、その権限は次のいずれかの文により付与されています。
GRANT INHERIT PRIVILEGES ON current_user TO PUBLIC GRANT INHERIT PRIVILEGES ON current_user TO unit_owner GRANT INHERIT ANY PRIVILEGES TO unit_owner
GRANT
文の詳細は『Oracle Database SQL言語リファレンス』を参照してください。
関連項目:
-
DRおよびIRユニットのセキュリティの管理の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。
-
DRおよびIRプログラム・ユニットのコンパイルに必要な権限の取得の詳細は、Oracle Database Vault管理者ガイドを参照してください
ここでのトピック
8.14.1 PL/SQLパッケージおよびスタンドアロン・サブプログラムへのロールの付与
SQL GRANT
コマンドを使用して、PL/SQLパッケージおよびスタンドアロン・サブプログラムにロールを付与できます。PL/SQLユニットに付与されたロールはコンパイルには影響しません。ロールはユニットが実行時に発行するSQL文の権限チェックに影響します。ユニットは、自身のロールと現在有効になっているその他のロールの両方の権限で実行されます。
通常、自分より低い権限のユーザーがユニットの実行に必要な権限でのみユニットを実行できるように、IRユニットにロールを付与します。DRユニット(起動者により定義者のすべての権限で実行される)にロールを付与するのは、そのDRユニットが実行時にのみチェックされる動的SQLを発行する場合のみです。
PL/SQLユニットにロールを付与するための基本構文は次のとおりです。
GRANT role [, role ]... TO unit [, unit ]...
たとえば、このコマンドはロールread
とexecute
をファンクションscott
.func
とパッケージsys
.pkg
に付与します。
GRANT read, execute TO FUNCTION scott.func, PACKAGE sys.pkg
GRANT
コマンドの構文およびセマンティクスの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
関連項目:
-
PL/SQLユニットのロールを取り消すための
REVOKE
コマンドの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
アプリケーション・ユーザーおよびアプリケーション・ロールの構成の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。
8.14.2 IRユニットにはテンプレート・オブジェクトが必要
1人のユーザー(つまり1つのスキーマ)がIRユニットを所有し、他のユーザーは自身のスキーマ内でそのユニットを実行します。IRユニットが静的SQL文を発行する場合、これらの文が影響するスキーマ・オブジェクトはコンパイル時には所有者のスキーマ内に存在する必要があり(コンパイラが参照を解決できるように)、また実行時には起動者のスキーマ内に存在する必要があります。対応するスキーマ・オブジェクトの定義が一致する必要があります(たとえば、対応する表の名前と列が同じである必要があります)。一致していない場合は、エラーまたは予期しない結果が発生します。ただし、コンパイラで不要なため、所有者のスキーマ内のオブジェクトはデータを含む必要はありません。したがって、これらのオブジェクトはテンプレート・オブジェクトと呼ばれます。
8.14.3 DRユニット内の接続ユーザー・データベース・リンク
DRユニット(定義者権限ユニット)に接続ユーザー・データベース・リンクを含める場合、DRユニットを実行するユーザーにINHERIT REMOTE PRIVILEGES
権限を付与する必要があります。
この権限をユーザーに付与すると、そのユーザーはDRユニットを実行できるようになります。付与していない場合、ORA-25433: User does not have INHERIT REMOTE PRIVILEGES
エラーとともに実行は失敗します。定義者権限(DR)プロシージャ内から接続ユーザー・データベース・リンクを含めるには、プロシージャに@database_link
を含めます。
次の例は、DRユニットがdblink
というデータベース・リンクを使用してHR.EMPLOYEES
表のEMPLOYEE_ID
列にアクセスする方法を示しています。
例8-43 DRユニット内のデータベース・リンク
CREATE OR REPLACE PROCEDURE hr_remote_db_link AS v_employee_id VARCHAR(50); BEGIN EXECUTE IMMEDIATE 'SELECT employee_id FROM employees@dblink' into v_employee_id; DBMS_OUTPUT.PUT_LINE('employee_id: ' || v_employee_id); END ; /
関連項目:
INHERIT REMOTE PRIVILEGES
権限の使用に関する詳細(DRユニットでデータベース・リンクを使用する方法のチュートリアルを含む)は、Oracle Databaseセキュリティ・ガイドを参照してください
8.15 外部サブプログラム
CプロシージャまたはJavaメソッドがデータベースに格納されている場合、それを外部サブプログラムとして公開し、PL/SQLから起動できます。
外部サブプログラムを公開するには、コール仕様を使用して、ストアドPL/SQLサブプログラムを定義します。コール仕様は、外部サブプログラムの名前、パラメータ型および戻り型をPL/SQLの同等要素にマップします。公開した外部サブプログラムは、そのPL/SQL名によって起動します。
たとえば、Adjuster
という次のJavaクラスがデータベースに格納されているとします。
import java.sql.*; import oracle.jdbc.driver.*; public class Adjuster { public static void raiseSalary (int empNo, float percent) throws SQLException { Connection conn = new OracleDriver().defaultConnection(); String sql = "UPDATE employees SET salary = salary * ? WHERE employee_id = ?"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setFloat(1, (1 + percent / 100)); pstmt.setInt(2, empNo); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) {System.err.println(e.getMessage());} } }
JavaクラスAdjuster
には、指定の従業員の給与を指定のパーセンテージ分のみ増やすraiseSalary
というメソッドがあります。raiseSalary
は、void
メソッドであるため、(ファンクションではなく)PL/SQLプロシージャとして公開します。
例8-44では、格納されたJavaメソッドAdjuster.raiseSalary
をPL/SQLスタンドアロン・プロシージャとして公開するため、Javaメソッド名のAdjuster.raiseSalary
をPL/SQLプロシージャ名のraise_salary
に、Javaデータ型のint
およびfloat
をPL/SQLデータ型のNUMBER
にマップしています。その後、無名ブロックでraise_salary
を起動します。
例8-45では、格納されたJavaメソッドjava.lang.Thread.sleep
をPL/SQLスタンドアロン・プロシージャとして公開するため、Javaメソッド名をPL/SQLプロシージャ名のjava_sleep
に、Javaデータ型のlong
をPL/SQLデータ型のNUMBER
にマップしています。PL/SQLスタンドアロン・プロシージャのsleep
で、java_sleep
を起動します。
関連項目:
外部プログラムのコールの詳細は、『Oracle Database開発ガイド』を参照してください。
例8-44 PL/SQL無名ブロックによる外部プロシージャの起動
-- Publish Adjuster.raiseSalary as standalone PL/SQL procedure: CREATE OR REPLACE PROCEDURE raise_salary ( empid NUMBER, pct NUMBER ) AS LANGUAGE JAVA NAME 'Adjuster.raiseSalary (int, float)'; -- call specification / BEGIN raise_salary(120, 10); -- invoke Adjuster.raiseSalary by PL/SQL name END; /
例8-45 PL/SQLスタンドアロン・プロシージャによる外部プロシージャの起動
-- Java call specification: CREATE PROCEDURE java_sleep ( milli_seconds IN NUMBER ) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; / CREATE OR REPLACE PROCEDURE sleep ( milli_seconds IN NUMBER ) AUTHID DEFINER IS BEGIN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time()); java_sleep (milli_seconds); DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time()); END; /