関数は、式として呼び出される単独の 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 がデフォルトの動作です。
  • IMMUTABLE は、関数がデータベースを変更できないことを指定し、同じ引数値が指定された場合は常に同じ結果に到達します。 他の方法では、データベースの検索を行ったり、引数リストに直接存在しない情報を使用したりしません。 この句を含めると、引数がすべて定数の関数の呼び出しはすべて、関数の値ですぐに置き換えることができます。
  • STABLE は、関数がデータベースを変更できないこと、および単一のテーブルスキャン内で、同じ引数値に対して常に同じ結果を返すことを指定します。 ただし、その結果は SQL 文間で変わる可能性があります。 これは、データベースの検索や現在のタイムゾーンなどのパラメーター変数に依存する関数に適したオプションです。
  • VOLATILE は、単一のテーブルスキャンでも関数値が変更される可能性があることを指定しているため、最適化を行うことはできません。 副作用がある関数は、その結果が予測可能であっても、呼び出しが最適化されないように、揮発性関数として分類する必要があることにご注意ください。
DETERMINISTIC DETERMINISTIC は、IMMUTABLE の同義語です。 DETERMINISTIC 関数はデータベースを変更できず、同じ引数値が指定された場合は常に同じ結果になります。 他の方法では、データベースの検索を行ったり、引数リストに直接存在しない情報を使用したりしません。 この句を含めると、引数がすべて定数の関数の呼び出しはすべて、関数の値ですぐに置き換えることができます。
[ NOT ] LEAKPROOF LEAKPROOF には副作用がなく、関数の呼び出しに使用される値に関する情報は明らかになりません。
CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

  • CALLED ON NULL INPUT は、引数の一部が NULL の場合にプロシージャが通常どおり呼び出されることを指定します。 CALLED ON NULL INPUT がデフォルト値です。 必要に応じて、作成者は NULL 値を確認し、適切に応答する必要があります。
  • RETURNS NULL ON NULL INPUT または STRICT は、引数のいずれかが NULL の場合にプロシージャが NULL を返すことを指定します。 これらの句が指定されている場合で、NULL 引数がある場合、プロシージャは実行されません。 NULL の結果は自動的に想定されます。
[ 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 は、並列順次スキャン (並列モード) の使用を可能にします。 シリアル順次スキャンとは対照的に、パラレル順次スキャンは複数のワーカーを使用して、照会中にリレーションを並行してスキャンします。
  • UNSAFE に設定すると、関数は並列モードで実行することができません。 SQL 文にそのような関数が存在すると、シリアル実行プランが強制されます。 PARALLEL 句が省略されている場合は、これがデフォルト設定です。
  • RESTRICTED に設定すると、関数は並列モードで実行できますが、実行は並列グループリーダーに制限されます。 特定のリレーションの資格に並列制限があるものがある場合には、そのリレーションは並列処理用に選択されません。
  • SAFE に設定すると、機能は制限なしに並列モードで実行することができます。
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 は、関数を自律型トランザクションに設定するディレクティブです。
STRICT、LEAKPROOF、PARALLEL、COST、ROWS、および SET キーワードは、Oracle と互換性のある PolarDB データベースに拡張機能を提供しますが、Oracle ではサポートされていません。

以下に、パラメーターを取らない単純な関数の例を示します。

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 句に置き換えます。