ユーザー定義ファンクション

PL/SQL、JavaまたはCでユーザー定義ファンクションを作成し、SQLまたはSQL組込みファンクションにはない機能を持たせることができます。ユーザー定義ファンクションは、式を指定できる場所であればどこでも、SQL文に指定できます。

たとえば、次の場所でユーザー定義ファンクションを使用できます。

SELECT文のselectリスト
WHERE句の条件
CONNECT BY句、START WITH句、ORDER BY句およびGROUP BY句
INSERT文のVALUES句
UPDATE文のSET句

注意:

Oracle SQLは、ブール値のパラメータまたは戻り値を持つファンクションのコールをサポートしていません。したがって、ユーザー定義ファンクションがSQL文からコールされる場合は、数値(0または1)または文字列('TRUE'または'FALSE')を戻すように設計する必要があります。

user_defined_function::=

図user_defined_function.gifの説明

オプションの式のリストは、ファンクション、パッケージまたは演算子の属性と一致する必要があります。

ユーザー定義ファンクションの制限事項: DISTINCTおよびALLキーワードは、ユーザー定義集計ファンクションのみで有効です。

前提条件

ユーザー定義ファンクションをSQL文で使用するには、トップレベル・ファンクションとして作成するか、またはパッケージ仕様部で宣言する必要があります。

SQLの式の中でユーザー・ファンクションを使用するには、ユーザーがユーザー・ファンクションのEXECUTE権限を持っている必要があります。ユーザー・ファンクションで定義したビューを問い合せるには、そのビューに対するREADまたはSELECT権限が必要です。ビューを検索するには個々のEXECUTE権限は必要ありません。

名前の優先順位

SQL文内では、データベースの列名は、パラメータなしのファンクション名より優先順位が高くなります。たとえば、Human Resourcesのマネージャが、hrスキーマに次の2つのオブジェクトを作成する場合は、次のようにします。

CREATE TABLE new_emps (new_sal NUMBER, ...);
CREATE FUNCTION new_sal RETURN NUMBER IS BEGIN ... END;

その後、次の2つの文のように、new_salを参照するとnew_emps.new_sal列を参照することになります。

SELECT new_sal FROM new_emps;
SELECT new_emps.new_sal FROM new_emps;

new_salファンクションにアクセスするには、次のように入力します。

SELECT hr.new_sal FROM new_emps;

SQLの式内で使用できるユーザー・ファンクションのコール例を次に示します。

circle_area (radius)
payroll.tax_rate (empno)
hr.employees.tax_rate (dependent, empno)@remote

例 hrスキーマからtax_rateユーザー・ファンクションをコールし、tax_table内のss_no列とsal列に対してこのファンクションを実行するには、次のように指定します。

SELECT hr.tax_rate (ss_no, sal)
    INTO income_tax
    FROM tax_table WHERE ss_no = tax_id;

INTO句は、結果をincome_tax変数に配置するために使用できるPL/SQLです。

ネーミング規則

オプションのスキーマ名またはパッケージ名を1つのみ指定すると、最初の識別子はスキーマ名またはパッケージ名のいずれかになります。たとえば、PAYROLL.TAX_RATEという参照内のPAYROLLがスキーマ名かパッケージ名かを判断するには、Oracle Databaseは次の手順を実行します。

カレント・スキーマ内のPAYROLLパッケージをチェックします。
PAYROLLパッケージが検出されない場合は、トップレベルのTAX_RATEファンクションを含むスキーマ名PAYROLLを検索します。このようなファンクションが検出されない場合は、エラーを戻します。
カレント・スキーマ内でPAYROLLパッケージが検出されると、PAYROLLパッケージの中でTAX_RATEファンクションを検索します。このようなファンクションが検出されない場合は、エラーを戻します。

また、ユーザーが定義したシノニムを使用して、ストアド・トップレベル・ファンクションを参照することもできます。