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 将创建新函数，或者替换现有定义。

如果包括 schema 名称，则在指定 schema 中创建函数。否则在当前 schema 中创建。对于任何现有函数，如果与新函数在相同的 schema 中具有相同的输入参数类型，则新函数名称不能与现有函数名称匹配。不过，具有不同输入参数类型的函数可共用一个名称（这称为重载）。（函数重载是PolarDB PostgreSQL版（兼容Oracle）的一项功能，重载已存储的独立函数这一功能与 Oracle 数据库不兼容。）

要更新现有函数的定义，请使用 CREATE OR REPLACE FUNCTION。无法以此方式更改函数的名称或参数类型（如果您尝试过此方式，实际上创建的是一个新的不同函数）。此外，CREATE OR REPLACE FUNCTION 不会让您更改现有函数的返回类型。要更改现有函数的返回类型，您必须删除并重新创建函数。此外，在使用 OUT 参数时，除非通过删除函数，否则您不能更改任何 OUT 参数的类型。

创建函数的用户成为函数的所有者。

PolarDB PostgreSQL版（兼容Oracle）允许函数重载，即对于同一个名称，只要其输入（IN、IN OUT）参数数据类型不同，就可以用于多个不同函数。

参数

示例

函数 emp_comp 接受两个数字作为输入并返回计算值。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 rows)

函数 sal_range 返回工资在指定范围内的员工数。以下匿名块多次调用函数，并在前两次调用中使用参数的默认值。

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;

付薪员工数：14
工资不低于 $2000.00 的员工数：6
工资在 $2000.00 到 $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 子句。