CREATE PROCEDURE

语法

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 参数的类型。

参数

示例

以下存储过程列出了 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 子句。