8 PL/SQLのサブプログラムの使用

PL/SQLサブプログラムの概要

PL/SQLサブプログラムは、一連のパラメータによって起動できる名前付きPL/SQLブロックです。 サブプログラムは、プロシージャまたはファンクションのいずれかです。一般に、プロシージャはアクションを実行するために使用し、ファンクションは値を計算して戻すために使用します。

サブプログラムは、スキーマ・レベル、パッケージ内またはPL/SQLブロック内（別のサブプログラムの可能性もあります）で作成できます。

スキーマ・レベルで作成されるサブプログラムは、スタンドアロン・ストアド・サブプログラムです。 このサブプログラムは、CREATE PROCEDURE文またはCREATE FUNCTION文を使用して作成できます。 このサブプログラムは、DROP PROCEDURE文またはDROP FUNCTION文を使用して削除するまでデータベースに格納されます。

パッケージ内で作成されるサブプログラムは、パッケージ・サブプログラムです。DROP PACKAGE文を使用してパッケージを削除するまで、データベースに格納されます。

PL/SQLブロック内で作成されるサブプログラムは、ネストしたサブプログラムです。 宣言と定義を同時に行うか、または最初に宣言（前方宣言）して、後で同じブロック内で定義できます。 ネストしたサブプログラムは、スタンドアロン・サブプログラムまたはパッケージ・サブプログラム内でネストした場合にのみ、データベースに格納されます。

サブプログラムのコール

サブプログラムのコールの形式は次のとおりです。

subprogram_name [ (parameter [, parameter]... ) ]

プロシージャ・コールはPL/SQL文です。次に例を示します。

raise_salary(employee_id, amount);

ファンクション・コールは式の一部です。次に例を示します。

IF salary_ok(new_salary, new_title) THEN ...

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

• サブプログラムを使用すると、PL/SQL言語を拡張できます。

  プロシージャ・コールは新しい文のように機能します。 ファンクション・コールは新しい式および演算子のように機能します。

• サブプログラムを使用すると、プログラムを管理の容易な、正しく定義されたモジュールに分けることができます。

  この特性を利用すると、トップダウン設計と段階的詳細化アプローチによって問題を解決できます。

• サブプログラムによって、再利用性が向上します。

  テスト済のサブプログラムは、いくつものアプリケーションで再利用できます。 PL/SQLサブプログラムは、多くの異なる環境から起動できます。そのため、新しい言語またはAPIを使用してデータベースにアクセスするたびにサブプログラムを最初から作成しなおす必要はありません。

• サブプログラムによって、メンテナンス性が向上します。

  起動元の他のサブプログラムを変更することなく、サブプログラム内の細部を変更できます。 サブプログラムは、パッケージやオブジェクト型などのメンテナンス性を向上させる他の機能の重要なコンポーネントです。

• ダミーのサブプログラム（スタブ）を使用すると、メイン・プログラムのテストが終了するまで、プロシージャまたはファンクションを定義しないで済ますことができます。

  処理の詳細にとらわれることなく、抽象的な思考方法によるトップダウン手法でアプリケーションを設計できます。

• サブプログラムは、PL/SQLパッケージにグループ化できます。

  パッケージにすると、コードがさらに再利用しやすく、メンテナンス性の高いものになります。また、パッケージは、APIの定義に使用できます。

• サブプログラムをPL/SQLパッケージに入れると、実装上の細部を隠ぺいできます。

  パッケージ仕様部でサブプログラム仕様部を宣言せずに、パッケージ本体でサブプログラムを定義できます。 ただし、このようなサブプログラムは、パッケージ内からのみ起動できます。 サブプログラムの実行部には、少なくとも1つの文が存在している必要があります。NULL文はこの条件を満たします。

サブプログラムの各部

サブプログラムには常に名前があり、パラメータ・リストがある場合もあります。

すべてのPL/SQLブロックと同様に、サブプログラムにはオプションの宣言部、必須の実行部およびオプションの例外処理部があり、PRAGMA AUTONOMOUS_TRANSACTIONを指定して自律型（独立型）にすることができます。

サブプログラム以外のブロックの宣言部とは異なり、サブプログラムの宣言部は、キーワードDECLAREでは始まりません。 宣言部には、型、カーソル、定数、変数、例外およびネストしたサブプログラムの宣言が含まれています。 これらの項目は、サブプログラムに対してローカルで、そのサブプログラムの実行が完了すると消滅します。

サブプログラムの実行部には、値の代入、実行の制御およびデータの操作を実行する文が含まれています。

サブプログラムの例外処理部には、ランタイム・エラーを処理するコードが含まれています。

例8-1では、無名ブロック内でプロシージャを（同時に）宣言および定義しています。 プロシージャには、必須の実行部とオプションの例外処理部がありますが、オプションの宣言部はありません。 プロシージャは、ブロックの実行部によって起動されます。

