日本語PDF

8 PL/SQLサブプログラム

PL/SQLのサブプログラムは、繰り返し起動できる名前付きPL/SQLブロックです。サブプログラムにパラメータが含まれている場合は、起動のたびにパラメータ値を変えることができます。

サブプログラムは、プロシージャまたはファンクションのいずれかです。通常、プロシージャはアクションを実行するために使用し、ファンクションは計算を行って値を戻すために使用します。

ここでのトピック

• サブプログラムを使用する理由

• ネストしたサブプログラム、パッケージ・サブプログラムおよびスタンドアロン・サブプログラム

• サブプログラムの起動

• サブプログラムのプロパティ

• サブプログラムの各部

• 前方宣言

• サブプログラムのパラメータ

• サブプログラムの起動の解決

• オーバーロードされたサブプログラム

• 再帰的サブプログラム

• サブプログラムの副作用

• PL/SQLファンクション結果キャッシュ

• SQL文によって起動できるPL/SQLファンクション

• 実行者権限および定義者権限(AUTHIDプロパティ)

• 外部サブプログラム

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プロパティのみです。スタンドアロン・サブプログラムの宣言には次のプロパティを使用できます。

• ACCESSIBLE BY句

• DEFAULT COLLATION句

• 実行者権限および定義者権限(AUTHIDプロパティ)

8.5 サブプログラムの各部

サブプログラムの先頭には、その名前およびオプションのパラメータ・リストを指定するサブプログラムのヘッダーがあります。

無名ブロックと同様に、サブプログラムには次の部分があります。

• 宣言部(オプション)

  この部分では、ローカル型、カーソル、定数、変数、例外およびネストしたサブプログラムを宣言および定義します。これらの項目は、サブプログラムの実行が完了すると消滅します。

  この部分は、プラグマを指定することもできます。

  ノート:

  無名ブロックの宣言部とは異なり、サブプログラムの宣言部は、キーワードDECLAREでは始まりません。

• 実行部(必須)

  この部分には、値の代入、実行の制御およびデータの操作を実行する1つ以上の文が含まれています。(アプリケーション設計プロセスの初期段階では、例4-35に示すとおり、この部分にNULL文のみを含めることも可能です。)

• 例外処理部(オプション)

  この部分には、ランタイム・エラーを処理するコードが含まれています。

ここでのトピック

• ファンクションの追加部分

• RETURN文

関連項目:

• 「プラグマ」

• プロシージャ宣言と定義の構文は、「プロシージャの宣言および定義」を参照してください

• サブプログラムのパラメータの詳細は、「サブプログラムのパラメータ」を参照してください

例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文」を参照してください。)

• ファンクションの宣言には、次に示すオプションを含めることができます。

オプション	説明
DETERMINISTICオプション	オプティマイザが冗長なファンクションの起動を回避するために役立ちます。
PARALLEL_ENABLEオプション	ファンクションのパラレル実行を可能にし、パラレルDML評価の同時セッションで安全に使用できるようにします。
PIPELINEDオプション	行ソースとして使用するためにテーブル・ファンクションをパイプライン化します。
RESULT_CACHEオプション	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文

• 無名ブロックの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 サブプログラムのパラメータ

サブプログラムにパラメータが含まれている場合は、起動のたびにパラメータ値を変えることができます。

ここでのトピック

• サブプログラムの仮パラメータと実パラメータ

• サブプログラムのパラメータの引渡し方法

• サブプログラムのパラメータ・モード

• サブプログラムのパラメータのエイリアシング

• サブプログラムのINパラメータのデフォルト値

• 実パラメータの位置表記法、名前表記法および混合表記法

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	仮パラメータはその型のデフォルト値に初期化されます。NULL以外のデフォルト値を持つレコード型を除いて、型のデフォルト値はNULLです(例8-16を参照)。 サブプログラムが起動すると、実パラメータの値にかかわらず、仮パラメータの値は初期値になります。サブプログラムで仮パラメータに値を割り当てることをお薦めします。	仮パラメータの型のデフォルト値がNULLである場合、実パラメータは、データ型がNOT NULLとして定義されていない変数である必要があります。	デフォルトでは、実パラメータは値によって渡されます。NOCOPYを指定すると、参照によって渡される場合があります。
IN OUT	仮パラメータは初期化された変数のように動作します(サブプログラムが起動すると、その値は実パラメータの値になります)。サブプログラムで値を更新することをお薦めします。	実パラメータは、変数である必要があります(通常、文字列バッファまたは数値アキュムレータです)。	デフォルトでは、実パラメータは値によって渡されます(どちらの向きに対しても)。NOCOPYを指定すると、参照によって渡される場合があります。

