| Oracle Databaseアドバンスト・アプリケーション開発者ガイド 11gリリース1（11.1） E05687-02 |
|
 戻る |
 次へ |
この章では、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アプリケーションで使用します。通常、ストアド・サブプログラムを起動するか、カーソル変数をオープンするために使用します。
例7-1の無名ブロックは、DBMS_OUTPUTパッケージを使用してHR.EMPLOYEES表の部門20のすべての従業員の名前を表示します。
例7-1 無名ブロック
SQL> DECLARE 2 last_name VARCHAR2(10); 3 cursor c1 IS SELECT LAST_NAME 4 FROM EMPLOYEES 5 WHERE DEPARTMENT_ID = 20; 6 BEGIN 7 OPEN c1; 8 LOOP 9 FETCH c1 INTO last_name; 10 EXIT WHEN c1%NOTFOUND; 11 DBMS_OUTPUT.PUT_LINE(last_name); 12 END LOOP; 13 END; 14 / Hartstein Fay PL/SQL procedure successfully completed. SQL>
例外を使用すると、PL/SQLプログラム・ロジック内のOracle Databaseエラー条件を処理できます。これによって、使用中のアプリケーションで、クライアント・アプリケーションを異常終了させるようなエラーをサーバーが発行しないようにできます。例7-2の無名ブロックは、事前定義されたOracle Database例外NO_DATA_FOUNDを処理します（この例外が処理されない場合は、ORA-01403が発生します）。
例7-2 事前定義されたエラーの例外ハンドラを持つ無名ブロック
SQL> DECLARE
2 Emp_number INTEGER := 9999
3 Emp_name VARCHAR2(10);
4 BEGIN
5 SELECT LAST_NAME INTO Emp_name
6 FROM EMPLOYEES
7 WHERE EMPLOYEE_ID = Emp_number;
8 DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);
12 END;
13 /
No such employee: 9999
PL/SQL procedure successfully completed.
SQL>
また、例7-3に示すように、独自の例外を定義してブロックの宣言部に宣言し、それをブロックの例外部分に指定できます。
例7-3 ユーザー定義例外の例外ハンドラを持つ無名ブロック
SQL> DECLARE 2 Emp_name VARCHAR2(10); 3 Emp_number INTEGER; 4 Empno_out_of_range EXCEPTION; 5 BEGIN 6 Emp_number := 10001; 7 IF Emp_number > 9999 OR Emp_number < 1000 THEN 8 RAISE Empno_out_of_range; 9 ELSE 10 SELECT LAST_NAME INTO Emp_name FROM EMPLOYEES 11 WHERE EMPLOYEE_ID = Emp_number; 12 DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name); 13 END IF; 14 EXCEPTION 15 WHEN Empno_out_of_range THEN 16 DBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number || 17 ' is out of range.'); 18 END; 19 / Employee number 10001 is out of range. PL/SQL procedure successfully completed. SQL>
|
関連項目:
|
ストアドPL/SQLユニットは、次のような特徴を持つサブプログラム（プロシージャまたはファンクション）またはパッケージです。
固有の名前を持っています。
パラメータをとり、値を戻すことができます。
データ・ディクショナリに格納されます。
多数のユーザーが起動できます。
パッケージに属しているサブプログラムをパッケージ・サブプログラム、そうでないサブプログラムをスタンドアロン・サブプログラムといいます。
内容は次のとおりです。
サブプログラムは、データベース内に格納されるため、名前を付ける必要があります。名前を付けることによって、他のストアド・サブプログラムと区別され、アプリケーションで起動できます。パブリックで参照できるスキーマ内の個々のサブプログラムは、一意の名前を持つ必要があります。その名前は、有効なPL/SQL識別子である必要があります。
|
注意: SQL*Moduleによって生成されたスタブを使用してストアド・サブプログラムを起動する場合、ストアド・サブプログラム名は、起動側ホストの3GL言語（AdaやCなど）の有効な識別子である必要もあります。 |
ストアド・サブプログラムには、パラメータを指定できます。例7-4のプロシージャでは、部門番号が入力パラメータになっています。入力パラメータは、パラメータ化されたカーソルc1のオープン時に使用されます。
例7-4 パラメータを使用するストアド・プロシージャ
SQL> CREATE OR REPLACE PROCEDURE get_emp_names ( 2 dept_num IN NUMBER 3 ) 4 IS 5 emp_name VARCHAR2(10); 6 CURSOR c1 (dept_num NUMBER) IS 7 SELECT LAST_NAME FROM EMPLOYEES 8 WHERE DEPARTMENT_ID = dept_num; 9 BEGIN 10 OPEN c1(dept_num); 11 LOOP 12 FETCH c1 INTO emp_name; 13 EXIT WHEN C1%NOTFOUND; 14 DBMS_OUTPUT.PUT_LINE(emp_name); 15 END LOOP; 16 CLOSE c1; 17 END; 18 / Procedure created. SQL>
サブプログラムの仮パラメータには、表7-1に示す3つの主要な属性があります。
表7-1 サブプログラム・パラメータの属性
| パラメータ属性 | 説明 |
|---|---|
|
名前 |
名前は、有効なPL/SQL識別子である必要があります。 |
|
モード |
入力のみのパラメータ（ |
|
データ型 |
パラメータのデータ型は、標準PL/SQLデータ型です。 |
内容は次のとおりです。
パラメータ・モードは、仮パラメータの動作を定義します。サブプログラムには、IN（デフォルト）、OUT、IN OUTという3つのパラメータ・モードを使用できます。ファンクションには、OUTモードおよびIN OUTモードは使用しないでください。実際のプログラミングでは、ファンクションにより単一の値が戻され、かつサブプログラムに対してローカルでない変数の値が変更されないことが求められます。
表7-2に、パラメータ・モードの概要を示します。
表7-2 パラメータ・モード
| IN | OUT | IN OUT |
|---|---|---|
|
デフォルト |
指定する必要があります。 |
指定する必要があります。 |
|
値をサブプログラムに渡します。 |
値をコール側に戻します。 |
初期値をサブプログラムに渡し、更新された値をコール側に戻します。 |
|
仮パラメータが定数として動作します。 |
仮パラメータが未初期化変数として動作します。 |
仮パラメータが初期化変数として動作します。 |
|
仮パラメータに値を割り当てることはできません。 |
仮パラメータを式の中で使用できません。値を割り当てる必要があります。 |
仮パラメータに値を割り当てる必要があります。 |
|
実パラメータを、定数、初期化変数、リテラルまたは式にできます。 |
実パラメータは変数である必要があります。 |
実パラメータは変数である必要があります。 |
|
関連項目: パラメータ・モードの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
NUMBERやVARCHAR2などの無制約の型名
%TYPE属性や%ROWTYPE属性を使用して制約される型
|
注意: NUMBER(2)または VARCHAR2(20)などの数値が制約される型は、パラメータ・リストでは使用できません。 |
型属性%TYPEおよび%ROWTYPEは、パラメータを制約するために使用します。たとえば、例7-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属性は、指定された表のすべての列を含むレコードを作成するために使用します。例7-5のプロシージャは、指定された従業員IDに関するPL/SQLレコード内のEMPLOYEES表のすべての列を返します。
例7-5 %TYPEおよび%ROWTYPE属性
SQL> CREATE OR REPLACE PROCEDURE get_emp_rec ( 2 emp_number IN EMPLOYEES.EMPLOYEE_ID%TYPE, 3 emp_info OUT EMPLOYEES%ROWTYPE 4 ) 5 IS 6 BEGIN 7 SELECT * INTO emp_info 8 FROM EMPLOYEES 9 WHERE EMPLOYEE_ID = emp_number; 10 END; 11 / Procedure created. SQL> SQL> -- Invoke procedure from PL/SQL block: SQL> SQL> DECLARE 2 emp_row EMPLOYEES%ROWTYPE; 3 BEGIN 4 get_emp_rec(206, emp_row); 5 DBMS_OUTPUT.PUT('EMPLOYEE_ID: ' || emp_row.EMPLOYEE_ID); 6 DBMS_OUTPUT.NEW_LINE; 7 DBMS_OUTPUT.PUT('FIRST_NAME: ' || emp_row.FIRST_NAME); 8 DBMS_OUTPUT.NEW_LINE; 9 DBMS_OUTPUT.PUT('LAST_NAME: ' || emp_row.LAST_NAME); 10 DBMS_OUTPUT.NEW_LINE; 11 DBMS_OUTPUT.PUT('EMAIL: ' || emp_row.EMAIL); 12 DBMS_OUTPUT.NEW_LINE; 13 DBMS_OUTPUT.PUT('PHONE_NUMBER: ' || emp_row.PHONE_NUMBER); 14 DBMS_OUTPUT.NEW_LINE; 15 DBMS_OUTPUT.PUT('HIRE_DATE: ' || emp_row.HIRE_DATE); 16 DBMS_OUTPUT.NEW_LINE; 17 DBMS_OUTPUT.PUT('JOB_ID: ' || emp_row.JOB_ID); 18 DBMS_OUTPUT.NEW_LINE; 19 DBMS_OUTPUT.PUT('SALARY: ' || emp_row.SALARY); 20 DBMS_OUTPUT.NEW_LINE; 21 DBMS_OUTPUT.PUT('COMMISSION_PCT: ' || emp_row.COMMISSION_PCT); 22 DBMS_OUTPUT.NEW_LINE; 23 DBMS_OUTPUT.PUT('MANAGER_ID: ' || emp_row.MANAGER_ID); 24 DBMS_OUTPUT.NEW_LINE; 25 DBMS_OUTPUT.PUT('DEPARTMENT_ID: ' || emp_row.DEPARTMENT_ID); 26 DBMS_OUTPUT.NEW_LINE; 27 END; 28 / EMPLOYEE_ID: 206 FIRST_NAME: William LAST_NAME: Gietz EMAIL: WGIETZ PHONE_NUMBER: 415.555.0100 HIRE_DATE: 07-JUN-94 JOB_ID: AC_ACCOUNT SALARY: 8300 COMMISSION_PCT: MANAGER_ID: 205 DEPARTMENT_ID: 110 PL/SQL procedure successfully completed. SQL>
ストアド・ファンクションは、%ROWTYPEを使用して宣言される値を戻すことができます。次に例を示します。
FUNCTION get_emp_rec (dept_num IN EMPLOYEES.DEPARTMENT_ID%TYPE) RETURN EMPLOYEES%ROWTYPE IS ...
PL/SQL表をパラメータとしてストアド・サブプログラムに渡すことができます。レコードの表も、パラメータとして渡せます。
|
注意: リモート・サブプログラムにPL/SQL表やレコードなどのユーザー定義型を渡す場合、タイプ・チェッカがソースを検証できるようにPL/SQLで同じ定義を使用するには、冗長なループバックDBLINKを作成してください。PL/SQLのコンパイル時に、両方のソースが同じ位置から引き出されます。 |
パラメータには、デフォルト値を設定できます。パラメータにデフォルト値を設定するには、DEFAULTキーワードまたは代入演算子を使用します。たとえば、Get_emp_namesプロシージャの仕様部は次のように作成できます。
PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT 20) IS ...
または
PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...
パラメータにデフォルト値を使用する場合は、サブプログラムの起動時に実パラメータ・リストからそのパラメータを省略できます。起動時にパラメータ値を指定すると、デフォルト値がオーバーライドされます。
|
注意: 無名PL/SQLブロック内とは異なり、ストアド・サブプログラム内では、変数、カーソルおよび例外の宣言の前にキーワードDECLAREを使用しないでください。使用するとエラーが発生します。 |
サブプログラムを作成するには、テキスト・エディタを使用します。その後、SQL*Plusなどの対話形式のツール製品を使用して次の文を入力し、プロシージャを含むテキスト・ファイルをロードします。
SQL> @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言語リファレンス』を参照してください。
パッケージとは、データベース内に格納されている関連プログラム・オブジェクト（サブプログラム、変数、定数、カーソル、例外など）のコレクションです。
パッケージは、サブプログラムをスタンドアロンのスキーマ・オブジェクトとして作成するかわりに使用します。パッケージには、スタンドアロンのサブプログラムに比べて、多数のメリットがあります。たとえば、次のことができます。
アプリケーション開発をより効率的に行えます。
権限をより効率的に付与できます。
依存スキーマ・オブジェクトを再コンパイルせずにパッケージ・オブジェクトを変更できます。
Oracle Databaseで複数のパッケージ・オブジェクトを一度にメモリー内に読み込めます。
パッケージ内のすべてのサブプログラムが使用できるグローバル変数およびグローバル・カーソルを、そのパッケージ内に含めることができます。
サブプログラムをオーバーロードします。サブプログラムのオーバーロードとは、同一パッケージ内に同じ名前のサブプログラムを複数作成することです。それぞれのサブプログラムが異なる数またはデータ型の引数をとることができます。
|
関連項目: サブプログラム名のオーバーロードの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
パッケージ仕様部は、パッケージの有効範囲外で参照できるパブリック型、変数、定数およびサブプログラムを宣言します。パッケージ本体は、パッケージ外のアプリケーションが参照できないプライベート・オブジェクトのみでなく、仕様部で宣言されているオブジェクトも定義します。
例7-6のSQL*Plusスクリプトは、1つのストアド・ファンクションおよび2つのストアド・プロシージャを含むパッケージを作成し、プロシージャの1つを起動します。
例7-6 PL/SQLパッケージの作成とパッケージ・サブプログラムの起動
SQL> -- Sequence that packaged function needs: SQL> SQL> CREATE SEQUENCE emp_sequence 2 START WITH 8000 3 INCREMENT BY 10; Sequence created. SQL> SQL> -- Package specification: SQL> SQL> CREATE or REPLACE PACKAGE employee_management IS 2 3 FUNCTION hire_emp ( 4 firstname VARCHAR2, 5 lastname VARCHAR2, 6 email VARCHAR2, 7 phone VARCHAR2, 8 hiredate DATE, 9 job VARCHAR2, 10 sal NUMBER, 11 comm NUMBER, 12 mgr NUMBER, 13 deptno NUMBER 14 ) RETURN NUMBER; 15 16 PROCEDURE fire_emp( 17 emp_id IN NUMBER 18 ); 19 20 PROCEDURE sal_raise ( 21 emp_id IN NUMBER, 22 sal_incr IN NUMBER 23 ); 24 END employee_management; 25 / Package created. SQL> -- Package body: SQL> SQL> CREATE or REPLACE PACKAGE BODY employee_management IS 2 3 FUNCTION hire_emp ( 4 firstname VARCHAR2, 5 lastname VARCHAR2, 6 email VARCHAR2, 7 phone VARCHAR2, 8 hiredate DATE, 9 job VARCHAR2, 10 sal NUMBER, 11 comm NUMBER, 12 mgr NUMBER, 13 deptno NUMBER 14 ) RETURN NUMBER 15 IS 16 new_empno NUMBER(10); 17 BEGIN
18 new_empno := emp_sequence.NEXTVAL; 19 20 INSERT INTO EMPLOYEES VALUES ( 21 new_empno, 22 firstname, 23 lastname, 24 email, 25 phone, 26 hiredate, 27 job, 28 sal, 29 comm, 30 mgr, 31 deptno 32 ); 33 34 RETURN (new_empno); 35 END hire_emp; 36 37 PROCEDURE fire_emp ( 38 emp_id IN NUMBER 39 ) IS 40 BEGIN 41 DELETE FROM EMPLOYEES 42 WHERE EMPLOYEE_ID = emp_id; 43 44 IF SQL%NOTFOUND THEN 45 raise_application_error( 46 -20011, 47 'Invalid Employee Number: ' || TO_CHAR(Emp_id) 48 ); 49 END IF; 50 END fire_emp; 51 52 PROCEDURE sal_raise ( 53 emp_id IN NUMBER, 54 sal_incr IN NUMBER 55 ) IS 56 BEGIN 57 UPDATE EMPLOYEES 58 SET SALARY = SALARY + sal_incr 59 WHERE EMPLOYEE_ID = emp_id; 60 61 IF SQL%NOTFOUND THEN 62 raise_application_error( 63 -20011, 64 'Invalid Employee Number: ' || TO_CHAR(Emp_id) 65 ); 66 END IF; 67 END sal_raise; 68 END employee_management; 69 / Package body created. SQL> SQL> -- Invoke packaged procedures: SQL> SQL> DECLARE 2 empno NUMBER(6); 3 sal NUMBER(6); 4 temp NUMBER(6); 5 BEGIN 6 empno := employee_management.hire_emp( 7 'John', 8 'Doe', 9 'john.doe@example.com', 10 '555-0100', 11 '20-SEP-07', 12 'ST_CLERK', 13 2500, 14 0, 15 100, 16 20); 17 18 DBMS_OUTPUT.PUT_LINE('New employee ID is ' || TO_CHAR(empno)); 19 END; 20 / New employee ID is 8000 PL/SQL procedure successfully completed. SQL>
サブプログラム、トリガー、パッケージなどの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回のみです。アプリケーションでこのエラーを処理する場合は、適切な処理方法を採用するように注意してください。たとえば、あるパッケージ内のサブプログラムにより別のパッケージ内のサブプログラムが起動される場合、アプリケーションでは両方のパッケージに対するセッション状態が失われていることを認識できる必要があります。 |
本番環境の多くでは、パッケージが無効になる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文のパフォーマンスが向上します。
例7-7のPL/SQLブロックは、管理職のID番号が7902、7698または7839の従業員について昇給を行うものですが、バルク・バインドを使用する場合と使用しない場合があります。PL/SQLがバルク・バインドを使用しないで、更新する各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例7-7 コレクションを参照するDML文
SQL> DECLARE 2 TYPE numlist IS VARRAY (100) OF NUMBER; 3 id NUMLIST := NUMLIST(7902, 7698, 7839); 4 BEGIN 5 -- Efficient method, using bulk bind: 6 7 FORALL i IN id.FIRST..id.LAST 8 UPDATE EMPLOYEES 9 SET SALARY = 1.1 * SALARY 10 WHERE MANAGER_ID = id(i); 11 12 -- Slower method: 13 14 FOR i IN id.FIRST..id.LAST LOOP 15 UPDATE EMPLOYEES 16 SET SALARY = 1.1 * SALARY 17 WHERE MANAGER_ID = id(i); 18 END LOOP; 19 END; 20 / PL/SQL procedure successfully completed. SQL>
BULK COLLECT INTO句によって、コレクションを参照する問合せのパフォーマンスを改善できます。スカラー値表または%TYPE値表にBULK COLLECT INTOを使用できます。
例7-8のPL/SQLブロックは、複数の値の問合せを行ってその値をPL/SQL表に格納するものですが、バルク・バインドを使用する場合と使用しない場合があります。PL/SQLがバルク・バインドを使用しないで、選択された各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例7-8 コレクションを参照するSELECT文
SQL> DECLARE 2 TYPE var_tab IS TABLE OF VARCHAR2(20) 3 INDEX BY PLS_INTEGER; 4 5 empno VAR_TAB; 6 ename VAR_TAB; 7 counter NUMBER; 8 9 CURSOR c IS 10 SELECT EMPLOYEE_ID, LAST_NAME 11 FROM EMPLOYEES 12 WHERE MANAGER_ID = 7698; 13 BEGIN 14 -- Efficient method, using bulk bind: 15 16 SELECT EMPLOYEE_ID, LAST_NAME BULK COLLECT 17 INTO empno, ename 18 FROM EMPLOYEES 19 WHERE MANAGER_ID = 7698; 20 21 -- Slower method: 22 23 counter := 1; 24 25 FOR rec IN c LOOP 26 empno(counter) := rec.EMPLOYEE_ID; 27 ename(counter) := rec.LAST_NAME; 28 counter := counter + 1; 29 END LOOP; 30 END; 31 / PL/SQL procedure successfully completed. SQL>
BULK COLLECT INTOキーワードとともにFORALLキーワードを使用すると、コレクションを参照しDMLを戻すFORループのパフォーマンスを改善できます。
例7-9のPL/SQLブロックは、従業員コレクションのボーナスを計算し、EMPLOYEES表を更新します。さらに、ボーナスをbonus_list_inst列に戻します。この操作をバルク・バインドを使用して実行し、また使用せずに実行します。PL/SQLがバルク・バインドを使用しないで、更新する各従業員についてSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
例7-9 コレクションを参照しDMLを返すFORループ
SQL> DECLARE 2 TYPE emp_list IS VARRAY(100) OF EMPLOYEES.EMPLOYEE_ID%TYPE; 3 empids emp_list := emp_list(182, 187, 193, 200, 204, 206); 4 5 TYPE bonus_list IS TABLE OF EMPLOYEES.SALARY%TYPE; 6 bonus_list_inst bonus_list; 7 8 BEGIN 9 -- Efficient method, using bulk bind: 10 11 FORALL i IN empids.FIRST..empids.LAST 12 UPDATE EMPLOYEES 13 SET SALARY = 0.1 * SALARY 14 WHERE EMPLOYEE_ID = empids(i) 15 RETURNING SALARY BULK COLLECT INTO bonus_list_inst; 16 17 -- Slower method: 18 19 FOR i IN empids.FIRST..empids.LAST LOOP 20 UPDATE EMPLOYEES 21 SET SALARY = 0.1 * SALARY 22 WHERE EMPLOYEE_ID = empids(i) 23 RETURNING SALARY INTO bonus_list_inst(i); 24 END LOOP; 25 END; 26 / PL/SQL procedure successfully completed. SQL>
共有ライブラリにあるネイティブ・コードに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でのカーソル変数の使用例を示します。
|
関連項目: プログラム・インタフェースを使用するカーソル変数の例がさらに必要な場合は、次のマニュアルを参照してください。
|
例7-10では、PL/SQLカーソル変数型および2つのプロシージャを定義するパッケージを作成し、PL/SQLブロックからプロシージャを起動します。最初のプロシージャは、WHERE句にバインド変数を使用してカーソル変数をオープンします。2番目のプロシージャは、カーソル変数を使用してEMPLOYEES表の行をフェッチします。
例7-10 カーソル変数を使用するデータのフェッチ
SQL> CREATE OR REPLACE PACKAGE emp_data AS 2 3 TYPE emp_val_cv_type IS REF CURSOR 4 RETURN EMPLOYEES%ROWTYPE; 5 6 PROCEDURE open_emp_cv ( 7 emp_cv IN OUT emp_val_cv_type, 8 dept_number IN EMPLOYEES.DEPARTMENT_ID%TYPE 9 ); 10 11 PROCEDURE fetch_emp_data ( 12 emp_cv IN emp_val_cv_type, 13 emp_row OUT EMPLOYEES%ROWTYPE 14 ); 15 END emp_data; 16 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY emp_data AS 2 PROCEDURE open_emp_cv ( 3 emp_cv IN OUT emp_val_cv_type, 4 dept_number IN EMPLOYEES.DEPARTMENT_ID%TYPE 5 ) IS 6 BEGIN 7 OPEN emp_cv FOR 8 SELECT * FROM EMPLOYEES 9 WHERE DEPARTMENT_ID = dept_number; 10 END open_emp_cv; 11 12 PROCEDURE fetch_emp_data ( 13 emp_cv IN emp_val_cv_type, 14 emp_row OUT EMPLOYEES%ROWTYPE 15 ) IS 16 BEGIN 17 FETCH emp_cv INTO emp_row; 18 END fetch_emp_data; 19 END emp_data; 20 / Package body created. SQL> SQL> -- Invoke packaged procedures: SQL> SQL> DECLARE 2 emp_curs emp_data.emp_val_cv_type; 3 dept_number EMPLOYEES.DEPARTMENT_ID%TYPE; 4 emp_row EMPLOYEES%ROWTYPE; 5 6 BEGIN 7 dept_number := 20; 8 9 -- Open cursor, using variable: 10 11 emp_data.open_emp_cv(emp_curs, dept_number); 12 13 -- Fetch and display data: 14 15 LOOP 16 emp_data.fetch_emp_data(emp_curs, emp_row); 17 EXIT WHEN emp_curs%NOTFOUND; 18 DBMS_OUTPUT.PUT(emp_row.LAST_NAME || ' '); 19 DBMS_OUTPUT.PUT_LINE(emp_row.SALARY); 20 END LOOP; 21 END; 22 / Hartstein 13000 Fay 6000 PL/SQL procedure successfully completed. SQL>
例7-11では、プロシージャは、パラメータdiscrimの値に応じてEMPLOYEES表またはDEPARTMENTS表のカーソル変数をオープンします。無名ブロックは、プロシージャを起動してEMPLOYEES表のカーソル変数をオープンしますが、DEPARTMENTS表からフェッチします。このため、事前定義の例外ROWTYPE_MISMATCHが発生します。
例7-11 識別子を使用したカーソル変数
SQL> CREATE OR REPLACE PACKAGE emp_dept_data AS 2 TYPE cv_type IS REF CURSOR; 3 4 PROCEDURE open_cv ( 5 cv IN OUT cv_type, 6 discrim IN POSITIVE 7 ); 8 END emp_dept_data; 9 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY emp_dept_data AS 2 PROCEDURE open_cv ( 3 cv IN OUT cv_type, 4 discrim IN POSITIVE) IS 5 BEGIN 6 IF discrim = 1 THEN 7 OPEN cv FOR 8 SELECT * FROM EMPLOYEES; 9 ELSIF discrim = 2 THEN 10 OPEN cv FOR 11 SELECT * FROM DEPARTMENTS; 12 END IF; 13 END open_cv; 14 END emp_dept_data; 15 / Package body created. SQL> DECLARE 2 emp_rec EMPLOYEES%ROWTYPE; 3 dept_rec DEPARTMENTS%ROWTYPE; 4 cv Emp_dept_data.CV_TYPE; 5 BEGIN 6 emp_dept_data.open_cv(cv, 1); -- Open cv for EMPLOYEES fetch. 7 FETCH cv INTO dept_rec; -- Fetch from DEPARTMENTS. 8 DBMS_OUTPUT.PUT(dept_rec.DEPARTMENT_ID); 9 DBMS_OUTPUT.PUT_LINE(' ' || dept_rec.LOCATION_ID); 10 EXCEPTION 11 WHEN ROWTYPE_MISMATCH THEN 12 BEGIN 13 DBMS_OUTPUT.PUT_LINE 14 ('Row type mismatch, fetching EMPLOYEES data ...'); 15 FETCH cv INTO emp_rec; 16 DBMS_OUTPUT.PUT(emp_rec.DEPARTMENT_ID); 17 DBMS_OUTPUT.PUT_LINE(' ' || emp_rec.LAST_NAME); 18 END; 19 END; 20 / Row type mismatch, fetching EMPLOYEES data ... 90 King PL/SQL procedure successfully completed. SQL>
コンパイル時のエラーをリスト表示するには、静的データ・ディクショナリ・ビュー*_ERRORSを問い合せます。これらのビューからは、元のソース・コードを取得できます。サブプログラムのコンパイルに関するエラー・メッセージは、サブプログラムを置き換えると更新され、サブプログラムを削除すると削除されます。
SQL*Plusによって、コンパイル時のエラーに関する警告メッセージが発行されます。詳細を確認するには、SHOW ERRORSコマンドを使用する必要があります。
|
注意: 長い行を出力するには、SHOW ERRORS文を発行する前にSET LINESIZE文を使用してください。通常、次のように値を132に指定することをお薦めします。次に例を示します。
SET LINESIZE 132 |
例7-12にはコンパイル時のエラーが2つあります。WHERはWHEREの誤りで、ENDの後にはセミコロンが必要です。SHOW ERRORSを使用すると、各エラーの行、列および説明が表示されます。
例7-12 コンパイル時のエラー
SQL> CREATE OR REPLACE PROCEDURE fire_emp ( 2 emp_id NUMBER 3 ) AS 4 BEGIN 5 DELETE FROM EMPLOYEES 6 WHER EMPLOYEE_ID = Emp_id; 7 END 8 / Warning: Procedure created with compilation errors. SQL> SHOW ERRORS; Errors for PROCEDURE FIRE_EMP: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/3 PL/SQL: SQL Statement ignored 6/10 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. SQL>
|
関連項目:
|
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にします。デフォルトでは、このオプションは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プロシージャを起動して、ユーザー定義エラーの番号およびメッセージを戻します。
例外ハンドラは、ユーザー定義エラー・メッセージを処理するために定義することもできます。たとえば、図7-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の詳細は、第8章「PL/Scopeの使用」を参照してください。
PL/SQL階層プロファイラは、サブプログラム・コール別に編成されたPL/SQLプログラムの動的実行プロファイルをレポートします。SQL実行時間およびPL/SQL実行時間が個別に説明されます。動的実行プロファイルにおけるサブプログラム・レベルの各サマリーには、サブプログラムへのコール数、サブプログラム自体に要した時間、サブプログラムのサブツリー（つまり依存サブプログラム）に要した時間、詳細な親子情報などが表示されます。
生成されたHTMLレポートは任意のブラウザで参照できます。ブラウザのナビゲーション機能と厳選したリンクを組み合せた効率的な手段により、大規模なアプリケーションのパフォーマンスを分析し、アプリケーションのパフォーマンスを向上させ、開発コストを削減できます。
PL/SQL階層プロファイラの詳細は、第9章「PL/SQL階層プロファイラの使用」を参照してください。
Oracle JDeveloperの最新のリリースには、PL/SQL、Javaおよびマルチ言語プログラムをデバッグする広範な機能が含まれています。各種Oracle製品の一部としてOracle JDeveloperを取得できます。通常、最新のリリースはhttp://otn.oracle.co.jp/からダウンロードできます。
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権限を付与することは、すべての権限をデータベースに付与することを意味します。 |
Oracle9iリリース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のツール製品から対話形式で起動できます。例7-13では、SQL*Plusを使用してプロシージャを作成し、2つの方法で起動します。
例7-13 SQL*Plusを使用した対話形式によるサブプログラムの起動
SQL> CREATE OR REPLACE PROCEDURE salary_raise ( 2 employee EMPLOYEES.EMPLOYEE_ID%TYPE, 3 raise EMPLOYEES.SALARY%TYPE 4 ) IS 5 BEGIN 6 UPDATE EMPLOYEES 7 SET SALARY = SALARY + raise 8 WHERE EMPLOYEE_ID = employee; 9 END; 10 / Procedure created. SQL> SQL> -- Invoke procedure from within PL/SQL block: SQL> SQL> BEGIN 2 salary_raise(205, 200); 3 END; 4 / PL/SQL procedure successfully completed. SQL> -- Invoke procedure with EXECUTE statement: SQL> SQL> EXECUTE salary_raise(205, 200); PL/SQL procedure successfully completed. SQL>
一部の対話形式ツールでは、セッション中に使用できるセッション変数を作成することができます。例7-14では、SQL*Plusを使用してセッション変数の作成、使用および出力を行います。
例7-14 SQL*Plusを使用したセッション変数の作成と使用
SQL> -- Create function for later use: SQL> SQL> CREATE OR REPLACE FUNCTION get_job_id ( 2 emp_id EMPLOYEES.EMPLOYEE_ID%TYPE 3 ) RETURN EMPLOYEES.JOB_ID%TYPE 4 IS 5 job_id EMPLOYEES.JOB_ID%TYPE; 6 BEGIN 7 SELECT JOB_ID INTO job_id 8 FROM EMPLOYEES 9 WHERE EMPLOYEE_ID = emp_id; 10 RETURN job_id; 11 END; 12 / Function created. SQL> SQL> -- Create session variable: SQL> SQL> VARIABLE job VARCHAR2(10); SQL> SQL> -- Execute function and store returned value in session variable: SQL> SQL> EXECUTE :job := get_job_id(204); PL/SQL procedure successfully completed. SQL> SQL> PRINT job; JOB -------------------------------- PR_REP SQL>
|
関連項目:
|
ストアド・サブプログラムは、別のサブプログラムやトリガーから起動できます。例7-15では、プロシージャprint_mgr_nameがプロシージャprint_emp_nameを起動します。
サブプログラムの再帰的な起動が可能です（つまり、サブプログラムがサブプログラム自体を起動できます）。
例7-15 別のサブプログラムからのサブプログラムの起動
SQL> -- Create procedure that takes employee's ID and prints employee's name: SQL> SQL> CREATE OR REPLACE PROCEDURE print_emp_name ( 2 emp_id EMPLOYEES.EMPLOYEE_ID%TYPE 3 ) 4 IS 5 fname EMPLOYEES.FIRST_NAME%TYPE; 6 lname EMPLOYEES.LAST_NAME%TYPE; 7 BEGIN 8 SELECT FIRST_NAME, LAST_NAME 9 INTO fname, lname 10 FROM EMPLOYEES 11 WHERE EMPLOYEE_ID = emp_id; 12 13 DBMS_OUTPUT.PUT_LINE ( 14 'Employee #' || emp_id || ': ' || fname || ' ' || lname 15 ); 16 END; 17 / Procedure created. SQL> -- Create procedure that takes employee's ID and prints manager's name: SQL> SQL> CREATE OR REPLACE PROCEDURE print_mgr_name ( 2 emp_id EMPLOYEES.EMPLOYEE_ID%TYPE 3 ) 4 IS 5 mgr_id EMPLOYEES.MANAGER_ID%TYPE; 6 BEGIN 7 SELECT MANAGER_ID 8 INTO mgr_id 9 FROM EMPLOYEES 10 WHERE EMPLOYEE_ID = emp_id; 11 12 DBMS_OUTPUT.PUT_LINE ( 13 'Manager of employee #' || emp_id || ' is: ' 14 ); 15 16 print_emp_name(mgr_id); 17 END; 18 / Procedure created. SQL> BEGIN 2 print_emp_name(200); 3 print_mgr_name(200); 4 END; 5 / Employee #200: Jennifer Whalen Manager of employee #200 is: Employee #101: Neena Kochhar PL/SQL procedure successfully completed. SQL>
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サブプログラムの起動方法の詳細は、次のマニュアルを参照してください。
|
リモート・サブプログラム（スタンドアロン・サブプログラムおよびパッケージ・サブプログラム）は、そのリモート・サブプログラム名、データベース・リンクおよびリモート・サブプログラムのパラメータを指定することによって、サブプログラム、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文からファンクションを起動しないでください。かわりにカーソルを使用します。 たとえば、選択した行ごとに1回ずつファンクションをコールすることがアプリケーションで必要な場合は、カーソルをオープンして、カーソルから行を選択し、各行に対してファンクションをコールします。このようにすると、ファンクションをコールする回数が、カーソルからフェッチされる行数と同じになります。 |
ストアドPL/SQLファンクションをSQL文から起動するには、そのファンクションをスキーマ・レベルまたはパッケージ仕様部で宣言する必要があります。
ストアドPL/SQLファンクションを起動できるSQL文は次のとおりです。
INSERT
UPDATE
DELETE
SELECT
CALL
（CALL文では、ストアドPL/SQLプロシージャも起動できます。）
SQLからPL/SQLサブプログラムを起動するには、その所有者であるか、またはそのサブプログラムに対するEXECUTE権限を保持している必要があります。PL/SQLファンクションを使用して定義されているビューから選択するには、そのビューに対するSELECT権限が必要です。そのビューからの選択には、別のEXECUTE権限は必要ありません。
パラメータを渡す方法など、PL/SQLサブプログラムを起動する方法の概要は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
内容は次のとおりです。
SQL文からPL/SQLサブプログラムを起動すると、次のような利点があります。
SQLの拡張によって、ユーザーの生産性が向上します。
実行する内容がSQL文のみで表現するには複雑すぎたり、非常に扱いにくかったり、不可能な場合に、SQL文の表現機能が強化されます。
問合せの効率を向上します。
問合せのWHERE句にファンクションを指定すると、条件を使用してデータをフィルタできます。ファンクションを使用できない場合は、アプリケーションで評価する必要があります。
特殊なデータ型（緯度、経度、温度など）を表すための文字列を操作できます。
パラレル問合せを実行できます。
問合せがパラレル化されると、PL/SQLサブプログラム内の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データ型を持つことができます（例7-17を参照してください）。
例7-16のファンクションは、これらの要件を満たしています。
例7-16 SQL式の中で使用できるPL/SQLファンクション
SQL> DROP TABLE payroll;
DROP TABLE payroll
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE payroll (
2 srate NUMBER,
3 orate NUMBER,
4 acctno NUMBER
5 );
Table created.
SQL> CREATE OR REPLACE FUNCTION gross_pay (
2 emp_id IN NUMBER,
3 st_hrs IN NUMBER DEFAULT 40,
4 ot_hrs IN NUMBER DEFAULT 0
5 ) RETURN NUMBER
6 IS
7 st_rate NUMBER;
8 ot_rate NUMBER;
9 BEGIN
10 SELECT srate, orate
11 INTO st_rate, ot_rate
12 FROM payroll
13 WHERE acctno = emp_id;
14
15 RETURN st_hrs * st_rate + ot_hrs * ot_rate;
16 END gross_pay;
17 /
Function created.
SQL>
例7-17のSQL*Plusスクリプトでは、SQL文CALLによりPL/SQLファンクションf1が起動されます。このファンクションの仮パラメータと戻り値のデータ型はPL/SQLデータ型PLS_INTEGERです。実パラメータ2は暗黙的にデータ型PLS_INTEGERへ変換されるため、このCALL文は正常に実行されます。実パラメータの値がPLS_INTEGERの範囲外であれば、このCALL文は正常には実行されません。
例7-17 SQL式から起動された、PL/SQLデータ型の仮パラメータを持つPL/SQLファンクション
SQL> CREATE OR REPLACE FUNCTION f1 ( 2 b IN PLS_INTEGER 3 ) RETURN PLS_INTEGER 4 IS 5 BEGIN 6 RETURN 7 CASE 8 WHEN b > 0 THEN 1 9 WHEN b <= 0 THEN -1 10 ELSE NULL 11 END; 12 END f1; 13 / Function created. SQL> VARIABLE x NUMBER; SQL> CALL f1(b=>2) INTO :x; Call completed. SQL> / Call completed. SQL> PRINT x; X ---------- 1 SQL>
ストアド・サブプログラムの純粋度とは、データベース表またはパッケージ変数に対してそのサブプログラムが及ぼす副作用のことをいいます。副作用によって、問合せのパラレル処理が妨害されたり、処理順序に依存する（したがって、不確定な）結果が発生したり、ユーザー・セッションにまたがったパッケージ状態のメンテナンスが必要になります。ファンクションがSQL問合せまたはDML文から起動される場合は、様々な副作用は受け入れられません。
Oracle8iより前のリリースでは、Oracle DatabaseはPL/SQLコンパイラを使用して、ストアド・サブプログラムまたはSQL文のコンパイル中に制限を施行していました。Oracle8iからは、コンパイル時の制限は緩和され、実行中の制限も少なくなっています。
この変更によって、PL/SQL、JavaおよびCで作成されたストアド・サブプログラムが統一してサポートされ、プログラマには最大限の柔軟性が提供されています。
内容は次のとおりです。
SQL文が実行されるとき、すでに実行中のSQL文の中にこのSQL文が論理的に埋め込まれているかどうかが確認されます。文がトリガーから、またはすでに実行中のSQL文から起動されたサブプログラムから実行されると、この確認が行われます。このような場合は、新しいSQL文が特定のコンテキスト内で安全かどうかを判断するために、さらに確認が行われます。
サブプログラムには次の制限が適用されます。
問合せまたはDML文から起動されるサブプログラムは、現行のトランザクションの終了、セーブポイントの作成またはセーブポイントまでのロールバック、あるいはシステムまたはセッションの変更（ALTER）を実行できません。
問合せ（SELECT）文またはパラレル化されたDML文から起動されるサブプログラムは、DML文を実行できません。またはデータベースを変更できません。
DML文から起動されるサブプログラムでは、そのDML文が変更中の表の読取りおよび変更はできません。
前述のすべての制限は、サブプログラムまたはトリガー内でSQL文が実行される方法にかかわらず適用されます。次に例を示します。
前述の制限は、PL/SQLから起動されるSQL文（サブプログラムまたはトリガーの本体に直接埋め込まれている文かどうかにかかわらず）が、ネイティブ動的メカニズム（EXECUTE IMMEDIATE）を使用して実行されるか、またはDBMS_SQLパッケージを使用して実行される場合、そのSQL文に適用されます。
SQLJ構文を使用してJavaに埋め込まれている文、またはJDBCを使用して実行される文に適用されます。
外部C関数内からコールバック・コンテキストを使用してOCIで実行される文に適用されます。
新しいSQL文の実行が、すでに実行中の文のコンテキストに論理的に埋め込まれていない場合は、前述の制約を回避できます。そのような回避方法の1つとして、PL/SQLの自律型トランザクションの使用があります。（「自律型トランザクション」を参照）。外部C関数からOracle Call Interface（OCI）を使用するという別の回避方法もあります。この場合は、OCIExtProcContext引数から使用できるハンドルを使用せず、新しい接続を作成します。
キーワードDETERMINISTICおよびPARALLEL_ENABLEを、ファンクションを宣言する構文内で使用できます。この2つのキーワードは最適化ヒントで、問合せオプティマイザおよび他のソフトウェア・コンポーネントに対して、次のファンクションについての情報を提供します。
重複して起動する必要のないファンクション
パラレル問合せまたはDML文の中で許可されているファンクション
ファンクション索引および特定のスナップショットやマテリアライズド・ビューで使用できるのは、DETERMINISTICを指定したファンクションのみです。
確定的な（DETERMINISTIC）ファンクションは、引数として渡される値のみに依存し、パッケージ変数またはデータベースの内容を参照したり変更することがなく、また、他の副作用を持ちません。このようなファンクションは、渡される引数値の組合せが同じであるかぎり、同じ戻り値を生成します。
the DETERMINISTICキーワードは、ファンクションの宣言の中で戻り値の型の後に入れます。次に例を示します。
SQL> CREATE OR REPLACE FUNCTION f1 (
2 p1 NUMBER
3 ) RETURN NUMBER DETERMINISTIC
4 IS
5 BEGIN
6 RETURN p1 * 2;
7 END;
8 /
Function created.
SQL>
このキーワードは、次の場所に挿入できます。
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 PL/SQL言語リファレンス』を参照してください。 |
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と構文的に類似しています。このキーワードは、次に示すように、ファンクション宣言の戻り値型の後に指定します。
SQL> CREATE OR REPLACE FUNCTION f1 (
2 p1 NUMBER
3 ) RETURN NUMBER PARALLEL_ENABLE
4 IS
5 BEGIN
6 RETURN p1 * 2;
7 END;
8 /
Function created.
SQL>
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をすべて削除することは不可能かつ非現実的です。別のサブプログラムS2に依存するサブプログラムS1からPRAGMA RESTRICT_REFERENCESを削除しない場合、PRAGMA RESTRICT_REFERENCESはS2でも必要になる場合があるため、S1はコンパイルします。
既存コード中の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] );
パラメータは次のとおりです。
引数はどんな順序でも渡せます。サブプログラム本体にあるSQL文のいずれかが規則に違反する場合は、その文が解析されるときにエラーが発生します。
例7-18では、ファンクションcompoundにより、データベースまたはパッケージ状態の読取りおよび書込みは行われないため、最大の純粋度レベルを宣言できます。PL/SQLコンパイラがサブプログラムを必要以上に拒否することがないように、サブプログラムで可能な最大の純粋度レベルを常に宣言してください。
例7-18 PRAGMA RESTRICT_REFERENCES
SQL> DROP TABLE accounts;
DROP TABLE accounts
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE accounts (
2 acctno INTEGER,
3 balance NUMBER
4 );
Table created.
SQL>
SQL> INSERT INTO accounts
2 VALUES (12345, 1000.00);
1 row created.
SQL>
SQL> CREATE OR REPLACE PACKAGE finance AS
2 FUNCTION compound (
3 years IN NUMBER,
4 amount IN NUMBER,
5 rate IN NUMBER
6 ) RETURN NUMBER;
7 PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS);
8 END finance;
9 /
Package created.
SQL> CREATE PACKAGE BODY finance AS
2 FUNCTION compound (
3 years IN NUMBER,
4 amount IN NUMBER,
5 rate IN NUMBER
6 ) RETURN NUMBER
7 IS
8 BEGIN
9 RETURN amount * POWER((rate / 100) + 1, years);
10 END compound;
11 -- No pragma in package body
12 END finance;
13 /
Package body created.
SQL> DECLARE
2 interest NUMBER;
3 BEGIN
4 SELECT finance.compound(5, 1000, 6)
5 INTO interest
6 FROM accounts
7 WHERE acctno = 12345;
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
内容は次のとおりです。
PRAGMA RESTRICT REFERENCESにキーワードTRUSTが含まれる場合、プラグマにリストされている制限は適用されませんが、真であると判断できます。
プラグマを使用するPL/SQLコードのセクションから、プラグマを使用しないコードのセクションにあるルーチン（Javaメソッドなど）を起動するときは、起動されるルーチンか起動するルーチンのどちらかについて、PRAGMA RESTRICT REFERENCESとTRUSTを指定します。
例7-19と例7-20では両方とも、PL/SQLファンクションfがJavaプロシージャjava_sleepを起動します。例7-19では、java_sleepがTRUST付きのWNDSになるように宣言されているためこれが可能です。例7-20では、fがTRUST付きのWNDSになるように宣言されているためこれが可能です。この場合、fはすべてのルーチンを起動できます。
例7-19 起動される側にTRUSTが指定されたPRAGMA RESTRICT REFERENCES
SQL> CREATE OR REPLACE PACKAGE p IS 2 PROCEDURE java_sleep (milli_seconds IN NUMBER) 3 AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; 4 PRAGMA RESTRICT_REFERENCES(java_sleep,WNDS,TRUST); 5 6 FUNCTION f (n NUMBER) RETURN NUMBER; 7 END p; 8 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY p IS 2 FUNCTION f ( 3 n NUMBER 4 ) RETURN NUMBER 5 IS 6 BEGIN 7 java_sleep(n); 8 END f; 9 END p; 10 / Package body created. SQL>
例7-20 起動する側にTRUSTが指定されたPRAGMA RESTRICT REFERENCES
SQL> CREATE OR REPLACE PACKAGE p IS 2 PROCEDURE java_sleep (milli_seconds IN NUMBER) 3 AS LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)'; 4 5 FUNCTION f (n NUMBER) RETURN NUMBER; 6 PRAGMA RESTRICT_REFERENCES(f,WNDS,TRUST); 7 END p; 8 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY p IS 2 FUNCTION f ( 3 n NUMBER 4 ) RETURN NUMBER 5 IS 6 BEGIN 7 java_sleep(n); 8 END f; 9 END p; 10 / Package body created. SQL>
静的なINSERT、UPDATEおよびDELETE文は、表の列などのデータベース状態を明示的に読み込まない場合は、RNDSには違反しません。ただし、動的なINSERT、UPDATEおよびDELETEの場合は、データベース状態を明示的に読み込むかどうかにかかわらず、常にRNDSに違反します。
次のINSERTは、動的に実行される場合はRNDSに違反しますが、静的に実行される場合はRNDSに違反しません。
INSERT INTO my_table values(3, 'BOB');
次のUPDATEは、my_tableの列名を明示的に読み込むため、静的に実行された場合も動的に実行された場合もRNDSに違反します。
UPDATE my_table SET id=777 WHERE name='BOB';
PL/SQLでは、パッケージ・ファンクション（スタンドアロン以外）のオーバーロードが可能です。つまり、仮パラメータの数、順序、データ型ファミリなどが異なっていれば、別のファンクションに対して同じ名前を使用できます。ただし、PRAGMA RESTRICT_REFERENCESが適用されるのは、1つのファンクション宣言（最新の宣言）のみです。
例7-21では、プラグマは、2番目のvalidの宣言に適用されます。
通常、PL/SQLパッケージでは、パッケージ内のパッケージ変数およびカーソルの数に応じて、ユーザー・グローバル領域（UGA）のメモリーが消費されます。このメモリーはユーザー数に比例して増加するため、スケーラビリティが制限されます。これを解決するには、プラグマ構文を使用して一部のパッケージにSERIALLY_REUSABLEのマークを付けます。
逐次再利用可能パッケージの場合、パッケージのグローバル・メモリーは各ユーザーのUGAではなく、小さなプールに保持され、複数の異なるユーザーのために再利用されます。したがって、このようなパッケージのグローバル・メモリーは、作業単位内でのみ使用されます。そのため、作業単位の終了時にメモリーはそのプールに解放され、（すべてのグローバル変数の初期化コードの実行後）別のユーザーによって再利用されます。
逐次再利用可能パッケージの作業単位とは、サーバーへのOCIコール、PL/SQLのクライアント・サーバー間RPCコール、PL/SQLのサーバー間RPCコールなどのサーバーへのコールです。
内容は次のとおりです。
再利用不可能パッケージ（SERIALLY_REUSABLEのマークが付いていない）の状態は、セッションの存続期間を通じて持続します。パッケージ状態には、グローバル変数やカーソルなどが含まれます。
逐次再利用可能パッケージの状態は、サーバーへのコールの存続期間中のみ持続します。サーバーへの後続のコールでは、逐次再利用パッケージが参照される場合、Oracle Databaseにより逐次再利用可能パッケージの新しいインスタンス化が作成され、すべてのグローバル変数をNULLに、または指定したデフォルト値に初期化します。サーバーへの以前のコール内の逐次再利用可能パッケージ状態に対して行われた変更は参照できません。
|
注意: サーバーへのコール時に逐次再利用可能パッケージの新しいインスタンス化が作成されても、Oracle Databaseによってメモリーが割り当てられたり、インスタンス化オブジェクトが構成されるとはかぎりません。Oracle Databaseは、SGAの前回使用されてから最も時間の経過している（LRU）プールにある、このパッケージで使用可能な（割当ておよび構成済の）インスタンス化作業領域を探します。サーバーへのコールの終了時に、この作業域はLRUプールに戻されます。SGA内にプールがあるのは、同じパッケージに対する要求を持つユーザー間で、作業域を再利用できるようにするためです。 |
再利用不可能パッケージの状態はセッションの存続期間を通じて持続するので、セッション全体のUGAメモリーがロックされます。Oracle Officeなどのアプリケーションでは、ログイン・セッションは一般に何日も持続します。アプリケーションでは、特定のパッケージがセッション中の短時間にのみ必要になることがよくあります。またこのようなアプリケーションでは、パッケージの使用後に（セッションの途中に）パッケージ状態が非インスタンス化されるのが理想的です。
SERIALLY_REUSABLEパッケージを使用すると、メモリーをより適切に管理してスケーラビリティを向上させるアプリケーションを設計できます。サーバーへのコール中にのみ管理されるパッケージ状態は、SERIALLY_REUSABLEパッケージ内で取得できます。
パッケージは、プラグマにより逐次再利用可能のマークが付けられます。プラグマの構文は、次のとおりです。
PRAGMA SERIALLY_REUSABLE;
パッケージ仕様部は、対応するパッケージ本体の有無にかかわらず、逐次再利用可能のマークを付けられます。パッケージに本体がある場合、対応する仕様部に逐次再利用可能プラグマがあると、本体にもそのプラグマが必要です。逐次再利用可能プラグマは、仕様部にそのプラグマがないかぎり、本体に含めることはできません。
SERIALLY_REUSABLEのマークが付いたパッケージには、次のプロパティがあります。
パッケージ変数は、サーバーへのコールに対応した作業境界（OCIコール境界またはサーバーへのPL/SQL RPCコールのいずれか）の中でのみ使用できます。
|
注意: アプリケーション・プログラマが、誤って前の作業単位で設定されたパッケージ変数を使用した場合、このアプリケーション・プログラムは、正常に実行されない可能性があります。PL/SQLでは、このようなケースはチェックされません。 |
パッケージ・インスタンス化のプールが保持され、作業単位にこのパッケージが必要な場合は、必ずこのインスタンス化の1つが次のように再利用されます。
パッケージ変数が再度初期化されます（たとえば、パッケージ変数がデフォルト値の場合、これらの変数は再度初期化されます）。
このパッケージ本体の初期化コードが、再実行されます。
作業終了境界で、クリーンアップが行われます。
カーソルがオープンされたままの場合、暗黙的にクローズされます。
再利用不可能なセカンダリ・メモリーの一部が解放されます（コレクション変数や長いVARCHAR2のメモリーなど）。
このパッケージ・インスタンス化は、このパッケージ用に保持された再利用可能インスタンス化のプールに戻されます。
データベース・トリガー、またはSQL文から起動される他のPL/SQLサブプログラムから、逐次再利用可能PL/SQLパッケージにアクセスすることはできません。アクセスしようとすると、Oracle Databaseによりエラーが生成されます。
例7-22で指定される2つのパッケージは、逐次再利用可能かどうかを除いて同じです。どちらのパッケージにも本体はありません。
例7-22 逐次再利用可能パッケージの仕様部
SQL> CREATE OR REPLACE PACKAGE pkg IS 2 n NUMBER := 5; 3 END pkg; 4 / Package created. SQL> CREATE OR REPLACE PACKAGE sr_pkg IS 2 PRAGMA SERIALLY_REUSABLE; 3 n NUMBER := 5; 4 END sr_pkg; 5 / Package created. SQL> BEGIN 2 pkg.n := 10; 3 sr_pkg.n := 10; 4 END; 5 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('pkg.n: ' || pkg.n); 3 DBMS_OUTPUT.PUT_LINE('sr_pkg.n: ' || sr_pkg.n); 4 END; 5 / pkg.n: 10 sr_pkg.n: 5 PL/SQL procedure successfully completed. SQL>
例7-23のパッケージには本体があります。このパッケージ仕様部は逐次利用可能であるため、パッケージ本体も逐次利用可能であることが必要です。
例7-23 逐次利用可能なパッケージ仕様部およびパッケージ本体
SQL> CREATE OR REPLACE PACKAGE sr_pkg IS 2 PRAGMA SERIALLY_REUSABLE; 3 TYPE str_table_type IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER; 4 num NUMBER := 10; 5 str VARCHAR2(200) := 'default-init-str'; 6 str_tab STR_TABLE_TYPE; 7 8 PROCEDURE print_pkg; 9 10 PROCEDURE init_and_print_pkg ( 11 n NUMBER, 12 v VARCHAR2 13 ); 14 END sr_pkg; 15 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY sr_pkg IS 2 PRAGMA SERIALLY_REUSABLE; 3 4 PROCEDURE print_pkg IS 5 num NUMBER; 6 str VARCHAR2(200); 7 str_tab STR_TABLE_TYPE; 8 BEGIN 9 DBMS_OUTPUT.PUT_LINE('num: ' || sr_pkg.num); 10 DBMS_OUTPUT.PUT_LINE('str: ' || sr_pkg.str); 11 DBMS_OUTPUT.PUT_LINE 12 ('number of table elements: ' || sr_pkg.str_tab.count); 13 FOR i IN 1..sr_pkg.str_tab.count LOOP 14 DBMS_OUTPUT.PUT_LINE(sr_pkg.str_tab(i)); 15 END LOOP; 16 END print_pkg; 17 18 PROCEDURE init_and_print_pkg ( 19 n NUMBER, 20 v VARCHAR2 21 ) IS 22 BEGIN 23 sr_pkg.num := n; 24 sr_pkg.str := v; 25 26 FOR i IN 1..n LOOP 27 sr_pkg.str_tab(i) := v || ' ' || i; 28 END LOOP; 29 30 Print_pkg; 31 END init_and_print_pkg; 32 END sr_pkg; 33 / Package body created. SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Initializing and printing package state ...'); 3 sr_pkg.init_and_print_pkg(4, 'abracadabra'); 4 DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL ...'); 5 sr_pkg.print_pkg; 6 END; 7 / Initializing and printing package state ... num: 4 str: abracadabra number of table elements: 4 abracadabra 1 abracadabra 2 abracadabra 3 abracadabra 4 Printing package state in the same CALL ... num: 4 str: abracadabra number of table elements: 4 abracadabra 1 abracadabra 2 abracadabra 3 abracadabra 4 PL/SQL procedure successfully completed. SQL>
逐次利用可能なパッケージのオープン・カーソルは、サーバー・コールが終了すると自動的にクローズされます。新しいサーバー・コールで再オープンする必要があります。例7-24に示すように、サーバー・コールはサブプログラム・コールとは異なる場合があります。
例7-24 コール境界での逐次再利用可能パッケージのオープン・カーソル
SQL> DROP TABLE people;
DROP TABLE people
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE people (name VARCHAR2(20));
Table created.
SQL> INSERT INTO people VALUES ('John Smith');
1 row created.
SQL> INSERT INTO people VALUES ('Mary Jones');
1 row created.
SQL> INSERT INTO people VALUES ('Joe Brown');
1 row created.
SQL> INSERT INTO people VALUES ('Jane White');
1 row created.
SQL> CREATE OR REPLACE PACKAGE sr_pkg IS
2 PRAGMA SERIALLY_REUSABLE;
3 CURSOR c IS SELECT name FROM people;
4 END sr_pkg;
5 /
Package created.
SQL> CREATE OR REPLACE PROCEDURE fetch_from_cursor IS
2 name VARCHAR2(200);
3 BEGIN
4 IF sr_pkg.c%ISOPEN THEN
5 DBMS_OUTPUT.PUT_LINE('Cursor is already open.');
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('Cursor is closed; opening now.');
8 OPEN sr_pkg.c;
9 END IF;
10
11 FETCH sr_pkg.c INTO name;
12 DBMS_OUTPUT.PUT_LINE('Fetched: ' || name);
13
14 FETCH sr_pkg.c INTO name;
15 DBMS_OUTPUT.PUT_LINE('Fetched: ' || name);
16 END fetch_from_cursor;
17 /
Procedure created.
SQL> -- First call to server:
SQL> BEGIN
2 fetch_from_cursor;
3 fetch_from_cursor;
4 END;
5 /
Cursor is closed; opening now.
Fetched: John Smith
Fetched: Mary Jones
Cursor is already open.
Fetched: Joe Brown
Fetched: Jane White
PL/SQL procedure successfully completed.
SQL> -- New call to server:
SQL> BEGIN
2 fetch_from_cursor;
3 fetch_from_cursor;
4 END;
5 /
Cursor is closed; opening now.
Fetched: John Smith
Fetched: Mary Jones
Cursor is already open.
Fetched: Joe Brown
Fetched: Jane White
PL/SQL procedure successfully completed.
SQL>
データ・ウェアハウス環境では、大量のデータを変換するためにPL/SQLファンクションを使用します。データは、異なるファンクションによる一連の変換を経由して渡されます。PL/SQL表ファンクションを使用すると、このような変換を、かなりのメモリー・オーバーヘッドを必要としたり、各変換の間でデータを表に格納する必要なく実行できます。これらのファンクションは、複数の行を受け入れて戻すことが可能で、行は、一度ではなく準備できた順に戻すことができます。さらにパラレル化も可能です。
|
関連項目: パイプライン・テーブル・ファンクションを使用して複数の変換を実行する方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
一連の行を分析して結果値を計算するために、次のようにSUMなどの組込み集計と同様に機能する集計関数をコード化することができます。
次のようなメンバー関数を定義するSQLオブジェクト・タイプを定義します。
ODCIAggregateInitialize
ODCIAggregateIterate
ODCIAggregateMerge
ODCIAggregateTerminate
メンバー関数をコーディングします。特に、ODCIAggregateIterate では、処理対象の各行について1回起動されると結果が蓄積されます。オブジェクト・タイプの属性を使用して中間結果を格納します。
集計関数を作成して、新しいオブジェクト・タイプと対応付けます。
SQL問合せ、DML文または組込み集計に使用する他の場所から集計関数をコールします。集計関数を起動する際は、DISTINCTやALLなどの一般的なオプションを指定できます。
|
関連項目: ユーザー定義の集計関数の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。 |