-- Declarative part of block begins
DECLARE
  in_string  VARCHAR2(100) := 'This is my test string.';
  out_string VARCHAR2(200);

  -- Procedure declaration and definition begins
  PROCEDURE double (original IN VARCHAR2,
                    new_string OUT VARCHAR2)
  IS
    -- Declarative part of procedure (optional) goes here
    -- Executable part of procedure begins
    BEGIN
      new_string := original || ' + ' || original;
    -- Executable part of procedure ends
    -- Exception-handling part of procedure begins
    EXCEPTION
      WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.');
    END;
    -- Exception-handling part of procedure ends
    -- Procedure declaration and definition ends
-- Declarative part of block ends
-- Executable part of block begins
BEGIN
  double(in_string, out_string);  -- Procedure invocation
  DBMS_OUTPUT.PUT_LINE(in_string || ' - ' || out_string);
END;
-- Executable part of block ends
/

プロシージャとファンクションの構造は、次の点を除いて同じです。

• ファンクションのヘッダーには、戻り値のデータ型を指定するRETURN句が含まれている必要があります。 プロシージャのヘッダーには、RETURN句を含めることはできません。

• ファンクションの実行部には、少なくとも1つのRETURN文が含まれている必要があります。 プロシージャでは、RETURN文はオプションです。 詳細は、「RETURN文」を参照してください。

• ファンクションのヘッダーにのみ、次のオプションを含めることができます。

RETURN文

RETURN文（ファンクションの戻り値のデータ型を指定するRETURN句とは異なる）は、この文を含むサブプログラムの実行を即座に終了し、コール元に制御を戻します。サブプログラム・コールの直後の文から、実行が継続されます。

サブプログラムでは、複数のRETURN文を使用できます。 サブプログラムをRETURN文で終える必要はありません。どのRETURN文を実行しても、サブプログラムは即座に終了します。

プロシージャでは、RETURN文に式を含めることはできず、値も戻されません。

ファンクションでは、RETURN文に式が含まれている必要があります。 RETURN文を実行すると、式が評価され、その値がファンクション識別子に代入されます。 ファンクション識別子は、RETURN句で指定された型の変数と同様に機能します。

ファンクションのRETURN文では、任意で複雑な式も使用できます。次に例を示します。

CREATE OR REPLACE FUNCTION half_of_square(original NUMBER)
  RETURN NUMBER IS
BEGIN
  RETURN (original * original)/2 + (original * 4);
END half_of_square;
/

ファンクションには、RETURN文へ導く少なくとも1つの実行パスが必要です。

例8-2では、無名ブロック内でファンクションを（同時に）宣言および定義しています。 ファンクションには、オプションの宣言部と必須の実行部がありますが、オプションの例外処理部はありません。 ファンクションはブロックの実行部によって起動されます。

-- Declarative part of block begins
DECLARE
  -- Function declaration and definition begins
  FUNCTION square (original NUMBER)
    RETURN NUMBER  -- RETURN clause
  AS
  -- Declarative part of function begins
    original_squared NUMBER;
  -- Declarative part of function ends
  -- Executable part of function begins
  BEGIN
    original_squared := original * original;
    RETURN original_squared;  -- RETURN statement
  -- Exception-handling part of function (optional) goes here
  END;
  -- Executable part of function ends
  -- Function declaration and definition ends
-- Declarative part of block ends
-- Executable part of block begins
BEGIN
  DBMS_OUTPUT.PUT_LINE(square(100));  -- Function invocation
END;
-- Executable part of block ends
/

相互に起動し合うネストしたサブプログラムの作成

ブロックでは、複数のネストしたサブプログラムを作成できます。 サブプログラムは起動の前に宣言する必要があるため、ネストしたサブプログラムが相互に起動し合う場合は、前方宣言が必要です。 前方宣言を使用してサブプログラムを宣言しますが、このプログラムは、起動元となる他のサブプログラムを定義するまで定義しないでください。 前方宣言および前方宣言に対応する定義は、同じブロックに指定する必要があります。

例8-3のブロックでは、相互に起動し合う2つのプロシージャを作成しています。

DECLARE
  -- Declare proc1 (forward declaration):
  PROCEDURE proc1(number1 NUMBER);

  -- Declare and define proc 2:
  PROCEDURE proc2(number2 NUMBER) IS
  BEGIN
    proc1(number2);
  END;

  -- Define proc 1:
  PROCEDURE proc1(number1 NUMBER) IS
  BEGIN
    proc2 (number1);
  END;

BEGIN
  NULL;
END;
/

サブプログラムのパラメータの宣言および引渡し

サブプログラムのヘッダーで、仮パラメータを宣言できます。 仮パラメータの各宣言で、モードおよびデフォルト値を指定できます。 サブプログラムの起動時に、サブプログラムに実パラメータを渡すことができます。

ここでのトピック:

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

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

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

• 位置表記法、名前表記法または混合表記法でのサブプログラムの実パラメータの引渡し

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
        WHERE employee_id = emp_id;
  END raise_salary;

BEGIN
  raise_salary(emp_num, bonus);  -- actual parameters
  raise_salary(emp_num, merit + bonus);  -- actual parameters
END;
/

PACKAGE pkg IS
  PROCEDURE s (n IN PLS_INTEGER);
END pkg;

DECLARE
  y PLS_INTEGER :=1;
BEGIN
  pkg.s(y);
END;

DECLARE
  y INTEGER :=1;
BEGIN
  pkg.s(y);
END;

i1 IN VARCHAR2 CHARACTER SET ANY_CS
i2 IN VARCHAR2 CHARACTER SET i1%CHARSET

DECLARE
  emp_num       NUMBER(6) := 120;
  bonus         NUMBER(6) := 50;
  emp_last_name VARCHAR2(25);
  PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER,
                          emp_name OUT VARCHAR2) IS
    BEGIN
      UPDATE employees SET salary =
        salary + amount WHERE employee_id = emp_id;
      SELECT last_name INTO emp_name
        FROM employees
       WHERE employee_id = emp_id;
  END raise_salary;
BEGIN
  raise_salary(emp_num, bonus, emp_last_name);
  DBMS_OUTPUT.PUT_LINE
    ('Salary was updated for: ' || emp_last_name);
END;
/

DECLARE
  emp_num NUMBER(6) := 120;
  bonus   NUMBER(6);
  merit   NUMBER(4);
  PROCEDURE raise_salary (emp_id IN NUMBER,
    amount IN NUMBER DEFAULT 100,
                          extra IN NUMBER DEFAULT 50) IS
    BEGIN
      UPDATE employees SET salary = salary + amount + extra
        WHERE employee_id = emp_id;
  END raise_salary;
BEGIN
  -- Same as raise_salary(120, 100, 50)
  raise_salary(120);
  -- Same as raise_salary(120, 100, 25)
  raise_salary(emp_num, extra => 25);
END;
/

DECLARE
  cnt pls_integer := 0;
  FUNCTION dflt RETURN pls_integer IS
  BEGIN
    cnt := cnt + 1;
    RETURN 42;
  END dflt;
  -- Default is expression
  PROCEDURE p(i IN pls_integer DEFAULT dflt()) IS
     BEGIN
       DBMS_Output.Put_Line(i);
  END p;
BEGIN
  FOR j IN 1..5 LOOP
    p(j);  -- Actual parameter is provided
  END loop;
  DBMS_Output.Put_Line('cnt: '||cnt);
  p();  -- Actual parameter is not provided
  DBMS_Output.Put_Line('cnt: '||cnt);
END;

1
2
3
4
5
Cnt: 0
42
Cnt: 1

SQL> DECLARE
  2    emp_num NUMBER(6) := 120;
  3    bonus   NUMBER(6) := 50;
  4    PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) IS
  5      BEGIN
  6        UPDATE employees SET salary =
  7          salary + amount WHERE employee_id = emp_id;
  8    END raise_salary;
  9  BEGIN
 10    -- Positional notation:
 11    raise_salary(emp_num, bonus);
 12    -- Named notation (parameter order is insignificant):
 13    raise_salary(amount => bonus, emp_id => emp_num);
 14    raise_salary(emp_id => emp_num, amount => bonus);
 15    -- Mixed notation:
 16    raise_salary(emp_num, amount => bonus);
 17  END;
 18  /

PL/SQL procedure successfully completed.

SQL> REM Clean up
SQL> ROLLBACK;

Rollback complete.

SQL>
SQL> CREATE OR REPLACE FUNCTION compute_bonus (emp_id NUMBER, bonus NUMBER)
  2    RETURN NUMBER
  3  IS
  4    emp_sal NUMBER;
  5  BEGIN
  6    SELECT salary INTO emp_sal
  7      FROM employees
  8        WHERE employee_id = emp_id;
  9    RETURN emp_sal + bonus;
 10  END compute_bonus;
 11  /

Function created.

SQL> SELECT compute_bonus(120, 50) FROM DUAL;                   -- positional
  2  SELECT compute_bonus(bonus => 50, emp_id => 120) FROM DUAL; -- named
  3  SELECT compute_bonus(120, bonus => 50) FROM DUAL;           -- mixed
  4
SQL>

PL/SQLサブプログラム名のオーバーロード

PL/SQLでは、ローカル・サブプログラム、パッケージ・サブプログラムおよび型のメソッドをオーバーロードできます。 仮パラメータの数、順序またはデータ型が異なっていれば、同じ名前を複数のサブプログラムで使用できます。

例8-9では、同じ名前（initialize）を持つ2つのサブプログラムを定義しています。プロシージャでは様々な型のコレクションを初期化します。この2つのプロシージャは同じ処理を実行しているため、同じ名前を与えるのが論理的です。

この2つのinitializeプロシージャは、同じブロック、サブプログラム、パッケージまたはオブジェクト型の中に置くことができます。PL/SQLは仮パラメータをチェックして、どちらのプロシージャを起動するかを判断します。PL/SQLが使用するinitializeのバージョンは、プロシージャをdate_tab_typパラメータまたはnum_tab_typパラメータのどちらで起動するかによって異なります。

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
    FOR i IN 1..n LOOP
      tab(i) := SYSDATE;
    END LOOP;
  END initialize;

  PROCEDURE initialize (tab OUT num_tab_typ, n INTEGER) IS
  BEGIN
    FOR i IN 1..n LOOP
      tab(i) := 0.0;
    END LOOP;
  END initialize;

BEGIN
  initialize(hiredate_tab, 50);  -- Invokes first (date_tab_typ) version
  initialize(sal_tab, 100);      -- Invokes second (num_tab_typ) version