ヒント:

ファンクションのパラメータには、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 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-1 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は、次の順序で、一致する数値パラメータを検索します。

1. PLS_INTEGER(または同じデータ型であるBINARY_INTEGER)

2. NUMBER

3. BINARY_FLOAT

4. 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 結果がキャッシュされるファンクションの高度なトピック

ここでのトピック

• キャッシュ・ヒットの規則

• 結果キャッシュのバイパス

• 結果がキャッシュされるファンクションによるセッション固有の設定の処理

• 結果がキャッシュされるファンクションによるセッション固有のアプリケーション・コンテキストの処理

• 結果キャッシュの粒度の選択

• Oracle RAC環境での結果キャッシュ

• 結果キャッシュの管理

• 結果がキャッシュされるファンクションが依存するPL/SQLユニットへのホット・パッチの適用

8.12.5.1 キャッシュ・ヒットの規則

結果がキャッシュされるファンクションが異なるパラメータ値で起動されるたびに、それらのパラメータおよびそれぞれの結果がキャッシュに格納されます。それ以降、同じファンクションが同じパラメータ値で起動されると(つまり、キャッシュ・ヒットがある場合)、結果は再計算されるのではなく、キャッシュから取り出されます。

キャッシュ・ヒット用のパラメータ比較の規則は、次に示すように、PL/SQLの「等号」(=)演算子の規則とは異なります。

カテゴリ	キャッシュ・ヒットの規則	「等号」演算子の規則
NULLの比較	NULLはNULLに一致します	NULL = NULLの評価結果はNULLです。
NULLでないスカラーの比較	NULLでないスカラーは、それぞれの値が同一である場合にのみ同じとなります(つまり、指定されたプラットフォームでそれぞれの値が同一のビット・パターンを持っている場合にのみ同じとなります)。たとえば、CHAR値'AA'と'AA 'は異なります。(この規則の方が、「等号」演算子の規則より厳密です。)	NULLでないスカラーは、指定されたプラットフォームでそれぞれの値が同一のビット・パターンを持っていない場合でも、等しくなる可能性があります。たとえば、CHAR値'AA'と'AA'は等しくなります。

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権限が必要です。

1. 結果キャッシュをバイパス・モードに設定し、既存の結果をフラッシュします。

   BEGIN
     DBMS_RESULT_CACHE.Bypass(TRUE);
     DBMS_RESULT_CACHE.Flush;
   END;
   /

   Oracle RAC環境では、各データベース・インスタンスに対してこのステップを実行します。

2. PL/SQLコードにパッチを適用します。
3. 結果キャッシュの使用を再開します。

   BEGIN
     DBMS_RESULT_CACHE.Bypass(FALSE);
   END;
   /

   Oracle RAC環境では、各データベース・インスタンスに対してこのステップを実行します。

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(優先用途)を指定できます。

• CREATE FUNCTION文

• CREATE PACKAGE文

• CREATE PROCEDURE文

• 「CREATE TYPE文」

• ALTER TYPE文

AUTHID値がCURRENT_USERのユニットは、実行者権限ユニットまたはIRユニットと呼ばれます。AUTHID値がDEFINER(デフォルト)のユニットは、定義者権限ユニットまたはDRユニットと呼ばれます。AUTHID値を指定できないPL/SQLユニットおよびスキーマ・オブジェクトは次のように動作します。

PL/SQLユニットまたはスキーマ・オブジェクト	動作
無名ブロック	IRユニット
BEQUEATH CURRENT_USERビュー	IRユニットに類似(『Oracle Databaseセキュリティ・ガイド』を参照)
BEQUEATH DEFINERビュー	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セキュリティ・ガイド』を参照

ここでのトピック

• PL/SQLパッケージおよびスタンドアロン・サブプログラムへのロールの付与

• IRユニットにはテンプレート・オブジェクトが必要

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