関数は、式として呼び出される単独の SPL プログラムです。 関数が評価されると、関数が埋め込まれている式に代入される値を返します。 また、入力パラメーターの形式で呼び出し側プログラムから値を取得することもできます。 関数は、それ自体で値を返すだけでなく、オプションで追加の値を出力パラメーターの形式で呼び出し元に返すことができます。 ただし、プログラミングの練習では、関数で出力パラメーターを使用しないことを推奨します。
CREATE FUNCTION 文は、データベースに格納されるスタンドアロン関数を定義して名前を指定します。
スキーマ名が含まれている場合は、関数は指定されたスキーマに作成されます。 それ以外の場合は、現在のスキーマに作成されます。 新しい関数の名前は、同じスキーマ内の同じ入力引数型を持つ既存の関数と一致してはなりません。 ただし、異なる入力引数型の関数は名前を共有することができます。 これはオーバーロードと呼ばれます。 関数のオーバーロードは、Oracle と互換性のある PolarDB データベースの機能です。格納されたスタンドアロン関数のオーバーロードは、Oracle データベースと互換性がありません。
既存の関数の定義を更新するには、CREATE OR REPLACE FUNCTION を使用します。 この方法では、関数の名前または引数型を変更することはできません。 実際に試してみると、実際には新しい明確な関数が作成されています。 また、CREATE OR REPLACE FUNCTION は、既存の関数の戻り値の型を変更しません。 変更するためには、関数を削除して再作成する必要があります。 また、OUT パラメーターを使用する場合、関数を削除しない限り、OUT パラメーターの型を変更することはできません。
CREATE [ OR REPLACE ] FUNCTION name [ (parameters) ]
RETURN data_type
[
IMMUTABLE
| STABLE
| VOLATILE
| DETERMINISTIC
| [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT
| RETURNS NULL ON NULL INPUT
| STRICT
| [ EXTERNAL ] SECURITY INVOKER
| [ EXTERNAL ] SECURITY DEFINER
| AUTHID DEFINER
| AUTHID CURRENT_USER
| PARALLEL { UNSAFE | RESTRICTED | SAFE }
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter
{ TO value | = value | FROM CURRENT }
...]
{ IS | AS }
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ declarations ]
BEGIN
statements
END [ name ];
引数 | 説明 |
---|---|
name | name は関数の識別子です。 |
parameters | パラメーターは、形式的なパラメーターのリストです。 |
data_type | data_type は、関数の RETURN 文によって返される値のデータ型です。 |
declarations | 宣言は、変数、カーソル、型、またはサブプログラムの宣言です。 サブプログラム宣言が含まれている場合は、他のすべての変数、カーソル、および型宣言の後に宣言する必要があります。 |
statements | ステートメントは SPL プログラム文です。 BEGIN-END ブロックには、EXCEPTION セクションを含めることができます。 |
IMMUTABLE
STABLE VOLATILE |
これらの属性は、関数の動作についてクエリオプティマイザーに通知します。 どちらか一方のみ指定することができます。 VOLATILE がデフォルトの動作です。
|
DETERMINISTIC | DETERMINISTIC は、IMMUTABLE の同義語です。 DETERMINISTIC 関数はデータベースを変更できず、同じ引数値が指定された場合は常に同じ結果になります。 他の方法では、データベースの検索を行ったり、引数リストに直接存在しない情報を使用したりしません。 この句を含めると、引数がすべて定数の関数の呼び出しはすべて、関数の値ですぐに置き換えることができます。 |
[ NOT ] LEAKPROOF | LEAKPROOF には副作用がなく、関数の呼び出しに使用される値に関する情報は明らかになりません。 |
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT STRICT |
|
[ EXTERNAL ] SECURITY DEFINER | SECURITY DEFINER は、関数を作成したユーザーの権限で実行することを指定します。 SECURITY DEFINER がデフォルト値です。 キーワード EXTERNAL は、SQL 適合のために許可されています。 これはオプションです。 |
[ EXTERNAL ] SECURITY INVOKER | SECURITY INVOKER は、関数を、それを呼び出すユーザーの特権で実行することを指定します。 キーワード EXTERNAL は、SQL 適合のために許可されています。 これはオプションです。 |
AUTHID DEFINER
AUTHID CURRENT_USER |
AUTHID DEFINER は、[EXTERNAL] SECURITY DEFINER の同義語です。 AUTHID 句が省略されている場合、または AUTHID
DEFINER が指定されている場合は、関数所有者の権限を使用して、データベースオブジェクトへのアクセス権限が決定されます。
AUTHID CURRENT_USER は、[EXTERNAL] SECURITY INVOKER の同義語です。 AUTHID CURRENT_USER が指定されている場合、関数を実行している現在のユーザーの権限を使用して、アクセス権限が決定されます。 |
PARALLEL { UNSAFE | RESTRICTED | SAFE } | PARALLEL は、並列順次スキャン (並列モード) の使用を可能にします。 シリアル順次スキャンとは対照的に、パラレル順次スキャンは複数のワーカーを使用して、照会中にリレーションを並行してスキャンします。
|
COST execution_cost | execution_cost は、関数の推定実行コストを示す正の数です。 Unit: cpu_operator_cost. 関数がセットを返す場合は、これは返される行ごとのコストです。 値が大きいと、プランナは必要以上に頻繁に関数を評価しないようにします。 |
ROWS result_rows | result_rows は正の数であり、関数が返すとプランナが予想する行の推定数を示します。 これは、関数がセットを返すように宣言されている場合にのみ許可されます。 デフォルトの想定は 1,000 行です。 |
SET configuration_parameter { TO value | = value | FROMCURRENT } | SET は、指定された設定パラメーターを、関数が入力されたときに指定された値に設定し、関数が終了したときに以前の値に戻します。 SET FROM CURRENT は、関数の入力時に適用される値として、セッションの現在のパラメーター値を保存します。
関数に SET 句がアタッチされている場合、同じ変数に対して関数内で実行される SET LOCAL 文の効果は、関数に制限されます。 関数が終了すると、以前の設定パラメーター値が復元されます。 通常の SET 文 (LOCAL なし) は、前の SET LOCAL 文の場合と同様に、SET 句を上書きします。このような文の影響は、現在のトランザクションがロールバックされない限り、プロシージャの終了後も持続します。 |
PRAGMA AUTONOMOUS_TRANSACTION | PRAGMA AUTONOMOUS_TRANSACTION は、関数を自律型トランザクションに設定するディレクティブです。 |
例
以下に、パラメーターを取らない単純な関数の例を示します。
CREATE OR REPLACE FUNCTION simple_function
RETURN VARCHAR2
IS
BEGIN
RETURN 'That''s All Folks!' ;
END simple_function;
以下の例では、2 つの入力パラメーターを受け取る関数について説明します。 パラメーターについては、後続のトピックで説明します。
CREATE OR REPLACE FUNCTION emp_comp (
p_sal NUMBER,
p_comm NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp;
以下の例は、関数宣言で AUTHID CURRENT_USER 句と STRICT キーワードを使用する方法を示しています。
CREATE OR REPLACE FUNCTION dept_salaries(dept_id int) RETURN NUMBER
STRICT
AUTHID CURRENT_USER
BEGIN
RETURN QUERY (SELECT sum(salary) FROM emp WHERE deptno = id);
END;
渡された入力パラメーターが NULL の場合に NULL を返すようにサーバーに指示するには、STRICT キーワードを含めます。 NULL 値が渡されると、関数は実行されません。
dept_salaries 関数は、関数を呼び出しているロールの特権で実行されます。 現在のユーザーが SELECT 文を実行して emp テーブルを照会し、従業員の給与を表示するための十分な権限を持っていない場合、関数はエラーを報告します。 関数を定義したロールに関連付けられた特権を使用するようサーバーに指示するには、AUTHID CURRENT_USER 句を AUTHID DEFINER 句に置き換えます。