END;
/

パッケージ内のオーバーロードされたプロシージャの例は、例10-3を参照してください。

ここでのトピック:

• 数値型のオーバーロードのガイドライン

• オーバーロードの制限

• コンパイラがオーバーロード・エラーを捕捉する場合

PACKAGE pkg1 IS
  PROCEDURE s (p VARCHAR2);
  PROCEDURE s (p VARCHAR2);
END pkg1;

PACKAGE pkg2 IS
  SUBTYPE t1 IS VARCHAR2(10);
  SUBTYPE t2 IS VARCHAR2(10);
  PROCEDURE s (p t1);
  PROCEDURE s (p t2);
END pkg2;

PROCEDURE p IS
  a pkg.t1 := 'a';
BEGIN
  pkg.s(a)  -- Causes compile-time error PLS-00307;
END p;

PACKAGE pkg2 IS
  SUBTYPE t1 IS VARCHAR2(10);
  SUBTYPE t2 IS VARCHAR2(10);
  PROCEDURE s (p1 t1);
  PROCEDURE s (p2 t2);
END pkg2;

PROCEDURE p IS
  a pkg.t1 := 'a';
BEGIN
  pkg.s(p1=>a);  -- Compiles without error
END p;

PACKAGE pkg3 IS
  PROCEDURE s (p1 VARCHAR2);
  PROCEDURE s (p1 VARCHAR2, p2 VARCHAR2 := 'p2');
END pkg3;

PROCEDURE p IS
  a1 VARCHAR2(10) := 'a1';
  a2 VARCHAR2(10) := 'a2';
BEGIN
  pkg.s(p1=>a1, p2=>a2);  -- Compiles without error
  pkg.s(p1=>a1);          -- Causes compile-time error PLS-00307
END p;

PL/SQLサブプログラムのコールの解決方法

図8-1に、PL/SQLコンパイラがサブプログラム・コールを解決する方法を示します。コンパイラは、サブプログラム・コールを検出すると、そのコールに合う宣言を検索します。コンパイラはまず現在の有効範囲を検索し、必要ならば外側の有効範囲を順に検索します。コールされたサブプログラムの名前と同じ名前のサブプログラム宣言が1つ以上見つかると、コンパイラはさらに厳密に検索します。

同じ有効範囲のレベルに同じような名前のサブプログラムが存在する場合は、コールを解決するために、コンパイラは実パラメータと仮パラメータが正確に一致するものを発見する必要があります。 パラメータは、（いくつかの仮パラメータにデフォルト値が代入されている場合を除き）数、順序およびデータ型が一致している必要があります。一致するものが見つからなかった場合、または一致するものが複数見つかった場合、コンパイラはセマンティック・エラーを生成します。

図8-1 PL/SQLコンパイラによるコールの解決方法

「図8-1 PL/SQLコンパイラによるコールの解決方法」の説明

例8-15は、ファンクションbalanceから外側のプロシージャswapを起動しています。現在の有効範囲の中にあるswapの宣言が、いずれもプロシージャ・コールと一致しないために、コンパイラはエラーを生成します。

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
      DBMS_OUTPUT.PUT_LINE('The following raises an exception');
--      swap(num1, num2);
--      wrong number or types of arguments in call to 'SWAP'
      RETURN x;
    END balance;
  BEGIN NULL;END swap;
BEGIN
  NULL;
END;
/

実行者権限または定義者権限の使用（AUTHID句）

ストアドPL/SQLユニットのAUTHIDプロパティは、実行時にユニットによって発行されるSQL文の名前解決および権限チェックに影響します。 AUTHIDプロパティはコンパイルには影響しません。また、コレクション型などのコードのないユニットに対しては意味を持ちません。

AUTHIDプロパティ値は、静的データ・ディクショナリ・ビュー*_PROCEDURESに公開されます。 AUTHIDが意味を持つユニットの場合はビューに値CURRENT_USERまたはDEFINERが表示され、他のユニットの場合はビューにNULLが表示されます。

次の文で作成または変更したストアドPL/SQLユニットでは、オプションのAUTHID句を使用して、CURRENT_USERまたはDEFINERのいずれかを指定できます。 デフォルトはDEFINERです。

• CREATE FUNCTION文

• CREATE PACKAGE文

• CREATE PROCEDURE文

• CREATE TYPE文

• ALTER TYPE文

AUTHID値がCURRENT_USERのユニットは、実行者権限ユニットまたはIRユニットと呼ばれます。 AUTHID値がDEFINERのユニットは、定義者権限ユニットまたはDRユニットと呼ばれます。 無名ブロックは、常にIRユニットのように動作します。 トリガーまたはビューは、常に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ユニットまたはビューがコール・スタックに格納されたり、コール・スタックから取り出されると、変更される場合があります。

