関数を作成 - PolarDB - Alibaba Cloud ドキュメントセンター

関数は、式として呼び出される単独の SPL プログラムです。評価されると、関数は、関数が埋め込まれている式に代入される値を返します。また、入力パラメーターの形式で呼び出し側プログラムから値を取得することもできます。関数は、それ自体で値を返すだけでなく、オプションで追加の値を出力パラメーターの形式で呼び出し元に返すことができます。ただし、プログラミングの練習では、関数で出力パラメーターを使用しないことを推奨します。

CREATE FUNCTION 文は、データベースに格納されるスタンドアロン関数を定義して名前を指定します。

スキーマ名が含まれている場合は、関数は指定されたスキーマに作成されます。指定がない場合は、現在のスキーマに作成されます。新しい関数の名前は、同じスキーマ内の同じ入力引数型を持つ既存の関数と一致してはなりません。ただし、異なる入力引数型の関数は名前を共有することができます。これはオーバーロードと呼ばれます。

説明

関数のオーバーロードは、PolarDB for PostgreSQL (Oracle互換) の機能です。ストアドスタンドアロン関数のオーバーロードは、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	関数の識別子。
parameters	正式なパラメータのリスト。
data_type	関数の RETURN ステートメントによって返される値のデータ型。
declarations	変数、カーソル、タイプ、またはサブプログラムの宣言。サブプログラム宣言が含まれている場合は、他のすべての変数、カーソル、および型宣言の後に宣言する必要があります。
statements	SPLプログラム文。 BEGIN - END ブロックには、EXCEPTION セクションを含めることができます。
IMMUTABLE STABLE VOLATILE	これらの属性は、関数の動作についてクエリオプティマイザーに通知します。どちらか一方のみ指定することができます。 VOLATILE がデフォルト動作です。 IMMUTABLE は、関数がデータベースを変更できないことを指定し、同じ引数値が指定された場合は常に同じ結果に到達します。他の方法では、データベースの検索を行ったり、引数リストに直接存在しない情報を使用したりしません。この句を含めると、引数がすべて定数の関数の呼び出しはすべて、関数の値ですぐに置き換えることができます。 STABLE は、関数がデータベースを変更できないこと、および単一のテーブルスキャン内で、同じ引数値に対して常に同じ結果を返すことを指定します。ただし、その結果は SQL 文間で変わる可能性があります。これは、データベースの検索や現在のタイムゾーンなどのパラメーター変数に依存する関数に適したオプションです。 VOLATILE は、単一のテーブルスキャンでも関数値が変更される可能性があることを指定しているため、最適化を行うことはできません。副作用がある関数は、その結果が予測可能であっても、呼び出しが最適化されないように、揮発性関数として分類する必要があることにご注意ください。
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 がデフォルト値です。キーワード EXTERNAL は、SQL 適合のために許可されています。これはオプションです。
[ EXTERNAL ] SECURITY INVOKER	関数を呼び出すユーザーの権限で関数が実行されることを指定します。キーワード EXTERNAL は、SQL 適合のために許可されています。これはオプションです。
AUTHID DEFINER AUTHID CURRENT_USER	[EXTERNAL] SECURITY DEFINERの同義語。 AUTHID 句が省略されている場合、または AUTHID DEFINER が指定されている場合は、関数の所有者の権限を使用して、データベースオブジェクトへのアクセス権限が決定されます。 AUTHID CURRENT_USER は、[EXTERNAL] SECURITY INVOKER の同義語です。 AUTHID CURRENT_USER が指定されている場合、関数を実行している現在のユーザーの権限を使用して、アクセス権限が決定されます。
PARALLEL { UNSAFE \| RESTRICTED \| SAFE }	パラレルシーケンシャルスキャン (パラレルモード) の使用を有効にします。シリアル順次スキャンは複数のワーカーを使用して、照会中にリレーションを並行してスキャンします。 UNSAFE に設定すると、関数は並列モードで実行することができません。 SQL 文にそのような関数が存在すると、シリアル実行プランが強制されます。 PARALLEL 句が省略されている場合、UNSAFE がデフォルトの設定です。 RESTRICTED に設定すると、関数は並列モードで実行できますが、実行は並列グループリーダーに制限されます。特定のリレーションの資格に並列制限があるものがある場合は、そのリレーションは並列処理用に選択されません。 SAFE に設定すると、機能は制限なしに並列モードで実行することができます。
COST execution_cost	関数の推定実行コストを示す正の数。 Unit: cpu_operator_cost. 関数がセットを返す場合は、これは返される行ごとのコストです。値が大きいと、プランナーは必要以上に頻繁に関数を評価しないようにします。
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	関数を自律トランザクションに設定するディレクティブ。

説明

STRICT、LEAKPROOF、PARALLEL、COST、ROWS、およびSETキーワードは、PolarDB for PostgreSQL (Compatible with Oracle) の拡張機能を提供しますが、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
AS
BEGIN
  RETURN (SELECT sum(sal) FROM emp WHERE deptno = dept_id);
END;

渡された入力パラメーターが NULL の場合に NULL を返すようにサーバーに指示するには、STRICT キーワードを含めます。 NULL 値が渡されると、関数は実行されません。

dept_salaries 関数は、関数を呼び出しているロールの特権で実行されます。現在のユーザーが SELECT 文を実行して emp テーブルを照会し、従業員の給与を表示するための十分な権限を持っていない場合、関数はエラーを報告します。関数を定義したロールに関連付けられた権限を使用するようサーバーに指示するには、AUTHID CURRENT_USER 句を AUTHID DEFINER 句に置き換えます。