この章では、文の集合を、再利用可能なサブプログラムとして作成する方法について説明します。サブプログラムは、メンテナンスしやすいモジュール構造のアプリケーションのビルディング・ブロックです。
ここでのトピック:
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環境では、各データベース・インスタンスに対してこの手順を実行します。