Amazon Redshift
数据库开发人员指南
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

CREATE PROCEDURE

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

语法

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name ( [ [ argname ] [ argmode ] argtype [, ...] ] ) 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

SECURITY INVOKER | SECURITY DEFINER

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

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

对于 SECURITY DEFINER 模式,使用数据库权限作为过程的拥有者来运行该过程。调用该过程的用户需要具有该过程的执行权限,但不需要对基础对象具有任何权限。

SET configuration_parameter { TO value | = value }

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

示例

注意

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

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;

以下示例使用一个 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;

本页内容: