| Oracle Database PL/SQL言語リファレンス 11g リリース1（11.1） E05670-03 |
|
 戻る |
 次へ |
この章では、文の集合を、再利用可能なサブプログラムとして作成する方法について説明します。サブプログラムは、メンテナンスしやすいモジュール構造のアプリケーションのビルディング・ブロックです。
ここでのトピック:
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では、無名ブロック内でプロシージャを（同時に）宣言および定義しています。 プロシージャには、必須の実行部とオプションの例外処理部がありますが、オプションの宣言部はありません。 プロシージャは、ブロックの実行部によって起動されます。
例8-1 単純なPL/SQLプロシージャの宣言、定義および起動
-- 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文」を参照してください。
ファンクションのヘッダーにのみ、次のオプションを含めることができます。
| オプション | 説明 |
|---|---|
DETERMINISTICオプション |
オプティマイザが冗長なファンクション・コールを回避するために役立ちます。 |
PARALLEL_ENABLEDオプション |
ファンクションがパラレルDML評価のスレーブ・セッションで安全に使用できるようにします。 |
PIPELINEDオプション |
テーブル・ファンクションの結果を反復的に戻します。 |
RESULT_CACHEオプション |
ファンクションの結果をPL/SQLファンクション結果キャッシュに格納します。 |
RESULT_CACHE句 |
ファンクションの結果のデータ・ソースを指定します。 |
|
参照:
|
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では、無名ブロック内でファンクションを（同時に）宣言および定義しています。 ファンクションには、オプションの宣言部と必須の実行部がありますが、オプションの例外処理部はありません。 ファンクションはブロックの実行部によって起動されます。
例8-2 単純なPL/SQLファンクションの宣言、定義および起動
-- 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つのプロシージャを作成しています。
例8-3 相互に起動し合うネストしたサブプログラムの作成
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; /
サブプログラムのヘッダーで、仮パラメータを宣言できます。 仮パラメータの各宣言で、モードおよびデフォルト値を指定できます。 サブプログラムの起動時に、サブプログラムに実パラメータを渡すことができます。
ここでのトピック:
仮パラメータは、サブプログラムのヘッダーで宣言され、サブプログラムの実行部で参照される変数です。実パラメータは、サブプログラムの起動時にそのサブプログラムに渡す変数または式です。 対応する仮パラメータと実パラメータのデータ型には、互換性が必要です。
例8-4に示すように、プログラミングの習慣として、仮パラメータと実パラメータには別々の名前を付けることをお薦めします。
例8-4 仮パラメータと実パラメータ
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;
/
サブプログラムを起動すると、PL/SQLによって各実パラメータが評価され、対応する仮パラメータにそれぞれの値が代入されます。 必要に応じて、PL/SQLは、代入前に、実パラメータのデータ型を対応する仮パラメータのデータ型に暗黙的に変換します（このため、対応する仮パラメータと実パラメータのデータ型に互換性が必要となります）。 暗黙的な変換の詳細は、「暗黙的な変換」を参照してください。
明示的な変換（「明示的な変換」を参照）を使用するか、または対応する仮パラメータと同じデータ型の実パラメータとして使用する変数を宣言することによって暗黙的変換を回避することは、推奨されるプログラミング方法です。たとえば、pkgに次の仕様部があるとします。
PACKAGE pkg IS
PROCEDURE s (n IN PLS_INTEGER);
END pkg;
pkg.sを次のように起動すると、暗黙的な変換は実行されません。
DECLARE
y PLS_INTEGER :=1;
BEGIN
pkg.s(y);
END;
pkg.sを次のように起動すると、暗黙的な変換が実行されます。
DECLARE
y INTEGER :=1;
BEGIN
pkg.s(y);
END;
|
注意: Oracle提供の多くのパッケージと型の仕様部では、次の表記法を使用して仮パラメータを宣言します。i1 IN VARCHAR2 CHARACTER SET ANY_CS i2 IN VARCHAR2 CHARACTER SET i1%CHARSET 独自の仮パラメータまたは実パラメータを宣言する場合は、この表記法を使用しないでください。この表記法は、Oracle提供のパッケージ・タイプの実装のために予約されています。 |
パラメータ・モードは、仮パラメータのアクションを定義します。パラメータ・モードには、IN（デフォルト）、OUTおよびIN OUTの3つがあります。
任意のサブプログラムで任意のパラメータ・モードを使用できます。ファンクションでは、OUTモードとIN OUTモードを使用しないでください。ファンクションが複数の値を戻すようなプログラミングは、好ましくありません。また、サブプログラム専用ではない変数の値を変更する副作用がファンクションで発生しないようにしてください。
ここでのトピック:
INパラメータは、起動されるサブプログラムに値を渡すために使用します。サブプログラムの中では、INパラメータは定数のように取り扱われます。値は代入できません。
定数、リテラル、初期化された変数または式をINパラメータとして渡せます。
INパラメータはデフォルト値で初期化できます。デフォルト値は、サブプログラム・コールでINパラメータが省略された場合に使用されます。 詳細は、「サブプログラムのパラメータのデフォルト値の指定」を参照してください。
OUTパラメータは、サブプログラムのコール元に値を戻します。サブプログラムの中では、OUTパラメータは変数のように取り扱われます。 例8-5に示すように、値を変更して、代入後に値を参照できます。
例8-5 OUTモードの使用
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;
/
OUTパラメータには変数を渡す必要があります。定数または式は渡せません。NOCOPYキーワードを指定していない場合、または未処理例外が発生してサブプログラムが正常に終了した場合、以前の値は失われます。 詳細は、「サブプログラムのパラメータのデフォルト値の指定」を参照してください。
OUTパラメータの初期値はNULLです。このため、OUTパラメータのデータ型は、組込みサブタイプNATURALNやPOSITIVENなどのNOT NULLとして定義されたサブタイプにはできません。これに従わなかった場合、サブプログラムの起動時にPL/SQLはVALUE_ERRORを呼び出します。
サブプログラムを終了する前に、すべてのOUT仮パラメータに値を代入してください。そうしないと、対応する実パラメータの値はNULLになります。正常に終了した場合、PL/SQLは実パラメータに値を代入します。未処理例外が発生して終了すると、PL/SQLは実パラメータに値を代入しません。
IN OUTパラメータは、サブプログラムに初期値を渡し、更新された値をコール元に戻します。IN OUTパラメータに値を代入したり、その値を読み取ることができます。通常、IN OUTパラメータは文字列バッファまたは数値アキュムレータであり、サブプログラム内で読み取られた後に更新されます。
IN OUT仮パラメータに対応する実パラメータは、定数や式ではなく、変数である必要があります。
サブプログラムを正常に終了した場合、PL/SQLは実パラメータに値を代入します。未処理例外が発生して終了すると、PL/SQLは実パラメータに値を代入しません。
表8-1に、パラメータ・モードの特性の概要を示します。
表8-1 パラメータのモード
| IN | OUT | IN OUT |
|---|---|---|
|
デフォルト |
指定する必要がある |
指定する必要がある |
|
サブプログラムに値を渡す |
コール元に値を戻す |
サブプログラムに初期値を渡し、更新された値をコール元に戻す |
|
仮パラメータは定数のように取り扱われる |
仮パラメータは初期化されてない変数のように取り扱われる |
仮パラメータは初期化された変数のように取り扱われる |
|
仮パラメータに値を代入できない |
仮パラメータには値を代入する必要がある |
仮パラメータには値を代入する必要がある |
|
実パラメータには定数、リテラル、初期化された変数または式が使用できる |
実パラメータは変数である必要がある |
実パラメータは変数である必要がある |
|
実パラメータは参照方式によって渡される（コール元がサブプログラムに値を指すポインタを渡す） |
|
|
IN仮パラメータをデフォルト値に初期化することで、サブプログラムに様々な数の実パラメータを渡し、省略された実パラメータにデフォルト値を指定できます。さらに、サブプログラムへのコールを個々に変更しなくても、仮パラメータを新しく追加できます。
実パラメータが省略されると、対応する仮パラメータのデフォルト値が使用されます。
実パラメータを省略して、仮パラメータを1つスキップすることはできません。 最初のパラメータを省略して2番目のパラメータを指定するには、名前表記法を使用します（「位置表記法、名前表記法または混合表記法でのサブプログラムの実パラメータの引渡し」を参照）。
実パラメータを省略して、初期化されていない仮パラメータにNULLを代入することはできません。デフォルト値としてNULLを代入するか、または明示的にNULLを渡す必要があります。
例8-6に、サブプログラム・パラメータのデフォルト値の使用法を示します。
例8-6 デフォルトのパラメータ値を使用したプロシージャ
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;
/
例8-7に示すように、仮パラメータのデフォルト値が式である場合、サブプログラムの起動時に対応する実パラメータを指定しても、その式は評価されません。
例8-7 デフォルト値として式が指定されている仮パラメータ
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;
例8-7の出力は次のようになります。
1 2 3 4 5 Cnt: 0 42 Cnt: 1
サブプログラムの起動時に、位置表記法、名前表記法または混合表記法のいずれかを使用して実パラメータを指定できます。表8-2では、これらの表記法を比較しています。
表8-2 PL/SQLサブプログラムのパラメータの表記法
| 表記法 | 説明 | 使用上の注意 |
|---|---|---|
|
位置 |
プロシージャで宣言されている順序と同じ順序で同じパラメータを指定します。 |
簡潔で読みやすいですが、次のデメリットがあります。
|
|
名前 |
結合演算子 |
位置表記法と比較すると、冗長ですが、読みやすく、メンテナンスも簡単です。（たとえば、パラメータの順序が変更されたり、新しいオプション・パラメータが追加されるなど、）プロシージャのパラメータ・リストが変更されたときに、コードを変更しなくてもよい場合があります。定義しなかったAPIを起動する場合、または他のユーザーが使用できるようにAPIを定義する場合、位置表記法より安全です。 |
|
混合 |
まず位置表記法を使用し、その後、残りのパラメータに名前表記法を使用します。 |
必須パラメータの後にオプションのパラメータが続くプロシージャを起動し、数個のみのオプションのパラメータを指定する必要がある場合に使用することをお薦めします。 |
例8-8に、位置表記法、名前表記法および混合表記法を使用した同等なサブプログラム・コールを示します。
例8-8 位置表記法、名前表記法または混合表記法でのサブプログラムのコール
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では、ローカル・サブプログラム、パッケージ・サブプログラムおよび型のメソッドをオーバーロードできます。 仮パラメータの数、順序またはデータ型が異なっていれば、同じ名前を複数のサブプログラムで使用できます。
例8-9では、同じ名前（initialize）を持つ2つのサブプログラムを定義しています。プロシージャでは様々な型のコレクションを初期化します。この2つのプロシージャは同じ処理を実行しているため、同じ名前を与えるのが論理的です。
この2つのinitializeプロシージャは、同じブロック、サブプログラム、パッケージまたはオブジェクト型の中に置くことができます。PL/SQLは仮パラメータをチェックして、どちらのプロシージャを起動するかを判断します。PL/SQLが使用するinitializeのバージョンは、プロシージャをdate_tab_typパラメータまたはnum_tab_typパラメータのどちらで起動するかによって異なります。
例8-9 サブプログラム名のオーバーロード
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を参照してください。
ここでのトピック:
サブプログラムの仮パラメータの違いが数値データ型のみの場合、それらのサブプログラムはオーバーロードできます。ファンクションの複数のバージョンが同じ名前を使用でき、それぞれが異なる数値型を受け取ることができるため、この手法は数値演算Application Programming Interface（API）を記述する場合に有効です。たとえば、BINARY_FLOATを受け取るファンクションはより高速で、BINARY_DOUBLEを受け取るファンクションは精度がより高くなる場合があります。
オーバーロードされたサブプログラムにパラメータを渡す場合、次のことに注意して、問題または予期しない結果を回避します。
予期されるパラメータ・セットごとに、目的のバージョンのサブプログラムが起動されることを確認します。たとえば、オーバーロードされるファンクションがBINARY_FLOATおよびBINARY_DOUBLEを受け取る場合、「5.0」のようなVARCHAR2リテラルを渡すと、どちらのファンクションが起動されるかを確認します。
数値リテラルを修飾したり、変換ファンクションを使用して、目的のパラメータ型を明示します。たとえば、5.0f（BINARY_FLOAT）、5.0d（BINARY_DOUBLE）のようなリテラル、またはTO_BINARY_FLOAT、TO_BINARY_DOUBLE、TO_NUMBERのような変換ファンクションを使用します。
PL/SQLは、次の順序で、一致する数値パラメータを検索します。
PLS_INTEGER（または同じデータ型であるBINARY_INTEGER）
NUMBER
BINARY_FLOAT
BINARY_DOUBLE
VARCHAR2値は、NUMBER、BINARY_FLOATまたはBINARY_DOUBLEパラメータに一致します。
PL/SQLは、指定されたパラメータに最初に一致してオーバーロードされるサブプログラムを使用します。たとえば、SQRTファンクションは1つのパラメータを受け取ります。NUMBER、BINARY_FLOATまたはBINARY_DOUBLEパラメータを受け取るオーバーロードされるファンクションがあるとします。PLS_INTEGERパラメータを渡した場合、最初に一致してオーバーロードされるファンクションは、NUMBERパラメータを持つファンクションです。
NUMBERパラメータを受け取るSQRTファンクションは、最も低速となる可能性が高くなります。 より高速なバージョンを使用するには、パラメータをSQRTファンクションに渡す前に、TO_BINARY_FLOATまたはTO_BINARY_DOUBLEファンクションを使用して、そのパラメータを別のデータ型に変換します。
PL/SQLは、パラメータを別のデータ型に変換する必要がある場合、まずそのパラメータをより上位のデータ型に変換しようとします。次に例を示します。
ATAN2ファンクションは、同じ型の2つのパラメータを受け取ります。異なる型のパラメータを渡した場合（たとえば、1つがPLS_INTEGERで、1つがBINARY_FLOATの場合）、PL/SQLは、両方のパラメータでより高度な型が使用されている場合に一致するものを検索します。この例では、2つのBINARY_FLOATパラメータを受け取るバージョンのATAN2が使用されます。PLS_INTEGERパラメータは、上位変換されます。
あるファンクションは、異なる型の2つのパラメータを受け取ります。オーバーロードされるバージョンの1つは、PLS_INTEGERパラメータおよびBINARY_FLOATパラメータを受け取ります。別のオーバーロードされるバージョンは、NUMBERパラメータおよびBINARY_DOUBLEパラメータを受け取ります。このファンクションを起動して、2つのNUMBERパラメータを渡した場合、PL/SQLは、まず2つ目のパラメータがBINARY_FLOATのオーバーロードされるバージョンを検出します。このパラメータは、他方のオーバーロードされるバージョンのBINARY_DOUBLEパラメータよりも一致度が高いため、次にPL/SQLは下位検索し、1つ目のNUMBERパラメータをPLS_INTEGERに変換します。
スタンドアロン・サブプログラム
仮パラメータの違いがモードのみのサブプログラム。次に例を示します。
PACKAGE pkg IS PROCEDURE s (p IN VARCHAR2); PROCEDURE s (p OUT VARCHAR2); END pkg;
仮パラメータの違いがサブタイプのみのサブプログラム。次に例を示します。
PACKAGE pkg IS PROCEDURE s (p INTEGER); PROCEDURE s (p REAL); END pkg;
INTEGERおよびREALは、NUMBERのサブタイプであるため、同じデータ型のファミリに属しています。
戻り値のデータ型のみが異なるファンクション（そのデータ型のファミリが異なっている場合でも）。次に例を示します。
PACKAGE pkg IS FUNCTION f (p INTEGER) RETURN BOOLEAN; FUNCTION f (p INTEGER) RETURN INTEGER; END pkg;
PL/SQLコンパイラは、起動されたサブプログラムを判別できなくなると判断するとすぐに、オーバーロード・エラーを捕捉します。複数のサブプログラムに同一のヘッダーがある場合、サブプログラム自体のコンパイル（サブプログラムがローカルである場合）、またはサブプログラムを宣言しているパッケージ仕様部のコンパイル（サブプログラムがパッケージ化されている場合）を試行すると、コンパイラはオーバーロード・エラーを捕捉します。複数のサブプログラムに同一のヘッダーがない場合は、サブプログラムの曖昧な起動のコンパイルを試行すると、コンパイラはエラーを捕捉します。
例8-10に示す、同一のヘッダーを持つ複数のサブプログラムを宣言しているパッケージ仕様部をコンパイルしようとすると、コンパイル時エラーPLS-00305が発生します。
例8-10 コンパイル時エラーの原因となるオーバーロード違反が含まれているパッケージ仕様部
PACKAGE pkg1 IS PROCEDURE s (p VARCHAR2); PROCEDURE s (p VARCHAR2); END pkg1;
例8-11に示すパッケージ仕様部は、仮パラメータの違いがサブタイプのみのサブプログラムはオーバーロードできないという規則に違反していますが、エラーを生成せずにコンパイルできます。
例8-11 エラーを生成せずにコンパイルできる、オーバーロード違反が含まれているパッケージ仕様部
PACKAGE pkg2 IS SUBTYPE t1 IS VARCHAR2(10); SUBTYPE t2 IS VARCHAR2(10); PROCEDURE s (p t1); PROCEDURE s (p t2); END pkg2;
ただし、例8-12に示すようにpkg2.sの起動をコンパイルしようとすると、コンパイル時エラーPLS-00307が発生します。
例8-12 不適切にオーバーロードされたサブプログラムの起動
PROCEDURE p IS
a pkg.t1 := 'a';
BEGIN
pkg.s(a) -- Causes compile-time error PLS-00307;
END p;
次に示すように、オーバーロードされたサブプログラムの仮パラメータに別の名前を付けることによって、例8-11に示されているオーバーロード違反を修正するとします。
PACKAGE pkg2 IS SUBTYPE t1 IS VARCHAR2(10); SUBTYPE t2 IS VARCHAR2(10); PROCEDURE s (p1 t1); PROCEDURE s (p2 t2); END pkg2;
これによって、名前表記法を使用して実パラメータを指定する場合に、エラーを生成せずにpkg2.sの起動をコンパイルできます。次に例を示します。
PROCEDURE p IS
a pkg.t1 := 'a';
BEGIN
pkg.s(p1=>a); -- Compiles without error
END p;
例8-12に示されているように位置表記法を使用して実パラメータを指定すると、コンパイル時エラーPLS-00307が発生します。
例8-13に示すパッケージ仕様部は、オーバーロード規則に違反していないため、エラーを生成せずにコンパイルできます。 ただし、オーバーロードされたプロシージャを起動すると、例8-14の2つ目の起動に示すように、コンパイル時エラーPLS-00307が発生します。
図8-1に、PL/SQLコンパイラがサブプログラム・コールを解決する方法を示します。コンパイラは、サブプログラム・コールを検出すると、そのコールに合う宣言を検索します。コンパイラはまず現在の有効範囲を検索し、必要ならば外側の有効範囲を順に検索します。コールされたサブプログラムの名前と同じ名前のサブプログラム宣言が1つ以上見つかると、コンパイラはさらに厳密に検索します。
同じ有効範囲のレベルに同じような名前のサブプログラムが存在する場合は、コールを解決するために、コンパイラは実パラメータと仮パラメータが正確に一致するものを発見する必要があります。 パラメータは、（いくつかの仮パラメータにデフォルト値が代入されている場合を除き）数、順序およびデータ型が一致している必要があります。一致するものが見つからなかった場合、または一致するものが複数見つかった場合、コンパイラはセマンティック・エラーを生成します。
例8-15は、ファンクションbalanceから外側のプロシージャswapを起動しています。現在の有効範囲の中にあるswapの宣言が、いずれもプロシージャ・コールと一致しないために、コンパイラはエラーを生成します。
例8-15 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
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;
/
ストアドPL/SQLユニットのAUTHIDプロパティは、実行時にユニットによって発行されるSQL文の名前解決および権限チェックに影響します。 AUTHIDプロパティはコンパイルには影響しません。また、コレクション型などのコードのないユニットに対しては意味を持ちません。
AUTHIDプロパティ値は、静的データ・ディクショナリ・ビュー*_PROCEDURESに公開されます。 AUTHIDが意味を持つユニットの場合はビューに値CURRENT_USERまたはDEFINERが表示され、他のユニットの場合はビューにNULLが表示されます。
次の文で作成または変更したストアドPL/SQLユニットでは、オプションのAUTHID句を使用して、CURRENT_USERまたはDEFINERのいずれかを指定できます。 デフォルトはDEFINERです。
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ユニットまたはビューがコール・スタックに格納されたり、コール・スタックから取り出されると、変更される場合があります。
|
注意: ALTER SESSION SET CURRENT_SCHEMAは、ストアド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であるか決まり、その結果に従ってユニットは処理されます。
ここでのトピック:
使用例: 表へのアクセスが制限されていないプロシージャを持つAPIを作成するとします。ただし、一般ユーザーが表のデータを直接選択し、INSERT文、UPDATE文およびDELETE文を使用して変更しないようにする必要があります。
解決方法: 特別なスキーマで、APIを構成する表およびプロシージャを作成します。 デフォルトでは各プロシージャはDRユニットであるため、作成時にAUTHID DEFINERを指定する必要はありません。 他のユーザーに対しては、EXECUTE権限を付与し、データにアクセスする権限は付与しないようにします。
使用例: 開発者に対してコンパイル・エラーを表示するPL/SQLプロシージャを作成するとします。 プロシージャは静的データ・ディクショナリ・ビューALL_SOURCEとALL_ERRORSを結合し、プロシージャDBMS_OUTPUT.PUT_LINEを使用して、エラーのリストの後に、各エラーの発生箇所を示す番号の付いたソース行のウィンドウを表示します。 すべての開発者がプロシージャを実行できるようにする必要があります。また、プロシージャが各開発者をALL_SOURCEおよびALL_ERRORSに対してCURRENT_USERとして扱うようにする必要があります。
解決方法: プロシージャの作成時に、AUTHID CURRENT_USERを指定します。 PUBLICに対してEXECUTE権限を付与します。 このプロシージャはIRユニットであるため、ALL_SOURCEおよびALL_ERRORSは、プロシージャを起動するユーザーの権限で動作します。
|
注意: 別の解決方法として、プロシージャをDRユニットにして、DBA_SOURCEおよびDBA_ERRORSの両方に対するSELECT権限を所有者に付与する方法もあります。 ただし、この解決方法では、プログラムの作成が難しくなるのみでなく、EXECUTE権限を持たないユニットのソース・コードをユーザーが参照できないようにするという条件を監査することが非常に難しくなります。 |
SQLコマンドSET ROLEは、コール・スタックにDRユニットがない場合にのみ正常に実行されます。 少なくとも1つのDRユニットがコール・スタックにある場合、SET ROLEコマンドを発行すると、ORA-06565が発生します。
|
注意: PL/SQLからSET ROLEコマンドを実行するには、動的SQLを使用する必要があります。EXECUTE IMMEDIATE文が推奨です。 この文の詳細は、「EXECUTE IMMEDIATE文の使用」を参照してください。 |
PL/SQLコンパイラは、表および他のオブジェクトへのすべての参照をコンパイル時に解決する必要があります。IRサブプログラムの所有者は、同じスキーマ内に正しい名前と列のオブジェクトを持つ必要があります。オブジェクトには、データが存在していなくてもかまいません。実行時に、起動元のスキーマ内の対応するオブジェクトは、一致する定義を持っている必要があります。そうでない場合、エラーまたは予期しない結果（起動元のスキーマには存在するがサブプログラムを含むスキーマには存在しない表の列が無視されるなど）が発生します。
IRユニットに対する実行時名前解決規則（起動するたびに同じ未修飾の名前が異なるオブジェクトに解決される）は適切でない場合があります。 むしろ、起動のたびに特定のオブジェクトを使用する必要があります。 ただし、別の理由からIRユニットが必要になります。 たとえば、CURRENT_USERに対する権限を評価することが重要である場合があります。 このような状況では、オブジェクトを所有するスキーマで名前を修飾します。
パブリック・シノニムであることを示す未修飾の名前は、CURRENT_USERのスキーマに競合する名前がある場合、取得されるリスクがあることに注意してください。 パブリック・シノニムは"PUBLIC"で修飾できます。 PUBLICを二重引用符で囲む必要があります。次に例を示します。
SELECT sysdate INTO today FROM "PUBLIC".DUAL;
|
注意: SQL文ALTER SESSION SET CURRENT_SCHEMAは、ストアドPL/SQLユニット内から発行しないことをお薦めします。 |
ビュー式内で実行されるIRサブプログラムの場合は、ビューを問い合せているユーザーではなく、ビューを作成したユーザーが現行ユーザーとみなされます。この規則は、データベース・トリガーにも適用されます。
|
注意: SYS_CONTEXTがビューのSQL文の定義で直接使用されると、CURRENT_USERに戻される値は、ビューの所有者ではなく問い合せているユーザーになります。 |
CREATE DATABASE LINK link_name CONNECT TO CURRENT_USER USING connect_string;
現行ユーザー・リンクでは、そのユーザー権限を持つ別のユーザーとしてリモート・データベースに接続できます。 接続するために、データベースでは現行ユーザーのユーザー名が使用されます（実行者はグローバル・ユーザーである必要があります）。ユーザーOEが所有するIRサブプログラムが、次のデータベース・リンクを参照するとします。グローバル・ユーザーHRがそのサブプログラムを起動していれば、現行ユーザーであるユーザーHRでデータベースDallasに接続します。
CREATE DATABASE LINK dallas CONNECT TO CURRENT_USER USING ...
定義者権限サブプログラムの場合、現行ユーザーはOEであり、サブプログラムはグローバル・ユーザーOEでデータベースDallasに接続します。
任意のスキーマで使用するオブジェクト型を定義するために、AUTHID CURRENT_USER句を指定します。オブジェクト型の詳細は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください。
例8-16で、ユーザーHRがオブジェクト型を作成するとします。
例8-16 AUTHID CURRENT USERを使用したオブジェクト型の作成
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;
/
次にユーザーHRは、オブジェクト型person_typに対するEXECUTE権限を、ユーザーOEに付与します。
GRANT EXECUTE ON person_typ TO OE;
最後に、ユーザーOEは、person_typ型のオブジェクトを格納するためにオブジェクト表を作成し、次にプロシージャnew_person_typを起動して、その表にデータを入れます。
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;
/
コールは成功しました。これはプロシージャがその所有者（HR）の権限ではなく現行ユーザー（OE）の権限で実行されたためです。
オブジェクト型階層内のサブタイプには、次の規則が適用されます。
サブタイプでAUTHID句が明示的に指定されていない場合は、スーパータイプのAUTHIDを継承します。
サブタイプでAUTHID句が指定されている場合、そのAUTHIDがスーパータイプのAUTHIDと一致する必要があります。また、AUTHIDがDEFINERの場合は、スーパータイプとサブタイプの両方が同じスキーマに作成されている必要があります。
IRインスタンス・メソッドは、インスタンスの作成者ではなく、実行者の権限で実行します。 person_typが例8-16で作成されたIRオブジェクト型で、ユーザーHRが、型person_typのオブジェクトであるp1を作成するとします。 例8-17に示すとおり、ユーザーOEが、オブジェクトp1で操作を行うためのインスタンス・メソッドchange_jobを起動する場合、メソッドの現行ユーザーは、HRではなくOEです。
例8-17 IRインスタンス・メソッドの起動
-- 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; /
再帰的サブプログラムとは、自分自身を起動するサブプログラムのことです。再帰的コールが行われるたびに、パラメータ、変数、カーソル、例外など、そのサブプログラムで宣言されているすべての項目の新しいインスタンスが作成されます。また、再帰を繰り返して進む過程の各レベルで、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ブロックから起動できます。
例8-18 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ファンクションへのコールを示します。
例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アドバンスト・アプリケーション開発者ガイド』を参照してください。
副作用が少なければ、特に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）であることを示します。
例8-20 RESTRICT_REFERENCESプラグマ
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コンパイラでは、2つのパラメータ引渡し方式のいずれかを選択できます。BY VALUE方式では、実パラメータの値がサブプログラムに渡されます。BY REFERENCE方式では、値へのポインタのみが渡されます。この場合、実パラメータと仮パラメータとは同じ項目を参照します。
NOCOPYコンパイラ・ヒントによって、エイリアシングの可能性が高くなります（つまり、異なる2つの名前が同じメモリー位置を参照するようになります）。これは、グローバル変数がサブプログラムのコールの中で実パラメータとして使用され、そのサブプログラム内で参照されると発生します。結果はコンパイラが選択するパラメータの引渡し方式に依存するため、予測不能になります。
例8-21では、プロシージャADD_ENTRYは、VARRAY LEXICONをパラメータとグローバル変数の両方として参照しています。ADD_ENTRYが起動されると、識別子WORD_LISTおよびLEXICONは同じVARRAYを指定します。
例8-21 NOCOPYヒントを指定したグローバル変数の引渡しによるエイリアシング
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;
/
コンパイラがNOCOPYヒントに従う場合、プログラムはaardwolfを出力します。WORD_LISTへの代入はポインタを通じてただちに実行され、LEXICONへの代入によって上書きされます。
NOCOPYヒントが省略された場合またはコンパイラがNOCOPYヒントに従わない場合、プログラムはaardvarkを出力します。WORD_LISTへの代入にはVARRAYの内部コピーが使用されます。これは、プロシージャの終了時に実パラメータにコピーされます（LEXICONの内容を上書きします）。
エイリアシングは、1回のサブプログラム・コールに、同じ実パラメータが2回以上現れる場合にも発生します。 例8-22では、n2がIN OUTのパラメータであるため、実パラメータの値は、プロシージャが終了するまで更新されません。そのため、最初のPUT_LINEは10（nの初期値）を出力し、3番目のPUT_LINEは20を出力します。ただし、n3はNOCOPYパラメータであるため、実パラメータの値はただちに更新されます。2番目のPUT_LINEが30を出力するのはこのためです。
例8-22 同じパラメータの複数回の引渡しによるエイリアシング
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番目のフェッチは失敗します。
例8-23 同じ作業領域へのカーソル変数の代入によるエイリアシング
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ファンクション結果キャッシュのメカニズムは、アプリケーションを実行するすべてのセッションで使用可能な共有グローバル領域（SGA）にPL/SQLファンクションの結果をキャッシュするための、言語サポートおよびシステム管理された手段を提供します。 このキャッシュ・メカニズムは、使いやすく効率的です。このメカニズムを使用すると、独自のキャッシュおよびキャッシュ管理ポリシーを設計および開発する負担がなくなります。
ファンクションの結果キャッシュを使用可能にするには、RESULT_CACHE句を使用します。結果がキャッシュされるファンクションが起動されると、システムによってキャッシュがチェックされます。 以前同じパラメータ値を指定してファンクションをコールした際の結果がキャッシュに含まれている場合は、キャッシュされた結果が起動元に戻され、ファンクション本体は再実行されません。キャッシュに結果が含まれていない場合は、ファンクション本体が実行され、制御が起動元に戻される前に、（これらのパラメータ値の）結果が追加されます。
|
注意: ファンクションを実行した結果、未処理例外が発生した場合、この例外結果はキャッシュに格納されません。 |
キャッシュには、非常に多くの結果を蓄積できます。つまり、結果がキャッシュされる各ファンクションの起動時に指定されたパラメータ値の一意の組合せごとに1つの結果を蓄積できます。システムでは、必要なメモリーが不足すると、キャッシュされた結果が1つ以上エージ・アウト（削除）されます。
キャッシュされる結果の計算に使用されるデータベース・オブジェクトは指定できます。このため、指定したデータベース・オブジェクトのいずれかが更新されると、キャッシュされた結果は無効となり、結果を再計算する必要があります。結果キャッシュの対象として最良のファンクションは、頻繁に起動され、ほとんどまたはまったく変更されない情報に依存するファンクションです。
ここでのトピック:
ファンクションの結果がキャッシュされるようにするには、次の操作を実行します。
ファンクション宣言に、オプションRESULT_CACHEを含めます。
ファンクション定義で、次の操作を実行します。
RESULT_CACHE句を含めます。
オプションのRELIES_ON句で、ファンクションの結果が依存する表またはビューを指定します。
RESULT_CACHE句およびRELIES_ON句の構文は、「ファンクション宣言と定義」を参照してください。
例8-24では、パッケージdepartment_pksが、結果がキャッシュされるファンクションget_dept_infoを宣言し、定義しています。このファンクションによって、指定した部門の平均給与および従業員数が戻されます。get_dept_infoは、データベース表EMPLOYEESに依存します。
例8-24 結果がキャッシュされるファンクションの宣言および定義
-- 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;
/
ファンクションget_dept_infoは、他のファンクションを起動する場合と同様に起動します。たとえば、次のコールでは、部門番号10の従業員の平均給与と従業員数が戻されます。
department_pks.get_dept_info(10);
次のコールでは、部門番号10の平均給与のみが戻されます。
department_pks.get_dept_info(10).average_salary;
get_dept_info(10)の結果がすでに結果キャッシュに含まれている場合、結果はこのキャッシュから戻されます。そうではない場合、結果は計算されてキャッシュに追加されます。RELIES_ON句にEMPLOYEESが指定されているため、EMPLOYEESが更新されると、department_pks.get_dept_infoのキャッシュされた結果はすべて無効になります。このため、EMPLOYEESが変更される可能性のあるすべての場所でキャッシュ無効化ロジックをプログラミングする必要がなくなります。
結果がキャッシュされるファンクションを使用するアプリケーションを開発する場合、指定したパラメータ値のセットに対してそのファンクションの本体が実行される回数については何も想定しないでください。
結果がキャッシュされるファンクションの本体が実行される状況をいくつか次に示します。
このデータベース・インスタンスでのセッションが、これらのパラメータ値を使用してファンクションを初めて起動したとき
これらのパラメータ値のキャッシュされた結果が無効である場合
ファンクション定義のRELIES_ON句に指定されたデータベース・オブジェクトのいずれかが変更されると、キャッシュされた結果は無効になります。
これらのパラメータ値のキャッシュされた結果がエージ・アウトされた場合
システムでは、必要なメモリーが不足すると、キャッシュされた値で最も古いものが破棄されます。
ファンクションがキャッシュをバイパスする場合（「結果キャッシュのバイパス」を参照）
結果がキャッシュされるようにするには、ファンクションで次のすべての条件が満たされている必要があります。
実行者権限を持つモジュール内または無名ブロック内で定義されていない
パイプライン・テーブル・ファンクションでない
OUTパラメータまたはIN OUTパラメータを含んでいない
次のいずれの型のINパラメータも含んでいない
BLOB
CLOB
NCLOB
REF CURSOR
コレクション
オブジェクト
レコード
戻り型が次のいずれでもない
BLOB
CLOB
NCLOB
REF CURSOR
オブジェクト
前述のサポートされていない戻り型のいずれかが含まれているレコードまたはPL/SQLコレクション
結果がキャッシュされるファンクションでは、次の条件も満たされていることが推奨されています。
副作用がない
たとえば、ファンクションでDBMS_OUTPUTを起動したり、電子メールを送信することによって、データベースの状態または外部の状態が変更されないことが推奨されています。
セッション固有の設定に依存しない
詳細は、「結果がキャッシュされるファンクションによるセッション固有の設定の処理」を参照してください。
セッション固有のアプリケーション・コンテキストに依存しない
詳細は、「結果がキャッシュされるファンクションによるセッション固有のアプリケーション・コンテキストの処理」を参照してください。
結果キャッシュの対象として最良のファンクションは、（最初の例がこれに該当している可能性がありますが）頻繁に起動され、ほとんど変更されない情報に依存するファンクションです。結果キャッシュを行うことによって、再帰ファンクションでの冗長計算が回避されます。
例
グローバル・レベル、アプリケーション・レベルまたはロール・レベルのいずれのレベルで設定できる構成パラメータを持つアプリケーションについて考えてみます。このアプリケーションは、構成情報を次の表に格納します。
-- 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) );
各構成パラメータで、ロール・レベルの設定はアプリケーション・レベルの設定をオーバーライドし、アプリケーション・レベルの設定はグローバル設定をオーバーライドします。パラメータに適用される設定を決定するために、このアプリケーションはPL/SQLファンクションget_valueを定義します。パラメータ名、アプリケーションIDおよびロールIDを指定すると、get_valueはそのパラメータに適用される設定を戻します。
ファンクションget_valueが頻繁に起動され、構成情報はほとんど変更されない場合、このファンクションは結果キャッシュの対象として最良のファンクションとなります。global_config_params、app_level_config_paramsまたはrole_level_config_paramsに対する変更がコミットされるとget_valueのキャッシュされた結果が無効になるようにするには、RELIES_ON句にそれらの名前を含めます。
例8-25に、get_valueに指定可能な定義を示します。
例8-25 構成パラメータの設定を戻す、結果がキャッシュされるファンクション
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;
フィボナッチ数列の数学的定義を模倣した、フィボナッチ数列のn番目の項を検索するための再帰ファンクションは、多くの冗長計算を実行する可能性があります。 たとえば、fibonacci(7)を評価するために、このファンクションはfibonacci(6)およびfibonacci(5)を計算する必要があります。 fibonacci(6)を計算するために、このファンクションはfibonacci(5)およびfibonacci(4)を計算する必要があります。 このため、fibonacci(5)などのいくつかの項は、重複して計算されます。結果キャッシュを行うことによって、これらの冗長計算が回避されます。RELIES_ON句は不要です。
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;
/
ここでのトピック:
結果がキャッシュされるファンクションが異なるパラメータ値で起動されるたびに、それらのパラメータおよびそれぞれの結果がキャッシュに格納されます。それ以降、同じファンクションが同じパラメータ値で起動されると（つまり、キャッシュ・ヒットがある場合）、結果は再計算されるのではなく、キャッシュから取り出されます。
キャッシュ・ヒット用のパラメータ比較の規則は、次に示すように、PL/SQLの「等号」（=）演算子の規則とは異なります。
| キャッシュ・ヒットの規則 | 「等号」演算子の規則 |
|---|---|
NULLはNULLと同じです。 |
NULL = NULLの評価結果はNULLです。 |
NULLでないスカラーは、それぞれの値が同一である場合にのみ同じとなります。つまり、指定されたプラットフォームでそれぞれの値が同一のビット・パターンを持っている場合にのみ同じとなります。たとえば、CHAR値'AA'と'AA 'は同じではありません（この規則の方が、「等号」演算子の規則より厳密です）。 |
NULLでないスカラーは、指定されたプラットフォームでそれぞれの値が同一のビット・パターンを持っていない場合でも、等しくなる可能性があります。たとえば、CHAR値'AA'と'AA 'は等しくなります。 |
場合によって、キャッシュはバイパスされます。キャッシュがバイパスされる場合を次に示します。
ファンクションが結果をキャッシュから取り出すのではなく、計算する場合。
ファンクションが計算する結果がキャッシュに追加されない場合。
キャッシュがバイパスされる場合の例を次にいくつか示します。
すべてのセッションでキャッシュを使用できない場合。
たとえば、データベース管理者がアプリケーションへのパッチの適用中に、結果キャッシュを使用できない状態にした場合などです（「結果がキャッシュされるファンクションが依存するPL/SQLユニットへのホット・パッチの適用」を参照）。
結果がキャッシュされるファンクションのRELIES_ON句に指定された表またはビューに対して、セッションがDML文を実行している場合。このセッションは、そのDML文が完了するまで（コミットまたはロールバックされるまで）そのファンクションの結果キャッシュをバイパスし、その後、そのファンクションのキャッシュの使用を再開します。
キャッシュをバイパスすると、次のことが保証されます。
各セッションのユーザーは、コミットされていないユーザー独自の変更を参照できます。
PL/SQLファンクションの結果キャッシュには、すべてのセッションで参照可能なコミットされた変更のみが含まれます。このため、あるセッションでコミットされていない変更は、他のセッションでは参照できません。
セッションによって異なる可能性がある設定（NLS_DATE_FORMATやTIME ZONEなど）にファンクションが依存している場合は、様々な設定を処理できるようにそのファンクションを変更できる場合にのみ、そのファンクションの結果がキャッシュされるようにします。
次のファンクションについて考えてみます。
例8-26
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; /
前述のファンクション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 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, fmt); END; /
アプリケーション・コンテキストとは、属性とそれらの値の集合のことです。アプリケーション・コンテキストは、グローバルまたはセッション固有のいずれかになります。PL/SQLファンクションは、次の1つ以上の項目を実行する場合、セッション固有のアプリケーション・コンテキストに依存します。
指定したコンテキストで指定した属性の値を戻す組込みファンクションSYS_CONTEXTの直接起動
ファイングレイン・セキュリティのための仮想プライベート・データベース（VPD）・メカニズムを使用したSYS_CONTEXTの間接起動
（VPDの詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください）
PL/SQLファンクションの結果キャッシュ機能は、セッション固有のアプリケーション・コンテキストへの依存性を自動的には処理しません。セッション固有のアプリケーション・コンテキストに依存しているファンクションの結果をキャッシュする必要がある場合は、アプリケーション・コンテキストをパラメータとしてファンクションに渡す必要があります。このパラメータにはデフォルト値を指定できるため、すべてのユーザーがこのパラメータを指定する必要があるわけではありません。
例8-27では、表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-27 セッション固有のアプリケーション・コンテキストに依存する、結果がキャッシュされるファンクション
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; /
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-3 粒度が細かいキャッシュと粗いキャッシュの比較
| 粒度が細かい | 粒度が粗い |
|---|---|
|
各ファンクション結果は、1つの論理結果に対応しています。 |
各ファンクション結果には、多数の論理的部分結果が含まれています。 |
|
1回以上必要とされるデータのみを格納します。 |
使用されることのないデータを格納する場合もあります。 |
|
各データ項目は、個別にエージ・アウトされます。 |
1つのデータ項目がエージ・アウトされると、全体がエージ・アウトされます。 |
|
バルク・ロードは最適化できません。 |
バルク・ロードを最適化できます。 |
次の4つの各例では、ファンクションproductNameがPRODUCT_IDおよびLANGUAGE_IDを受け取って、関連付けられたTRANSLATED_NAMEを戻しています。productNameの各バージョンは、変換された名前をキャッシュしますが、キャッシュする際の粒度はそれぞれ異なっています。
例8-28では、get_product_name_1は結果がキャッシュされるファンクションです。get_product_name_1は、別のPRODUCT_IDおよびLANGUAGE_IDで起動されると、常に関連付けられたTRANSLATED_NAMEをキャッシュします。get_product_name_1がコールされるたびに、最大1つのTRANSLATED_NAMEがキャッシュに追加されます。
例8-28 一度に1つの名前のキャッシュ（粒度が細かい）
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;
例8-29では、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-29 一度に1つの言語の変換された名前のキャッシュ（粒度が粗い）
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;
キャッシュされた結果はシステム・グローバル領域（SGA）に格納されます。Oracle RAC環境では、各データベース・インスタンスに、そのインスタンス上のセッションでのみ使用できるプライベートなファンクション結果キャッシュがあります。
インスタンスのアクセス・パターンおよびワークロードによって、そのインスタンスのプライベート・キャッシュに格納される結果セットが決定されます。このため、インスタンスが異なると、そのプライベート・キャッシュに格納される結果セットも異なります。
必要な結果がローカル・インスタンスのプライベート・キャッシュから欠落している場合、ファンクションの本体が実行されて、結果が計算されます。その後、この結果がローカル・キャッシュに追加されます。この結果が別のインスタンスのプライベート・キャッシュから取り出されることはありません。
各データベース・インスタンスには、そのインスタンス独自のキャッシュされた結果セットが含まれている可能性がありますが、無効な結果を処理するメカニズムはOracle RAC環境全体にわたります。ローカル・インスタンスの結果キャッシュでのみ結果が無効にされた場合、他のインスタンスで無効な結果が使用される可能性があります。たとえば、データベース表内のデータから計算される品目の価格の結果キャッシュについて考えてみます。ある品目の価格に影響を与える方法でこれらのデータベース表のいずれかが更新された場合、キャッシュされたその品目の価格をOracle RAC環境内のすべてのデータベース・インスタンスで無効にする必要があります。
PL/SQLファンクション結果キャッシュは、その管理および管理性インフラストラクチャを結果キャッシュと共有します。結果キャッシュの詳細は、『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
データベース管理者は、次のものを使用して、結果キャッシュを管理できます。
RESULT_CACHE_MAX_SIZEおよびRESULT_CACHE_MAX_RESULT初期化パラメータ
RESULT_CACHE_MAX_SIZEは、結果キャッシュが使用できるSGAメモリーの最大量を（バイト単位で）指定し、RESULT_CACHE_MAX_RESULTは、1つの結果が使用できる結果キャッシュの最大パーセンテージを指定します。これらのパラメータの詳細は、『Oracle Databaseリファレンス』および『Oracle Databaseパフォーマンス・チューニング・ガイド』を参照してください。
|
参照:
|
DBMS_RESULT_CACHEパッケージ
DBMS_RESULT_CACHEパッケージでは、DBAが、SQL結果キャッシュおよびPL/SQLファンクション結果キャッシュによって使用される共有プールのその部分を管理できるインタフェースが提供されます。このパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
動的パフォーマンス・ビュー:
[G]V$RESULT_CACHE_STATISTICS
[G]V$RESULT_CACHE_MEMORY
[G]V$RESULT_CACHE_OBJECTS
[G]V$RESULT_CACHE_DEPENDENCY
[G]V$RESULT_CACHE_STATISTICS、[G]V$RESULT_CACHE_MEMORY、[G]V$RESULT_CACHE_OBJECTSおよび[G]V$RESULT_CACHE_DEPENDENCYの詳細は、『Oracle Databaseリファレンス』を参照してください。
結果がキャッシュされるファンクションが依存するPL/SQLユニットに（直接または間接的に）ホット・パッチを適用する際、結果がキャッシュされるファンクションに関連付けられているキャッシュされた結果がすべての場合に自動的にフラッシュされるとはかぎりません。
たとえば、結果がキャッシュされるファンクションP1.foo()がパッケージ・サブプログラムP2.bar()に依存しているとします。パッケージP2の本体の新しいバージョンがロードされた場合、P1.foo()に関連付けられているキャッシュされた結果は、自動的にはフラッシュされません。
このため、PL/SQLユニットへのホット・パッチの適用には、次の手順を実行することをお薦めします。
結果キャッシュをバイパス・モードに設定し、既存の結果をフラッシュします。
BEGIN DBMS_RESULT_CACHE.Bypass(TRUE); DBMS_RESULT_CACHE.Flush; END; /
Oracle RAC環境では、各データベース・インスタンスに対してこの手順を実行します。
PL/SQLコードにパッチを適用します。
結果キャッシュの使用を再開します。
BEGIN DBMS_RESULT_CACHE.Bypass(FALSE); END; /
Oracle RAC環境では、各データベース・インスタンスに対してこの手順を実行します。
