CREATE PROCEDURE - Amazon Redshift
所需的权限语法参数使用说明示例
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

CREATE PROCEDURE

创建新的存储过程或者替换当前数据库的现有过程。

有关更多信息以及示例，请参阅 在 Amazon Redshift 中创建存储过程。

所需的权限

您必须通过以下方式之一获得权限，才能运行 CREATE OR REPLACE PROCEDURE：

  • CREATE PROCEDURE：

    • Superuser

    • 具有 CREATE [ OR REPLACE ] PROCEDURE 权限的用户

  • REPLACE PROCEDURE：

    • Superuser

    • 具有 CREATE [ OR REPLACE ] PROCEDURE 权限的用户

    • 程序拥有者

语法

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

参数

OR REPLACE

一个子句，指定如果某个过程与已存在的此过程具有相同的名称和输入参数数据类型或签名，则替换现有的过程。您只能将某个过程替换为定义一组相同数据类型的新过程。

如果您定义的过程与现有过程具有相同的名称，但签名不同，则您将创建新的过程。换句话说，过程名称已重载。有关更多信息，请参阅 重载过程名称。

sp_procedure_name

过程的名称。如果您指定 schema 名称（例如 myschema.myprocedure），则在指定的 schema 中创建该过程。否则，将在当前 schema 中创建过程。有关有效名称的更多信息，请参阅名称和标识符。

我们建议您将所有的存储过程名称添加前缀 sp_。Amazon Redshift 保留 sp_ 前缀，用于存储过程名称。通过使用前缀 sp_，可以确保存储过程名称不会与任何现有或将来的 Amazon Redshift 内置存储过程或函数名称冲突。有关更多信息，请参阅 命名存储过程。

如果输入参数的数据类型或签名不同，您可以定义多个具有相同名称的过程。换句话说，在这种情况下过程名称会重载。有关更多信息，请参阅重载过程名称

[argname] [ argmode] argtype

参数名称、参数模式和数据类型的列表。仅需要数据类型。名称和模式是可选的，可以切换它们的位置。

参数模式可以是 IN、OUT 或 INOUT。默认值为 IN。

您可以使用 OUT 和 INOUT 参数从过程调用中返回一个或多个值。当存在 OUT 或 INOUT 参数时，过程调用返回一个包含 n 列的结果行，其中 n 是 OUT 或 INOUT 参数的总数。

INOUT 参数同时是输入和输出参数。输入参数 包括 IN 和 INOUT 参数，而输出参数 包括 OUT 和 INOUT 参数。

OUT 参数未指定为 CALL 语句的一部分。在存储过程 CALL 语句中指定 INOUT 参数。当从嵌套调用传递和返回值时，以及返回 refcursor 时，INOUT 参数很有用。有关 refcursor 类型的更多信息，请参阅 游标。

参数数据类型可以是任何标准的 Amazon Redshift 数据类型。另外，参数数据类型可以是 refcursor。

您最多可以指定 32 个输入参数和 32 个输出参数。

AS $$ procedure_body $$

包含要执行的过程的构造。需要文字关键字 AS $$ 和 $$。

Amazon Redshift 要求您使用称为“美元引号”的格式，在您的过程中包含语句。包含的任何内容将按原样传递。您不必对任何特殊字符进行转义，因为字符串的内容是按照其字面涵义编写的。

通过美元引号 格式，您可以使用一对美元符号 ($$) 来指示要运行的语句的开头和结尾，如以下示例所示。

$$ my statement $$

（可选）在每对美元符号之间，可以指定字符串来帮助识别语句。您使用的字符串必须在括起字符对的开始和结束都是相同的。该字符串区分大小写，它遵循与不带括起字符的标识符相同的约束，但有一点除外，它不能包含美元符号。以下示例使用字符串 test。

$test$ my statement $test$

此语法对嵌套的美元引号也很有用。有关“美元引号”格式的更多信息，请参阅 PostgreSQL 文档的词法结构中的“使用美元符号括起的常量字符串”。

procedure_body

一组有效的 PL/pgSQL 语句。PL/pgSQL 语句使用程序性结构（包括循环和条件表达式）增强 SQL 命令，以控制逻辑流。可以在过程主体中使用大部分 SQL 命令，包括数据修改语言 (DML)（例如 COPY、UNLOAD 和 INSERT）以及数据定义语言 (DDL)（例如 CREATE TABLE）。有关更多信息，请参阅 PL/pgSQL 语言参考。

LANGUAGE plpgsql

语言值。指定 plpgsql。您必须具有使用 plpgsql 语言的权限。有关更多信息，请参阅 GRANT。

NONATOMIC

在非原子事务模式下创建存储过程。NONATOMIC 模式会自动提交过程内部的语句。此外，当 NONATOMIC 过程内部出现错误时，如果错误由异常块处理，则不会重新引发错误。有关更多信息，请参阅 管理事务和 RAISE。

当您将存储过程定义为 NONATOMIC 时，请考虑以下各项：

  • 当您进行嵌套的存储过程调用时，所有过程都必须在相同的事务模式下创建。

  • 在 NONATOMIC 模式下创建过程时，不支持 SECURITY DEFINER 选项和 SET configuration_parameter 选项。

  • 任何打开的游标（显式或隐式）在处理隐式提交时会自动关闭。因此，您必须在开始游标循环之前打开显式事务，以确保不会隐式提交循环迭代中的任何 SQL。

SECURITY INVOKER | SECURITY DEFINER

指定 NONATOMIC 时不支持 SECURITY DEFINER 选项。

该过程的安全模式确定过程在运行时的访问权限。该过程必须具有访问基础数据库对象的权限。

对于 SECURITY INVOKER 模式，该过程使用调用该过程的用户的权限。用户必须对基础数据库对象具有显式权限。默认值为 SECURITY INVOKER。

对于 SECURITY DEFINER 模式，此过程使用过程拥有者的权限。过程拥有者定义为在运行时拥有此过程的用户，而不一定是最初定义此过程的用户。调用该过程的用户需要具有该过程的执行权限，但不需要对基础对象具有任何权限。

SET configuration_parameter { TO value | = value }

指定 NONATOMIC 时不支持这些选项。

输入过程时，SET 子句会将指定的 configuration_parameter 设置为指定的值。然后，当该过程退出时，该子句会将 configuration_parameter 还原为其早期值。

使用说明

如果存储过程是使用 SECURITY DEFINER 选项创建的，则在存储过程中调用 CURRENT_USER 函数时，Amazon Redshift 会返回存储过程拥有者的用户名。

示例

注意

如果在运行这些示例时，您遇到了类似于下文的错误：

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

请参阅 Amazon Redshift 中的存储过程概览。

以下示例创建带有两个输入参数的过程。

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;
注意

在编写存储过程时，我们建议使用最佳实践来保护敏感值：

不要在存储过程逻辑中对任何敏感信息进行硬编码。例如，不要在存储过程主体的 CREATE USER 语句中分配用户密码。这会带来安全风险，因为硬编码值可以作为架构元数据记录在目录表中。而是应通过参数将诸如密码之类的敏感值作为参量传递给存储过程。

有关存储过程的更多信息，请参阅 CREATE PROCEDURE 和在 Amazon Redshift 中创建存储过程。有关目录表的更多信息，请参阅系统目录表。

以下示例使用一个 IN 参数、一个 OUT 参数和一个 INOUT 参数创建一个过程。

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;