サーバー・コール中にDRユニットがコール・スタックに格納されると、データベースによって、現在有効になっているロール、およびCURRENT_USERとCURRENT_SCHEMAの現在の値が格納されます。 次に、CURRENT_USERとCURRENT_SCHEMAの両方がDRユニットの所有者に変更され、ロールPUBLICのみが有効にされます。 （格納されたロールおよび値と新しいロールおよび値は、異なるとはかぎりません。） DRユニットがコール・スタックから取り出されると、データベースによって、格納されたロールおよび値がリストアされます。 これに対して、IRユニットがコール・スタックに格納されるか、またはコール・スタックから取り出されても、CURRENT_USERとCURRENT_SCHEMAの値、および現在有効になっているロールは変更されません。

PL/SQLユニットによって発行される動的SQL文の場合、名前解決および権限チェックは実行時に1回のみ行われます。 静的SQL文の場合、名前解決および権限チェックは、PL/SQLユニットのコンパイル時、およびその後の実行時の2回行われます。 コンパイル時に、AUTHIDプロパティは影響しません。DRユニットとIRユニットの両方がDRユニットのように処理されます。 ただし、実行時には、AUTHIDプロパティによってユニットがIRであるかDRであるか決まり、その結果に従ってユニットは処理されます。

ここでのトピック:

• AUTHID CURRENT_USERとAUTHID DEFINERとの使い分け

• AUTHIDおよびSQLコマンドSET ROLE

• IRサブプログラムでのテンプレート・オブジェクトの必要性

• IRサブプログラムでのデフォルトの名前解決のオーバーライド

• IRサブプログラムでのビューおよびデータベース・トリガーの使用

• IRサブプログラムでのデータベース・リンクの使用

• IRサブプログラムでのオブジェクト型の使用

• IRインスタンス・メソッドの起動

SELECT sysdate INTO today FROM "PUBLIC".DUAL;

CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER
  USING connect_string;

CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...

CREATE TYPE person_typ AUTHID CURRENT_USER AS OBJECT (
  person_id   NUMBER,
  person_name VARCHAR2(30),
  person_job  VARCHAR2(10),
  STATIC PROCEDURE new_person_typ (
    person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2,
    schema_name VARCHAR2, table_name VARCHAR2),
  MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ,
                               new_job VARCHAR2)
  );
/
CREATE TYPE BODY person_typ AS
  STATIC PROCEDURE new_person_typ (
    person_id NUMBER, person_name VARCHAR2, person_job VARCHAR2,
    schema_name VARCHAR2, table_name VARCHAR2) IS
    sql_stmt VARCHAR2(200);
  BEGIN
    sql_stmt := 'INSERT INTO ' || schema_name || '.'
       || table_name || ' VALUES (HR.person_typ(:1, :2, :3))';
    EXECUTE IMMEDIATE sql_stmt
      USING person_id, person_name, person_job;
  END;
  MEMBER PROCEDURE change_job (SELF IN OUT NOCOPY person_typ,
                               new_job VARCHAR2) IS
  BEGIN
    person_job := new_job;
  END;
END;
/

GRANT EXECUTE ON person_typ TO OE;

CREATE TABLE person_tab OF hr.person_typ;

BEGIN
  hr.person_typ.new_person_typ(1001,
                               'Jane Smith',
                               'CLERK',
                               'oe',
                               'person_tab');
  hr.person_typ.new_person_typ(1002,
                               'Joe Perkins',
                               'SALES','oe',
                               'person_tab');
  hr.person_typ.new_person_typ(1003,
                               'Robert Lange',
                               'DEV',
                               'oe', 'person_tab');
                               'oe', 'person_tab');
END;
/

-- OE creates a procedure that invokes change_job
CREATE PROCEDURE reassign
  (p IN OUT NOCOPY hr.person_typ, new_job VARCHAR2) AS
BEGIN
   p.change_job(new_job); -- executes with the privileges of oe
END;
/
-- OE grants EXECUTE to HR on procedure reassign
GRANT EXECUTE ON reassign to HR;

-- HR passes a person_typ object to the procedure reassign
DECLARE
   p1 person_typ;
BEGIN
   p1 := person_typ(1004,  'June Washburn', 'SALES');
   oe.reassign(p1, 'CLERK'); -- current user is oe, not hr
END;
/

再帰的PL/SQLサブプログラムの使用

再帰的サブプログラムとは、自分自身を起動するサブプログラムのことです。再帰的コールが行われるたびに、パラメータ、変数、カーソル、例外など、そのサブプログラムで宣言されているすべての項目の新しいインスタンスが作成されます。また、再帰を繰り返して進む過程の各レベルで、SQL文の新しいインスタンスが作成されます。

再帰的コールを入れる位置には注意してください。 カーソルFORループの中や、OPEN文とCLOSE文の間に入れると、コールのたびに新しいカーソルがオープンされます。これによって、データベースのOPEN_CURSORS初期化パラメータによって設定された限界を超える場合があります。

再帰的サブプログラムには、再帰的コールへ導くパスとそうではないパスの、少なくとも2つのパスが必要です。終了条件へ導くパスが少なくとも1つは必要だということです。そうでない場合、メモリーが足りなくなり、PL/SQLによって事前定義の例外STORAGE_ERRORが呼び出されるまで、再帰が続きます。

