10gリリース2（10.2）
B19248-02
 目次 |
 索引 |
| Oracle Database アプリケーション開発者ガイド-基礎編 10gリリース2（10.2） B19248-02 |
|
この章では、Oracle Databaseがアプリケーション開発用に提供するプロシージャ機能のいくつかを説明します。この章の内容は次のとおりです。
PL/SQLは、ブロック構造化プログラミング言語です。この言語が持ついくつかの機能を使用すると、高性能のデータベース・アプリケーションを容易に作成できます。たとえば、PL/SQLは、ループ文や条件文など標準SQLにはないプロシージャ構造を提供します。
PL/SQLブロック内部でSQLデータ操作言語（DML）の文を直接入力できます。また、Oracleが提供するプロシージャを使用して、データ定義言語（DDL）の文を実行できます。
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）として、またはユーザー定義の例外としてコールされるコードが含まれています。
次の無名PL/SQLブロックの例では、DBMS_OUTPUTパッケージを使用して、hr.employees表の部門20に所属するすべての従業員の名前を表示します。
DECLARE Last_name VARCHAR2(10); Cursor c1 IS SELECT last_name FROM employees WHERE department_id = 20; BEGIN OPEN c1; LOOP FETCH c1 INTO Last_name; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(Last_name); END LOOP; END; /
例外を使用すると、PL/SQLプログラム・ロジック内のOracle Databaseエラー条件を処理できます。これによって、使用中のアプリケーションで、クライアント・アプリケーションを異常終了させるようなエラーをサーバーが発行しないようにできます。次の無名ブロックは、事前定義されたOracle Database例外NO_DATA_FOUNDを処理します（この例外が処理されない場合は、ORA-01403エラーが発生します）。
DECLARE Emp_number INTEGER := 9999; Emp_name VARCHAR2(10); BEGIN SELECT Ename INTO Emp_name FROM Emp_tab WHERE Empno = Emp_number; -- no such 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;
また、独自の例外を定義してブロックの宣言部に宣言し、それをブロックの例外部分に指定できます。次に例を示します。
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 Ename INTO Emp_name FROM Emp_tab WHERE Empno = 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;
無名ブロックは、通常、SQL*Plusなどのツール製品から対話形式で使用するか、プリコンパイラ、OCIまたはSQL*Moduleアプリケーションで使用します。通常、ストアド・プロシージャをコールするか、カーソル変数をオープンするために使用します。
|
関連項目
|
ストアド・プロシージャ、ファンクションおよびパッケージは、次の特長を持つPL/SQLプログラム・ユニットです。
プロシージャまたはファンクションはデータベース内に格納されるため、名前を付ける必要があります。名前を付けることによって、他のストアド・プロシージャと区別され、アプリケーションでコールすることができます。パブリックで参照できるスキーマ内の個々のプロシージャまたはファンクションは、一意の名前を持つ必要があります。その名前は、有効なPL/SQL識別子である必要があります。
ストアド・プロシージャおよびファンクションには、パラメータを指定できます。次に、「無名ブロック」で説明されている無名ブロックに類似したストアド・プロシージャの例を示します。
PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS Emp_name VARCHAR2(10); CURSOR c1 (Depno NUMBER) IS SELECT Ename FROM Emp_tab WHERE deptno = Depno; 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;
このストアド・プロシージャの例では、部門番号が入力パラメータになっています。入力パラメータは、パラメータ化されたカーソルc1のオープン時に使用されます。
プロシージャの仮パラメータには、表7-1に示す3つの主要な属性があります。
| パラメータ属性 | 説明 |
|---|---|
|
名前 |
名前は、有効なPL/SQL識別子である必要があります。 |
|
モード |
入力のみのパラメータ（ |
|
データ型 |
パラメータのデータ型は、標準PL/SQLデータ型です。 |
パラメータ・モードは、仮パラメータの動作を定義します。3つのパラメータ・モード、IN（デフォルト）、OUTおよびIN OUTは、どのようなサブプログラムを使用する場合にも使用できます。ただし、OUTモードおよびIN OUTモードはファンクションには使用しないでください。ファンクションの目的は、引数をとらず、1つの値を戻すことです。ファンクションが複数の値を戻すようなプログラミングは、効率的ではありません。また、サブプログラムでローカルではない変数の値を変更するような副作用をファンクションが与えないようにしてください。
表7-2に、パラメータ・モードの概要を示します。
仮パラメータのデータ型は、次のいずれかで構成されています。
型属性%TYPEおよび%ROWTYPEは、パラメータを制約するために使用します。たとえば、「プロシージャおよびファンクションのパラメータ」にあるGet_emp_namesプロシージャの仕様部は、次のように作成できます。
PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)
これによって、Dept_numパラメータがEmp_tab表のDeptno列と同じデータ型を取ります。%TYPE（または%ROWTYPE）を使用した宣言を作成する場合は、列および表が使用可能である必要があります。
表の列の型が変更されてもアプリケーション・コードを変更する必要がないため、%TYPEの使用をお薦めします。
Get_emp_namesプロシージャがパッケージの一部である場合は、前に宣言したパブリック（パッケージ）変数を使用して、パラメータのデータ型を制約できます。次に例を示します。
Dept_number number(2); ... PROCEDURE Get_emp_names(Dept_num IN Dept_number%TYPE);
%ROWTYPE属性は、指定された表のすべての列を含むレコードを作成するために使用します。次の例では、Get_emp_recプロシージャを定義して、指定されたempnoに関する PL/SQLレコード内のEmp_tab表のすべての列を戻します。
PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE, Emp_ret OUT Emp_tab%ROWTYPE) IS BEGIN SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno INTO Emp_ret FROM Emp_tab WHERE Empno = Emp_number; END;
次のようにして、PL/SQLブロックからこのプロシージャをコールできます。
DECLARE Emp_row Emp_tab%ROWTYPE; -- declare a record matching a -- row in the Emp_tab table BEGIN Get_emp_rec(7499, Emp_row); -- call for Emp_tab# 7499 DBMS_OUTPUT.PUT(Emp_row.Ename || ' ' || Emp_row.Empno); DBMS_OUTPUT.PUT(' ' || Emp_row.Job || ' ' || Emp_row.Mgr); DBMS_OUTPUT.PUT(' ' || Emp_row.Hiredate || ' ' || Emp_row.Sal); DBMS_OUTPUT.PUT(' ' || Emp_row.Comm || ' '|| Emp_row.Deptno); DBMS_OUTPUT.NEW_LINE; END;
ストアド・ファンクションは、%ROWTYPEを使用して宣言される値を戻すこともできます。次に例を示します。
FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE) RETURN Emp_tab%ROWTYPE IS ...
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 ...
パラメータにデフォルト値を使用する場合は、プロシージャのコール時に実パラメータ・リストからそのパラメータを省略できます。コール時にパラメータ値を指定すると、デフォルト値がオーバーライドされます。
プロシージャまたはファンクションを作成するには、テキスト・エディタを使用します。プロシージャの先頭に、次の文を記述します。
CREATE PROCEDURE Procedure_name AS ...
たとえば、「%TYPE属性および%ROWTYPE属性」にある例を使用する場合は、次のコードを含むget_emp.sqlというテキスト（ソース）・ファイルを作成します。
CREATE PROCEDURE Get_emp_rec (Emp_number IN Emp_tab.Empno%TYPE, Emp_ret OUT Emp_tab%ROWTYPE) AS BEGIN SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno INTO Emp_ret FROM Emp_tab WHERE Empno = Emp_number; END; /
その後、SQL*Plusなどの対話形式のツール製品を使用して次の文を入力し、プロシージャを含むテキスト・ファイルをロードします。
SQL> @get_emp
プロシージャが、get_emp.sqlファイル（sqlは、デフォルトのファイル拡張子）から現行のスキーマにロードされます。コードの終わりにはスラッシュ（/）を付けてください。これはコードの一部ではありません。プロシージャのロードをアクティブにするためのものです。
ファンクションを格納するには、CREATE [OR REPLACE] FUNCTION...文を使用します。
プロシージャ・パラメータ・リストの後にキーワードISまたはASを使用できます。
スタンドアロン・プロシージャまたはファンクション、あるいはパッケージ仕様部または本体を作成するには、次の権限が必要です。
CREATE PROCEDUREシステム権限が必要です。他のユーザーのスキーマにプロシージャまたはパッケージを作成するには、CREATE ANY PROCEDUREシステム権限が必要です。プロシージャまたはパッケージの所有者の権限が変更された場合、実行前にそのプロシージャを再認証する必要があります。参照オブジェクトに必要な権限が、そのプロシージャまたはパッケージの所有者から取り消されている場合、そのプロシージャは実行できません。
プロシージャのEXECUTE権限があれば、他のユーザーが所有するプロシージャを実行できます。権限が付与されたユーザーは、そのプロシージャの所有者のセキュリティ・ドメインでプロシージャを実行します。このため、ユーザーは、プロシージャが参照するオブジェクトの権限を得る必要はありません。これによって、データベース・アプリケーションおよびそのユーザーによるさらに統制のとれた効率的なセキュリティ計画が可能になります。また、すべてのプロシージャおよびパッケージが（SYSTEM表領域内の）データ・ディクショナリに格納されます。プロシージャおよびパッケージを作成するユーザーが使用できる領域の容量は、割当て制限によっては制御されません。
ストアド・プロシージャまたはファンクションを変更するには、DROP PROCEDURE文またはDROP FUNCTION文を使用して削除（DROP）した後、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プロシージャは、データベースのアドレス空間とは別のアドレス空間で実行されます。
パッケージとは、データベース内に格納されている関連プログラム・オブジェクト（プロシージャ、ファンクション、変数、定数、カーソル、例外など）がカプセル化されたコレクションです。
パッケージは、プロシージャおよびファンクションをスタンドアロンのスキーマ・オブジェクトとして作成するかわりに使用します。パッケージは、スタンドアロンのプロシージャおよびファンクションに比べて、多数のメリットがあります。たとえば、次のことができます。
パッケージ仕様部は、パッケージの有効範囲外で参照できるパブリック型、変数、定数およびサブプログラムを宣言します。パッケージ本体は、パッケージ外のアプリケーションが参照できないプライベート・オブジェクトのみでなく、仕様部で宣言されているオブジェクトも定義します。
次に、Employee_managementというパッケージのパッケージ本体を示します。パッケージには、1つのストアド・ファンクションおよび2つのストアド・プロシージャが含まれています。このパッケージ本体は、ファンクションおよびプロシージャを定義します。
CREATE PACKAGE BODY Employee_management AS FUNCTION Hire_emp (Name VARCHAR2, Job VARCHAR2, Mgr NUMBER, Hiredate DATE, Sal NUMBER, Comm NUMBER, Deptno NUMBER) RETURN NUMBER IS New_empno NUMBER(10); -- This function accepts all arguments for the fields in -- the employee table except for the employee number. -- A value for this field is supplied by a sequence. -- The function returns the sequence number generated -- by the call to this function. BEGIN SELECT Emp_sequence.NEXTVAL INTO New_empno FROM dual; INSERT INTO Emp_tab VALUES (New_empno, Name, Job, Mgr, Hiredate, Sal, Comm, Deptno); RETURN (New_empno); END Hire_emp; PROCEDURE fire_emp(emp_id IN NUMBER) AS -- This procedure deletes the employee with an employee -- number that corresponds to the argument Emp_id. If -- no employee is found, then an exception is raised. BEGIN DELETE FROM Emp_tab WHERE Empno = 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) AS -- This procedure accepts two arguments. Emp_id is a -- number that corresponds to an employee number. -- SAL_INCR is the amount by which to increase the -- employee's salary. If employee exists, then update -- salary with increase. BEGIN UPDATE Emp_tab SET Sal = Sal + Sal_incr WHERE Empno = 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;
プロシージャ、ファンクション、トリガー、パッケージなどのPL/SQLストアド・データベース・オブジェクトのサイズは、共有プール内のDIANA（Descriptive Intermediate Attributed Notation for Ada）コードのサイズ（バイト単位）に制限されています。 フラット化されたDIANA/pcodeのサイズは、UNIXでは64KBに制限されていますが、デスクトップ・プラットフォームでは32KBに制限されている場合があります。
ユーザーがアクセスできるもので最も密接に関連する数値は、データ・ディクショナリ・ビューUSER_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: パッケージstring string stringの既存状態は廃棄されました。
2度目にセッションがこのようなパッケージ・コールを行うと、エラーは発生せずに、パッケージはセッションに対して再インスタンス化されます。
本番環境の多くでは、パッケージが無効になるDDL操作は、通常、業務時間外に行われます。したがって、エンド・ユーザー・アプリケーションでは、このような状況は問題にならない可能性もあります。しかし、パッケージが業務時間中に無効になることがよくある場合は、パッケージ・コールが行われたときにこのエラーを処理するように、アプリケーションを作成することが必要になります。
データベースの機能性を拡張できるように、またはPL/SQLでSQL機能を使用できるように、Oracle Databaseには多数のパッケージが組み込まれています。これらのパッケージをアプリケーションからコールできます。
Oracle Databaseは2つのエンジンを使用して、PL/SQLブロックおよびサブプログラムを実行します。PL/SQLエンジンは手続き型の文を実行し、SQLエンジンはSQL文を実行します。実行中は、すべてのSQL文がこの2つのエンジン間でコンテキストをスイッチングするため、パフォーマンスが低下します。
特定のブロックまたはサブプログラムの実行に必要なコンテキストのスイッチング回数を最小化すると、パフォーマンスを大幅に改善できます。バインド変数としてコレクション要素を使用するループ内でSQL文が実行される場合、ブロックが必要とする多数のコンテキストのスイッチングによってパフォーマンスが低下することがあります。コレクションには次のものが含まれます。
バインドとは、SQL文内のPL/SQL変数に対して値を代入することです。バルク・バインドとは、コレクション全体を一度にバインドすることです。バルク・バインドは1つの操作でコレクション全体を2つのエンジン間で受け渡すことができます。
通常、バルク・バインドの使用によって、4つ以上のデータベース行に影響するSQL文のパフォーマンスが改善されます。SQL文によって影響される行数が多いほど、バルク・バインドによるパフォーマンスの向上率は高くなります。
アプリケーションで次のような使用例がある場合、パフォーマンスを向上させるために、バルク・バインドの使用を検討します。
FORALLキーワードによって、コレクション文を参照するINSERT文、UPDATE文またはDELETE文のパフォーマンスを改善できます。
たとえば、次のPL/SQLブロックは、管理者のID番号が7902、7698または7839の従業員の給料を増額するものですが、バルク・バインドを使用する部分としない部分の両方があります。
DECLARE TYPE Numlist IS VARRAY (100) OF NUMBER; Id NUMLIST := NUMLIST(7902, 7698, 7839); BEGIN -- Efficient method, using a bulk bind FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY UPDATE Emp_tab SET Sal = 1.1 * Sal WHERE Mgr = Id(i); -- Slower method, running the UPDATE statements within a regular loop FOR i IN Id.FIRST..Id.LAST LOOP UPDATE Emp_tab SET Sal = 1.1 * Sal WHERE Mgr = Id(i); END LOOP; END;
PL/SQLがバルク・バインドを使用しないで、各従業員を更新するためにSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
PL/SQL表に用意された一連の行がある場合、次のようなループを使用して、データをバルク挿入またはバルク更新できます。
FORALL i in Emp_Data.FIRST..Emp_Data.LAST INSERT INTO Emp_tab VALUES(Emp_Data(i));
BULK COLLECT INTO句によって、コレクションを参照する問合せのパフォーマンスを改善できます。
たとえば、次のPL/SQLブロックは、バルク・バインドを使用して複数の値をPL/SQL表に問い合せます。
-- Find all employees whose manager's ID number is 7698. DECLARE TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; Empno VAR_TAB; Ename VAR_TAB; Counter NUMBER; CURSOR C IS SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698; BEGIN -- Efficient method, using a bulk bind SELECT Empno, Ename BULK COLLECT INTO Empno, Ename FROM Emp_Tab WHERE Mgr = 7698; -- Slower method, assigning each collection element within a loop. counter := 1; FOR rec IN C LOOP Empno(Counter) := rec.Empno; Ename(Counter) := rec.Ename; Counter := Counter + 1; END LOOP; END;
スカラー値表または%TYPE値表にBULK COLLECT INTOを使用できます。
PL/SQLがバルク・バインドを使用しないで、選択された各従業員に対してSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
BULK COLLECT INTOキーワードとともにFORALLキーワードを使用すると、コレクションを参照しDMLを戻すFORループのパフォーマンスを改善できます。
たとえば、次のPL/SQLブロックは、従業員コレクションの賞与を計算してEmp_tab表を更新し、次にBonlistという列に賞与を戻します。この操作をバルク・バインドを使用して行います。
DECLARE TYPE Emplist IS VARRAY(100) OF NUMBER; Empids EMPLIST := EMPLIST(7369, 7499, 7521, 7566, 7654, 7698); TYPE Bonlist IS TABLE OF Emp_tab.sal%TYPE; Bonlist_inst BONLIST; BEGIN Bonlist_inst := BONLIST(1,2,3,4,5); FORALL i IN Empids.FIRST..empIDs.LAST UPDATE Emp_tab SET Bonus = 0.1 * Sal WHERE Empno = Empids(i) RETURNING Sal BULK COLLECT INTO Bonlist; FOR i IN Empids.FIRST..Empids.LAST LOOP UPDATE Emp_tab Set Bonus = 0.1 * sal WHERE Empno = Empids(i) RETURNING Sal INTO BONLIST(i); END LOOP; END;
PL/SQLがバルク・バインドを使用しないで、各従業員を更新するためにSQLエンジンにSQL文を送信すると、コンテキストのスイッチングによってパフォーマンスが低下します。
トリガーは、特殊な種類の無名PL/SQLブロックです。文レベルで、または影響を受ける各行に対して、SQL文の前後で起動するようにトリガーを定義できます。INSTEAD OFトリガーまたはシステム・トリガー（DATABASEまたはSCHEMAに対するトリガー）も定義できます。
共有ライブラリにあるネイティブ・コードにPL/SQLプロシージャをコンパイルすると、PL/SQLプロシージャの処理を高速化できます。プロシージャはCコードに変換され、通常のCコンパイラでコンパイルされた後、Oracle Databaseプロセスにリンクされます。
Oracle DatabaseのPL/SQLパッケージおよびユーザーが作成したプロシージャの両方に、この方法を使用できます。ALTER SYSTEMまたはALTER SESSIONコマンドを使用するか、あるいは初期化ファイルを更新して、PLSQL_CODE_TYPEパラメータをNATIVEに設定できます（デフォルト設定はINTERPRETEDです）。
この方法では、これらのプロシージャからコールされたSQL文の処理は高速化されないため、SQL実行にあまり時間を必要としない計算集中型プロシージャに最も有効です。
Javaの場合は、ncompツールを使用して独自のパッケージおよびクラスをコンパイルできます。
PL/SQLプログラム・ユニット間の依存性は、次の2つの方法で処理できます。
PL/SQLプログラム・ユニット間の依存性を処理するためにタイムスタンプを使用する場合は、プログラム・ユニットまたは関連するスキーマ・オブジェクトを変更するたびに、すべての依存ユニットに無効のマークが付けられるため、再コンパイルしないかぎり実行できません。
各プログラム・ユニットは、そのユニットが作成または再コンパイルされるときに、サーバーによってタイムスタンプが設定されます。図7-1に、この依存性について示します。プロシージャP1およびP2は、ストアド・プロシージャP3をコールします。ストアド・プロシージャP3はT1表を参照します。この例では、各プロシージャは、いずれもT1表に依存しています。P3はT1に直接依存しますが、P1とP2は間接的に依存します。
P1およびP2がP3と同じサーバー上にある場合に、P3が変更されると、P1およびP2に無効のマークがすぐに付けられます。P1およびP2がコンパイルされた状態では、P3のタイムスタンプのレコードが含まれています。そのため、プロシージャP3が変更され再コンパイルされた場合、P3のタイムスタンプは、P1およびP2のコンパイル中にP3に対して記録された値と一致しなくなります。
P1およびP2がクライアント・システム上にある場合、または分散環境内の別のOracle Databaseインスタンス上にある場合、実行時に、タイムスタンプ情報を使用して、この2つのプロシージャに無効のマークが付けられます。
この依存性モデルのデメリットは、必要以上に制限が多いということです。ネットワークを介した依存オブジェクトは、必ずしも必要でないときにも再コンパイルされることが多いため、パフォーマンスが低下します。
さらに、クライアント側のアプリケーションがPL/SQLバージョン2を使用して作成されている場合には、クライアント側では、タイムスタンプ・モデルによってアプリケーションが実行しない状態になる可能性があります。クライアント側でPL/SQLバージョン1を使用していたOracle Formsなどの初期のリリースのツールでは、この依存性モデルを使用していませんでした。PL/SQLバージョン1がストアド・プロシージャをサポートしていなかったためです。
クライアント側のPL/SQLバージョン2と統合されたOracle Formsのリリースの場合、タイムスタンプ・モデルで問題が発生する可能性があります。たとえば、そのアプリケーションが使用するクライアント側のPL/SQLプロシージャがクライアント側で再コンパイルされないかぎり、アプリケーションはインストール中に無効にされます。また、クライアント側のプロシージャがサーバー側のプロシージャに依存しており、そのサーバー側のプロシージャが変更または自動的に再コンパイルされた場合には、クライアント側のPL/SQLプロシージャも再コンパイルする必要があります。ただし、多くのアプリケーション環境（たとえば、Formsランタイム・アプリケーション）では、クライアントで使用できるPL/SQLコンパイラはありません。このため、アプリケーションは実行できません。このような場合、クライアント・アプリケーションの開発者は、すべての顧客に対して、そのアプリケーションの新しいバージョンを再度配布する必要があります。
タイムスタンプのみの依存性モデルに関する問題のいくつかを軽減するために、Oracle Databaseではシグネチャを使用したリモート依存性という追加機能を提供します。シグネチャ機能は、リモート依存性のみに影響します。ローカル（同一サーバー）依存性には影響しません。この環境では、再コンパイルが常に可能なためです。
シグネチャは、コンパイル済の各ストアド・プログラム・ユニットと対応付けられます。シグネチャは、次の基準でユニットを識別します。
ユーザーは、シグネチャまたはタイムスタンプがリモート依存性を管理するかどうかを制御できます。
シグネチャ依存性モデルが使用されるとき、その依存ユニットに親ユニット内のサブプログラムへのコールが含まれており、このサブプログラムのシグネチャの変更により非互換が発生した場合は、リモート・プログラム・ユニットへの依存性によって、その依存ユニットは無効になります。
たとえば、ボストンにあるサーバー（BOSTON_SERVER）に格納されているプロシージャGet_emp_nameについて検討してみます。このプロシージャは、次のように定義されています。
CREATE OR REPLACE PROCEDURE get_emp_name ( emp_number IN NUMBER, hire_date OUT VARCHAR2, emp_name OUT VARCHAR2) AS BEGIN SELECT ename, to_char(hiredate, 'DD-MON-YY') INTO emp_name, hire_date FROM emp WHERE empno = emp_number; END;
Get_emp_nameがBOSTON_SERVERでコンパイルされると、そのシグネチャは、そのタイムスタンプとともに記録されます。
カリフォルニアにある別のサーバーで、PL/SQLコードがBOSTON_SERVERというDBリンクを使用してget_emp_nameを識別し、次のようにget_emp_nameをコールするとします。
CREATE OR REPLACE PROCEDURE print_ename (emp_number IN NUMBER) AS hire_date VARCHAR2(12); ename VARCHAR2(10); BEGIN get_emp_name@BOSTON_SERVER(emp_number, hire_date, ename); dbms_output.put_line(ename); dbms_output.put_line(hire_date); END;
このカリフォルニアのサーバー・コードがコンパイルされるときに、次の処理が行われます。
実行時には、変更の有無にかかわらず、カリフォルニアのサーバーからボストンのサーバーへのリモート・プロシージャのコール中に、print_enameのコンパイルされた状態で保存されていたget_emp_nameの記録済のシグネチャが、ボストンのサーバーまで送信されます。
タイムスタンプ依存性モードが有効である場合、タイムスタンプの不一致によって、コール側プロシージャにエラー状況が戻されます。
ただし、シグネチャ・モードが有効である場合は、タイムスタンプに不一致があっても無視され、カリフォルニアのサーバーのPrint_enameのコンパイル済状態にあるget_emp_nameの記録済のシグネチャが、ボストンのサーバーにあるget_emp_nameの現行のシグネチャと比較されます。2つのシグネチャが一致した場合は、コールは正常に進行します。2つのシグネチャが一致しない場合は、エラー・ステータスがprint_nameプロシージャに戻されます。
ボストンのサーバーのget_emp_nameプロシージャは、変更されている可能性もあることに注意してください。または、そのタイムスタンプは、サーバーが新しいリリースでインストールされたなどの理由で、カリフォルニアのサーバーのprint_nameプロシージャに記録されたタイムスタンプと異なっている場合があります。シグネチャ・リモート依存性モードがカリフォルニアのサーバーで有効であるかぎり、get_emp_nameがコールされたときにタイムスタンプの不一致が原因でエラーが発生することはありません。
次に、シグネチャが変更される時点について示します。
シグネチャは、あるクラスのデータ型を別のクラスに切り替えた場合に変更されます。各データ型クラスには、複数の型が存在している可能性があります。1つのクラス内でパラメータのデータ型を別の型に変更しても、シグネチャが変更されることはありません。次の一覧に示されていないNCHARやTIMESTAMPなどのデータ型は、どのクラスの一部でもありません。その型を変更すると、必ずシグネチャの不一致が発生します。
VARCHAR型: VARCHAR2、VARCHAR、STRING、LONG、ROWID
文字列型: CHARACTER、CHAR
RAW型: RAW、LONG RAW
整数型: BINARY_INTEGER、PLS_INTEGER、BOOLEAN、NATURAL、POSITIVE、POSITIVEN、NATURALN
数値型: NUMBER、INTEGER、INT、SMALLINT、DECIMAL、DEC、REAL、FLOAT、NUMERIC、DOUBLE PRECISION、DOUBLE PRECISION、NUMERIC
日付型: DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL YEAR TO MONTH、INTERVAL DAY TO SECOND
デフォルトのパラメータ・モードINの明示的指定に変更があっても、サブプログラムのシグネチャは変更されません。たとえば、次の2つを入れ換えます。
PROCEDURE P1 (Param1 NUMBER); PROCEDURE P1 (Param1 IN NUMBER);
入れ換えてもシグネチャは変更されません。これ以外のパラメータ・モードの変更が行われた場合、シグネチャは変更されます。
デフォルトのパラメータ値の指定を変更しても、シグネチャは変更されません。たとえば、プロシージャP1は、次の2つの例では同じシグネチャを持っています。
PROCEDURE P1 (Param1 IN NUMBER := 100); PROCEDURE P1 (Param1 IN NUMBER := 200);
コール側で新しいデフォルト値を取得できるようにする必要がある場合、アプリケーション開発者は、コールされたプロシージャを再コンパイルする必要がありますが、デフォルトのパラメータ値の割当てが変更されても、シグネチャに基づいて無効にされることはありません。
「プロシージャおよびファンクションのパラメータ」に定義されているGet_emp_namesプロシージャを使用します。このプロシージャ本体が次のように変更されるとします。
DECLARE Emp_number NUMBER; Hire_date DATE; BEGIN -- date format model changes SELECT Ename, To_char(Hiredate, 'DD/MON/YYYY') INTO Emp_name, Hire_date FROM Emp_tab WHERE Empno = Emp_number; END;
プロシージャ仕様部は変更されていないため、シグネチャも変更されていません。
ただし、プロシージャ仕様部が次のように変更されたとします。
CREATE OR REPLACE PROCEDURE Get_emp_name ( Emp_number IN NUMBER, Hire_date OUT DATE, Emp_name OUT VARCHAR2) AS
それに応じて本体が変更された場合、シグネチャは変更されます。これは、パラメータHire_dateが別のデータ型になっているためです。
ただし、そのパラメータの名前がWhen_hiredに変更され、データ型はVARCHAR2、モードはOUTのままの場合、シグネチャは変更されません。仮パラメータの名前が変更されても、そのユニットのシグネチャは変更されません。
次の例について検討してみます。
CREATE OR REPLACE PACKAGE Emp_package AS TYPE Emp_data_type IS RECORD ( Emp_number NUMBER, Hire_date VARCHAR2(12), Emp_name VARCHAR2(10)); PROCEDURE Get_emp_data (Emp_data IN OUT Emp_data_type); END; CREATE OR REPLACE PACKAGE BODY Emp_package AS PROCEDURE Get_emp_data (Emp_data IN OUT Emp_data_type) IS BEGIN SELECT Empno, Ename, TO_CHAR(Hiredate, 'DD/MON/YY') INTO Emp_data FROM Emp_tab WHERE Empno = Emp_data.Emp_number; END; END;
レコードのフィールド名が変更されるようにパッケージ仕様部が変更された場合、型がそのままである場合はシグネチャには影響しません。たとえば、次のパッケージ仕様部は、前のパッケージ仕様部の例と同じシグネチャを持っています。
CREATE OR REPLACE PACKAGE Emp_package AS TYPE Emp_data_type IS RECORD ( Emp_num NUMBER, -- was Emp_number Hire_dat VARCHAR2(12), -- was Hire_date Empname VARCHAR2(10)); -- was Emp_name PROCEDURE Get_emp_data (Emp_data IN OUT Emp_data_type); END;
型が以前のままである場合には、パラメータの型の名前を変更してもシグネチャは変更されません。たとえば、次のようなEmp_packageのパッケージ仕様部は、最初のものと同じです。
CREATE OR REPLACE PACKAGE Emp_package AS TYPE Emp_data_record_type IS RECORD ( Emp_number NUMBER, Hire_date VARCHAR2(12), Emp_name VARCHAR2(10)); PROCEDURE Get_emp_data (Emp_data IN OUT Emp_data_record_type); END;
タイムスタンプまたはシグネチャの依存性モデルが有効であるかどうかは、REMOTE_DEPENDENCIES_MODE動的初期化パラメータによって制御されます。
この場合は、タイムスタンプのみを使用して依存性が解決されます（動的に明示的にオーバーライドされない場合）。
この場合は、シグネチャを使用して依存性が解決されます（動的に明示的にオーバーライドされない場合）。
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = {SIGNATURE | TIMESTAMP}
Thise example alters the dependency model systemwide after startup: ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = {SIGNATURE | TIMESTAMP}
REMOTE_DEPENDENCIES_MODEパラメータが、init.oraパラメータ・ファイル内で指定されていないか、DDL文ALTER SESSIONまたはALTER SYSTEMを使用して指定されていない場合には、タイムスタンプがデフォルト値です。したがって、明示的にREMOTE_DEPENDENCIES_MODEパラメータまたは適切なDDL文を使用しないかぎり、使用中のサーバーは、タイムスタンプ依存性モデルを使用して動作します。
REMOTE_DEPENDENCIES_MODE=SIGNATUREを使用する場合は、次の点に注意してください。
REMOTE_DEPENDENCIES_MODE = TIMESTAMP（デフォルト値）の場合、プログラム・ユニット間の依存性は、実行時にタイムスタンプを比較して処理されます。コールされたリモート・プロシージャのタイムスタンプが、コールされたプロシージャのタイムスタンプと一致しない場合、コール側の（依存）ユニットは無効になり、再コンパイルする必要があります。この場合、ローカルPL/SQLコンパイラがない場合は、コール側のアプリケーションを処理できません。
タイムスタンプ依存性モードでは、シグネチャは比較されません。ローカルPL/SQLコンパイラがある場合には、コール側プロシージャが実行されると自動的に再コンパイルされます。
REMOTE_DEPENDENCIES_MODE = SIGNATUREの場合、コール側のユニット内に記録されたタイムスタンプは、まず最初に、コールされたリモート・ユニット内の現在のタイムスタンプと比較されます。2つのタイムスタンプが一致した場合は、コールが進行します。タイムスタンプが一致しなかった場合、コールされたリモート・サブプログラムのシグネチャは、コール側サブプログラムに記録されたままの状態で、コールされたサブプログラムの現行のシグネチャと比較されます。この2つのシグネチャが一致しない場合は、「シグネチャが変更される時点」の項で説明されている基準を使用した結果、コール側セッションにエラーが戻されます。
次のガイドラインに従ってREMOTE_DEPENDENCIES_MODEパラメータを設定してください。
TIMESTAMPに設定して（またはTIMESTAMPをデフォルト値にして）、タイムスタンプ依存性モードにできます。
SIGNATUREに設定する必要があります。このように設定すると、次のことができます。
カーソルは静的オブジェクトであり、カーソル変数はカーソルへのポインタです。そのため、プロシージャおよびファンクションとの間でパラメータとして受け渡すことができます。カーソル変数は、その存続期間内に別のカーソルを参照することもできます。
カーソル変数には、前述以外に次のような利点もあります。
EXECUTE権限が必要です。ただし、ユーザーには、問合せで使用される表に対するREAD権限は必要ありません。この機能は、表の列へのアクセスおよび他のストアド・プロシージャへのアクセスを制限するために使用できます。メモリーは、通常、適切なALLOCATE文を使用してクライアント・アプリケーションのカーソル変数に割り当てられます。Pro*Cでは、EXEC SQL ALLOCATE <cursor_name>文を使用します。OCIでは、カーソル・データ域を使用します。
また、1つのサーバー・セッションのみで実行するアプリケーションでも、カーソル変数を使用できます。PL/SQLサブプログラムでカーソル変数を宣言してオープンし、他のPL/SQLサブプログラムのパラメータとして使用できます。
この項には、PL/SQLでのカーソル変数の使用例がいくつか示されています。プログラム・インタフェースを使用するカーソル変数の例がさらに必要な場合は、次のマニュアルを参照してください。
次のパッケージは、PL/SQLカーソル変数型Emp_val_cv_typeおよび2つのプロシージャを定義しています。最初のプロシージャ（Open_emp_cv）は、WHERE句にバインド変数を使用してカーソル変数をオープンします。2番目のプロシージャ（Fetch_emp_data）は、カーソル変数を使用してEmp_tab表から行をフェッチします。
CREATE OR REPLACE PACKAGE Emp_data AS TYPE Emp_val_cv_type IS REF CURSOR RETURN Emp_tab%ROWTYPE; PROCEDURE Open_emp_cv (Emp_cv IN OUT Emp_val_cv_type, Dept_number IN INTEGER); PROCEDURE Fetch_emp_data (emp_cv IN Emp_val_cv_type, emp_row OUT Emp_tab%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 INTEGER) IS BEGIN OPEN emp_cv FOR SELECT * FROM Emp_tab WHERE deptno = dept_number; END open_emp_cv; PROCEDURE Fetch_emp_data (Emp_cv IN Emp_val_cv_type, Emp_row OUT Emp_tab%ROWTYPE) IS BEGIN FETCH Emp_cv INTO Emp_row; END Fetch_emp_data; END Emp_data;
次に、PL/SQLブロックからEmp_dataパッケージ・プロシージャをコールする方法を示します。
DECLARE -- declare a cursor variable Emp_curs Emp_data.Emp_val_cv_type; Dept_number Dept_tab.Deptno%TYPE; Emp_row Emp_tab%ROWTYPE; BEGIN Dept_number := 20; -- open the cursor using a variable Emp_data.Open_emp_cv(Emp_curs, Dept_number); -- fetch the data and display it LOOP Emp_data.Fetch_emp_data(Emp_curs, Emp_row); EXIT WHEN Emp_curs%NOTFOUND; DBMS_OUTPUT.PUT(Emp_row.Ename || ' '); DBMS_OUTPUT.PUT_LINE(Emp_row.Sal); END LOOP; END;
カーソル変数は、異なるカーソルを指す機能にその本質があります。次のパッケージ例では、判別子を使用して、2つの異なるカーソルのうちの1つを指すようにカーソル変数をオープンします。
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 Emp_tab WHERE Sal > 2000; ELSIF Discrim = 2 THEN OPEN Cv FOR SELECT * FROM Dept_tab; END IF; END Open_cv; END Emp_dept_data;
Open_cvプロシージャをコールしてカーソル変数をオープンし、Emp_tab表またはDept_tab表に関する問合せを指すことができます。次のPL/SQLブロックは、カーソル変数を使用してフェッチする方法、およびROWTYPE_MISMATCH事前定義例外を使用して各フェッチ・レコードを処理する方法を示しています。
DECLARE Emp_rec Emp_tab%ROWTYPE; Dept_rec Dept_tab%ROWTYPE; Cv Emp_dept_data.CV_TYPE; BEGIN Emp_dept_data.open_cv(Cv, 1); -- Open Cv For Emp_tab Fetch Fetch cv INTO Dept_rec; -- but fetch into Dept_tab record -- which raises ROWTYPE_MISMATCH DBMS_OUTPUT.PUT(Dept_rec.Deptno); DBMS_OUTPUT.PUT_LINE(' ' || Dept_rec.Loc); EXCEPTION WHEN ROWTYPE_MISMATCH THEN BEGIN DBMS_OUTPUT.PUT_LINE ('Row type mismatch, fetching Emp_tab data...'); FETCH Cv INTO Emp_rec; DBMS_OUTPUT.PUT(Emp_rec.Deptno); DBMS_OUTPUT.PUT_LINE(' ' || Emp_rec.Ename); END;
SQL*Plusを使用してPL/SQLコードを送り、そのコードにエラーがあると、コンパイル・エラーが発生したことが通知されますが、エラーの種類はすぐには識別されません。たとえば、ファイルproc1.sqlのスタンドアロン（またはストアド）・プロシージャPROC1を次のように送るとします。
SQL> @proc1
コードに1つ以上のエラーが存在すると、次のようなエラー・メッセージが戻されます。
MGR-00072: 警告: プロシージャproc1が作成されましたが、コンパイル・エラーがあります。
この場合、SQL*PlusでSHOW ERRORS文を使用し、検出されたエラーのリストを取得します。引数を持たないSHOW ERRORSは、最後のコンパイルで発生したエラーをリストします。SHOW ERRORSは、プロシージャ、ファンクション、パッケージまたはパッケージ本体の名前を使用して修飾できます。
SQL> SHOW ERRORS PROC1 SQL> SHOW ERRORS PROCEDURE PROC1
SQL*Plusを使用して従業員表のレコードを削除する簡単なプロシージャを作成します。
CREATE OR REPLACE PROCEDURE Fire_emp(Emp_id NUMBER) AS BEGIN DELETE FROM Emp_tab WHER Empno = Emp_id; END /
CREATE PROCEDURE文にエラーが2つあることに注意してください。まず、DELETE文にエラーがあります（WHEREに「E」がありません）。また、ENDの後にセミコロン（;）がありません。
CREATE PROCEDURE文が入力されエラーが戻されると、SHOW ERRORS文は次の行を戻します。
SHOW ERRORS; ERRORS FOR PROCEDURE Fire_emp: LINE/COL ERROR -------------- -------------------------------------------- 3/27 PL/SQL-00103: Encountered the symbol "EMPNO" wh. . . 5/0 PL/SQL-00103: Encountered the symbol "END" when . . . 2 rows selected.
SHOW ERRORS文によって、エラーが発生した行および列の番号がそれぞれ表示されます。
他のツールまたはアプリケーションを使用している場合は、次のデータ・ディクショナリ・ビューを使用してエラーを表示できます。
プロシージャのコンパイルに関するエラー・メッセージは、プロシージャを置き換えると更新され、プロシージャを削除すると削除されます。
ALL_SOURCE、USER_SOURCE、DBA_SOURCEの各ビューを使用すると、データ・ディクショナリから元のソース・コードを取得できます。
Oracle Databaseでは、ユーザー定義エラーの番号およびメッセージがクライアント・アプリケーションに戻されるようにPL/SQLコード内のユーザー定義エラーを処理できます。クライアント・アプリケーションでは、Oracle Databaseが戻したユーザー定義エラーの番号およびメッセージに基づいて、エラーを処理します。
ユーザー定義エラーのメッセージは、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-2では、次のことが示されています。
ユーザー定義例外は、プロシージャ本体またはパッケージ本体で宣言するか（プライベート例外）、パッケージ仕様部で宣言します（パブリック例外）。例外ハンドラは、プロシージャ本体（スタンドアロンまたはパッケージ）に定義します。
データベースのPL/SQLプログラム・ユニットでは、適切な例外ハンドラによって検出されない未処理のユーザー・エラー条件または内部エラー条件が原因で、プログラム・ユニットの暗黙的なロールバックが発生します。プログラム・ユニットで未処理例外がある場所の前にCOMMIT文が含まれている場合、そのプログラム・ユニットの暗黙的なロールバックは直前のCOMMITまで実行されます。
さらに、データベースに格納されたPL/SQLのプログラム・ユニットの未処理例外は、プログラム・ユニットをコールするクライアント側のアプリケーションに渡されます。このアプリケーションでは、その例外はデータベースにSQL文として送られるため、アプリケーション・プログラム・ユニット・コールのみがロールバックされます（アプリケーション・プログラム・ユニット全体ではありません）。
データベースのPL/SQLプログラム・ユニット内の未処理例外がデータベース・アプリケーションに戻される場合は、例外を処理するためにデータベースのPL/SQLコードを変更する必要があります。アプリケーションで、データベース・プログラム・ユニットをコールしたときに未処理例外を検出し、それらのエラーを処理できます。
分散問合せは、トリガーまたはストアド・プロシージャを使用して作成できます。この分散問合せは、ローカルのOracle Databaseインスタンスによって、対応する数のリモート問合せに分解されてリモート・ノードに送られます。リモート・ノードはその問合せを実行し、ローカル・ノードにその結果を送ります。その後、ローカル・ノードは必要な後処理を行い、ユーザーまたはアプリケーションに結果を戻します。
たとえば、整合性制約違反のために分散問合せ文の一部でエラーが発生すると、Oracle Databaseはエラー番号ORA-02055を戻します。後続する文またはプロシージャ・コールは、ロールバック、またはセーブポイントまでのロールバックが入力されるまで、エラー番号ORA-02067を戻します。
分散更新の一部でエラーが発生したことを示すエラー・メッセージがチェックされるように、アプリケーションを設計してください。エラーを検出した場合、アプリケーションが処理を継続する前に、トランザクション全体をロールバック（またはセーブポイントまでロールバック）してください。
プロシージャがローカルまたはリモートで実行される場合、次の4種類の例外が発生する可能性があります。
EXCEPTIONを使用した宣言が必要なPL/SQLのユーザー定義例外
NO_DATA_FOUNDなどのPL/SQL事前定義例外
ORA-00900やORA-02015などの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を戻すため、これらの例外は処理できません。その他すべてのリモート例外は、ローカル例外と同じ方法で処理できます。
ストアド・プロシージャのコンパイルにはコードの構文エラーの修正が含まれます。プロシージャが正常に実行され、エラーが修正されていることを確認するには、追加のデバックを行う必要があります。次のようなデバッグが考えられます。
Oracle JDeveloperの最新のリリースには、PL/SQL、Javaおよびマルチ言語プログラムをデバッグする広範な機能が含まれています。各種Oracle製品の一部としてOracle JDeveloperを取得できます。通常、最新のリリースはhttp://otn.oracle.co.jp/からダウンロードできます。
Oracle Procedure Builderは、データベース・アプリケーションを透過的にデバッグする高度なクライアント/サーバー・デバッガです。Oracle Procedure Builderを使用すると、制御されたデバッグ環境でPL/SQLプロシージャおよびトリガーを実行し、ブレークポイントの設定、変数の値のリスト、その他のデバッグ作業を実行できます。Oracle Procedure Builderは、Oracle Developerセットの一部です。デバッグ情報を出力する場合に有効なTEXT_IOパッケージも提供されています。
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システム権限を所有するユーザーが接続操作を実行する必要があります。接続コールに関係する定義者権限ルーチンの所有者であるユーザーも、この操作を実行できます。
デバッガがセッションに接続されると、セッション・ログイン・ユーザーおよび現在有効なセッションレベルのロールが、このデバッグ用の接続の権限環境として決定されます。デバッグに必要なすべてのDEBUG権限またはEXECUTE権限を、このユーザーとロールの組合せに付与する必要があります。
EXECUTE権限またはDEBUG権限をデバッグ用の接続に付与する必要があります。
DEBUG権限を付与する必要があります。このような権限要件に加えて、コードの各行で停止する機能とデバッガからの変数へのアクセスを実行できるのは、生成されたデバッグ情報とともにコンパイルされているコード内のみという制限があります。ALTER PACKAGEなどのコマンドでPLSQL_DEBUGパラメータおよびDEBUGキーワードを使用し、PL/SQLコンパイラで結果にデバッグ情報を含めるかどうかを制御できます。デバッグ情報を含めない場合、変数にはアクセスできず、コードの各行でステップ実行やブレークポイントを指定することもできません。PL/SQLのwrapユーティリティを使用してあいまいにしているコードでは、PL/SQLコンパイラによってデバッグ情報は生成されません。
DEBUG ANY PROCEDUREシステム権限の効力は、データベース内のすべてのオブジェクトにDEBUG権限を付与したときの効力と同じです。O7_DICTIONARY_ACCESSIBILITYパラメータの値がTRUEである場合は、SYSが所有しているオブジェクトにも付与します。
デバッグ・ロール・メカニズムを使用すると、セッションでは通常使用できない、デバッグ用の権限を保持できます。デバッグ・ロールおよび必要な関連パスワードの指定方法の詳細は、マニュアルでDBMS_DEBUGパッケージおよびDBMS_DEBUG_JDWPパッケージについての説明を参照してください。
JAVADEBUGPRIVロールは、DEBUG CONNECT SESSION権限およびDEBUG ANY PROCEDURE権限を保持します。必要な場合にのみ、これらの権限をこのロールに付与してください。
実際にデバッガの一部のコードを書き込む場合は、DBMS_DEBUG_JDWPやDBMS_DEBUGなどのパッケージを使用する必要があります。
Oracle9iリリース2から提供されているDBMS_DEBUG_JDWPパッケージは、将来DBMS_DEBUGパッケージと置き換わるマルチ言語デバッグ用のフレームワークを提供します。PL/SQLとJavaの組合せのプログラムには特に有効です。
Oracle8iから提供されているDBMS_DEBUGパッケージでは、サーバー側のデバッガが実装されていて、サーバー側のPL/SQLプログラム・ユニットをデバッグする方法を提供します。Oracle Procedure Builderやその他の様々なサード・パーティ・ベンダーが提供するソリューションなど、デバッガのいくつかでは、このAPIが使用されています。
プロシージャは、次のように様々な環境から起動できます。次に例を示します。
LENGTHまたはROUNDなどの組込みSQL関数のコールと同様の方法でSQL文からコールできます。
この項では、これらの環境からのプロシージャの起動に関して一般的な例をいくつか紹介します。
プロシージャまたはトリガーによって、別のストアド・プロシージャをコールできます。たとえば、プロシージャ本体に次の行を組み込むことができます。
. . . Sal_raise(Emp_id, 200); . . .
この行がSal_raiseプロシージャをコールします。Emp_idは、このプロシージャのコンテキスト内の変数です。PL/SQL内では再帰プロシージャ・コールが可能なため、プロシージャがプロシージャ自身をコールできます。
プロシージャは、SQL*Plusなど、Oracle Databaseのツール製品から対話形式でコールできます。たとえば、自分が所有しているSAL_RAISEというプロシージャをコールするには、次のように無名PL/SQLブロックを使用できます。
BEGIN Sal_raise(7369, 200); END;
SQL*PlusのEXECUTE文を使用すると、ブロックをより簡単に実行できます。これによって、コードに入力するBEGIN文およびEND文がラップされます。次に例を示します。
EXECUTE Sal_raise(7369, 200);
対話形式のツール製品を使用すると、セッション変数を作成できます。SQL*Plusを使用している場合、次の文によってセッション変数が作成されます。
VARIABLE Assigned_empno NUMBER
一度定義したセッション変数は、そのセッション中にのみ有効です。たとえば、あるファンクションを実行して、その戻り値をセッション変数に格納できます。
EXECUTE :Assigned_empno := Hire_emp('JSMITH', 'President', 1032, SYSDATE, 5000, NULL, 10); PRINT Assigned_empno; ASSIGNED_EMPNO -------------- 2893
プリコンパイラやOCIアプリケーションなどの3GLデータベース・アプリケーションでは、プロシージャへのコールをそのアプリケーションのコード内に記述できます。
アプリケーションのPL/SQLブロック内のプロシージャを実行するには、単にそのプロシージャをコールします。次のPL/SQLブロックでは、Fire_empプロシージャをコールします。
Fire_emp1(:Empnun);
この場合、:Empnoは、アプリケーションのコンテキスト内のホスト（バインド）変数です。
プリコンパイラ・アプリケーションからプロシージャを実行するには、EXECコール・インタフェースを使用する必要があります。たとえば、次の文は、プリコンパイラ・アプリケーションのコード内でFire_empプロシージャをコールします。
EXEC SQL EXECUTE BEGIN Fire_emp1(:Empnum); END; END-EXEC;
プロシージャおよびパッケージに対する参照は、『Oracle Database管理者ガイド』の第13章「スキーマ・オブジェクトの一般的な管理」、「オブジェクトの名前解決の管理」の項で説明されているアルゴリズムに従って変換されます。
スタンドアロン・プロシージャまたはパッケージを所有している場合は、前の項で説明したように、スタンドアロン・プロシージャまたはパッケージ・プロシージャ、あるいはパブリック・プロシージャまたはパッケージ・プロシージャをいつでも実行できます。他のユーザーが所有するスタンドアロン・プロシージャまたはパッケージ・プロシージャを実行するには、次の条件を満たす必要があります。
EXECUTE権限、またはEXECUTE ANY PROCEDUREシステム権限が必要です。リモート・プロシージャを実行する場合は、EXECUTE権限またはEXECUTE ANY PROCEDUREシステム権限が、ロールを介してではなく直接付与されている必要があります。
EXECUTE Jward.Fire_emp (1043); EXECUTE Jward.Hire_fire.Fire_emp (1043);
プロシージャをコールするときには、そのプロシージャの引数に対してそれぞれ値またはパラメータを指定します。引数の値は次のどちらか、または両方を組み合せて指定します。
たとえば、次の文はどちらもSal_raiseプロシージャをコールして、従業員番号が7369の社員の給与を500増額します。
Sal_raise(7369, 500); Sal_raise(Sal_incr=>500, Emp_id=>7369); Sal_raise(7369, Sal_incr=>500);
最初の文ではプロシージャ仕様部で宣言されている順序で、引数の値を指定します。
2番目の文では引数の値を名前で指定し、プロシージャ仕様部で宣言されている順序とは異なる順序で指定します。引数の名前を指定する場合、引数は任意の順序で指定できます。
3番目の文では、前述の2つの方法を組み合せて引数の値を指定します。引数の名前と順序を組み合せて指定する場合は、順序で指定する値が名前で指定する値よりも前にある必要があります。
DEFAULTオプションを使用して、サブプログラムに対するINパラメータのデフォルト値を定義した場合（『PL/SQLユーザーズ・ガイドおよびリファレンス』を参照）は、異なる数のパラメータをサブプログラムに渡し、デフォルト値をそのまま使用するか、またはオーバーライドします。実際の値が渡されない場合は、対応するデフォルト値が使用されます。指定を省略する引数（対応するデフォルト値を使用する引数）の後の引数に値を割り当てる場合は、その値のみでなく引数の名前も明示的に指定する必要があります。
リモート・プロシージャは、適切なデータベース・リンクおよびプロシージャの名前を使用してコールします。次のSQL*Plus文は、BOSTON_SERVERというローカル・データベース・リンクが示すデータベース内にあるプロシージャFire_empを実行します。
EXECUTE fire_emp1@boston_server(1043);
デフォルト値がある場合でも、すべてのリモート・プロシージャのパラメータに対して値を明示的に渡す必要があります。リモート・パッケージ変数および定数にはアクセスできません。
リモート・オブジェクトは、ローカルで定義されているプロシージャ本体で参照できます。次のプロシージャでは、リモートの従業員表の行を削除します。
CREATE OR REPLACE PROCEDURE fire_emp(emp_id NUMBER) IS BEGIN DELETE FROM emp@boston_server WHERE empno = emp_id; END;
リモート・プロシージャをコールする方法を、コール側の環境別に示します。
CREATE OR REPLACE PROCEDURE local_procedure(arg IN NUMBER) AS BEGIN fire_emp1@boston_server(arg); END;
FIRE_EMP1@BOSTON_SERVERにシノニムを作成できます。これによって、プロシージャ、OCIアプリケーションまたはプリコンパイラ・アプリケーションのみでなく、SQL*Formsアプリケーションなど、Oracle Databaseのツール製品のアプリケーションからリモート・プロシージャをコールできるようになります。
CREATE SYNONYM synonym1 for fire_emp1@boston_server; CREATE OR REPLACE PROCEDURE local_procedure(arg IN NUMBER) AS BEGIN synonym1(arg); END;
DECLARE arg NUMBER; BEGIN local_procedure(arg); END;
ここで、local_procedureはこのリストの最初の項目と同じ定義です。
リモート・プロシージャのコールは、更新処理を前提とします。このためこのような参照でも、常に2フェーズ・コミットのトランザクションが必要です（リモート・プロシージャが読取り専用の場合を含む）。また、リモート・プロシージャを含むトランザクションをロールバックする場合、リモート・プロシージャのコールによって実行された処理も同時にロールバックされます。
リモート・プロシージャでは、ローカル・プロシーシャと同じCOMMIT、ROLLBACK、SAVEPOINT文を実行できます。ただし、次のように、動作に少し違いがあります。
分散更新ではデータは複数のデータベースで更新されます。異なるデータベースのデータにアクセスする複数のリモート更新を含むプロシージャを使用できます。構文内の文はリモート・データベースに送信され、構文の実行はユニット単位で正常終了または異常終了します。分散更新の一部がエラーとなり、一部が正常終了した場合、処理を続けるには（トランザクション全体またはセーブポイントまでの）ロールバックが必要です。分散更新を実行するプロシージャを作成する場合は、この点を考慮する必要があります。
リモート・プロシージャのコールにローカル・プロシージャを使用する場合は、特に注意が必要です。ローカル・プロシージャの実行中にタイムスタンプの不一致が見つかると、リモート・プロシージャは実行されず、そのローカル・プロシージャは無効となります。
スタンドアロン・プロシージャおよびパッケージのシノニムは、次の目的で作成します。
権限が付与されているユーザーがプロシージャをコールする場合、対応するシノニムを使用できます。パッケージ内に定義されているプロシージャは個々のオブジェクトではないため（パッケージはオブジェクトです）、パッケージ内の個々のプロシージャのシノニムは作成できません。
ユーザー作成のPL/SQLファンクションをSQL式に組み込むことができます（ただし、PL/SQLリリース2.1以上が必要です）。SQL文でPL/SQLファンクションを使用すると、次のことができます。
WHERE句にファンクションを指定すると、条件を使用してデータをフィルタできます。ファンクションを使用できない場合は、アプリケーションで評価する必要があります。
PL/SQLファンクションは、SQL文内で使用される前に、トップレベルのファンクションとして作成するか、またはパッケージ仕様部内部で宣言する必要があります。ストアドPL/SQLファンクションは、組込みOracleファンクション（SUBSTR、ABSなど）と同じ方法で使用できます。
PL/SQLファンクションは、SQL文内でOracleファンクションを入れることができる場所、またはSQL内で式を入れることができる場所であればどこにでも入れることができます。たとえば、PL/SQLファンクションは、次の場所からコールできます。
SELECT構文のリスト
WHERE句およびHAVING句の条件
CONNECT BY句、START WITH句、ORDER BY句およびGROUP BY句
INSERT文のVALUES句
UPDATE文のSET句
CREATE文またはALTER TABLE文のCHECK制約句からPL/SQLストアド・ファンクションをコールしたり、PL/SQLストアド・ファンクションを使用して列のデフォルト値を指定することはできません。このような状況では、不変の定義が必要になるためです。
SQLからPL/SQLファンクションを参照するには、次の構文を使用します。
[[schema.]package.]function_name[@dblink][(param_1...param_n)]
たとえば、Scottスキーマ内のMy_funcs_pkgパッケージに作成した、2つの数値パラメータをとるMy_funcという名前のファンクションを参照するには、次のようにコールします。
SELECT Scott.My_funcs_pkg.My_func(10,20) FROM dual;
オプションのスキーマ名またはパッケージ名のどちらか1つが指定されている場合、最初の識別子は、スキーマ名またはパッケージ名のいずれかです。たとえば、リファレンスPayroll.Tax_rateのPayrollがスキーマ名かパッケージ名かを判断するために、Oracle Databaseは次の処理を行います。
Payrollパッケージを確認します。
PAYROLLパッケージが見つかった場合、Oracle DatabaseはPayrollパッケージ内でTax_rateファンクションを探します。Payrollパッケージ内にTax_rateファンクションが見つからない場合は、エラー・メッセージが戻されます。
Payrollパッケージが見つからない場合、Oracle DatabaseはトップレベルのTax_rateファンクションを含むPayrollというスキーマを探します。Payrollスキーマ内にTax_rateファンクションが見つからない場合は、エラー・メッセージが戻されます。
トップレベルのストアド・ファンクションに対して定義したシノニムを使用して、そのファンクションを参照することもできます。
SQL文では、データベースの列の名前は、パラメータなしのファンクションの名前より優先されます。たとえば、スキーマScottは、次の2つのオブジェクトを作成します。
CREATE TABLE Emp_tab(New_sal NUMBER ...); CREATE FUNCTION New_sal RETURN NUMBER IS ...;
その後、次の2つの文では、New_salへの参照は、列Emp_tab.New_salを参照します。
SELECT New_sal FROM Emp_tab; SELECT Emp_tab.New_sal FROM Emp_tab;
new_salファンクションにアクセスするには、次のように入力します。
SELECT Scott.New_sal FROM Emp_tab;
スキーマScottからPL/SQLファンクションTax_rateをコールし、このファンクションをTax_table内のSs_no列およびsal列に対して実行し、その結果を変数Income_taxに入れるには、次のように指定します。
DECLARE Tax_id NUMBER; Income_tax NUMBER; BEGIN SELECT scott.tax_rate (Ss_no, Sal) INTO Income_tax FROM Tax_table WHERE Ss_no = Tax_id; END;
これらのPL/SQLファンクションのコール例は、SQL式で使用できます。
Circle_area(Radius) Payroll.Tax_rate(Empno) scott.Payroll.Tax_rate@boston_server(Dependents, Empno)
任意の数の引数を1つのファンクションに渡すには、引数をカッコに入れます。この場合、位置表記法を使用する必要があります。名前表記法は、サポートされていません。引数の不要なファンクションの場合は、カッコを使用します。
ストアド・ファンクションGross_payでは、次のようにDEFAULT句を使用してその仮パラメータの2つをデフォルト値に初期化します。次に例を示します。
CREATE OR REPLACE FUNCTION Gross_pay (Emp_id IN NUMBER, St_hrs IN NUMBER DEFAULT 40, Ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS ...
手続き型の文からGross_payをコールする場合は、常にSt_hrsのデフォルト値を受け入れることができます。これは、パラメータをスキップする名前表記法を使用できるためです。次に例を示します。
IF Gross_pay(Eenum, Ot_hrs => Otime) > Pay_limit THEN ...
ただし、SQL式からGross_payをコールする場合、Ot_hrsのデフォルト値を受け入れないかぎり、St_hrsのデフォルト値を受け入れることはできません。これは、名前表記法を使用できないためです。
SQLからPL/SQLファンクションをコールするには、その所有者であるか、またはそのファンクションに対するEXECUTE権限が必要です。PL/SQLファンクションを使用して定義されているビューから選択するには、そのビューに対するSELECT権限が必要です。そのビューからの選択には、別のEXECUTE権限は必要ありません。
SQL式からコールできるようにするには、ユーザー定義のPL/SQLファンクションが次の基本的な要件を満たす必要があります。
INパラメータである必要があります。どの仮パラメータも、OUTまたはIN OUTパラメータにすることはできません。
CHAR、DATE、NUMBERなどのOracleの組込み型である必要があります。BOOLEAN、RECORD、TABLEなどのPL/SQL型にはできません。
たとえば、次のストアド・ファンクションは、これらの基本的な要件を満たしています。
CREATE FUNCTION Gross_pay (Emp_id IN NUMBER, St_hrs IN NUMBER DEFAULT 40, Ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS 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;
ストアド・サブプログラム（ファンクションまたはプロシージャ）の純粋度とは、データベース表またはパッケージ変数に対してそのサブプログラムが及ぼす副作用のことをいいます。副作用によって、問合せのパラレル処理が妨害されたり、処理順序に依存する（したがって、不確定な）結果が発生したり、ユーザー・セッションにまたがったパッケージ状態のメンテナンスが必要になります。サブプログラムがSQL問合せまたはDML文からコールされる場合は、様々な副作用は受け入れられません。
Oracle8iより前のリリースでは、Oracle DatabaseはPL/SQLコンパイラを使用して、ストアド・サブプログラムまたはSQL文のコンパイル中に制限を施行していました。Oracle8iからは、コンパイル時の制限は緩和され、実行中の制限も少なくなっています。
この変更によって、PL/SQL、JavaおよびCで作成されたストアド・サブプログラムが統一してサポートされ、プログラマには最大限の柔軟性が提供されています。
SQL文が実行されるとき、すでに実行中のSQL文の中にこのSQL文が論理的に埋め込まれているかどうかが確認されます。文がトリガーまたはすでに実行中のSQL文からコールされたサブプログラムから実行されると、この確認が行われます。このような場合は、新しいSQL文が特定のコンテキスト内で安全かどうかを判断するために、さらに確認が行われます。
サブプログラムには次の制限が適用されます。
ALTER）を実行できません。
SELECT）文またはパラレル化されたDML文からコールされるサブプログラムは、DML文を実行できません。またはデータベースを変更できません。
前述のすべての制限は、サブプログラムまたはトリガー内でSQL文が実行される方法にかかわらず適用されます。次に例を示します。
EXECUTE IMMEDIATE）を使用して実行されるか、またはDBMS_SQLパッケージを使用して実行される場合、そのSQL文に適用されます。
新しいSQL文の実行が、すでに実行中の文のコンテキストに論理的に埋め込まれていない場合は、前述の制約を回避できます。PL/SQLの自律型トランザクションが1つの回避方法を提供します（「自律型トランザクション」を参照）。外部C関数からOracle Call Interface（OCI）を使用するという別の回避方法もあります。この場合は、OCIExtProcContext引数から使用できるハンドルを使用せず、新しい接続を作成します。
キーワードDETERMINISTICおよびPARALLEL_ENABLEを、ファンクションを宣言する構文内で使用できます。この2つのキーワードは最適化ヒントで、問合せオプティマイザおよび他のソフトウェア・コンポーネントに対して、次のファンクションについての情報を提供します。
ファンクション索引および特定のスナップショットやマテリアライズド・ビューで使用できるのは、DETERMINISTICを指定したファンクションのみです。
確定的な（DETERMINISTIC）ファンクションは、引数として渡される値のみに依存し、パッケージ変数またはデータベースの内容を参照したり変更することがなく、また、他の副作用を持ちません。このようなファンクションは、渡される引数値の組合せが同じであるかぎり、同じ戻り値を生成します。
the DETERMINISTICキーワードは、ファンクションの宣言の中で戻り値の型の後に入れます。次に例を示します。
CREATE FUNCTION F1 (P1 NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN RETURN P1 * 2; END;
このキーワードは、次の場所に挿入できます。
CREATE PACKAGE BODYまたはCREATE TYPE BODY文のファンクションまたはメソッドには、キーワードは置けません。
DETERMINISTICとマークされているファンクションに対するコールでは、他にアクションを実行しなくても、パフォーマンスがある程度最適化されます。次の機能では、その機能で使用されるすべてのファンクションをDETERMINISTICとして宣言する必要があります。
ENABLE QUERY REWRITEとしてマークされるマテリアライズド・ビューで使用されるファンクション
これらの機能では、ファンクションをコールするのではなく、事前に計算されている結果をできるだけ使用しようとします。
次のカテゴリのファンクションは、通常DETERMINISTICです。
WHERE句、ORDER BY句またはGROUP BY句の中で使用されるファンクション
MAPメソッドまたはORDERメソッドであるファンクション
Oracle Databaseでは、既存のアプリケーションを壊すことなく前述のカテゴリのファンクションを明示的にDETERMINISTICと宣言するように要求することはできませんが、このキーワードを使用することはアプリケーションのスタイルとして賢明な選択といえます。
DETERMINISTICを作成する場合は、次の点に注意してください。
DETERMINISTICキーワードが適用されると、そのファンクションが関係する問合せの結果は予測できません。
DETERMINISTICファンクションのセマンティックを変更し、再コンパイルすると、既存のファンクション索引およびマテリアライズド・ビューにより、旧バージョンのファンクションの結果がレポートされます。このように、ファンクションのセマンティックを変更する場合、依存するファンクション索引およびマテリアライズド・ビューを手動で作成する必要があります。Oracle Databaseのパラレル実行機能により、SQL文の実行作業が複数のプロセスにわたって分割されます。パラレルで実行されるSQL文からコールされるファンクションは、各プロセス内で実行される個別のコピーを持つことができ、それぞれのコピーは、そのプロセスによって処理される行のサブセットのためにのみコールされます。
各プロセスには、そのプロセス専用のパッケージ変数のコピーがあります。パラレル実行が開始されると、コピーされたパッケージ変数は、新しいユーザーがシステムにログインするときのように、パッケージ仕様部および本体の情報に基づいて初期化されます。パッケージ変数内の値は、元のログイン・セッションからはコピーされません。パッケージ変数に対する変更は、多数のセッション間で伝播したり、元のセッションに伝播することはありません。JavaのSTATICクラス属性は、同様に、各プロセス内で独立して初期化され変更されます。ファンクションでは、検出される様々な行の値をパッケージ（またはJavaのSTATIC）変数を使用して蓄積できるため、Oracle Databaseでは、すべてのユーザー定義ファンクションの実行をパラレル化しても安全であるとはいえません。
検索（SELECT）文の場合、8.1.5より前のリリースのOracle Databaseでは、ファンクションがPRAGMA RESTRICT_REFERENCES宣言内でRNPSおよびWNPSとして記されているかどうかは、パラレル問合せ最適化機能により調べられていました。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 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バージョンでは、プログラマはサブプログラムの純粋度レベルの宣言にプラグマRESTRICT_REFERENCESを使用していました。それ以降のバージョンでは、かわりにヒントparallel-enableおよびdeterministicを使用して、サブプログラムの純粋度についてOracle Databaseと通信します。
コードからRESTRICT_REFERENCESを削除できます。ただし、このプラグマは次のような下位互換性が必要となる場合に備えて残されています。
RESTRICT_REFERENCESをすべて削除することは不可能かつ非現実的です。別のサブプログラムS2に依存するサブプログラムS1からRESTRICT_REFERENCESを削除しない場合、RESTRICT_REFERENCESはS2でも必要になる場合があるため、S1はコンパイルします。
RESTRICT_REFERENCESをヒントparallel-enableおよびdeterministicに置き換えると、新しい依存コードの動作に悪影響を与える可能性があります。既存コードの動作を保持するために、RESTRICT_REFERENCESを使用してください。
既存のPL/SQLアプリケーションでは、既存コードとの統合を容易にするために、新しいファンクションに対してもプラグマを引き続き使用することができます。まったく新しいアプリケーションではプラグマは使用しないでください。
プラグマRESTRICT_REFERENCESを使用する場合は、パッケージ本体ではなく、パッケージ仕様部に含めます。このプラグマは、サブプログラム（ファンクションまたはプロシージャ）の宣言の後に置く必要がありますが、直後に置く必要はありません。所定のサブプログラム宣言を参照できるプラグマは、1つのみです。
プラグマRESTRICT_REFERENCESをコーディングするには、次の構文を使用します。
PRAGMA RESTRICT_REFERENCES ( Function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] );
パラメータは次のとおりです。
引数はどんな順序でも渡せます。サブプログラム本体にあるSQL文のいずれかが規則に違反する場合は、その文が解析されるときにエラーが発生します。
次の例では、ファンクションcompoundにより、データベースまたはパッケージ状態の読取りおよび書込みは行われないため、最大の純粋度レベルを宣言できます。サブプログラムで可能な最高の純粋度レベルを常に保つようにしてください。これによって、PL/SQLコンパイラがサブプログラムを必要以上に拒否することはなくなります。
CREATE PACKAGE Finance AS -- package specification 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 --package body 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;
後で、次のようにPL/SQLブロックからcompoundをコールできます。
DECLARE Interest NUMBER; Acct_id NUMBER; BEGIN SELECT Finance.Compound(Yrs, Amt, Rte) -- function call INTO Interest FROM Accounts WHERE Acctno = Acct_id;
キーワードTRUSTをRESTRICT_REFERENCES構文で使用すると、RESTRICT_REFERENCES宣言を持つファンクションからこの宣言を持たないファンクションへのコールが容易になります。TRUSTが指定されていると、プラグマにリストされている制限は実際には適用されませんが、真であると判断できます。
プラグマを使用するコード・セクションからプラグマを使用しないコード・セクションをコールするときは、2種類の使用スタイルがあります。1つは、コールされるルーチン上にプラグマを入れるスタイルです。たとえば、Javaメソッド用のコール仕様上に入れます。これで、PL/SQLからこのメソッドをコールした場合、メソッドの制約がコール側のサブプログラムの制約より少ない場合、コールでエラーが発生します。次に例を示します。
CREATE OR REPLACE PACKAGE P1 IS FUNCTION F1 (P1 NUMBER) RETURN NUMBER IS LANGUAGE JAVA NAME 'CLASS1.METHODNAME(int) return int'; PRAGMA RESTRICT_REFERENCES(F1,WNDS,TRUST); FUNCTION F2 (P1 NUMBER) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES(F2,WNDS); END; CREATE OR REPLACE PACKAGE BODY P1 IS FUNCTION F2 (P1 NUMBER) RETURN NUMBER IS BEGIN RETURN F1(P1); END; END;
ここで、F1はWNDSとして宣言されているため、F2がF1をコールできます。
もう1つのスタイルは、コール側のみをマークする方法です。マークされたコール側は、エラーなしで任意のサブプログラムをコールできます。次に例を示します。
CREATE OR REPLACE PACKAGE P1a IS FUNCTION F1 (P1 NUMBER) RETURN NUMBER IS LANGUAGE JAVA NAME 'CLASS1.METHODNAME(int) return int'; FUNCTION F2 (P1 NUMBER) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES(F2,WNDS,TRUST); END; CREATE OR REPLACE PACKAGE BODY P1a IS FUNCTION F2 (P1 NUMBER) RETURN NUMBER IS BEGIN RETURN F1(P1); END; END;
ここでは、F2がF1をコールできます。これは、F2はWNDSと指定されています（TRUSTが指定されているため）が、F2の本体がWNDS制約を本当に満たすかどうかが実際に調べられていないためです。F2が調べられないため、F1にはPRAGMA RESTRICT_REFERENCESが指定されていないにもかかわらず、F2からF1にコールできます。
静的なINSERT、UPDATEおよびDELETE文は、表の列などのデータベース状態を明示的に読み込まない場合は、RNDSには違反しません。ただし、動的なINSERT、UPDATEおよびDELETE文の場合は、データベース状態を明示的に読み込むかどうかにかかわらず、常にRNDSに違反します。
次のINSERTは、動的に実行される場合はRNDSに違反しますが、静的に実行される場合はRNDSに違反しません。
INSERT INTO my_table values(3, 'SCOTT');
次のUPDATEは、my_tableの列名を明示的に読み込むため、静的に実行された場合も動的に実行された場合もRNDSに違反します。
UPDATE my_table SET id=777 WHERE name='SCOTT';
PL/SQLでは、パッケージ・ファンクション（スタンドアロン以外）のオーバーロードが可能です。仮パラメータの数、順序、データ型ファミリなどが異なっていれば、別のファンクションに対して同じ名前を使用できます。
ただし、RESTRICT_REFERENCESプラグマは、1つのファンクション宣言にのみ適用できます。したがって、オーバーロードされたファンクションの名前を参照するプラグマは、常に前にある、最も近いファンクション宣言に適用されます。
次の例では、プラグマは、2番目のvalidの宣言に適用されます。
CREATE PACKAGE Tests AS FUNCTION Valid (x NUMBER) RETURN CHAR; FUNCTION Valid (x DATE) RETURN CHAR; PRAGMA RESTRICT_REFERENCES (valid, WNDS); END;
通常、PL/SQLパッケージでは、パッケージ内のパッケージ変数およびカーソルの数に応じて、ユーザー・グローバル領域（UGA）のメモリーが消費されます。このメモリーはユーザー数に比例して増加するため、スケーラビリティが制限されます。これを解決するには、プラグマ構文を使用して一部のパッケージにSERIALLY_REUSABLEのマークを付けます。
逐次再利用可能パッケージの場合、パッケージのグローバル・メモリーは各ユーザーのUGAではなく、小さなプールに保持され、複数の異なるユーザーのために再利用されます。これは、このようなパッケージのグローバル・メモリーは、作業単位内でのみ使用されることを意味します。そのため、作業単位の終了時にメモリーはそのプールに解放され、（すべてのグローバル変数の初期化コードの実行後）別のユーザーによって再利用されます。
逐次再利用可能パッケージの作業単位とは、サーバーへのOCIコール、PL/SQLのクライアント・サーバー間RPCコール、PL/SQLのサーバー間RPCコールなどのサーバーへのコールです。
再利用不可能パッケージ（SERIALLY_REUSABLEのマークが付いていない）の状態は、セッションの存続期間を通じて持続します。パッケージ状態には、グローバル変数やカーソルなどが含まれます。
逐次再利用可能パッケージの状態は、サーバーへのコールの存続期間中のみ持続します。サーバーへの後続のコールでは、逐次再利用パッケージが参照される場合、Oracle Databaseにより逐次再利用可能パッケージの新しいインスタンス化が作成され、すべてのグローバル変数をNULLに、または指定したデフォルト値に初期化します。サーバーへの以前のコール内の逐次再利用可能パッケージ状態に対して行われた変更は参照できません。
再利用不可能パッケージの状態はセッションの存続期間を通じて持続するので、セッション全体のUGAメモリーがロックされます。Oracle Officeなどのアプリケーションでは、ログイン・セッションは一般に何日も持続します。アプリケーションでは、特定のパッケージをセッション中の特定のローカル期間のみで使用することが必要な場合がよくあります。また、パッケージの使用後は、セッションの途中でパッケージ状態を非インスタンス化するのが理想的です。
逐次再利用可能パッケージ（SERIALLY_REUSABLE）を使用すると、アプリケーション開発者は、メモリーをより適切に管理してスケーラビリティを向上させるアプリケーションをモデル化できます。サーバーへのコール中にのみ管理されるパッケージ状態は、SERIALLY_REUSABLEパッケージ内に獲得する必要があります。
パッケージは、PRAGMA SERIALLY_REUSABLEにより逐次再利用可能のマークが付けられます。プラグマの構文は、次のとおりです。
PRAGMA SERIALLY_REUSABLE;
パッケージ仕様部は、対応するパッケージ本体の有無にかかわらず、逐次再利用可能のマークを付けられます。パッケージに本体がある場合、対応する仕様部に逐次再利用可能プラグマがあると、本体にもそのプラグマが必要です。逐次再利用可能プラグマは、仕様部にそのプラグマがないかぎり、本体に含めることはできません。
SERIALLY_REUSABLEのマークが付いたパッケージには、次のプロパティがあります。
この項では、逐次再利用可能パッケージの例を示します。
次に、逐次再利用可能パッケージ仕様部の例を示します（本体はありません）。
CONNECT Scott/Tiger CREATE OR REPLACE PACKAGE Sr_pkg IS PRAGMA SERIALLY_REUSABLE; N NUMBER := 5; -- default initialization END Sr_pkg;
Enterprise Manager（またはSQL*Plus）アプリケーションから次の文が発行されるとします。
CONNECT Scott/Tiger # first CALL to server BEGIN Sr_pkg.N := 10; END; # second CALL to server BEGIN DBMS_OUTPUT.PUT_LINE(Sr_pkg.N); END;
このプログラムにより、次のような結果が出力されます。
5
次に、逐次再利用可能なパッケージ仕様部およびパッケージ本体の例を示します。
CONNECT Scott/Tiger DROP PACKAGE Sr_pkg; CREATE OR REPLACE PACKAGE Sr_pkg IS PRAGMA SERIALLY_REUSABLE; TYPE Str_table_type IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER; Num NUMBER := 10; Str VARCHAR2(200) := 'default-init-str'; Str_tab STR_TABLE_TYPE; PROCEDURE Print_pkg; PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2); END Sr_pkg; CREATE OR REPLACE PACKAGE BODY Sr_pkg IS -- the body is required to have the pragma because the -- specification of this package has the pragma PRAGMA SERIALLY_REUSABLE; PROCEDURE Print_pkg IS BEGIN DBMS_OUTPUT.PUT_LINE('num: ' || Sr_pkg.Num); DBMS_OUTPUT.PUT_LINE('str: ' || Sr_pkg.Str); DBMS_OUTPUT.PUT_LINE('number of table elems: ' || Sr_pkg.Str_tab.Count); FOR i IN 1..Sr_pkg.Str_tab.Count LOOP DBMS_OUTPUT.PUT_LINE(Sr_pkg.Str_tab(i)); END LOOP; END; PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2) IS BEGIN -- init the package globals Sr_pkg.Num := N; Sr_pkg.Str := V; FOR i IN 1..n LOOP Sr_pkg.Str_tab(i) := V || ' ' || i; END LOOP; -- print the package Print_pkg; END; END Sr_pkg; SET SERVEROUTPUT ON; Rem SR package access in a CALL: BEGIN -- initialize and print the package DBMS_OUTPUT.PUT_LINE('Initing and printing pkg state..'); Sr_pkg.Init_and_print_pkg(4, 'abracadabra'); -- print it in the same call to the server. -- we should see the initialized values. DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL...'); Sr_pkg.Print_pkg; END; Initing and printing pkg state.. num: 4 str: abracadabra number of table elems: 4 abracadabra 1 abracadabra 2 abracadabra 3 abracadabra 4 Printing package state in the same CALL... num: 4 str: abracadabra number of table elems: 4 abracadabra 1 abracadabra 2 abracadabra 3 abracadabra 4 REM SR package access in subsequent CALL: BEGIN -- print the package in the next call to the server. -- We should that the package state is reset to the initial (default) values. DBMS_OUTPUT.PUT_LINE('Printing package state in the next CALL...'); Sr_pkg.Print_pkg; END; Statement processed. Printing package state in the next CALL... num: 10 str: default-init-str number of table elems: 0
この例は、逐次再利用可能パッケージのすべてのオープン・カーソルが作業境界（コール）の終了時に自動的にクローズされることを示します。また、新しいコールでは、これらのカーソルを再度オープンする必要があります。
REM For serially reusable pkg: At the end work boundariesREM (which is currently the OCI call boundary) all openREM cursors will be closed.REMREM Because the cursor is closed - every time we fetch weREM will start at the first row again.CONNECT Scott/Tiger DROP PACKAGE Sr_pkg; DROP TABLE People; CREATE TABLE People (Name VARCHAR2(20)); INSERT INTO People VALUES ('ET'); INSERT INTO People VALUES ('RAMBO'); CREATE OR REPLACE PACKAGE Sr_pkg IS PRAGMA SERIALLY_REUSABLE; CURSOR C IS SELECT Name FROM People; END Sr_pkg; SQL> SET SERVEROUTPUT ON; SQL> CREATE OR REPLACE PROCEDURE Fetch_from_cursor IS Name VARCHAR2(200); BEGIN IF (Sr_pkg.C%ISOPEN) THEN DBMS_OUTPUT.PUT_LINE('cursor is already open.'); ELSE DBMS_OUTPUT.PUT_LINE('cursor is closed; opening now.'); OPEN Sr_pkg.C; END IF; -- fetching from cursor. FETCH sr_pkg.C INTO name; DBMS_OUTPUT.PUT_LINE('fetched: ' || Name); FETCH Sr_pkg.C INTO name; DBMS_OUTPUT.PUT_LINE('fetched: ' || Name); -- Oops forgot to close the cursor (Sr_pkg.C). -- But, because it is a Serially Reusable pkg's cursor, -- it will be closed at the end of this CALL to the server. END; EXECUTE fetch_from_cursor; cursor is closed; opening now. fetched: ET fetched: RAMBO
データ・ウェアハウス環境では、大量のデータを変換するためにPL/SQLファンクションを使用します。データは、異なるファンクションによる一連の変換を経由して渡されます。PL/SQL表ファンクションを使用すると、このような変換を、かなりのメモリー・オーバーヘッドを必要としたり、各変換の間でデータを表に格納する必要なく実行できます。これらのファンクションは、複数の行を受け入れて戻すことが可能で、行は、一度ではなく準備できた順に戻すことができます。さらにパラレル化も可能です。
この方法は、次のように行います。
PIPELINEDキーワードを使用します。
OUTパラメータ（レコード）を使用します。このパラメータは結果セットの行に対応しています。
PIPE ROWキーワードを使用するコンシューマ・ファンクションに送信されます。
RETURN文で終了します。
TABLEキーワードを使用して、通常の表のように結果としての行を処理します。
次に例を示します。
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet PIPELINED IS out_rec TickerType := TickerType(NULL,NULL,NULL); in_rec p%ROWTYPE; BEGIN LOOP -- Function accepts multiple rows through a REF CURSOR argument. FETCH p INTO in_rec; EXIT WHEN p%NOTFOUND; -- Return value is a record type that matches the table definition. out_rec.ticker := in_rec.Ticker; out_rec.PriceType := 'O'; out_rec.price := in_rec.OpenPrice; -- Once a result row is ready, we send it back to the calling program, -- and continue processing. PIPE ROW(out_rec); -- This function outputs twice as many rows as it receives as input. out_rec.PriceType := 'C'; out_rec.Price := in_rec.ClosePrice; PIPE ROW(out_rec); END LOOP; CLOSE p; -- The function ends with a RETURN statement that does not specify any value. RETURN; END; / -- Here we use the result of this function in a SQL query. SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable))); -- Here we use the result of this function in a PL/SQL block. DECLARE total NUMBER := 0; price_type VARCHAR2(1); BEGIN FOR item IN (SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable)))) LOOP -- Access the values of each output row. -- We know the column names based on the declaration of the output type. -- This computation is just for illustration. total := total + item.price; price_type := item.price_type; END LOOP; END; /
一連の行を分析して結果値を計算するために、次のようにSUMなどの組込み集計と同様に機能する集計関数をコード化することができます。
ODCIAggregateIterate は、処理された各行に対して一度コールされたように結果を蓄積します。オブジェクト・タイプの属性を使用して中間結果を格納します。
DISTINCTやALLなどの一般的なオプションを含めることができます。
1
次のようなデータ構造を設定しないと機能しない例もあります。 CONNECT SYS/password AS SYSDBA;CREATE USER Jward IDENTIFIED BY Jward;GRANT CREATE ANY PACKAGE TO Jward;GRANT CREATE SESSION TO Jward;GRANT EXECUTE ANY PROCEDURE TO Jward;CONNECT Scott/Tiger
|
 Copyright © 2006 Oracle Corporation. All Rights Reserved. |
|