Amazon Redshift 中的存储过程概览
本主题详细介绍了存储过程的用途和用法。
存储过程通常用于针对数据转换、数据验证和特定于业务的逻辑来封装逻辑。通过将多个 SQL 步骤组合到一个存储过程中,可以减少应用程序与数据库之间的往返次数。
如需细粒度的访问控制,您可以创建存储过程来执行函数,而无需向用户提供对基础表的访问权限。例如,只有拥有者或超级用户可以截断表,用户需要写入权限来将数据插入到表中。您无需向用户提供对基础表的权限,而是可以创建执行该任务的存储过程。然后,向用户提供运行存储过程的权限。
具有 DEFINER 安全属性的存储过程使用存储过程拥有者的权限运行。默认情况下,存储过程具有 INVOKER 安全性,这意味着过程使用调用该过程的用户的权限。
要创建存储过程,请使用 CREATE PROCEDURE 命令。要运行过程,请使用 CALL 命令。此部分的后文中提供了示例。
注意
一些客户端在创建 Amazon Redshift 存储过程时可能会显示以下错误。
ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$
由于客户端无法使用分隔语句的分号和引用的美元符号 ($) 来正确解析 CREATE PROCEDURE 语句,因此会发生此错误。这导致只有此语句的一部分发送到 Amazon Redshift 服务器。通常,您可以通过使用客户端的 Run as batch
或 Execute selected
选项来解决此错误。
例如,使用 Aginity 客户端时,请使用 Run entire script as
batch
选项。使用 SQL Workbench/J 时,建议使用 124 版。使用 SQL Workbench/J 125 版时,请考虑指定备用分隔符作为解决方法。
CREATE PROCEDURE 包含以分号(;)分隔的 SQL 语句。定义备用分隔符 [如正斜杠(/)]并将其放在 CREATE PROCEDURE 语句的末尾,会将整个语句发送到 Amazon Redshift 服务器进行处理。以下为示例。
CREATE OR REPLACE PROCEDURE test() AS $$ BEGIN SELECT 1 a; END; $$ LANGUAGE plpgsql ; /
有关更多信息,请参阅 SQL Workbench/J 文档中的备用分隔符
以下示例显示没有输出参数的过程。默认情况下,参数是输入 (IN) 参数。
CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar) AS $$ BEGIN RAISE INFO 'f1 = %, f2 = %', f1, f2; END; $$ LANGUAGE plpgsql; call test_sp1(5, 'abc'); INFO: f1 = 5, f2 = abc CALL
注意
在编写存储过程时,我们建议使用最佳实践来保护敏感值:
不要在存储过程逻辑中硬编码任何敏感信息。例如,不要在存储过程主体的 CREATE USER 语句中分配用户密码。这会带来安全风险,因为硬编码值会作为架构元数据记录在目录表中。而是应通过参数将诸如密码之类的敏感值作为参量传递给存储过程。
有关存储过程的更多信息,请参阅 CREATE PROCEDURE 和在 Amazon Redshift 中创建存储过程。有关目录表的更多信息,请参阅系统目录表。
以下示例显示具有输出参数的过程。参数为 (IN)、输入和输出 (INOUT) 以及输出 (OUT)。
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; call test_sp2(2,'2019'); f2 | column2 ---------------------+--------- 2019+2019+2019+2019 | 2 (1 row)