再帰はアルゴリズムの設計を単純化する強力な手法です。一般に、再帰とは自己参照を意味します。再帰的な数列の個々の項は、それ以前の項に計算式を適用することで得られます。フィボナッチ数列（0, 1, 1, 2, 3, 5, 8, 13, 21, ...）がその一例です。この数列では、2番以降の各項が、すぐ前の2つの項の合計になっています。

再帰定義では、それ自身をさらに単純なバージョンに定義します。nの階乗（n!、1からnのすべての整数の積）の定義を考えてみます。

n! = n * (n - 1)!

外部サブプログラムの起動

PL/SQLは強力かつ柔軟な言語ですが、他の言語を使用した方が容易に実行できる作業もあります。Cのような低レベルの言語は非常に高速です。 Javaのような広く使用されている言語には、共通の設計パターン用の再利用可能なライブラリが存在します。

PL/SQLのコール仕様を使用すると、別の言語で記述された外部サブプログラムを起動して、それらの機能およびライブラリをPL/SQLから使用できます。たとえば、Javaストアド・プロシージャは、任意のPL/SQLブロック、サブプログラムまたはパッケージから起動できます。Javaストアド・プロシージャの詳細は、『Oracle Database Java開発者ガイド』を参照してください。

次のJavaクラスがデータベースに格納されている場合は、例8-18に示すとおり、このクラスを起動できます。

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

クラスAdjusterには、従業員の給与を指定のパーセンテージ分のみ増やすメソッドがあります。 raiseSalaryはvoidメソッドであるため、例8-18に示すコール仕様を使用してプロシージャとしてパブリッシュしてから、プロシージャraise_salaryを無名PL/SQLブロックから起動できます。

CREATE OR REPLACE PROCEDURE raise_salary (empid NUMBER, pct NUMBER)
AS LANGUAGE JAVA
NAME 'Adjuster.raiseSalary(int, float)';
/

DECLARE
   emp_id  NUMBER := 120;
   percent NUMBER := 10;
BEGIN
   -- get values for emp_id and percent
   raise_salary(emp_id, percent);  -- invoke external subprogram
END;
/

Javaコール仕様はネストされたプロシージャとしては宣言できませんが、オブジェクト型の仕様部、オブジェクト型の本体、PL/SQLパッケージの仕様部、PL/SQLパッケージの本体で指定したり、トップレベルPL/SQLプロシージャおよびファンクションとして指定することができます。

例8-19に、PL/SQLプロシージャからのJavaファンクションへのコールを示します。

-- the following invalid nested Java call spec throws PLS-00999
--   CREATE PROCEDURE sleep (milli_seconds in number) IS
--     PROCEDURE java_sleep (milli_seconds IN NUMBER) AS ...

-- Create Java call spec, then call from PL/SQL procedure
CREATE PROCEDURE java_sleep (milli_seconds IN NUMBER)
  AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
/
CREATE PROCEDURE sleep (milli_seconds in number) IS
-- the following nested PROCEDURE spec is not legal
--  PROCEDURE java_sleep (milli_seconds IN NUMBER)
--    AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
BEGIN
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time());
  java_sleep (milli_seconds);
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.get_time());
END;
/

外部Cサブプログラムは、組込みシステムとのインタフェース、技術的な分野の問題解決、データの分析、リアルタイムのデバイスや処理の制御に使用します。外部Cサブプログラムを使用すると、データベース・サーバーの機能性を拡張し、計算専用プログラムをクライアントからサーバーに移動できます。サーバーの方が高速に処理できます。外部Cサブプログラムの詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。

PL/SQLサブプログラムの副作用の制御

副作用が少なければ、特にPARALLEL_ENABLEヒントまたはDETERMINISTICヒントを使用している場合に、問合せ内でファンクションをより効率化できます。

ストアド・ファンクション（およびそのファンクションによって起動されるサブプログラム）は、次に示す副作用を制御するための純正規則に従っている場合にのみ、SQL文からコールできます。

• SELECT文またはパラレル化INSERT文、UPDATE文またはDELETE文から起動された場合、ファンクションはデータベース表を変更できません。

• INSERT文、UPDATE文またはDELETE文から起動された場合、ファンクションは、その文によって変更されたデータベース表の問合せまたは変更を実行できません。

• SELECT文、INSERT文、UPDATE文またはDELETE文から起動された場合、ファンクションはSQLトランザクション制御文（COMMITなど）、セッション制御文（SET ROLEなど）またはシステム制御文（ALTER SYSTEMなど）を実行できません。また、DDL文（CREATEなど）には自動コミットが続くため、これも実行できません。

ファンクション本体内のSQL文が規則に違反すると、実行時（文が解析されるとき）にエラーが発生します。

コンパイル時に純正規則に違反していないかどうかを確認するには、RESTRICT_REFERENCESプラグマを使用して、ファンクションがデータベース表またはパッケージ変数に対する読取りまたは書込みを行っていないことを示します（構文は、「RESTRICT_REFERENCESプラグマ」を参照）。

例8-20では、RESTRICT_REFERENCESプラグマは、パッケージ・ファンクションcredit_okがデータベースに対する書込み禁止状態（WNDS）、およびパッケージに対する読取り禁止状態（RNPS）であることを示します。

CREATE PACKAGE loans AS
   FUNCTION credit_ok RETURN BOOLEAN;
   PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS);
