定义新存储过程。

语法

CREATE [OR REPLACE] PROCEDURE name [ (parameters) ]
   [
          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 PROCEDURE 可定义新存储过程。CREATE OR REPLACE PROCEDURE 将创建新的存储过程或替换现有定义。

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

要更新现有存储过程的定义,可使用 CREATE OR REPLACE PROCEDURE。无法以这种方式更改存储过程的名称或参数类型(如果您尝试过,实际上创建的是一个新的不同存储过程)。使用 OUT 参数时,除非通过删除存储过程,否则不能更改任何 OUT 参数的类型。

参数

参数说明
namename 是存储过程的标识符。
parametersparameters 是形参的列表。
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,则该存储过程不能以并行模式执行。存在此类存储过程时,会强制执行串行执行计划。如果省略 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 不支持这些关键字。
  • 只有PolarDB PostgreSQL版(兼容Oracle)存储过程支持 IMMUTABLE、STABLE、STRICT、LEAKPROOF、COST、ROWS 及 PARALLEL { UNSAFE | RESTRICTED | SAFE } 属性。
  • 默认情况下存储过程创建为 SECURITY DEFINERS。在 plpgsql 中定义的存储过程创建为 SECURITY INVOKERS。

示例

以下存储过程列出了 emp 表中的员工:

CREATE OR REPLACE PROCEDURE list_emp
IS
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    CURSOR emp_cur IS
        SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
    OPEN emp_cur;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_cur INTO v_empno, v_ename;
        EXIT WHEN emp_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_empno || '     ' || v_ename);
    END LOOP;
    CLOSE emp_cur;
END;

EXEC list_emp;

EMPNO    ENAME
-----    -------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER
7876     ADAMS
7900     JAMES
7902     FORD
7934     MILLER

以下存储过程使用 IN OUT 和 OUT 参数返回员工的编号、姓名和职位,首先采用给定的员工编号进行搜索,如果找不到则采用给定的姓名。一个匿名块调用该存储过程。

CREATE OR REPLACE PROCEDURE emp_job (
    p_empno         IN OUT emp.empno%TYPE,
    p_ename         IN OUT emp.ename%TYPE,
    p_job           OUT    emp.job%TYPE
)
IS
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
BEGIN
    SELECT ename, job INTO v_ename, v_job FROM emp WHERE empno = p_empno;
    p_ename := v_ename;
    p_job   := v_job;
    DBMS_OUTPUT.PUT_LINE('Found employee # ' || p_empno);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        BEGIN
            SELECT empno, job INTO v_empno, v_job FROM emp
                WHERE ename = p_ename;
            p_empno := v_empno;
            p_job   := v_job;
            DBMS_OUTPUT.PUT_LINE('Found employee ' || p_ename);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('Could not find an employee with ' ||
                    'number, ' || p_empno || ' nor name, '  || p_ename);
                p_empno := NULL;
                p_ename := NULL;
                p_job   := NULL;
        END;
END;

DECLARE
    v_empno      emp.empno%TYPE;
    v_ename      emp.ename%TYPE;
    v_job        emp.job%TYPE;
BEGIN
    v_empno := 0;
    v_ename := 'CLARK';
    emp_job(v_empno, v_ename, v_job);
    DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name       : ' || v_ename);
    DBMS_OUTPUT.PUT_LINE('Job        : ' || v_job);
END;

Found employee CLARK
Employee No: 7782
Name       : CLARK
Job        : MANAGER

以下示例演示了如何在存储过程声明中使用 AUTHID DEFINER 和 SET 子句。update_salary 存储过程将定义了该存储过程的角色的特权传递给正在调用该存储过程的角色(在执行该存储过程时):

CREATE OR REPLACE PROCEDURE update_salary(id INT, new_salary NUMBER)
  SET SEARCH_PATH = 'public' SET WORK_MEM = '1MB'
  AUTHID DEFINER IS
BEGIN
  UPDATE emp SET salary = new_salary WHERE emp_id = id;
END;

包括 SET 子句,将存储过程的搜索路径设置为 public,并将工作内存设置为 1MB。其他存储过程、函数及对象不受这些设置的影响。

在此示例中,AUTHID DEFINER 子句将特权临时授予可能不允许执行存储过程内语句的角色。要指示服务器使用与调用存储过程的角色相关联的特权,可将 AUTHID DEFINER 子句替换为 AUTHID CURRENT_USER 子句。