定义新函数。

语法

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)参数数据类型不同,就可以用于多个不同函数。

参数

参数说明
name函数的标识符。
parameters形参的列表。
data_type函数的 RETURN 语句所返回值的数据类型。
declarations变量、游标、类型或子程序声明。如果包括子程序声明,则它们必须在所有其他变量、游标和类型声明之后。
statementsSPL程序语句(BEGIN - END 块可以包含 EXCEPTION 部分)。
IMMUTABLE / STABLE / VOLATILE这些属性将函数的行为通知给查询优化器;您只能指定一个选项。VOLATILE 是默认行为。
  • IMMUTABLE 指示函数不能修改数据库,并在提供相同参数值时始终会得到相同结果;它不执行数据库查找,也不以其他方式使用其参数列表中不直接存在的信息。如果包括此子句,则使用全常量参数对函数的任意调用将立即替换为函数值。
  • STABLE 指示函数不能修改数据库,并在单表扫描中,它将一致地为相同参数值返回同一结果,但其结果可能在 SQL 语句之间发生更改。对于依赖于数据库查找、参数变量(例如当前时区)等的函数,这是合适的选择。
  • VOLATILE 指示即使在单表扫描中函数值也可以更改,因此不能进行任何优化。请注意,任何具有负面影响的函数必须分类为易失性函数,即使其结果可预测性很好也是如此,这是为了防止调用由于优化而被去除。
DETERMINISTICDETERMINISTIC 是 IMMUTABLE 的同义词。DETERMINISTIC 函数不能修改数据库,并在提供相同参数值时始终会得到相同结果;它不执行数据库查找,也不以其他方式使用其参数列表中不直接存在的信息。如果包括此子句,则使用全常量参数对函数的任意调用将立即替换为函数值。
[ NOT ] LEAKPROOFLEAKPROOF 函数没有负面影响,也不会公开有关调用函数所用值的任何信息。
CALLED ON NULL INPUT / RETURNS NULL ON NULL INPUT / STRICT
  • CALLED ON NULL INPUT(默认值)指示当存储过程的某些参数为 NULL 时,将正常调用该存储过程。如果需要,作者需要负责检查 NULL 值并做出适当的响应。
  • RETURNS NULL ON NULL INPUT 或 STRICT 指示只要存储过程的任何参数为 NULL,该存储过程就始终返回 NULL。如果指定了这些子句,则当存在 NULL 参数时,不会执行该存储过程,而是自动假定为 NULL 结果。
[ EXTERNAL ] SECURITY DEFINERSECURITY DEFINER 指定函数将使用创建该函数的用户的特权执行;这是默认值。为了符合 SQL 要求,允许使用关键字 EXTERNAL,但这是可选的。
[ EXTERNAL ] SECURITY INVOKERSECURITY INVOKER 子句指示函数将使用调用该函数的用户的特权执行。为了符合 SQL 要求,允许使用关键字 EXTERNAL,但这是可选的。
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_costexecution_cost是一个正数,给出函数的估计执行成本,单位为 cpu_operator_cost。如果函数返回一个集合,则这是每个返回行的成本。较大值会导致计划程序尝试避免超出必要的频率来对函数求值。
ROWS result_rowsresult_rows 是一个正数,给出计划程序预计函数返回的估计行数。只有当函数声明为返回一个集合时,才允许使用此值。默认假定值为 1000 行。
SET configuration_parameter { TO value | = value | FROM CURRENT }SET 子句使指定的配置参数在进入函数时设置为指定值,然后在函数退出时恢复为其之前的值。SET FROM CURRENT 将会话的当前参数值保存为进入函数时要应用的值。

如果 SET 子句附加到函数,则在函数内针对相同变量执行 SET LOCAL 命令的效果仅限于该函数;函数退出时配置参数将恢复为之前的值。普通的 SET 命令(没有 LOCAL)会重写 SET 子句,与对之前 SET LOCAL 命令的操作很相似,此命令的效果在退出存储过程后会保留,除非回滚当前事务。

PRAGMA AUTONOMOUS_TRANSACTIONPRAGMA AUTONOMOUS_TRANSACTION 是将函数设置为自治事务的指令。
说明 STRICT、LEAKPROOF、PARALLEL、COST、ROWS 和 SET 关键字可以为PolarDB PostgreSQL版(兼容Oracle)提供扩展功能,但 Oracle 不支持这些关键字。

示例

函数 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 子句。