END loans;
/

静的INSERT文、UPDATE文またはDELETE文は、常にWNDSに違反します。また、列を読み取る場合は、RNDS（データベースに対する読取り禁止状態）にも違反します。動的INSERT文、UPDATE文またはDELETE文は、常にWNDSおよびRNDSの両方に違反します。

PL/SQLサブプログラムのパラメータのエイリアシングの理解

サブプログラムのコールを最適化するために、PL/SQLコンパイラでは、2つのパラメータ引渡し方式のいずれかを選択できます。BY VALUE方式では、実パラメータの値がサブプログラムに渡されます。BY REFERENCE方式では、値へのポインタのみが渡されます。この場合、実パラメータと仮パラメータとは同じ項目を参照します。

NOCOPYコンパイラ・ヒントによって、エイリアシングの可能性が高くなります（つまり、異なる2つの名前が同じメモリー位置を参照するようになります）。これは、グローバル変数がサブプログラムのコールの中で実パラメータとして使用され、そのサブプログラム内で参照されると発生します。結果はコンパイラが選択するパラメータの引渡し方式に依存するため、予測不能になります。

例8-21では、プロシージャADD_ENTRYは、VARRAY LEXICONをパラメータとグローバル変数の両方として参照しています。ADD_ENTRYが起動されると、識別子WORD_LISTおよびLEXICONは同じVARRAYを指定します。

DECLARE
  TYPE Definition IS RECORD (
    word    VARCHAR2(20),
    meaning VARCHAR2(200));
  TYPE Dictionary IS VARRAY(2000) OF Definition;
  lexicon Dictionary := Dictionary();
  PROCEDURE add_entry (word_list IN OUT NOCOPY Dictionary) IS
    BEGIN
      word_list(1).word := 'aardvark';
      lexicon(1).word := 'aardwolf';
    END;
BEGIN
  lexicon.EXTEND;
  add_entry(lexicon);
  DBMS_OUTPUT.PUT_LINE(lexicon(1).word);
END;
/

エイリアシングは、1回のサブプログラム・コールに、同じ実パラメータが2回以上現れる場合にも発生します。 例8-22では、n2がIN OUTのパラメータであるため、実パラメータの値は、プロシージャが終了するまで更新されません。そのため、最初のPUT_LINEは10（nの初期値）を出力し、3番目のPUT_LINEは20を出力します。ただし、n3はNOCOPYパラメータであるため、実パラメータの値はただちに更新されます。2番目のPUT_LINEが30を出力するのはこのためです。

DECLARE
   n NUMBER := 10;
   PROCEDURE do_something (
      n1 IN NUMBER,
      n2 IN OUT NUMBER,
      n3 IN OUT NOCOPY NUMBER) IS
   BEGIN
      n2 := 20;
      DBMS_OUTPUT.put_line(n1);  -- prints 10
      n3 := 30;
      DBMS_OUTPUT.put_line(n1);  -- prints 30
   END;
BEGIN
   do_something(n, n, n);
   DBMS_OUTPUT.put_line(n);  -- prints 20
END;
/

カーソル変数はポインタであるため、カーソル変数にもエイリアシングの可能性があります。 例8-23では、代入の後、emp_cv2はemp_cv1の別名になります。これは、両者が同じ問合せ作業領域を指すためです。最初の2行はすでにemp_cv1からフェッチされているため、emp_cv2からの1番目のフェッチは、1番目ではなく3番目の行をフェッチします。emp_cv1は閉じられているため、emp_cv2からの2番目のフェッチは失敗します。

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;
    FETCH emp_cv1 INTO emp_rec;  -- fetches first row
    FETCH emp_cv1 INTO emp_rec;  -- fetches second row
    FETCH emp_cv2 INTO emp_rec;  -- fetches third row
    CLOSE emp_cv1;
    DBMS_OUTPUT.put_line('The following raises an invalid cursor');
--  FETCH emp_cv2 INTO emp_rec;
--  raises invalid cursor when get_emp_data is invoked
  END;
BEGIN
  get_emp_data(c1, c2);
END;
/

PL/SQLファンクション結果キャッシュの使用

PL/SQLファンクション結果キャッシュのメカニズムは、アプリケーションを実行するすべてのセッションで使用可能な共有グローバル領域（SGA）にPL/SQLファンクションの結果をキャッシュするための、言語サポートおよびシステム管理された手段を提供します。 このキャッシュ・メカニズムは、使いやすく効率的です。このメカニズムを使用すると、独自のキャッシュおよびキャッシュ管理ポリシーを設計および開発する負担がなくなります。

ファンクションの結果キャッシュを使用可能にするには、RESULT_CACHE句を使用します。結果がキャッシュされるファンクションが起動されると、システムによってキャッシュがチェックされます。 以前同じパラメータ値を指定してファンクションをコールした際の結果がキャッシュに含まれている場合は、キャッシュされた結果が起動元に戻され、ファンクション本体は再実行されません。キャッシュに結果が含まれていない場合は、ファンクション本体が実行され、制御が起動元に戻される前に、（これらのパラメータ値の）結果が追加されます。

