存储过程是作为单个 SPL 程序语句调用的独立 SPL 程序。调用时,存储过程可选择以输入参数的形式从调用方接收值,并可选择以输出参数的形式向调用方返回值。
CREATE PROCEDURE 命令可定义并命名一个将存储在数据库中的独立存储过程。
如果包括 schema 名称,则在指定的 schema 中创建存储过程。否则在当前 schema 中创建。新存储过程的名称不得与同一 schema 中具有相同输入参数类型的任何现有存储过程匹配。不过,具有不同输入参数类型的存储过程可共用一个名称(这称为重载)。(存储过程重载是一项PolarDB PostgreSQL版(兼容Oracle)功能 - 独立存储过程的重载与 Oracle 数据库不兼容。)
要更新现有存储过程的定义,可使用 CREATEOR REPLACE PROCEDURE。无法以这种方式更改存储过程的名称或参数类型(如果您尝试过,实际上创建的是一个新的不同存储过程)。使用 OUT 参数时,除非通过删除存储过程,否则不能更改任何 OUT 参数的类型。
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 ];
参数 | 说明 |
---|---|
name | name 是存储过程的标识符。 |
parameters | parameters 是形参的列表。 |
declarations | declarations 是变量、游标、类型或子程序声明。如果包括子程序声明,则它们必须在所有其他变量、游标和类型声明之后。 |
statements | statements 是 SPL 程序语句(BEGIN - END 块可以包含 EXCEPTION 部分)。 |
IMMUTABLE STABLE VOLATILE | 这些属性将存储过程的行为通知给查询优化器;您只能指定一个选项。VOLATILE 是默认行为。
|
DETERMINISTIC | DETERMINISTIC 是 IMMUTABLE 的同义词。DETERMINISTIC 存储过程不能修改数据库,并在提供相同参数值时始终会得到相同结果;它不执行数据库查找,也不以其他方式使用其参数列表中不直接存在的信息。如果包括此子句,则使用全常量参数对存储过程的任何调用将立即替换为存储过程值。 |
[ NOT ] LEAKPROOF | LEAKPROOF 存储过程没有负面影响,也不会公开有关调用存储过程所用值的任何信息。 |
CALLED ON NULL INPUT RETURNS NULL ON NULL INPUT STRICT |
|
[ EXTERNAL ] SECURITY DEFINER | SECURITY DEFINER 指定存储过程将使用创建它的用户的特权来执行;这是默认值。为了符合 SQL 要求,允许使用关键字 EXTERNAL,但这是可选的。 |
[ EXTERNAL ] SECURITY INVOKER | SECURITY INVOKER 子句指示存储过程将使用调用它的用户的特权执行。为了符合 SQL 要求,允许使用关键字 EXTERNAL,但这是可选的。 |
AUTHID DEFINER AUTHID CURRENT_USER |
|
PARALLEL { UNSAFE | RESTRICTED | SAFE } | 通过 PARALLEL 子句可以使用并行顺序扫描(并行模式)。在查询期间,相比串行顺序扫描,并行顺序扫描使用多个工作线程并行扫描一个关系。
|
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 PROCEDURE simple_procedure
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END simple_procedure;
通过在PolarDB PostgreSQL版(兼容Oracle)中输入存储过程代码,将该存储过程存储在数据库中。
以下示例演示了如何在存储过程声明中使用 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 子句。