| Oracle® Databaseアドバンスト・アプリケーション開発者ガイド 11gリリース2 (11.2) B56259-09 |
|
 前 |
 次 |
ここでは、Oracle Databaseがアプリケーション開発用に提供するプロシージャ機能のいくつかを説明します。
内容は次のとおりです。
|
参照:
|
PL/SQLは、現代的なブロック構造化プログラミング言語です。この言語が持ついくつかの機能を使用すると、高性能のデータベース・アプリケーションを容易に作成できます。たとえば、PL/SQLは、ループ文や条件文など標準SQLにはないプロシージャ構造を提供します。
PL/SQLブロック内部でSQLデータ操作言語(DML)の文を直接入力できます。また、Oracleが提供するサブプログラムを使用して、データ定義言語(DDL)の文を実行できます。
PL/SQLコードはサーバー上で実行されるため、PL/SQLを使用するとデータベース・アプリケーションのかなりの部分を集中化でき、メンテナンス性およびセキュリティが強化されます。また、クライアント/サーバー・アプリケーションでは、ネットワークのオーバーヘッドも大幅に削減できます。
|
注意: Oracle Formsなど、Oracleの一部のツール製品にはPL/SQLエンジンが組み込まれ、ローカルでPL/SQLを実行できます。 |
また、一部のデータベース・アプリケーションでは、埋込みSQLまたはOracle Call Interface(OCI)を使用する3GLプログラムのかわりにPL/SQLを使用できます。
PL/SQLユニットには、次のものが含まれます。
|
参照:
|
無名ブロックとは、名前のないPL/SQLユニットのことです。無名ブロックは、オプションの宣言部分、実行可能部分および1つまたは複数のオプションの例外ハンドラで構成されます。
宣言部にはPL/SQLの変数、例外およびカーソルを宣言します。実行可能部分にはPL/SQLコードおよびSQL文を含むネストされたブロックを含めることができます。
例外ハンドラには、例外状況が発生したときに、事前定義のPL/SQL例外(NO_DATA_FOUNDまたはZERO_DIVIDE)として、またはユーザー定義の例外として起動されるコードが含まれています。
無名ブロックは、通常、SQL*Plusなどのツール製品から対話形式で使用するか、プリコンパイラ、OCIまたはSQL*Moduleアプリケーションで使用します。通常、ストアド・サブプログラムを起動するか、カーソル変数をオープンするために使用します。
例6-1の無名ブロックは、DBMS_OUTPUTパッケージを使用してHR.EMPLOYEES表の部門20のすべての従業員の名前を表示します。
例6-1 無名ブロック
DECLARE
last_name VARCHAR2(10);
cursor c1 IS
SELECT LAST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20
ORDER BY LAST_NAME;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO last_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(last_name);
END LOOP;
END;
/
結果:
Fay Hartstein
例外を使用すると、PL/SQLプログラム・ロジック内のOracle Databaseエラー条件を処理し、使用中のアプリケーションで、クライアント・アプリケーションを異常終了させるようなエラーをサーバーが発行しないようにすることができます。例6-2の無名ブロックは、事前定義されたOracle Database例外NO_DATA_FOUNDを処理します(この例外が処理されない場合は、ORA-01403が発生します)。
例6-2 事前定義されたエラーの例外ハンドラを持つ無名ブロック
DECLARE
Emp_number INTEGER := 9999
Emp_name VARCHAR2(10);
BEGIN
SELECT LAST_NAME INTO Emp_name
FROM EMPLOYEES
WHERE EMPLOYEE_ID = Emp_number;
DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);
END;
/
結果:
No such employee: 9999
また、独自の例外を定義することもできます。つまり、例6-3に示すように、ブロックの宣言部に宣言し、それをブロックの例外部分に指定できます。
例6-3 ユーザー定義例外の例外ハンドラを持つ無名ブロック
DECLARE Emp_name VARCHAR2(10); Emp_number INTEGER; Empno_out_of_range EXCEPTION; BEGIN Emp_number := 10001; IF Emp_number > 9999 OR Emp_number < 1000 THEN RAISE Empno_out_of_range; ELSE SELECT LAST_NAME INTO Emp_name FROM EMPLOYEES WHERE EMPLOYEE_ID = Emp_number; DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name); END IF; EXCEPTION WHEN Empno_out_of_range THEN DBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number || ' is out of range.'); END; /
結果:
Employee number 10001 is out of range.
|
参照:
|
ストアドPL/SQLユニットは、次のような特徴を持つサブプログラム(プロシージャまたはファンクション)またはパッケージです。
固有の名前を持っています。
パラメータをとり、値を戻すことができます。
データ・ディクショナリに格納されます。
多数のユーザーが起動できます。
パッケージに属しているサブプログラムをパッケージ・サブプログラム、そうでないサブプログラムをスタンドアロン・サブプログラムといいます。
内容は次のとおりです。
サブプログラムは、データベース内に格納されるため、名前を付ける必要があります。これによって、他のストアド・サブプログラムと区別され、アプリケーションで起動できます。パブリックで参照できるスキーマ内の各サブプログラムは、一意の名前を持つ必要があり、その名前は、有効なPL/SQL識別子である必要があります。
|
注意: SQL*Moduleによって生成されたスタブを使用してストアド・サブプログラムを起動する場合、ストアド・サブプログラム名は、起動側ホストの3GL言語(AdaやCなど)の有効な識別子である必要もあります。 |
ストアド・サブプログラムには、パラメータを指定できます。例6-4のプロシージャでは、部門番号が入力パラメータになっており、パラメータ化されたカーソルc1のオープン時に使用されます。
例6-4 パラメータを使用するストアド・プロシージャ
CREATE OR REPLACE PROCEDURE get_emp_names ( dept_num IN NUMBER ) IS emp_name VARCHAR2(10); CURSOR c1 (dept_num NUMBER) IS SELECT LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID = dept_num; BEGIN OPEN c1(dept_num); LOOP FETCH c1 INTO emp_name; EXIT WHEN C1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_name); END LOOP; CLOSE c1; END; /
サブプログラムの仮パラメータには、表6-1に示す3つの主要な属性があります。
表6-1 サブプログラム・パラメータの属性
| パラメータ属性 | 説明 |
|---|---|
|
名前 |
名前は、有効なPL/SQL識別子である必要があります。 |
|
モード |
入力のみのパラメータ( |
|
データ型 |
パラメータのデータ型は、標準PL/SQLデータ型です。 |
内容は次のとおりです。
パラメータ・モードは、仮パラメータの動作を定義します。サブプログラムには、IN(デフォルト)、OUT、IN OUTという3つのパラメータ・モードを使用できます。ファンクションには、OUTモードおよびIN OUTモードは使用しないでください。実際のプログラミングでは、ファンクションにより単一の値が戻され、かつサブプログラムに対してローカルでない変数の値が変更されないことが求められます。
|
参照: パラメータ・モードの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
NUMBERやVARCHAR2などの無制約の型名
%TYPE属性や%ROWTYPE属性を使用して制約される型
|
注意: NUMBER(2)または VARCHAR2(20)などの数値が制約される型は、パラメータ・リストでは使用できません。 |
型属性%TYPEおよび%ROWTYPEは、パラメータを制約するために使用します。たとえば、例6-4のプロシージャ・ヘッダーは次のように記述することができます。
PROCEDURE get_emp_names(dept_num IN EMPLOYEES.DEPARTMENT_ID%TYPE)
このようにすると、dept_numパラメータのデータ型がEMPLOYEES表のDEPARTMENT_ID列と同じになります。%TYPE(または%ROWTYPE)を使用した宣言を作成する場合は、列および表が使用可能である必要があります。
表の列の型が変更されてもアプリケーション・コードを変更する必要がないため、%TYPEの使用をお薦めします。
get_emp_namesプロシージャがパッケージの一部である場合は、前に宣言したパブリック(パッケージ)変数を使用して、パラメータのデータ型を制約できます。次に例を示します。
dept_number NUMBER(2); ... PROCEDURE get_emp_names(dept_num IN dept_number%TYPE);
%ROWTYPE属性は、指定された表のすべての列を含むレコードを作成するために使用します。例6-5のプロシージャは、指定された従業員IDに関するPL/SQLレコード内のEMPLOYEES表のすべての列を返します。
例6-5 %TYPEおよび%ROWTYPE属性
CREATE OR REPLACE PROCEDURE get_emp_rec ( emp_number IN EMPLOYEES.EMPLOYEE_ID%TYPE, emp_info OUT EMPLOYEES%ROWTYPE ) IS BEGIN SELECT * INTO emp_info FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_number; END; /
PL/SQLブロックからプロシージャを起動します。
DECLARE
emp_row EMPLOYEES%ROWTYPE;
BEGIN
get_emp_rec(206, emp_row);
DBMS_OUTPUT.PUT('EMPLOYEE_ID: ' || emp_row.EMPLOYEE_ID);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('FIRST_NAME: ' || emp_row.FIRST_NAME);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('LAST_NAME: ' || emp_row.LAST_NAME);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('EMAIL: ' || emp_row.EMAIL);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('PHONE_NUMBER: ' || emp_row.PHONE_NUMBER);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('HIRE_DATE: ' || emp_row.HIRE_DATE);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('JOB_ID: ' || emp_row.JOB_ID);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('SALARY: ' || emp_row.SALARY);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('COMMISSION_PCT: ' || emp_row.COMMISSION_PCT);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('MANAGER_ID: ' || emp_row.MANAGER_ID);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT('DEPARTMENT_ID: ' || emp_row.DEPARTMENT_ID);
DBMS_OUTPUT.NEW_LINE;
END;
/
結果:
EMPLOYEE_ID: 206 FIRST_NAME: William LAST_NAME: Gietz EMAIL: WGIETZ PHONE_NUMBER: 515.123.8181 HIRE_DATE: 07-JUN-02 JOB_ID: AC_ACCOUNT SALARY: 8300 COMMISSION_PCT: MANAGER_ID: 205 DEPARTMENT_ID: 110
ストアド・ファンクションは、%ROWTYPEを使用して宣言される値を戻すことができます。次に例を示します。
FUNCTION get_emp_rec (dept_num IN EMPLOYEES.DEPARTMENT_ID%TYPE) RETURN EMPLOYEES%ROWTYPE IS ...
PL/SQLコンポジット変数(コレクションおよびレコード)をパラメータとしてストアド・サブプログラムに渡すことができます。
サブプログラムがリモートである場合は、冗長なループバックDBLINKを作成する必要があります。これにより、リモート・サブプログラムがコンパイルされたときに、ソースを検証するタイプ・チェッカが、実行者の使用しているものと同じユーザー定義のコンポジット変数型定義を使用するようになります。
パラメータには、初期値を設定できます。パラメータに初期値を設定するには、代入演算子、またはDEFAULTキーワードを使用します。たとえば、次の2つは同じ設定です。
PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ... PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT 20) IS ...
パラメータに初期値を使用する場合は、サブプログラムの起動時に実パラメータ・リストからそのパラメータを省略できます。起動時に初期値を指定すると、デフォルト値がオーバーライドされます。
|
注意: 無名PL/SQLブロック内とは異なり、ストアド・サブプログラム内では、変数、カーソルおよび例外の宣言の前にキーワードDECLAREを使用しないでください。使用するとエラーが発生します。 |
サブプログラムを作成するには、テキスト・エディタを使用します。その後、SQL*Plusなどの対話形式のツール製品を使用して次の文を入力し、プロシージャを含むテキスト・ファイルをロードします。
@get_emp
プロシージャが、get_emp.sqlファイル(sqlは、デフォルトのファイル拡張子)から現行のスキーマにロードされます。コードの末尾にあるスラッシュ(/)はコードの一部ではなく、プロシージャのロードをアクティブにするためのものです。
|
注意: サブプログラムを作成する場合、通常はCREATE OR REPLACE PROCEDURE文またはCREATE OR REPLACE FUNCTION文を使用することをお薦めします。この文は、同一スキーマ内の前のバージョンのサブプログラムを新しいバージョンに置き換えますが、警告なしで実行されます。 |
サブプログラム・パラメータ・リストの後にキーワードISまたはASを使用できます。
|
参照:
|
必要な権限
サブプログラム、パッケージ仕様部またはパッケージ本体を作成するには、次の前提条件を満たす必要があります。
スキーマにサブプログラムまたはパッケージを作成するには、CREATE PROCEDUREシステム権限が必要で、他のユーザーのスキーマにサブプログラムまたはパッケージを作成するには、CREATE ANY PROCEDUREシステム権限が必要です。いずれの場合も、パッケージ本体をパッケージと同じスキーマに作成する必要があります。
|
注意: エラーなしで作成する(サブプログラムまたはパッケージを正常にコンパイルする)には、さらに次の権限が必要です。
|
サブプログラムまたはパッケージの所有者の権限が変更された場合、実行前にそのサブプログラムを再認証する必要があります。参照オブジェクトに必要な権限が、そのサブプログラムまたはパッケージの所有者から取り消されている場合、そのサブプログラムは実行できません。
サブプログラムのEXECUTE権限があれば、他のユーザーが所有するサブプログラムを実行できます。権限が付与されたユーザーは、そのサブプログラムの所有者のセキュリティ・ドメインでサブプログラムを実行します。このため、ユーザーは、サブプログラムが参照するオブジェクトの権限を得る必要はありません。これによって、データベース・アプリケーションおよびそのユーザーによるさらに統制のとれた効率的なセキュリティ計画が可能になります。また、すべてのサブプログラムおよびパッケージが(SYSTEM表領域内の)データ・ディクショナリに格納されます。サブプログラムおよびパッケージを作成するユーザーが使用できる領域の容量は、割当て制限によっては制御されません。
サブプログラムを変更するには、最初にDROP PROCEDURE文またはDROP FUNCTION文を使用して、そのサブプログラムを削除し、CREATE PROCEDURE文またはCREATE FUNCTION文を使用して、そのサブプログラムを再作成する必要があります。または、CREATE OR REPLACE PROCEDURE文またはCREATE OR REPLACE FUNCTION文を使用します(この文は、サブプログラムがすでに存在する場合、最初にそれを削除してから、指定どおりに再作成します)。
|
注意: サブプログラムが削除される際、警告は表示されません。 |
SQL文のDROP PROCEDURE、DROP FUNCTION、DROP PACKAGE BODY、DROP PACKAGEを使用して、スタンドアロン・サブプログラム、スタンドアロン・ファンクション、パッケージ本体またはパッケージ全体をそれぞれ削除できます。DROP PACKAGE文は、パッケージの仕様部と本体の両方を削除します。
次の文は、スキーマ内にあるOld_sal_raiseプロシージャを削除します。
DROP PROCEDURE Old_sal_raise;
必要な権限
サブプログラムまたはパッケージを削除するには、それらが自スキーマ内にあるか、またはDROP ANY PROCEDURE権限が必要です。パッケージ内の個々のサブプログラムは削除できないため、これらを削除せずに、パッケージ仕様部および本体を再作成する必要があります。
Oracle Databaseのインスタンス上で実行するPL/SQLサブプログラムは、第三世代言語(3GL)で作成された外部サブプログラムを起動できます。3GLサブプログラムは、データベースのアドレス空間とは別のアドレス空間で実行されます。
PL/SQLファンクション結果キャッシュを使用すると、領域と時間を大幅に節約できます。結果キャッシュPL/SQLファンクションを、異なるパラメータ値を使用して起動するたびに、それらのパラメータおよびその結果がキャッシュに格納されます。それ以降、同じパラメータ値を使用して同じファンクションを起動すると、その結果は再計算されるのではなく、キャッシュから取得されます。キャッシュは共有グローバル領域(SGA)に格納されるため、アプリケーションが実行されるすべてのセッションで使用可能です。
キャッシュ結果を計算する際に使用したデータベース・オブジェクトが更新されると、そのキャッシュ結果は無効になり、再計算が必要になります。
結果キャッシュの対象として最適のファンクションは、起動される頻度が高く、かつほとんど変更されない情報に依存するものです。
PL/SQLファンクション結果キャッシュの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
パッケージとは、データベース内に1つの単位として格納されている関連プログラム・オブジェクト(サブプログラム、変数、定数、カーソル、例外など)のコレクションです。
パッケージは、サブプログラムをスタンドアロンのスキーマ・オブジェクトとして作成するかわりに使用します。パッケージには、スタンドアロンのサブプログラムに比べて、多数のメリットがあります。たとえば、次のことができます。
アプリケーション開発をより効率的に行えます。
権限をより効率的に付与できます。
依存スキーマ・オブジェクトを再コンパイルせずにパッケージ・オブジェクトを変更できます。
Oracle Databaseで複数のパッケージ・オブジェクトを一度にメモリー内に読み込めます。
パッケージ内のすべてのサブプログラムが使用できるグローバル変数およびグローバル・カーソルを、そのパッケージ内に含めることができます。
サブプログラムをオーバーロードします。サブプログラムのオーバーロードとは、同一パッケージ内に同じ名前のサブプログラムを複数作成することで、それぞれのサブプログラムには異なる数またはデータ型の引数が指定されます。
|
参照: サブプログラムの名前のオーバーロードの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
パッケージ仕様部は、パッケージの有効範囲外で参照できるパブリック型、変数、定数およびサブプログラムを宣言します。パッケージ本体は、パッケージ外のアプリケーションが参照できないプライベート・オブジェクトのみでなく、仕様部で宣言されているオブジェクトも定義します。
例6-6は、1つのストアド・ファンクションおよび2つのストアド・プロシージャを含むパッケージを作成し、プロシージャの1つを起動します。
例6-6 PL/SQLパッケージの作成とパッケージ・サブプログラムの起動
-- Sequence that package function needs: CREATE SEQUENCE emp_sequence START WITH 8000 INCREMENT BY 10; -- Package specification: CREATE or REPLACE PACKAGE employee_management IS FUNCTION hire_emp ( firstname VARCHAR2, lastname VARCHAR2, email VARCHAR2, phone VARCHAR2, hiredate DATE, job VARCHAR2, sal NUMBER, comm NUMBER, mgr NUMBER, deptno NUMBER ) RETURN NUMBER; PROCEDURE fire_emp( emp_id IN NUMBER ); PROCEDURE sal_raise ( emp_id IN NUMBER, sal_incr IN NUMBER ); END employee_management; / -- Package body: CREATE or REPLACE PACKAGE BODY employee_management IS FUNCTION hire_emp ( firstname VARCHAR2, lastname VARCHAR2, email VARCHAR2, phone VARCHAR2, hiredate DATE, job VARCHAR2, sal NUMBER, comm NUMBER, mgr NUMBER, deptno NUMBER ) RETURN NUMBER IS new_empno NUMBER(10); BEGIN new_empno := emp_sequence.NEXTVAL; INSERT INTO EMPLOYEES ( employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ) VALUES ( new_empno, firstname, lastname, email, phone, hiredate, job, sal, comm, mgr, deptno ); RETURN (new_empno); END hire_emp; PROCEDURE fire_emp ( emp_id IN NUMBER ) IS BEGIN DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; IF SQL%NOTFOUND THEN raise_application_error( -20011, 'Invalid Employee Number: ' || TO_CHAR(Emp_id) ); END IF; END fire_emp; PROCEDURE sal_raise ( emp_id IN NUMBER, sal_incr IN NUMBER ) IS BEGIN UPDATE EMPLOYEES SET SALARY = SALARY + sal_incr WHERE EMPLOYEE_ID = emp_id; IF SQL%NOTFOUND THEN raise_application_error( -20011, 'Invalid Employee Number: ' || TO_CHAR(Emp_id) ); END IF; END sal_raise; END employee_management; /
パッケージ・プロシージャを起動します。
DECLARE
empno NUMBER(6);
sal NUMBER(6);
temp NUMBER(6);
BEGIN
empno := employee_management.hire_emp(
'John',
'Doe',
'john.doe@company.com',
'555-0100',
'20-SEP-07',
'ST_CLERK',
2500,
0,
100,
20);
DBMS_OUTPUT.PUT_LINE('New employee ID is ' || TO_CHAR(empno));
END;
/
サブプログラム、トリガー、パッケージなどのPL/SQLストアド・データベース・オブジェクトのサイズは、共有プール内のDescriptive Intermediate Attributed Notation for Ada(DIANA)コードのサイズ(バイト単位)に制限されています。フラット化されたDIANA/codeのサイズの制限は、LinuxやUNIXでは64KBですが、デスクトップ・プラットフォームでは32KBに制限されている場合があります。
ユーザーがアクセスできるもので最も密接に関連する数値は、静的データ・ディクショナリ・ビュー*_OBJECT_SIZEのPARSED_SIZEです。これには、SYS.IDL_xxx$表に格納されたDIANAのサイズがバイト単位で示されています。これは共有プールでのサイズではありません。(コンパイル中に使用される)PL/SQLコードのDIANA部分のサイズは、システム表内より共有プール内で非常に大きくなります。
パッケージの各部は、異なる文を使用して作成します。パッケージ仕様部は、CREATE PACKAGE文を使用して作成します。CREATE PACKAGE文でパブリック・パッケージ・オブジェクトを宣言します。
パッケージ本体を作成するには、CREATE PACKAGE BODY文を使用します。CREATE PACKAGE BODY文は、パッケージ仕様部で宣言されているパブリック・サブプログラムの手続き型コードを定義します。
パッケージ本体にはプライベート(またはローカル)・パッケージ・サブプログラムおよび変数も定義できます。これらのオブジェクトは、同一パッケージの本体内の他のサブプログラムでのみアクセスできます。外部ユーザーはどの権限を持っていても参照できません。
初めてアプリケーションを開発する場合、CREATE PACKAGE文またはCREATE PACKAGE BODY文にOR REPLACE句を追加すると便利な場合があります。このオプションの効果は、警告なしでパッケージまたはパッケージ本体が削除されることです。CREATE文は次のようになります。
CREATE OR REPLACE PACKAGE Package_name AS ...
および
CREATE OR REPLACE PACKAGE BODY Package_name AS ...
パッケージ本体には、次のものを含めることができます。
パッケージ仕様部に宣言されているサブプログラム。
パッケージ仕様部に宣言されているカーソルの定義。
パッケージ仕様部に宣言されていないローカル・サブプログラム。
ローカル変数。
パッケージ仕様部に宣言されているサブプログラム、カーソルおよび変数はグローバルです。これらを起動または使用できるのは、パッケージに対するEXECUTE権限を持つ外部ユーザーまたはEXECUTE ANY PROCEDURE権限を持つ外部ユーザーです。
パッケージ本体を作成する場合は、本体に定義する個々のサブプログラムが、パッケージ仕様部の宣言と同じパラメータ(名前、データ型およびモード)を持つようにする必要があります。パッケージ本体内のファンクションは、パラメータと戻り型が、名前およびデータ型について仕様部と一致する必要があります。
パッケージ仕様部またはパッケージ本体を作成または削除するために必要な権限は、スタンドアロン・サブプログラムの作成または削除に必要な権限と同じです。「サブプログラムの作成」および「サブプログラムおよびパッケージの削除」を参照してください。
パッケージおよびパッケージ内のすべてのパブリック・オブジェクトの名前は、所定のスキーマ内で一意である必要があります。パッケージ仕様部およびその本体は、同じ名前である必要があります。また、パッケージのメンバーの名前は、サブプログラム名の重複が必要な場合を除き、そのパッケージの有効範囲内で一意である必要があります。
パッケージ・オブジェクトを参照する各セッションには、対応するパッケージの独自のインスタンスがあり、この中には、パブリック変数、プライベート変数、カーソルおよび定数に対する持続状態が含まれます。セッションのインスタンス化されたパッケージ(仕様部または本体)のいずれかが無効化されると、そのセッションのすべてのパッケージ・インスタンスが無効になり、再コンパイルされます。これにより、そのセッションのすべてのパッケージ・インスタンスに関するセッション状態は失われます。
セッションのパッケージが無効である場合、セッションが無効なパッケージ・インスタンスのオブジェクトを最初に使用しようとしたときに、ORA-04068が戻されます。2度目にセッションがこのようなパッケージ・コールを行うと、エラーは発生せずに、パッケージはセッションに対して再インスタンス化されます。ただし、このエラーをアプリケーションで処理する場合は、次の点に注意してください。
パフォーマンスを最適な状態に保つため、Oracle Databaseがこのエラー・メッセージを戻すのは、パッケージ状態が破棄されるとき1回のみです。あるパッケージ内のサブプログラムにより別のパッケージ内のサブプログラムが起動される場合、両方のパッケージに対するセッション状態が失われます。
サーバー・セッションがORA-04068をトラップした場合、クライアント・セッションに対してORA-04068は発生しません。したがって、このクライアント・セッションがパッケージ内のオブジェクトを使用しようとすると、このパッケージは再インスタンス化されません。パッケージを再インスタンス化するには、クライアント・セッションをデータベースに再接続するか、またはクライアント・セッションでパッケージを再コンパイルする必要があります。
例6-7では、RAISE文により、処理中の例外ORA-06508の原因である現在の例外ORA-04068が呼び出されます。ORA-04068は検出されません。
例6-7 ORA-04068の呼び出し
PROCEDURE p IS
package_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (package_exception, -6508);
BEGIN
...
EXCEPTION
WHEN package_exception THEN
RAISE;
END;
/
例6-8では、RAISE文により、ORA-06508に応えて、現在の例外ORA-04068ではなく、例外ORA-20001が呼び出されます。ORA-04068が検出されます。このような場合、ORA-04068エラーはマスクされ、これにより、パッケージの再インスタンス化が停止します。
例6-8 ORA-04068の検出
PROCEDURE p IS
package_exception EXCEPTION;
other_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (package_exception, -6508);
PRAGMA EXCEPTION_INIT (other_exception, -20001);
BEGIN
...
EXCEPTION
WHEN package_exception THEN
...
RAISE other_exception;
END;
/
本番環境の多くでは、パッケージが無効になるDDL操作は、通常、業務時間外に行われるため、エンドユーザー・アプリケーションでは、このような状況は問題にならない可能性もあります。しかし、パッケージが業務時間中に無効になることがよくある場合は、パッケージ・コールが行われたときにこのエラーを処理するように、アプリケーションを作成することが必要になります。
データベースの機能性を拡張できるように、またはPL/SQLでSQL機能を使用できるように、Oracle Databaseには多数のパッケージが組み込まれています。これらのパッケージは、アプリケーションから起動できます。
|
参照: これらのOracle Databaseパッケージの概要は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 |
Oracle Databaseは2つのエンジンを使用して、PL/SQLブロックおよびサブプログラムを実行します。PL/SQLエンジンは手続き型の文を実行し、SQLエンジンはSQL文を実行します。実行中は、すべてのSQL文がこの2つのエンジン間でコンテキストをスイッチングするため、パフォーマンスが低下します。
特定のブロックまたはサブプログラムの実行に必要なコンテキストのスイッチング回数を最小化すると、パフォーマンスを大幅に改善できます。バインド変数としてコレクション要素を使用するループ内でSQL文が実行される場合、ブロックが必要とする多数のコンテキストのスイッチングによってパフォーマンスが低下することがあります。コレクションには次のものが含まれます。
VARRAY
ネストした表
索引付き表
ホスト配列
バインドとは、SQL文内のPL/SQL変数に対して値を代入することです。バルク・バインドとは、コレクション全体を一度にバインドすることです。バルク・バインドは1つの操作でコレクション全体を2つのエンジン間で受け渡すことができます。
通常、バルク・バインドの使用によって、4つ以上のデータベース行に影響するSQL文のパフォーマンスが改善されます。SQL文によって影響される行数が多いほど、バルク・バインドによるパフォーマンスの向上率は高くなります。
|
注意: この項では、PL/SQLアプリケーション内でバルク・バインドを使用するかどうかを判断するために役立つ、バルク・バインドの概要を説明します。バルク・バインドを操作する場合の例外の処理方法を含むバルク・バインドの使用方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。バルク・バインドを使用する場合、パラレルDML文は無効になります。 |
次について、パフォーマンスを高めるようなバルク・バインドの使用方法を示します。
FORALLキーワードを使用するバルク・バインドによって、コレクション要素を参照するINSERT、UPDATEまたはDELETE文のパフォーマンスが向上します。
例6-9のPL/SQLブロックは、管理職のID番号が7902、7698または7839の従業員について昇給を行うものですが、バルク・バインドを使用する場合と使用しない場合があります。PL/SQLがバルク・バインドを使用しないで、更新する各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例6-9 コレクションを参照するDML文
DECLARE TYPE numlist IS VARRAY (100) OF NUMBER; id NUMLIST := NUMLIST(7902, 7698, 7839); BEGIN -- Efficient method, using bulk bind: FORALL i IN id.FIRST..id.LAST UPDATE EMPLOYEES SET SALARY = 1.1 * SALARY WHERE MANAGER_ID = id(i); -- Slower method: FOR i IN id.FIRST..id.LAST LOOP UPDATE EMPLOYEES SET SALARY = 1.1 * SALARY WHERE MANAGER_ID = id(i); END LOOP; END; /
BULK COLLECT INTO句によって、コレクションを参照する問合せのパフォーマンスを改善できます。スカラー値表または%TYPE値表にBULK COLLECT INTOを使用できます。
例6-10のPL/SQLブロックは、複数の値の問合せを行ってその値をPL/SQL表に格納するものですが、バルク・バインドを使用する場合と使用しない場合があります。PL/SQLがバルク・バインドを使用しないで、選択された各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例6-10 コレクションを参照するSELECT文
DECLARE
TYPE var_tab IS TABLE OF VARCHAR2(20)
INDEX BY PLS_INTEGER;
empno VAR_TAB;
ename VAR_TAB;
counter NUMBER;
CURSOR c IS
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE MANAGER_ID = 7698;
BEGIN
-- Efficient method, using bulk bind:
SELECT EMPLOYEE_ID, LAST_NAME BULK COLLECT
INTO empno, ename
FROM EMPLOYEES
WHERE MANAGER_ID = 7698;
-- Slower method:
counter := 1;
FOR rec IN c LOOP
empno(counter) := rec.EMPLOYEE_ID;
ename(counter) := rec.LAST_NAME;
counter := counter + 1;
END LOOP;
END;
/
BULK COLLECT INTOキーワードとともにFORALLキーワードを使用すると、コレクションを参照しDMLを戻すFORループのパフォーマンスを改善できます。
例6-11のPL/SQLブロックは、従業員コレクションのボーナスを計算し、EMPLOYEES表を更新します。さらに、ボーナスをbonus_list_inst列に戻します。この操作をバルク・バインドを使用して実行し、また使用せずに実行します。PL/SQLがバルク・バインドを使用しないで、更新する各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例6-11 コレクションを参照しDMLを返すFORループ
DECLARE TYPE emp_list IS VARRAY(100) OF EMPLOYEES.EMPLOYEE_ID%TYPE; empids emp_list := emp_list(182, 187, 193, 200, 204, 206); TYPE bonus_list IS TABLE OF EMPLOYEES.SALARY%TYPE; bonus_list_inst bonus_list; BEGIN -- Efficient method, using bulk bind: FORALL i IN empids.FIRST..empids.LAST UPDATE EMPLOYEES SET SALARY = 0.1 * SALARY WHERE EMPLOYEE_ID = empids(i) RETURNING SALARY BULK COLLECT INTO bonus_list_inst; -- Slower method: FOR i IN empids.FIRST..empids.LAST LOOP UPDATE EMPLOYEES SET SALARY = 0.1 * SALARY WHERE EMPLOYEE_ID = empids(i) RETURNING SALARY INTO bonus_list_inst(i); END LOOP; END; /
共有ライブラリにあるネイティブ・コードにPL/SQLサブプログラムをコンパイルすると、PL/SQLサブプログラムの処理を高速化できます。
ネイティブ・コンパイルは、提供されるパッケージおよびユーザーが独自に作成したサブプログラムの両方に使用できます。この方法でコンパイルされたサブプログラムは、共有サーバー構成(従来のマルチ・スレッド・サーバー)やOracle Real Application Clusters(Oracle RAC)など、すべてのサーバー環境で動作します。
この方法では、サブプログラムから起動されたSQL文の処理は高速化されないため、SQL実行にあまり時間を必要としない計算集中型サブプログラムに使用するのが効果的です。
Javaの場合は、ncompツールを使用して独自のパッケージおよびクラスをコンパイルできます。
|
参照:
|
カーソルは静的オブジェクトであり、カーソル変数はカーソルへのポインタです。そのため、サブプログラムとの間でパラメータとして受け渡すことができます。カーソル変数は、その存続期間内に別のカーソルを参照することもできます。
カーソル変数には、その他にも次のような利点があります。
カプセル化
カーソル変数をオープンするストアド・サブプログラムに問合せを集中化できます。
メンテナンスの容易さ
カーソルの変更が必要な場合は、ストアド・サブプログラムの変更のみで済みます。各アプリケーションで変更を行う必要はありません。
セキュリティの利便性
アプリケーションのユーザーは、アプリケーションがサーバーに接続したときに使用したユーザー名です。ユーザーには、カーソルをオープンするストアド・サブプログラムに対するEXECUTE権限が必要です。ただし、ユーザーには、問合せで使用される表に対するREAD権限は必要ありません。この機能を使用して、表の列へのアクセスおよび他のストアド・サブプログラムへのアクセスを制限できます。
|
参照: カーソル変数の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
内容は次のとおりです。
メモリーは、通常、適切なALLOCATE文を使用してクライアント・アプリケーションのカーソル変数に割り当てられます。Pro*Cでは、EXEC SQL ALLOCATE cursor_name文を使用します。OCIでは、カーソル・データ域を使用します。
また、1つのサーバー・セッションのみで実行するアプリケーションでも、カーソル変数を使用できます。PL/SQLサブプログラムでカーソル変数を宣言してオープンし、他のPL/SQLサブプログラムのパラメータとして使用できます。
この項では、PL/SQLでのカーソル変数の使用例を示します。
|
参照: プログラム・インタフェースを使用するカーソル変数の例がさらに必要な場合は、次のマニュアルを参照してください。
|
例6-12では、PL/SQLカーソル変数型および2つのプロシージャを定義するパッケージを作成し、PL/SQLブロックからプロシージャを起動します。最初のプロシージャは、WHERE句にバインド変数を使用してカーソル変数をオープンします。2番目のプロシージャは、カーソル変数を使用してEMPLOYEES表の行をフェッチします。
例6-12 カーソル変数を使用するデータのフェッチ
CREATE OR REPLACE PACKAGE emp_data AS TYPE emp_val_cv_type IS REF CURSOR RETURN EMPLOYEES%ROWTYPE; PROCEDURE open_emp_cv ( emp_cv IN OUT emp_val_cv_type, dept_number IN EMPLOYEES.DEPARTMENT_ID%TYPE ); PROCEDURE fetch_emp_data ( emp_cv IN emp_val_cv_type, emp_row OUT EMPLOYEES%ROWTYPE ); END emp_data; / CREATE OR REPLACE PACKAGE BODY emp_data AS PROCEDURE open_emp_cv ( emp_cv IN OUT emp_val_cv_type, dept_number IN EMPLOYEES.DEPARTMENT_ID%TYPE ) IS BEGIN OPEN emp_cv FOR SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = dept_number ORDER BY last_name; END open_emp_cv; PROCEDURE fetch_emp_data ( emp_cv IN emp_val_cv_type, emp_row OUT EMPLOYEES%ROWTYPE ) IS BEGIN FETCH emp_cv INTO emp_row; END fetch_emp_data; END emp_data; /
パッケージ・プロシージャを起動します。
DECLARE emp_curs emp_data.emp_val_cv_type; dept_number EMPLOYEES.DEPARTMENT_ID%TYPE; emp_row EMPLOYEES%ROWTYPE; BEGIN dept_number := 20; -- Open cursor, using variable: emp_data.open_emp_cv(emp_curs, dept_number); -- Fetch and display data: LOOP emp_data.fetch_emp_data(emp_curs, emp_row); EXIT WHEN emp_curs%NOTFOUND; DBMS_OUTPUT.PUT(emp_row.LAST_NAME || ' '); DBMS_OUTPUT.PUT_LINE(emp_row.SALARY); END LOOP; END; /
例6-13では、プロシージャは、パラメータdiscrimの値に応じてEMPLOYEES表またはDEPARTMENTS表のカーソル変数をオープンします。無名ブロックは、プロシージャを起動してEMPLOYEES表のカーソル変数をオープンしますが、DEPARTMENTS表からフェッチするため、事前定義の例外ROWTYPE_MISMATCHが発生します。
例6-13 識別子を使用したカーソル変数
CREATE OR REPLACE PACKAGE emp_dept_data AS
TYPE cv_type IS REF CURSOR;
PROCEDURE open_cv (
cv IN OUT cv_type,
discrim IN POSITIVE
);
END emp_dept_data;
/
CREATE OR REPLACE PACKAGE BODY emp_dept_data AS
PROCEDURE open_cv (
cv IN OUT cv_type,
discrim IN POSITIVE) IS
BEGIN
IF discrim = 1 THEN
OPEN cv FOR
SELECT * FROM EMPLOYEES ORDER BY employee_id;
ELSIF discrim = 2 THEN
OPEN cv FOR
SELECT * FROM DEPARTMENTS ORDER BY department_id;
END IF;
END open_cv;
END emp_dept_data;
/
無名ブロックからプロシージャopen_cvを起動します。
DECLARE emp_rec EMPLOYEES%ROWTYPE; dept_rec DEPARTMENTS%ROWTYPE; cv Emp_dept_data.CV_TYPE; BEGIN emp_dept_data.open_cv(cv, 1); -- Open cv for EMPLOYEES fetch. FETCH cv INTO dept_rec; -- Fetch from DEPARTMENTS. DBMS_OUTPUT.PUT(dept_rec.DEPARTMENT_ID); DBMS_OUTPUT.PUT_LINE(' ' || dept_rec.LOCATION_ID); EXCEPTION WHEN ROWTYPE_MISMATCH THEN BEGIN DBMS_OUTPUT.PUT_LINE ('Row type mismatch, fetching EMPLOYEES data ...'); FETCH cv INTO emp_rec; DBMS_OUTPUT.PUT(emp_rec.DEPARTMENT_ID); DBMS_OUTPUT.PUT_LINE(' ' || emp_rec.LAST_NAME); END; END; /
結果:
Row type mismatch, fetching EMPLOYEES data ...
90 King
コンパイル時のエラーをリスト表示するには、静的データ・ディクショナリ・ビュー*_ERRORSを問い合せます。これらのビューからは、元のソース・コードを取得できます。サブプログラムのコンパイルに関するエラー・メッセージは、サブプログラムを置き換えると更新され、サブプログラムを削除すると削除されます。
SQL*Plusによって、コンパイル時のエラーに関する警告メッセージが発行されます。詳細を確認するには、SHOW ERRORSコマンドを使用する必要があります。
|
注意: 長い行を出力するには、SHOW ERRORS文を発行する前にSET LINESIZE文を使用してください。通常、次のように値を132に指定することをお薦めします。次に例を示します。
SET LINESIZE 132 |
例6-14にはコンパイル時のエラーが2つあり、WHERはWHEREの誤りで、ENDの後にはセミコロンが必要です。SHOW ERRORSを使用すると、各エラーの行、列および説明が表示されます。
例6-14 コンパイル時のエラー
CREATE OR REPLACE PROCEDURE fire_emp ( emp_id NUMBER ) AS BEGIN DELETE FROM EMPLOYEES WHER EMPLOYEE_ID = Emp_id; END /
結果:
Warning: Procedure created with compilation errors.
コマンド:
SHOW ERRORS;
結果:
Errors for PROCEDURE FIRE_EMP: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/3 PL/SQL: SQL Statement ignored 6/8 PL/SQL: ORA-00933: SQL command not properly ended 7/3 PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier> current delete exists prior <a single-quoted SQL string> The symbol ";" was substituted for "end-of-file" to continue.
|
参照:
|
Oracle Databaseでは、PL/SQLコード内のユーザー定義エラーを処理できます。ユーザー定義エラーの番号およびメッセージがクライアント・アプリケーションに戻されて、そこで処理されます。
ユーザー定義エラーのメッセージは、RAISE_APPLICATION_ERRORプロシージャを使用して戻されます。次に例を示します。
RAISE_APPLICATION_ERROR(error_number, 'text', keep_error_stack)
このプロシージャはサブプログラムの実行を停止し、サブプログラムによるすべての影響をロールバックして、ユーザー定義エラー番号およびメッセージを戻します(例外ハンドラによってエラーが検出されない場合)。error_numberは、-20000から-20999の範囲内にある必要があります。
エラー番号-20000は、ユーザーに情報を伝えることが重要ですが、一意のエラー番号は必要とされないメッセージの一般的な番号として使用します。テキストは、2KB以下の文字式である必要があります(それより長いメッセージは無視されます)。このエラーをスタック上のエラーに追加するには、Keep_error_stackをTRUEに設定し、既存のエラーに上書きするには、FALSE(デフォルト)に設定します。
RAISE_APPLICATION_ERRORプロシージャは、例外ハンドラまたは論理PL/SQLコードによく使用されます。たとえば、次の例外ハンドラは、ユーザー定義エラー・メッセージに関係する文字列を選択した後、RAISE_APPLICATION_ERRORプロシージャを起動します。
...
WHEN NO_DATA_FOUND THEN
SELECT Error_string INTO Message
FROM Error_table,
V$NLS_PARAMETERS V
WHERE Error_number = -20101 AND Lang = v.value AND
v.parameter = "NLS_LANGUAGE";
Raise_application_error(-20101, Message);
...
内容は次のとおりです。
ユーザー定義例外は、そのアプリケーションに固有のエラーを処理するためにPL/SQLブロック内で明示的に定義され、呼び出されます。例外が発生すると、通常のPL/SQLブロックの実行は停止し、例外ハンドラが起動されます。この例外ハンドラによって内部例外またはユーザー定義例外が処理されます。
アプリケーション・コードを使用すると、IF文により特に注意が必要な条件をチェックできます。エラー条件がある場合、次の2つのオプションのどちらかを選択できます。
適切な例外を指示するRAISE文を入力します。RAISE文によってサブプログラムの実行は中断され、例外ハンドラがあれば制御が渡されます。
RAISE_APPLICATION_ERRORプロシージャを起動して、ユーザー定義エラーの番号およびメッセージを戻します。
例外ハンドラは、ユーザー定義エラー・メッセージを処理するために定義することもできます。たとえば、図6-1では、次を示しています。
サブプログラム内の例外およびその例外に対応する例外ハンドラ
エラー(預金がないのに振込みを行うなど)をチェックし、ユーザー定義エラーの番号およびメッセージをトリガーに入力する条件文
ユーザー定義エラー番号を起動側の環境(この場合はサブプログラム)に戻す方法、およびアプリケーションでユーザー定義エラーの番号に対応する例外を定義する方法
ユーザー定義例外は、サブプログラム本体またはパッケージ本体で宣言するか(プライベート例外)、パッケージ仕様部で宣言します(パブリック例外)。例外ハンドラは、サブプログラム本体(スタンドアロンまたはパッケージ)に定義します。
データベースのPL/SQLユニットでは、適切な例外ハンドラによって検出されない未処理のユーザー・エラー条件または内部エラー条件が原因で、プログラム・ユニットの暗黙的なロールバックが発生します。プログラム・ユニットで未処理例外がある場所の前にCOMMIT文が含まれている場合、そのプログラム・ユニットの暗黙的なロールバックは直前のCOMMITまで実行されます。
さらに、データベースに格納されたPL/SQLユニットの未処理例外は、ユニットを起動するクライアント側のアプリケーションに渡されます。このアプリケーションでは、その例外はデータベースにSQL文として送られるため、アプリケーション・プログラム・ユニットの起動のみがロールバックされます(アプリケーション・プログラム・ユニット全体ではありません)。
データベースのPL/SQLユニット内の未処理例外がデータベース・アプリケーションに戻される場合は、例外を処理するためにデータベースのPL/SQLコードを変更する必要があります。アプリケーションで、データベース・プログラム・ユニットを起動したときに未処理例外を検出し、それらのエラーを処理できます。
分散問合せは、トリガーまたはストアド・サブプログラムを使用して作成できます。この分散問合せは、ローカルのOracle Databaseインスタンスによって、対応する数のリモート問合せに分解されてリモート・ノードに送られます。リモート・ノードはその問合せを実行し、ローカル・ノードにその結果を送ります。その後、ローカル・ノードは必要な後処理を行い、ユーザーまたはアプリケーションに結果を戻します。
たとえば、制約違反のために分散問合せ文の一部でエラーが発生すると、Oracle DatabaseはORA-02055を戻します。後続する文またはサブプログラムの起動は、ロールバック、またはセーブポイントまでのロールバックが入力されるまで、ORA-02067を戻します。
分散更新の一部でエラーが発生したことを示すエラー・メッセージがチェックされるように、アプリケーションを設計してください。エラーを検出した場合、アプリケーションが処理を継続する前に、トランザクション全体をロールバック(またはセーブポイントまでロールバック)してください。
サブプログラムがローカルまたはリモートで実行される場合、次の例外が発生する可能性があります。
キーワードEXCEPTIONを使用した宣言が必要なPL/SQLのユーザー定義例外
NO_DATA_FOUNDなどのPL/SQL事前定義例外
ORA-00900などのSQLエラー
RAISE_APPLICATION_ERRORプロシージャを使用して生成されるアプリケーション例外
ローカル・サブプログラムを使用する場合、これらのすべてのメッセージは次のような例外ハンドラを作成することによって検出できます。
EXCEPTION
WHEN ZERO_DIVIDE THEN
/* Handle the exception */
WHEN句の例外名は必須です。RAISE_APPLICATION_ERRORで生成される例外のように、発生した例外に名前がない場合は、プラグマPRAGMA_EXCEPTION_INITを使用して名前を割り当てることができます。次に例を示します。
DECLARE
...
Null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(Null_salary, -20101);
BEGIN
...
RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
...
EXCEPTION
WHEN Null_salary THEN
...
リモート・サブプログラムを起動する場合、例外はローカル例外ハンドラを作成することでも処理されます。リモート・サブプログラムは、ローカルの起動側サブプログラムにエラー番号を戻す必要があり、その後、ローカル・サブプログラムは、前述の例に示したように、例外を処理します。PL/SQLのユーザー定義例外は、常にローカル・サブプログラムにORA-06510を戻すため、これらの例外は処理できません。その他のリモート例外はすべて、ローカル例外と同じ方法で処理できます。
ストアド・サブプログラムのコンパイルにはコードの構文エラーの修正が含まれます。サブプログラムが正常に実行され、エラーが修正されていることを確認するには、追加のデバッグを行う必要があります。次のようなデバッグが考えられます。
出力文を追加して、実行処理の検証およびサブプログラム内の任意の点でのデータ値のチェックをする。
別のデバッガを実行して、実行を詳細に分析する。
内容は次のとおりです。
PL/Scopeはコンパイラ駆動方式のツールであり、PL/SQLソース・コードからユーザー定義の識別子に関するデータを収集して構成します。PL/Scopeはコンパイラ駆動方式のツールであるため、直接使用するのではなく、対話型の開発環境(SQL DeveloperやJDeveloperなど)を介して使用します。
PL/Scopeによって、強力で効率的なPL/Scopeソース・コード・ブラウザの開発が可能になります。このブラウザは、ソース・コードの参照および理解に費やされる時間を最小限にすることによって、PL/SQL開発者の生産性を向上させます。
PL/Scopeの詳細は、第7章「PL/Scopeの使用」を参照してください。
PL/SQL階層プロファイラは、サブプログラム・コール別に編成されたPL/SQLプログラムの動的実行プロファイルをレポートします。SQL実行時間およびPL/SQL実行時間が個別に説明されます。動的実行プロファイルにおけるサブプログラム・レベルの各サマリーには、サブプログラムへのコール数、サブプログラム自体に要した時間、サブプログラムのサブツリー(つまり依存サブプログラム)に要した時間、詳細な親子情報などが表示されます。
生成されたHTMLレポートは任意のブラウザで参照できます。ブラウザのナビゲーション機能と厳選したリンクを組み合せた効率的な手段により、大規模なアプリケーションのパフォーマンスを分析し、アプリケーションのパフォーマンスを向上させ、開発コストを削減できます。
PL/SQL階層プロファイラの詳細は、第8章「PL/SQL階層プロファイラの使用」を参照してください。
Oracle JDeveloperの最新のリリースには、PL/SQL、Javaおよびマルチ言語プログラムをデバッグする広範な機能が含まれています。各種Oracle製品の一部としてOracle JDeveloperを取得できます。通常、最新のリリースは次のURLからダウンロードできます。
http://www.oracle.com/technetwork/developer-tools/jdev/downloads/index.html
OracleパッケージDBMS_OUTPUTを使用すると、ストアド・サブプログラムおよびトリガーもデバッグできます。コードにはPUT文およびPUT_LINE文を入れて、変数および式の値を端末に出力します。
Oracle Database 10gから、データベース内で実行されるPL/SQLおよびJavaコードのデバッグに新しい権限モデルが適用されるようになりました。このモデルは、Oracle JDeveloper、Oracle Developer、または各種サード・パーティのPL/SQLやJavaの開発環境のいずれを使用していても適用され、DBMS_DEBUG APIおよびDBMS_DEBUG_JDWP APIの両方に影響します。
デバッガに接続するセッションでは、DEBUG CONNECT SESSIONシステム権限を所有するユーザーが接続操作を実行する必要があります。接続コールに関係するDRサブプログラムの所有者であるユーザーも、この操作を実行できます。
デバッガがセッションに接続されると、セッション・ログイン・ユーザーおよび有効なセッションレベルのロールが、このデバッグ用の接続の権限環境として決定されます。デバッグに必要なすべてのDEBUG権限またはEXECUTE権限を、このユーザーとロールの組合せに付与する必要があります。
Javaのパブリック変数またはPL/SQLのパッケージ仕様部内に宣言された変数を表示および変更できるようにするために、関連するコードのEXECUTE権限またはDEBUG権限をデバッグ用の接続に付与する必要があります。
プライベート変数またはブレークポイントを表示および変更し、コードを1行ずつ実行できるようにするために、関連するコードのDEBUG権限を付与する必要があります。
|
注意: DEBUG権限を付与することにより、デバッグ対象のサブプログラムが実行するようにコーディングされている処理をデバッグ・セッションで実行することが可能になります。 |
このような権限要件に加えて、コードの各行で停止する機能とデバッガからの変数へのアクセスを実行できるのは、生成されたデバッグ情報とともにコンパイルされているコード内のみという制限があります。ALTER PACKAGEなどの文でPL/SQLコンパイル・パラメータPLSQL_DEBUGおよびDEBUGキーワードを使用し、PL/SQLコンパイラで結果にデバッグ情報を含めるかどうかを制御できます。デバッグ情報を含めない場合、変数にはアクセスできず、コードの各行でステップ実行やブレークポイントを指定することもできません。PL/SQLコンパイラでは、PL/SQLのwrapユーティリティにより非表示になっているコードに対しては、デバッグ情報は生成されません。
|
参照: wrapユーティリティの詳細は、『Oracle Database PL/SQL言語リファレンス』 |
DEBUG ANY PROCEDUREシステム権限の効力は、データベース内のすべてのオブジェクトにDEBUG権限を付与したときの効力と同じです。O7_DICTIONARY_ACCESSIBILITYパラメータの値がTRUEである場合は、SYSが所有しているオブジェクトにも付与します。
デバッグ・ロール・メカニズムを使用すると、セッションでは通常使用できない、デバッグ用の権限を保持できます。デバッグ・ロールおよび必要な関連パスワードの指定方法の詳細は、マニュアルでDBMS_DEBUGパッケージおよびDBMS_DEBUG_JDWPパッケージについての説明を参照してください。
JAVADEBUGPRIVロールは、DEBUG CONNECT SESSION権限およびDEBUG ANY PROCEDURE権限を保持します。必要な場合にのみ、これらの権限をこのロールに付与してください。
|
注意: SYSが所有するオブジェクトにDEBUG ANY PROCEDURE権限またはDEBUG権限を付与することは、すべての権限をデータベースに付与することを意味します。 |
Oracle Database 9gリリース2から提供されているDBMS_DEBUG_JDWPパッケージは、将来DBMS_DEBUGパッケージと置き換わるマルチ言語デバッグ用のフレームワークを提供します。PL/SQLとJavaの組合せのプログラムには特に有効です。
Oracle8iから提供されているDBMS_DEBUGパッケージでは、サーバー側のデバッガが実装されており、サーバー側のPL/SQLユニットをデバッグする方法を提供します。Oracle Procedure Builderやその他の様々なサード・パーティ・ベンダーが提供するソリューションなどデバッガのいくつかでは、このAPIが使用されています。
|
参照:
|
ストアドPL/SQLサブプログラムは、次のように様々な環境から起動できます。次に例を示します。
Oracle Databaseのツール製品を使用した対話形式による起動
別のサブプログラム本体からの起動
アプリケーション(SQL*Formsやプリコンパイラなど)の内部からの起動
トリガー本体からの起動
ストアドPL/SQLファンクション(プロシージャではない)は、SQL文から起動することもできます。詳細は、「SQL文からのストアドPL/SQLファンクションの起動」を参照してください。
内容は次のとおりです。
|
参照:
|
次のようなサブプログラムを起動する場合、権限は必要ありません。
所有しているスタンドアロン・サブプログラム
所有しているパッケージ内のサブプログラム
パブリック・スタンドアロン・サブプログラム
パブリック・パッケージ内のサブプログラム
別のユーザーが所有するスタンドアロン・サブプログラムまたはパッケージ・サブプログラムを起動する場合は、次のようになります。
スタンドアロン・サブプログラムまたはサブプログラムを含むパッケージのEXECUTE権限、またはEXECUTE ANY PROCEDUREシステム権限が必要です。
リモート・サブプログラムを実行する場合は、EXECUTE権限またはEXECUTE ANY PROCEDUREシステム権限が、ロールを介してではなく直接付与されている必要があります。
起動の際に所有者の名前を指定する必要があります。次に例を示します。
EXECUTE jdoe.Fire_emp (1043); EXECUTE jdoe.Hire_fire.Fire_emp (1043);
サブプログラムが定義者権限(DR)サブプログラムである場合、このサブプログラムはその所有者の権限を使用して実行されます。所有者は、参照オブジェクトに必要なオブジェクト権限をすべて所有している必要があります。
サブプログラムが実行者権限(IR)サブプログラムである場合、このサブプログラムはユーザーの権限を使用して実行されます。すべての参照オブジェクト、つまり自スキーマ内で変換される外部参照を介してサブプログラムがアクセスするオブジェクトに対して、必要なオブジェクト権限をすべて所有している必要があります。これらの権限は直接、またはロールを介して保持できます。IRサブプログラムが直接、または間接的にDRサブプログラムから起動されないかぎり、ロールは有効です。
サブプログラムは、SQL*Plusなど、Oracle Databaseのツール製品から対話形式で起動できます。例6-15では、SQL*Plusを使用してプロシージャを作成し、2つの方法で起動します。
例6-15 SQL*Plusを使用した対話形式によるサブプログラムの起動
CREATE OR REPLACE PROCEDURE salary_raise ( employee EMPLOYEES.EMPLOYEE_ID%TYPE, increase EMPLOYEES.SALARY%TYPE ) IS BEGIN UPDATE EMPLOYEES SET SALARY = SALARY + increase WHERE EMPLOYEE_ID = employee; END; /
PL/SQLブロックの内部からプロシージャを起動します。
BEGIN
salary_raise(205, 200);
END;
/
結果:
PL/SQL procedure successfully completed.
EXECUTE文でプロシージャを起動します。
EXECUTE salary_raise(205, 200);
結果:
PL/SQL procedure successfully completed.
一部の対話形式ツールでは、セッション中に使用できるセッション変数を作成することができます。例6-16では、SQL*Plusを使用してセッション変数の作成、使用および出力を行います。
例6-16 SQL*Plusを使用したセッション変数の作成と使用
-- Create function for later use: CREATE OR REPLACE FUNCTION get_job_id ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) RETURN EMPLOYEES.JOB_ID%TYPE IS job_id EMPLOYEES.JOB_ID%TYPE; BEGIN SELECT JOB_ID INTO job_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; RETURN job_id; END; / -- Create session variable: VARIABLE job VARCHAR2(10); -- Run function and store returned value in session variable: EXECUTE :job := get_job_id(204); PL/SQL procedure successfully completed.
SQL*Plusコマンド:
PRINT job;
結果:
JOB -------------------------------- PR_REP
|
参照:
|
ストアド・サブプログラムは、別のサブプログラムやトリガーから起動できます。例6-17では、プロシージャprint_mgr_nameがプロシージャprint_emp_nameを起動します。
サブプログラムの再帰的な起動が可能です(つまり、サブプログラムがサブプログラム自体を起動できます)。
例6-17 別のサブプログラムからのサブプログラムの起動
-- Create procedure that takes employee's ID and prints employee's name: CREATE OR REPLACE PROCEDURE print_emp_name ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) IS fname EMPLOYEES.FIRST_NAME%TYPE; lname EMPLOYEES.LAST_NAME%TYPE; BEGIN SELECT FIRST_NAME, LAST_NAME INTO fname, lname FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; DBMS_OUTPUT.PUT_LINE ( 'Employee #' || emp_id || ': ' || fname || ' ' || lname ); END; / -- Create procedure that takes employee's ID and prints manager's name: CREATE OR REPLACE PROCEDURE print_mgr_name ( emp_id EMPLOYEES.EMPLOYEE_ID%TYPE ) IS mgr_id EMPLOYEES.MANAGER_ID%TYPE; BEGIN SELECT MANAGER_ID INTO mgr_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; DBMS_OUTPUT.PUT_LINE ( 'Manager of employee #' || emp_id || ' is: ' ); print_emp_name(mgr_id); END; /
プロシージャを起動します。
BEGIN print_emp_name(200); print_mgr_name(200); END; /
結果:
Employee #200: Jennifer Whalen Manager of employee #200 is: Employee #101: Neena Kochhar
3GLデータベース・アプリケーション(プリコンパイラまたはOCIアプリケーションなど)では、コード内からサブプログラムを起動することができます。
プロシージャFire_emp1が次のように作成されているとします。
CREATE OR REPLACE PROCEDURE fire_emp1 (Emp_id NUMBER) AS
BEGIN
DELETE FROM Emp_tab WHERE Empno = Emp_id;
END;
プリコンパイラ・アプリケーションからサブプログラムを実行するには、EXECコール・インタフェースを使用する必要があります。たとえば、次の文は、プリコンパイラ・アプリケーションのコード内でFire_empプロシージャを起動します。
EXEC SQL EXECUTE
BEGIN
Fire_emp1(:Empnum);
END;
END-EXEC;
|
参照: 3GLアプリケーション内部からのPL/SQLサブプログラムの起動方法の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。 |
リモート・サブプログラム(スタンドアロン・サブプログラムおよびパッケージ・サブプログラム)は、そのリモート・サブプログラム名、データベース・リンクおよびリモート・サブプログラムのパラメータを指定することによって、サブプログラム、OCIアプリケーションまたはプリコンパイラの中から起動できます。
たとえば、次のSQL*Plus文はプロシージャfire_emp1を起動します。このプロシージャはデータベース内にあり、boston_serverという名前のローカル・データベース・リンクによって参照されています。
EXECUTE fire_emp1@boston_server(1043);
デフォルト値がある場合でも、すべてのリモート・サブプログラムのパラメータに対して値を指定する必要があります。リモート・パッケージ変数および定数にはアクセスできません。
|
注意:
|
内容は次のとおりです。
リモート・サブプログラム名とデータベース・リンクのシノニムを作成し、シノニムを使用してサブプログラムを起動することができます。次に例を示します。
CREATE SYNONYM synonym1 for fire_emp1@boston_server; EXECUTE synonym1(1043); /
シノニムによって、サブプログラム、OCIアプリケーションまたはプリコンパイラのみでなく、SQL*Formsアプリケーションなど、Oracle Databaseのツール製品のアプリケーションからリモート・サブプログラムを起動できるようになります。
シノニムによってデータの独立性と場所の透過性の両方が実現されます。シノニムを使用すると、オブジェクトを所有するユーザーやオブジェクトを格納するデータベースに関係なく、アプリケーションを変更せずに使用できます。ただし、シノニムはデータベース・オブジェクトに対する権限の代用とはなりません。ユーザーがシノニムを使用するためには、そのユーザーが適切な権限を付与されている必要があります。
パッケージ内に定義されているサブプログラムは個別のオブジェクトではないため(パッケージはオブジェクトです)、パッケージ内の個々のサブプログラムのシノニムは作成できません。
シノニムを使用しない場合は、リモート・サブプログラムを起動するローカル・サブプログラムを作成することもできます。次に例を示します。
CREATE OR REPLACE PROCEDURE local_procedure (arg IN NUMBER) AS BEGIN fire_emp1@boston_server(arg); END; / DECLARE arg NUMBER; BEGIN local_procedure(arg); END; /
|
参照:
|
リモートのストアド・サブプログラムの起動は、更新処理を前提とするため、このような参照でも、常に2フェーズ・コミットのトランザクションが必要です(リモート・サブプログラムが読取り専用の場合を含む)。さらに、リモート・サブプログラムの起動を含むトランザクションをロールバックする場合、そのリモート・サブプログラムによって実行された処理も同時にロールバックされます。
リモートで起動されたサブプログラムでは通常、ローカル・サブプログラムと同じように、COMMIT、ROLLBACK、SAVEPOINTの各文を実行できます。ただし、次のように、アクションに少し違いがあります。
トランザクションがOracle Database以外のデータベースによって開始された場合(XAアプリケーションなどの場合)、リモート・サブプログラムでこれらの操作はできません。
これらの操作の1つを実行すると、リモート・サブプログラムは独自の分散トランザクションを開始できません。
リモート・サブプログラムがその作業をコミットまたはロールバックしない場合、データベース・リンクがクローズした時点で暗黙的にコミットが実行されます。このとき、トランザクションは実行中とみなされるため、リモート・サブプログラムを起動できません。
分散トランザクションではデータは複数のデータベースで更新されます。異なるデータベースのデータにアクセスする複数のリモート更新を含むサブプログラムを使用できます。構文内の文はリモート・データベースに送信され、構文の実行はユニット単位で正常終了または異常終了します。分散更新の一部がエラーとなり、一部が正常終了した場合、処理を続けるには(トランザクション全体またはセーブポイントまでの)ロールバックが必要です。分散更新を実行するサブプログラムを作成する場合は、この点を考慮する必要があります。
|
注意: SQLは命令型(または手続き型)言語ではなく宣言型言語であるため、ファンクションが命令型言語のPL/SQLで記述されていても、SQL文から起動されるファンクションが何回実行されるかはわかりません。アプリケーションでファンクションが特定の回数実行されることが必要な場合には、SQL文からファンクションを起動しないでください。かわりにカーソルを使用します。 たとえば、選択した行ごとにファンクションをコールすることがアプリケーションで必要な場合は、カーソルをオープンして、カーソルから行を選択し、各行に対してファンクションをコールします。このようにすると、ファンクションをコールする回数が、カーソルからフェッチされる行数と同じになります。 |
ストアドPL/SQLファンクションをSQL文から起動するには、そのファンクションをスキーマ・レベルまたはパッケージ仕様部で宣言する必要があります。
ストアドPL/SQLファンクションを起動できるSQL文は次のとおりです。
INSERT
UPDATE
DELETE
SELECT
CALL
(CALLは、ストアドPL/SQLプロシージャを起動することもできます。)
SQLからPL/SQLサブプログラムを起動するには、その所有者であるか、またはそのサブプログラムに対するEXECUTE権限を保持している必要があります。PL/SQLファンクションを使用して定義されているビューから選択するには、そのビューに対するSELECT権限が必要です。そのビューからの選択には、別のEXECUTE権限は必要ありません。
パラメータを渡す方法など、サブプログラムを起動する方法の概要は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
内容は次のとおりです。
SQL文からPL/SQLサブプログラムを起動すると、次のような利点があります。
SQLの拡張によって、ユーザーの生産性が向上します。
実行する内容がSQL文のみで表現するには複雑すぎたり、非常に扱いにくかったり、不可能な場合に、SQL文の表現機能が強化されます。
問合せの効率を向上します。
問合せのWHERE句にファンクションを指定すると、条件を使用してデータをフィルタできます。ファンクションを使用できない場合は、アプリケーションで評価する必要があります。
特殊なデータ型(緯度、経度、温度など)を表すための文字列を操作できます。
パラレル問合せを実行できます。
問合せがパラレル化されると、PL/SQLサブプログラム内のSQL文も(パラレル問合せオプションを使用して)パラレルに実行できます。
SQL文では、PL/SQLファンクションや式を使用できる任意の箇所でPL/SQLファンクションを使用できます。次に例を示します。
SELECT文の選択リスト
WHERE句またはHAVING句の条件
CONNECT BY句、START WITH句、ORDER BY句またはGROUP BY句
INSERT文のVALUES句
UPDATE文のSET句
SELECT文では、次のかわりにPL/SQL表ファンクション(複数の行をまとめて戻す)を使用できます。
SELECTリストの列名
FROM句の表名
不変の定義が必要な次のようなコンテキストには、PL/SQLファンクションは使用できません。
CREATE文またはALTER TABLE文のCHECK制約句
列に対するデフォルト値の指定
PL/SQLファンクションをSQL式から起動するには、そのファンクションが次の要件を満たしている必要があります。
列(グループ)ファンクションではなく、行ファンクションであること。つまり、列全体をその引数としてとることはできません。
仮パラメータが、OUTパラメータやIN OUTパラメータではなく、INパラメータであること。
仮パラメータおよび戻り値(存在する場合)のデータ型が、PL/SQLデータ型(BOOLEAN、RECORD、TABLEなど)ではなく、Oracleの組込みデータ型(CHAR、DATE、NUMBERなど)であること。
ただし、このルールの例外として、対応する実パラメータが暗黙的に仮パラメータのデータ型に変換される場合には、その仮パラメータはPL/SQLデータ型を持つことができます(例6-19を参照してください)。
例6-18のファンクションは、これらの要件を満たしています。
例6-18 SQL式でのPL/SQLファンクション(規則に従った場合)
DROP TABLE payroll; -- in case it exists CREATE TABLE payroll ( srate NUMBER, orate NUMBER, acctno NUMBER ); CREATE OR REPLACE FUNCTION gross_pay ( emp_id IN NUMBER, st_hrs IN NUMBER := 40, ot_hrs IN NUMBER := 0 ) RETURN NUMBER IS st_rate NUMBER; ot_rate NUMBER; BEGIN SELECT srate, orate INTO st_rate, ot_rate FROM payroll WHERE acctno = emp_id; RETURN st_hrs * st_rate + ot_hrs * ot_rate; END gross_pay; /
例6-19では、SQL文CALLによりPL/SQLファンクションf1が起動され、このファンクションの仮パラメータと戻り値のデータ型はPL/SQLデータ型PLS_INTEGERです。実パラメータ2は暗黙的にデータ型PLS_INTEGERへ変換されるため、このCALL文は正常に実行されます。実パラメータの値がPLS_INTEGERの範囲外であれば、このCALLは正常には実行されません。
ストアド・サブプログラムの純粋度とは、データベース表またはパッケージ変数に対してそのサブプログラムが及ぼす副作用のことをいいます。副作用によって、問合せのパラレル処理が妨害されたり、処理順序に依存する(したがって、不確定な)結果が発生したり、ユーザー・セッションにまたがったパッケージ状態のメンテナンスが必要になります。ファンクションがSQL問合せまたはDML文から起動される場合は、様々な副作用は受け入れられません。
Oracle Database 8gリリース1より前のリリースでは、Oracle DatabaseはPL/SQLコンパイラを使用して、ストアド・サブプログラムまたはSQL文のコンパイル中に制限を施行していました。Oracle Database 8gリリース1からは、コンパイル時の制限は緩和され、実行中の制限も少なくなっています。
この変更によって、PL/SQL、JavaおよびCで作成されたストアド・サブプログラムが統一してサポートされ、プログラマには最大限の柔軟性が提供されています。
内容は次のとおりです。
新しいSQL文が実行されるとき、実行中のSQL文の中にこのSQL文が論理的に埋め込まれているかどうかが確認されます。文がトリガーから、または実行中のSQL文から起動されたサブプログラムから実行されると、この確認が行われます。このような場合は、新しいSQL文が特定のコンテキスト内で安全かどうかを判断するために、さらに確認が行われます。
サブプログラムには次の制限が適用されます。
問合せ(SELECT文)またはDML文から起動されるサブプログラムは、現行のトランザクションの終了、セーブポイントの作成またはセーブポイントまでのロールバック、あるいはシステムまたはセッションの変更(ALTER)を実行できません。
問合せまたはパラレル化されたDML文から起動されるサブプログラムは、DML文を実行できないか、またはデータベースを変更できません。
DML文から起動されるサブプログラムでは、そのDML文が変更中の表の読取りおよび変更はできません。
前述のすべての制限は、サブプログラムまたはトリガー内でSQL文が実行される方法にかかわらず適用されます。次に例を示します。
前述の制限は、PL/SQLから起動されるSQL文(サブプログラムまたはトリガーの本体に直接埋め込まれている文かどうかにかかわらず)が、ネイティブ動的メカニズム(EXECUTE IMMEDIATE)を使用して実行されるか、またはDBMS_SQLパッケージを使用して実行される場合、そのSQL文に適用されます。
SQLJ構文を使用してJavaに埋め込まれている文、またはJDBCを使用して実行される文に適用されます。
外部C関数内からコールバック・コンテキストを使用してOCIで実行される文に適用されます。
新しいSQL文の実行が、実行中の文のコンテキストに論理的に埋め込まれていない場合は、前述の制約を回避できます。そのような回避方法の1つとして、PL/SQLの自律型トランザクションの使用があります(「自律型トランザクション」を参照)。外部C関数からOCIを使用するという別の回避方法もあり、この場合は、OCIExtProcContext引数から使用できるハンドルを使用せず、新しい接続を作成します。
キーワードDETERMINISTICおよびPARALLEL_ENABLEを、ファンクションを宣言する構文内で使用できます。この2つのキーワードは最適化ヒントで、問合せオプティマイザおよび他のソフトウェア・コンポーネントに対して、次のファンクションについての情報を提供します。
重複して起動する必要のないファンクション
パラレル問合せまたはDML文の中で許可されているファンクション
ファンクション索引および特定のスナップショットやマテリアライズド・ビューで使用できるのは、DETERMINISTICを指定したファンクションのみです。
確定的な(DETERMINISTIC)ファンクションは、引数として渡される値のみに依存し、パッケージ変数またはデータベースの内容を参照したり変更することがなく、また、他の副作用を持ちません。このようなファンクションは、渡される引数値の組合せが同じであるかぎり、同じ戻り値を生成します。
the DETERMINISTICキーワードは、ファンクションの宣言の中で戻り値の型の後に入れます。次に例を示します。
CREATE OR REPLACE FUNCTION f1 (
p1 NUMBER
) RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN p1 * 2;
END;
/
このキーワードは、次の場所に挿入できます。
CREATE FUNCTION文で定義されたファンクション
CREATE PACKAGE文で宣言されたファンクション
CREATE TYPE文のメソッド宣言
CREATE PACKAGE BODY文またはCREATE TYPE BODY文のファンクション本体またはメソッド本体には、キーワードは配置できません。
DETERMINISTICとマークされているファンクションの起動では、他にアクションを実行しなくても、パフォーマンスがある程度最適化されます。次の機能では、その機能で使用されるすべてのファンクションをDETERMINISTICとして宣言する必要があります。
ファンクション索引で使用されるすべてのユーザー定義ファンクション。
高速リフレッシュ対象、またはENABLE QUERY REWRITEとしてマークされるマテリアライズド・ビューで使用されるファンクション
これらの機能では、ファンクションを起動するのではなく、事前に計算されている結果をできるかぎり使用しようとします。
プログラミングにおいては、次のDETERMINISTICカテゴリに属するファンクションを作成するのが適切です。
WHERE句、ORDER BY句またはGROUP BY句の中で使用されるファンクション
SQL型のMAPメソッドまたはORDERメソッドであるファンクション
結果セットに行を入れるかどうか、またはどこに入れるかを決定するファンクション
DETERMINISTICを作成する場合は、次の点に注意してください。
ファンクションのアクションが本当に確定的であるかどうかをデータベースで認識することはできません。そのアクションが本当の意味で確定的ではないファンクションに対してDETERMINISTICキーワードが適用されると、そのファンクションが関係する問合せの結果は予測できません。
DETERMINISTICファンクションのセマンティックを変更し、再コンパイルすると、既存のファンクション索引およびマテリアライズド・ビューにより、旧バージョンのファンクションの結果がレポートされます。このように、ファンクションのセマンティックを変更する場合、依存するファンクション索引およびマテリアライズド・ビューを手動で作成する必要があります。
|
参照: CREATE FUNCTIONの制限事項は、 |
Oracle Databaseのパラレル実行機能により、SQL文の実行作業が複数のプロセスにわたって分割されます。パラレルで実行されるSQL文から起動されるファンクションは、各プロセス内で実行される個別のコピーを持つことができ、それぞれのコピーは、そのプロセスによって処理される行のサブセットのためにのみ起動されます。
各プロセスには、そのプロセス専用のパッケージ変数のコピーがあります。パラレル実行が開始されると、これらは、ユーザーがシステムにログインするときのように、パッケージ仕様部および本体の情報に基づいて初期化され、パッケージ変数内の値は、元のログイン・セッションからはコピーされません。パッケージ変数に対する変更は、多数のセッション間で自動的に伝播したり、元のセッションに戻ることはありません。JavaのSTATICクラス属性は、同様に、各プロセス内で独立して初期化され変更されます。ファンクションでは、検出される様々な行の値をパッケージ(またはJavaのSTATIC)変数を使用して蓄積できるため、Oracle Databaseでは、すべてのユーザー定義ファンクションの実行をパラレル化しても安全であるとはいえません。
リリース8.1.5より前のOracle DatabaseのSELECT文では、パラレル問合せ最適化機能により、PRAGMA RESTRICT_REFERENCES宣言内にRNPSおよびWNPSとして記述されたファンクションは、パラレルで実行できました。CREATE FUNCTION文を使用して定義されているファンクションでは、ファンクションが十分に純粋であるかどうかを判断するために、暗黙的にコードが調べられており、パラレル実行は、これらのファンクションに対してプラグマを指定できない場合でも、発生する可能性がありました。
DML文の場合、8.1.5より前のリリースのOracle Databaseでは、ファンクションがPRAGMA RESTRICT_REFERENCES宣言内でRNDS、WNDS、RNPSおよびWNPSの4つすべてが記されているかどうかは、パラレル化最適化機能により調べられており、データベースまたはパッケージ変数のいずれかに対して読取りでもなく書込みでもないとマークされたファンクションは、パラレルで実行できました。ここでも、CREATE FUNCTION文を使用して定義されているファンクションでは、ファンクションが十分に純粋であるかどうかを判断するために、暗黙的にコードが調べられており、パラレル実行は、これらのファンクションに対してプラグマを指定できない場合でも、発生する可能性がありました。
Oracle Databaseリリース8.1.5以降でも、以前のバージョンでパラレル化可能として認識されていたファンクションは、引き続きパラレル化されます。コードをパラレル実行用として安全であるとマークする方法として、PARALLEL_ENABLEキーワードをお薦めします。このキーワードは、「ファンクションの宣言」で説明しているDETERMINISTICと構文的に類似しており、次に示すように、ファンクション宣言の戻り値型の後に指定します。
CREATE OR REPLACE FUNCTION f1 (
p1 NUMBER
) RETURN NUMBER PARALLEL_ENABLE
IS
BEGIN
RETURN p1 * 2;
END;
/
CREATE FUNCTIONを使用して定義されるPL/SQLファンクションは、そのファンクションがパッケージ変数の読取りも書込みを行わず、パッケージ変数の読取りまたは書込みを行う可能性のあるファンクションも起動しないことをシステムで判断できる場合は、パラレルで実行しても安全であると明示的に宣言しなくても、パラレルで実行できます。JavaメソッドまたはC関数は、プログラマがコール仕様にPARALLEL_ENABLEと明示的に指定するか、またはPRAGMA RESTRICT_REFERENCESを指定してファンクションが十分に純粋であることを示さないかぎり、システムはパラレルでの実行が安全であるとはみなしません。
パラレルDML文の一部としてパラレル実行されるファンクションに対しては、追加のランタイム制約が設けられています。このようなファンクションは、DML文の実行が許可されず、問合せ(SELECT)文の中で実行されるファンクションに対して適用される制約と同じ制約を受けます。
8.1.5(Oracle8i)より前のOracle Databaseバージョンでは、プログラマはサブプログラムの純粋度レベルの宣言にPRAGMA RESTRICT_REFERENCESを使用していました。それ以降のバージョンでは、かわりにヒントPARALLEL_ENABLEおよびDETERMINISTICを使用して、サブプログラムの純粋度についてOracle Databaseと通信します。
コードからPRAGMA RESTRICT_REFERENCESを削除できます。ただし、このプラグマは次のような状況での下位互換性が残されています。
既存コードを編集してPRAGMA RESTRICT_REFERENCESをすべて削除することが不可能かつ非現実的な場合。
たとえば、サブプログラムS1がサブプログラムS2に依存し、S1からプラグマを削除しない場合、S1のコンパイルにS2のプラグマが必要になる場合があります。
既存コード中のPRAGMA RESTRICT_REFERENCESをヒントPARALLEL_ENABLEおよびDETERMINISTICに置き換えると、新しい依存コードのアクションに悪影響を与える可能性があります。(既存コードのアクションを保持するために、PRAGMA RESTRICT_REFERENCESを使用してください。)
既存のPL/SQLアプリケーションでは、既存コードとの統合を容易にするために、新しいファンクションに対してもプラグマを引き続き使用することができます。新しいアプリケーションではプラグマは使用しないでください。
PRAGMA RESTRICT_REFERENCESを使用する場合は、パッケージ本体ではなく、パッケージ仕様部に含めます。このプラグマは、サブプログラムの宣言の後に置く必要がありますが、直後に置く必要はありません。所定のサブプログラム宣言を参照できるプラグマは、1つのみです。
PRAGMA RESTRICT_REFERENCESをコーディングするには、次の構文を使用します。
PRAGMA RESTRICT_REFERENCES (
Function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] );
パラメータは次のとおりです。
| オプション | 説明 |
|---|---|
WNDS |
サブプログラムはデータベース状態を書き込みません(データベース表を変更しないということです)。 |
RNDS |
サブプログラムはデータベース状態を読み込みません(データベース表を問い合せないということです)。 |
WNPS |
サブプログラムはパッケージ状態を書き込みません(パッケージ変数の値を変更しないということです)。 |
RNPS |
サブプログラムはパッケージ状態を読み込みません(パッケージ変数の値を参照しないということです)。 |
TRUST |
プラグマにリストされているその他の制限は適用されず、真であると判断できます。これにより、RESTRICT_REFERENCES宣言を持つファンクションから、この宣言を持たないファンクション簡単に起動できます。 |
引数はどんな順序でも渡せます。サブプログラム本体にあるSQL文のいずれかが規則に違反する場合は、その文が解析されるときにエラーが発生します。
例6-20では、ファンクションcompound_により、データベースまたはパッケージ状態の読取りおよび書込みは行われないため、最大の純粋度レベルを宣言できます。PL/SQLコンパイラがサブプログラムを必要以上に拒否することがないように、サブプログラムで可能な最大の純粋度レベルを常に宣言してください。
例6-20 PRAGMA RESTRICT_REFERENCES
DROP TABLE accounts; -- in case it exists CREATE TABLE accounts ( acctno INTEGER, balance NUMBER ); INSERT INTO accounts (acctno, balance) VALUES (12345, 1000.00); CREATE OR REPLACE PACKAGE finance AS FUNCTION compound_ ( years IN NUMBER, amount IN NUMBER, rate IN NUMBER ) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES (compound_, WNDS, WNPS, RNDS, RNPS); END finance; / CREATE PACKAGE BODY finance AS FUNCTION compound_ ( years IN NUMBER, amount IN NUMBER, rate IN NUMBER ) RETURN NUMBER IS BEGIN RETURN amount * POWER((rate / 100) + 1, years); END compound_; -- No pragma in package body END finance; / DECLARE interest NUMBER; BEGIN SELECT finance.compound_(5, 1000, 6) INTO interest FROM accounts WHERE acctno = 12345; END; /
内容は次のとおりです。
PRAGMA RESTRICT REFERENCESにキーワードTRUSTが含まれる場合、プラグマにリストされている制限は適用されませんが、真であると判断できます。
プラグマを使用するPL/SQLコードのセクションから、プラグマを使用しないコードのセクションにあるサブプログラム(Javaメソッドなど)を起動するときは、起動されるサブプログラムか起動するサブプログラムのどちらかについて、PRAGMA RESTRICT REFERENCESとTRUSTを指定します。
例6-21と例6-22では両方とも、PL/SQLファンクションfがJavaプロシージャjava_sleepを起動します。例6-21では、java_sleepがTRUST付きのWNDSになるように宣言されているためこれが可能です。例6-22では、fがTRUST付きのWNDSになるように宣言されているため、サブプログラムを起動できます。
例6-21 起動される側にTRUSTが指定されたPRAGMA RESTRICT REFERENCES
CREATE OR REPLACE PACKAGE p IS PROCEDURE java_sleep (milli_seconds IN NUMBER) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; PRAGMA RESTRICT_REFERENCES(java_sleep,WNDS,TRUST); FUNCTION f (n NUMBER) RETURN NUMBER; END p; / CREATE OR REPLACE PACKAGE BODY p IS FUNCTION f ( n NUMBER ) RETURN NUMBER IS BEGIN java_sleep(n); RETURN n; END f; END p; /
例6-22 起動する側にTRUSTが指定されたPRAGMA RESTRICT REFERENCES
CREATE OR REPLACE PACKAGE p IS PROCEDURE java_sleep (milli_seconds IN NUMBER) AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; FUNCTION f (n NUMBER) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES(f,WNDS,TRUST); END p; / CREATE OR REPLACE PACKAGE BODY p IS FUNCTION f ( n NUMBER ) RETURN NUMBER IS BEGIN java_sleep(n); RETURN n; END f; END p; /
静的なINSERT、UPDATEおよびDELETE文は、表の列などのデータベース状態を明示的に読み込まない場合は、RNDSには違反しません。ただし、動的なINSERT、UPDATEおよびDELETEの場合は、データベース状態を明示的に読み込むかどうかにかかわらず、常にRNDSに違反します。
次のINSERT文は、動的に実行される場合はRNDSに違反しますが、静的に実行される場合はRNDSに違反しません。
INSERT INTO my_table values(3, 'BOB');
次のUPDATE文は、my_tableの列nameを明示的に読み込むため、静的に実行された場合も動的に実行された場合もRNDSに違反します。
UPDATE my_table SET id=777 WHERE name='BOB';
サブプログラムがオーバーロードされる場合、PRAGMA RESTRICT_REFERENCESが適用されるのは、最も新しく宣言されたバージョンに対してのみです。
例6-23では、プラグマは、2番目のvalidの宣言に適用されます。
データ・ウェアハウス環境では、大量のデータを変換するためにPL/SQLファンクションを使用します。データは、異なるファンクションによる一連の変換を経由して渡されます。PL/SQL表ファンクションを使用すると、このような変換を、かなりのメモリー・オーバーヘッドを必要としたり、各変換の間でデータを表に格納する必要なく実行できます。これらのファンクションは、複数の行を受け入れて戻すことが可能で、一度ではなく準備できた順に行を戻すことができるだけでなく、パラレル化も可能です。
|
参照: パイプライン・テーブル・ファンクションを使用して複数の変換を実行する方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
一連の行を分析して結果値を計算するために、次のようにSUMなどのSQL集計関数と同様に機能する集計関数をコード化することができます。
次のようなメンバー関数を定義するADTを定義します。
ODCIAggregateInitialize
ODCIAggregateIterate
ODCIAggregateMerge
ODCIAggregateTerminate
メンバー関数をコーディングします。特に、ODCIAggregateIterate では、処理対象の各行について起動されると結果が蓄積されます。ADTの属性を使用して中間結果を格納します。
集計関数を作成して、ADTと対応付けます。
SQL問合せ、DML文またはSQL集計関数に使用する他の場所から集計関数をコールします。集計関数を起動する際は、DISTINCTやALLなどの一般的なオプションを指定できます。
|
参照: ユーザー定義の集計関数の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。 |
