函数是作为表达式调用的独立 SPL 程序。进行求值后,函数返回一个值,该值在该函数所嵌入的表达式中被替换。函数可以选择以输入参数的形式从调用程序获取值。除了函数实际上本身返回值之外,函数还可以选择以输出参数的形式向调用方返回其他值。不过,在函数中使用输出参数并不是提倡的编程做法。

CREATE FUNCTION 命令定义并命名一个将存储在数据库中的独立函数。

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

要更新现有函数的定义,请使用 CREATEOR 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 ];
参数说明
namename 是函数的标识符。
parametersparameters 是形参的列表。
data_typedata_type 是函数的 RETURN 语句所返回值的数据类型。
declarationsdeclarations 是变量、游标、类型或子程序声明。如果包括子程序声明,则它们必须在所有其他变量、游标和类型声明之后。
statementsstatements 是 SPL 程序语句(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 | FROMCURRENT }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 不支持这些关键字。

示例

下面是一个不采用参数的简单函数的示例。

CREATE OR REPLACE FUNCTION simple_function
    RETURN VARCHAR2
IS
BEGIN
    RETURN 'That''s All Folks!';
END simple_function;

以下函数采用两个输入参数。参数将在后面的章节中更详细地讲述。

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;

包括 STRICT 关键字以指示在传递的任意输入参数为 NULL 时,服务器返回 NULL;如果传递了 NULL 值,则不执行函数。

dept_salaries 函数使用调用该函数的角色的特权执行。如果当前用户没有足够的特权来执行查询 emp 表的 SELECT 语句(以显示员工工资),则函数将报告错误。要指示服务器使用与定义了函数的角色关联的特权,可将 AUTHID CURRENT_USER 子句替换为 AUTHID DEFINER 子句。