キャッシュには、非常に多くの結果を蓄積できます。つまり、結果がキャッシュされる各ファンクションの起動時に指定されたパラメータ値の一意の組合せごとに1つの結果を蓄積できます。システムでは、必要なメモリーが不足すると、キャッシュされた結果が1つ以上エージ・アウト（削除）されます。

キャッシュされる結果の計算に使用されるデータベース・オブジェクトは指定できます。このため、指定したデータベース・オブジェクトのいずれかが更新されると、キャッシュされた結果は無効となり、結果を再計算する必要があります。結果キャッシュの対象として最良のファンクションは、頻繁に起動され、ほとんどまたはまったく変更されない情報に依存するファンクションです。

ここでのトピック:

• ファンクションの結果キャッシュの有効化

• 結果がキャッシュされるファンクションを使用するアプリケーションの開発

• 結果がキャッシュされるファンクションの制限

• 結果がキャッシュされるファンクションの例

• 結果がキャッシュされるファンクションの高度なトピック

-- Package specification
CREATE OR REPLACE PACKAGE department_pks IS
  TYPE dept_info_record IS RECORD (average_salary      NUMBER,
                                   number_of_employees NUMBER);
  -- Function declaration
  FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record
  RESULT_CACHE;
END department_pks;
/
CREATE OR REPLACE PACKAGE BODY department_pks AS
  -- Function definition
  FUNCTION get_dept_info (dept_id NUMBER) RETURN dept_info_record
    RESULT_CACHE RELIES_ON (EMPLOYEES)
  IS
    rec dept_info_record;
  BEGIN
    SELECT AVG(SALARY), COUNT(*) INTO rec
      FROM EMPLOYEES
        WHERE DEPARTMENT_ID = dept_id;
    RETURN rec;
  END get_dept_info;
END department_pks;
/
DECLARE
  dept_id   NUMBER := 50;
  avg_sal   NUMBER;
  no_of_emp NUMBER;
BEGIN
  avg_sal   := department_pks.get_dept_info(50).average_salary;
  no_of_emp := department_pks.get_dept_info(50).number_of_employees;
  DBMS_OUTPUT.PUT_LINE('dept_id = ' ||dept_id);
  DBMS_OUTPUT.PUT_LINE('average_salary = '|| avg_sal);
  DBMS_OUTPUT.PUT_LINE('number_of_employees = ' ||no_of_emp);
END;
/

department_pks.get_dept_info(10);

department_pks.get_dept_info(10).average_salary;

-- Global Configuration Settings
CREATE TABLE global_config_params
  (name  VARCHAR2(20), -- parameter NAME
   value 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
   value   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
   value    VARCHAR2(20),  -- parameter VALUE
   PRIMARY KEY (role_id, name)
  );

CREATE OR REPLACE FUNCTION get_value
  (p_param VARCHAR2,
   p_app_id  NUMBER,
   p_role_id NUMBER
  )
  RETURN VARCHAR2
  RESULT_CACHE RELIES_ON
    (role_level_config_params,
     app_level_config_params,
     global_config_params
    )
IS
  answer VARCHAR2(20);
BEGIN
  -- Is parameter set at role level?
  BEGIN
    SELECT value 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 value 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 value INTO answer
     FROM global_config_params
      WHERE name = p_param;
    RETURN answer;
END;

CREATE OR REPLACE FUNCTION fibonacci (n NUMBER)
   RETURN NUMBER RESULT_CACHE IS
BEGIN
  IF (n =0) OR (n =1) THEN
    RETURN 1;
  ELSE
    RETURN fibonacci(n - 1) + fibonacci(n - 2);
  END IF;
END;
/

CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR
 RESULT_CACHE RELIES_ON (HR.EMPLOYEES)
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;
/

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

CREATE OR REPLACE FUNCTION get_param_value
  (param_name VARCHAR,
   appctx     VARCHAR  DEFAULT SYS_CONTEXT('Config', 'App_ID')
  )
  RETURN VARCHAR
  RESULT_CACHE RELIES_ON (config_tab)
IS
  rec VARCHAR(2000);
BEGIN
  SELECT value INTO rec
    FROM config_tab
      WHERE Name = param_name;
END;
/

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)

CREATE OR REPLACE FUNCTION get_product_name_1 (prod_id NUMBER, lang_id VARCHAR2)
  RETURN NVARCHAR2
  RESULT_CACHE RELIES_ON (Product_Descriptions)
IS
  result VARCHAR2(50);
BEGIN
  SELECT translated_name INTO result
    FROM Product_Descriptions
      WHERE PRODUCT_ID = prod_id
        AND LANGUAGE_ID = lang_id;
  RETURN result;
END;

CREATE OR REPLACE FUNCTION get_product_name_2 (prod_id NUMBER, lang_id VARCHAR2)
  RETURN NVARCHAR2
IS
  TYPE product_names IS TABLE OF NVARCHAR2(50) INDEX BY PLS_INTEGER;

  FUNCTION all_product_names (lang_id NUMBER) RETURN product_names
    RESULT_CACHE RELIES_ON (Product_Descriptions)
  IS
    all_names product_names;
  BEGIN
    FOR c IN (SELECT * FROM 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;