ユーザー定義ファンクション
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::=
オプションの式のリストは、ファンクション、パッケージまたは演算子の属性と一致する必要があります。
ユーザー定義ファンクションの制限事項
DISTINCT
およびALL
キーワードは、ユーザー定義の集計ファンクションでのみ有効です。
関連項目:
-
ファンクションの作成(ユーザー定義ファンクションの制限を含む)の詳細は、「CREATE FUNCTION」を参照してください。
-
ユーザー・ファンクションの作成および使用方法の詳細は、『Oracle Database開発ガイド』を参照してください。
前提条件
ユーザー定義ファンクションをSQL文で使用するには、トップレベル・ファンクションとして作成するか、またはパッケージ仕様部で宣言する必要があります。
SQLの式の中でユーザー・ファンクションを使用するには、ユーザーがユーザー・ファンクションのEXECUTE
権限を持っている必要があります。ユーザー・ファンクションで定義したビューを問い合せるには、そのビューに対するREAD
またはSELECT
権限が必要です。ビューを検索するには個々のEXECUTE
権限は必要ありません。
関連項目:
トップレベル・ファンクションの詳細は、「CREATE FUNCTION」を参照してください。パッケージ・ファンクションの詳細は、「CREATE PACKAGE」を参照してください。
名前の優先順位
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
ファンクションを検索します。このようなファンクションが検出されない場合は、エラーを戻します。
また、ユーザーが定義したシノニムを使用して、ストアド・トップレベル・ファンクションを参照することもできます。