:CREATE FUNCTION

構文

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 ];

説明

CREATE FUNCTION は関数を作成します。 CREATE OR REPLACE FUNCTION は、新しい関数を作成するか、既存の定義を置き換えます。

スキーマ名を指定すると、指定したスキーマで関数が作成されます。 指定しない場合、関数は現在のスキーマに作成されます。 新しい関数は、同じスキーマ内の同じ入力引数型を持つ既存の関数と同じ名前にすることはできません。 ただし、異なる入力引数型を持つ関数とであれば、名前を共有することができます。 これはオーバーロードと呼ばれます。 関数のオーバーロードは、Oracle と互換性のある PolarDB データベースの機能です。 格納されたスタンドアロン関数のオーバーロードは、Oracle データベースと互換性がありません。

既存の関数の定義を更新するには、CREATE OR REPLACE FUNCTION ステートメントを使用できます。 このステートメントを使用して関数の名前または引数型を変更することはできません。 使用した場合、新しい個別の関数が作成されます。 また、CREATE OR REPLACE FUNCTION ステートメントを使用して既存の関数の戻り値の型を変更することはできません。 既存の関数の戻り値の型を変更するには、関数を削除して、再度作成する必要があります。 OUT パラメーターを使用する場合、既存の関数を削除しない限り OUT パラメーターのタイプを変更することはできません。

関数を作成したユーザーが関数の所有者になります。

Oracle と互換性のある PolarDB データベースが関数のオーバーロードをサポートします。 入力 (IN、IN OUT) 引数のデータ型が異なる場合、種々の異なる関数に同じ名前を使用できます。

パラメーター

例

emp_comp 関数は 2 つの数値を入力としてアクセプトし、計算値を返します。 SELECT コマンドは、関数の使用方法を説明するために使用されます。

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;

SELECT ename "Name", sal "Salary", comm "Commission", emp_comp(sal, comm)
    "Total Compensation" FROM emp;

  Name | Salary | Commission | Total Compensation
--------+---------+------------+--------------------
 SMITH | 800.00 | | 19200.00
 ALLEN | 1600.00 | 300.00 | 45600.00
 WARD | 1250.00 | 500.00 | 42000.00
 JONES | 2975.00 | | 71400.00
 MARTIN | 1250.00 | 1400.00 | 63600.00
 BLAKE | 2850.00 | | 68400.00
 CLARK | 2450.00 | | 58800.00
 SCOTT | 3000.00 | | 72000.00
 KING | 5000.00 | | 120000.00
 TURNER | 1500.00 | 0.00 | 36000.00
 ADAMS | 1100.00 | | 26400.00
 JAMES | 950.00 | | 22800.00
 FORD | 3000.00 | | 72000.00
 MILLER | 1300.00 | | 31200.00
(14 行)

sal_range 関数は、給与が指定された範囲にある従業員の数を返します。 次の匿名ブロックは関数を複数回呼び出し、引数のデフォルト値が最初の 2 回の呼び出しで使用されます。

CREATE OR REPLACE FUNCTION sal_range (
    p_sal_min NUMBER DEFAULT 0,
    p_sal_max NUMBER DEFAULT 10000
) RETURN INTEGER
IS
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM emp
        WHERE sal BETWEEN p_sal_min AND p_sal_max;
    RETURN v_count;
END;

BEGIN
    DBMS_OUTPUT.PUT_LINE('Number of employees with a salary: ' ||
        sal_range);
    DBMS_OUTPUT.PUT_LINE('Number of employees with a salary of at least '
        || '$2000.00: ' || sal_range(2000.00));
    DBMS_OUTPUT.PUT_LINE('Number of employees with a salary between '
        || '$2000.00 and $3000.00: ' || sal_range(2000.00, 3000.00));

END;

Number of employees with a salary: 14
Number of employees with a salary of at least $ 2000.00: 6
Number of employees with a salary between $ 2000.00 and $ 3000.00: 5

次の例は、関数宣言で 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;

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

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