使用CREATEFUNCTION命令创建函数_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

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

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

如果包括Schema名称，则在指定Schema中创建函数，否则在当前Schema中创建。对于任何现有函数，如果与新函数在相同的Schema中具有相同的输入参数类型，则新函数名称不能与现有函数名称匹配。不过，具有不同输入参数类型的函数可共用一个名称（这称为重载）。

说明

函数重载是PolarDB 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	name是函数的标识符。
parameters	parameters是形参的列表。
data_type	data_type是函数的RETURN语句所返回值的数据类型。
declarations	declarations是变量、游标、类型或子程序声明。如果包括子程序声明，则它们必须在所有其他变量、游标和类型声明之后。
statements	statements是SPL程序语句（BEGIN - END块可以包含EXCEPTION部分）。
IMMUTABLE STABLE VOLATILE	这些属性将函数的行为通知给查询优化器，您只能指定一个选项。VOLATILE是默认行为。 IMMUTABLE指示函数不能修改数据库，并在提供相同参数值时始终会得到相同结果；它不执行数据库查找，也不以其他方式使用其参数列表中不直接存在的信息。如果包括此子句，则使用全常量参数对函数的任意调用将立即替换为函数值。 STABLE指示函数不能修改数据库，并在单表扫描中，它将一致地为相同参数值返回同一结果，但其结果可能在 SQL 语句之间发生更改。对于依赖于数据库查找、参数变量（例如当前时区）等的函数，这是合适的选择。 VOLATILE指示即使在单表扫描中函数值也可以更改，因此不能进行任何优化。请注意，任何具有负面影响的函数必须分类为易失性函数，即使其结果可预测性很好也是如此，这是为了防止调用由于优化而被去除。
DETERMINISTIC	DETERMINISTIC是IMMUTABLE的同义词。DETERMINISTIC函数不能修改数据库，并在提供相同参数值时始终会得到相同结果；它不执行数据库查找，也不以其他方式使用其参数列表中不直接存在的信息。如果包括此子句，则使用全常量参数对函数的任意调用将立即替换为函数值。
[ NOT ] LEAKPROOF	LEAKPROOF函数没有负面影响，也不会公开有关调用函数所用值的任何信息。
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 DEFINER	SECURITY DEFINER指定函数将使用创建该函数的用户的特权执行；这是默认值。为了符合SQL要求，允许使用关键字EXTERNAL，但这是可选的。
[ EXTERNAL ] SECURITY INVOKER	SECURITY 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_cost	execution_cost是一个正数，给出函数的估计执行成本，单位为cpu_operator_cost。如果函数返回一个集合，则这是每个返回行的成本。较大值会导致计划程序尝试避免超出必要的频率来对函数求值。
ROWS result_rows	result_rows是一个正数，给出计划程序预计函数返回的估计行数。只有当函数声明为返回一个集合时，才允许使用此值。默认假定值为1000行。
SET configuration_parameter { TO value \| = value \| FROMCURRENT }	SET子句使指定的配置参数在进入函数时设置为指定值，然后在函数退出时恢复为其之前的值。SET FROM CURRENT将会话的当前参数值保存为进入函数时要应用的值。如果SET子句附加到函数，则在函数内针对相同变量执行SET LOCAL命令的效果仅限于该函数；函数退出时配置参数将恢复为之前的值。普通的SET命令（没有 LOCAL）会重写SET子句，与对之前SET LOCAL命令的操作很相似，此命令的效果在退出存储过程后会保留，除非回滚当前事务。
PRAGMA AUTONOMOUS_TRANSACTION	PRAGMA 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
AS
BEGIN
  RETURN (SELECT sum(sal) FROM emp WHERE deptno = dept_id);
END;

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

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