SnowConvert AI - Redshift - CREATE PROCEDURE¶

描述¶

为当前数据库创建新的存储过程或替换现有过程。（Redshift SQL 语言参考 Create Procedure (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html)）。

有关过程子句的更多信息，请参阅以下定义：

ARGUMENTS MODE
POSITIONAL ARGUMENTS
NONATOMIC
PROCEDURE BODY
SECURITY (DEFINER | INVOKER)

语法¶

The following is the SQL syntax to create a Procedure in Amazon Redshift. See the Redshift CREATE PROCEDURE specification (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html) for this syntax.

 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 } ]       

示例源模式¶

输入代码：¶

Redshift¶

 CREATE PROCEDURE TEST_PROCEDURE()
LANGUAGE PLPGSQL
AS
$$
BEGIN
    NULL;
END;
$$;

输出代码：¶

Snowflake¶

 CREATE PROCEDURE TEST_PROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/07/2025",  "domain": "test" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;

ALIAS DECLARATION¶

描述¶

如果存储过程的签名省略了实参名称，则可以为该实参声明别名。

Snowflake 不支持此功能。

为了实现功能等效，将删除别名，并将重命名所有用法。

为无名参数声明别名时，将为该参数和用法创建生成的名称。当别名是带名称的参数时，别名将替换为真实的参数名称。

语法¶

 name ALIAS FOR $n;

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE test_procedure (integer)
LANGUAGE plpgsql
AS
$$
DECLARE
    first_alias ALIAS  FOR $1;
    second_alias ALIAS  FOR $1;
BEGIN
   INSERT INTO t1
   VALUES (first_alias + 1);
   INSERT INTO t1
   VALUES (second_alias + 2);
END;
$$;

--Notice the parameter already has a name
--and we are defining two alias to the same parameter
CREATE OR REPLACE PROCEDURE test_procedure (PARAMETER1 integer)
LANGUAGE plpgsql
AS
$$
DECLARE
    first_alias ALIAS  FOR $1;
    second_alias ALIAS  FOR $1;
BEGIN
   INSERT INTO t1
   VALUES (first_alias + 1);
   INSERT INTO t1
   VALUES (second_alias + 2);
END;
$$;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE test_procedure (SC_ARG1 integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
BEGIN
   INSERT INTO t1
   VALUES (:SC_ARG1 + 1);
   INSERT INTO t1
   VALUES (:SC_ARG1 + 2);
END;
$$;

--Notice the parameter already has a name
--and we are defining two alias to the same parameter
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "t1" **
CREATE OR REPLACE PROCEDURE test_procedure (PARAMETER1 integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
BEGIN
   INSERT INTO t1
   VALUES (:PARAMETER1 + 1);
   INSERT INTO t1
   VALUES (:PARAMETER1 + 2);
END;
$$;

已知问题¶

没有已知问题。

ARGUMENTS MODE¶

描述¶

Amazon Redshift 存储过程支持可在过程调用期间传递的参数。这些参数允许您提供输入值、检索输出值或将其用于输入和输出操作。以下是参数的类型、模式及其用法示例的详细说明。Snowflake 仅支持输入值。

IN（输入参数）¶

用途：用于向过程中传递参数值。

默认模式：如果未指定模式，则参数视为 IN。

行为：传递到过程中的值不能在过程内修改。

OUT（输出参数）¶

用途：用于从过程中返回值。

行为：可以在过程内修改参数并将其返回给调用者。您无法发送初始值。

INOUT（输入/输出参数）¶

用途：用于将值传递到过程中，并对其进行修改以返回更新的值。

行为：结合了 IN 和 OUT 的行为。无论输出如何，都必须发送初始值。

语法¶

 [ argname ] [ argmode ] argtype

示例源模式¶

输入代码：¶

Redshift¶

CREATE OR REPLACE PROCEDURE SP_PARAMS(
IN PARAM1 INTEGER,
OUT PARAM2 INTEGER,
INOUT PARAM3 INTEGER)
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE SP_PARAMS (PARAM1 INTEGER, PARAM2 OUT INTEGER, PARAM3 OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;

已知问题¶

没有已知问题。

PROCEDURE BODY¶

提示

SnowConvert does not support translation for PostgreSQL string constant definition in procedures. Use arrange option

描述¶

Like Redshift, Snowflake supports CREATE PROCEDURE using $$ procedure_logic $$ as the body. There is a difference in the Redshift syntax where a word can be inside the $$ like $word$ and used as a delimiter body like $word$ procedure_logic $word$. SnowConvert AI will transform it by removing the word, leaving the $$.

语法¶

 AS
$Alias$
  procedure_body
$Alias$ 

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE SP()
AS 
$somename$
BEGIN
   NULL;
END;
$somename$ 
LANGUAGE plpgsql;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE SP ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/07/2025",  "domain": "test" }}'
AS
$$
   BEGIN
      NULL;
   END;
$$;

已知问题¶

没有已知问题。

BLOCK STATEMENT¶

描述¶

PL/pgSQL 是一种块结构语言。过程的完整主体通过块进行定义，其中包含变量声明和 PL/pgSQL 语句。语句也可以是嵌套块或子块。

语法¶

 [ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
EXCEPTION
  WHEN OTHERS THEN
    statements
END [ label ];

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE MY_PROCEDURE() 
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE MY_PROCEDURE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/10/2025",  "domain": "test" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;

已知问题¶

没有已知问题。

DECLARE¶

描述¶

该段用于声明过程的所有变量（循环变量除外）。\ Redshift 支持在每个块语句中包含多个 DECLARE 段，但由于 Snowflake 不支持此行为，必须将它们合并为每个块的单个声明语句。

语法¶

 [ DECLARE declarations ]

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE first_procedure (first_parameter integer)
LANGUAGE plpgsql
    AS
$$
DECLARE
    i int := first_parameter;
BEGIN
   select i;
END;
$$;

CREATE OR REPLACE PROCEDURE second_procedure (first_parameter integer)
LANGUAGE plpgsql
    AS
$$
DECLARE
    i int := first_parameter;
DECLARE
    j int := first_parameter;
BEGIN
   select i;
END;
$$;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE first_procedure (first_parameter integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
    AS
$$
   DECLARE
      i int := first_parameter;
BEGIN
   select i;
END;
$$;

CREATE OR REPLACE PROCEDURE second_procedure (first_parameter integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
    AS
$$
   DECLARE
      i int := first_parameter;
      j int := first_parameter;
BEGIN
   select i;
END;
$$;

已知问题¶

没有已知问题。

EXCEPTION¶

描述¶

当发生异常并添加异常处理块时，可以编写 RAISE 语句和大多数其他 PL/pgSQL 语句。例如，您可以使用自定义消息引发异常，或者将记录插入到日志表中。

语法¶

 EXCEPTION
  WHEN OTHERS THEN
    statements

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE update_employee_sp() AS
$$
BEGIN
    select var;
EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'An exception occurred.';
END;
$$
LANGUAGE plpgsql;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE update_employee_sp ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
BEGIN
    select var;
EXCEPTION WHEN OTHER THEN
        CALL RAISE_MESSAGE_UDF('INFO', 'An exception occurred.');
        RAISE;
END;
$$;

已知问题¶

没有已知问题。

LABEL¶

描述¶

在 Redshift 中，标签用于限定块或使用 EXIT 或 END 语句。Snowflake 不支持标签。

警告

由于 Snowflake 不支持标签，因此将打印 EWI。

语法¶

 [<<label>>]
BEGIN
    ...
END [label]

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE test_procedure (first_parameter integer)
LANGUAGE plpgsql
AS
$$
    <<Begin_block_label>>
BEGIN
   INSERT INTO my_test_table
   VALUES (first_parameter);
END;
$$;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE test_procedure (first_parameter integer)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
   !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<Begin_block_label>> ***/!!!
BEGIN
   INSERT INTO my_test_table
   VALUES (:first_parameter);
END;
$$;

已知问题¶

没有已知问题。

NONATOMIC¶

描述¶

存储过程中每条语句之后的 NONATOMIC 提交。Snowflake 支持 AUTOCOMMIT 参数。AUTOCOMMIT 的默认设置为 TRUE（启用）。

启用 AUTOCOMMIT 后，显式事务外的每条语句都被视为隐式单语句事务内的语句。换言之，如果该语句成功，则自动提交，如果失败，则自动回滚。换句话说，Snowflake“默认”以 NONATOMIC 模式运行。

语法¶

 NONATOMIC

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE SP_NONATOMIC()
NONATOMIC 
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE SP_NONATOMIC ()
RETURNS VARCHAR
----** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--NONATOMIC
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/10/2025",  "domain": "test" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;

已知问题¶

没有已知问题。

POSITIONAL ARGUMENTS¶

描述¶

Redshift supports nameless parameters by referencing the parameters by their position using $. Snowflake does not support this behavior. To ensure functional equivalence, SnowConvert AI can convert those references by the parameter’s name if the name is present in the definition. If not, SnowConvert AI will generate a name for the parameter, and the uses will be replaced with the new name.

语法¶

$n

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE SP_POSITIONAL_REFERENCES(
INTEGER,
param2 INTEGER,
INTEGER)
AS 
$$
    DECLARE
        localVariable INTEGER := 0;
    BEGIN
        localVariable := $2 + $3 + $1;
    END;
$$ 
LANGUAGE plpgsql;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE SP_POSITIONAL_REFERENCES (SC_ARG1
INTEGER,
param2 INTEGER, SC_ARG3 INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS
$$
    DECLARE
        localVariable INTEGER := 0;
    BEGIN
        localVariable := param2 + SC_ARG3 + SC_ARG1;
    END;
$$;

已知问题¶

没有已知问题。

RAISE¶

描述¶

Use the RAISE level statement to report messages and raise errors.

(Redshift SQL Language Reference RAISE (https://docs.aws.amazon.com/es_es/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-messages-errors))

Note

RAISE are fully supported by Snowflake.

语法¶

 RAISE level 'format' [, variable [, ...]];

在 Amazon Redshift 中，RAISE 语句用于在控制台中生成消息或引发自定义异常。Redshift 允许您指定不同的 levels 来指示消息的严重性。在 Snowflake 中，可以使用用户定义的函数 (UDF) 来模拟此功能，该函数根据指定的级别调用控制台。

异常：\ 当级别为“EXCEPTION”时，会引发自定义异常，并显示一条通用消息：“要查看 EXCEPTIONMESSAGE，需要检查日志。” 异常代码为 -20002，用于通知用户可以在日志中找到自定义消息。这是由于在 Snowflake 中发送自定义异常时的限制。
警告：\ 如果级别为“WARNING”，则使用 SYSTEM$LOG_WARN 将警告消息打印到 Snowflake 的日志中，这有助于在不中断执行流程的情况下突出显示潜在问题。
信息：\ 对于任何其他级别（例如“INFO”），SYSTEM$LOG_INFO 用于将消息打印到控制台日志，从而在不造成严重中断的情况下提供有关系统状态的更详细反馈。

这种方法允许模拟 Redshift 的严重级别功能，使其适应 Snowflake 的语法和功能，同时保持灵活性，并控制执行期间产生的消息和异常。

限制

要在 Snowflake 中查看日志，必须具有特定的权限，例如 ACCOUNTADMIN 或 SECURITYADMIN 角色。
Snowflake 中的日志无法立即获取，在信息可见之前可能会稍有延迟。
异常中的个性化错误消息不会像在 Redshift 中那样显示。要查看自定义消息，必须直接访问日志。

For further information, please refer to the following page.

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE raise_example(IN user_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
	RAISE EXCEPTION 'User % not exists.', user_id;
END;
$$;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE raise_example (user_id INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
AS $$
BEGIN
	CALL RAISE_MESSAGE_UDF('EXCEPTION', 'User % not exists.', array_construct(:user_id));
END;
$$;

UDFs ¶

RAISE_MESSAGE_UDF¶

 CREATE OR REPLACE PROCEDURE RAISE_MESSAGE_UDF(LEVEL VARCHAR, MESSAGE VARCHAR, ARGS VARIANT)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
    DECLARE
        MY_EXCEPTION EXCEPTION (-20002, 'To view the EXCEPTION MESSAGE, you need to check the log.');
        SC_RAISE_MESSAGE VARCHAR;
    BEGIN
        SC_RAISE_MESSAGE := STRING_FORMAT_UDF(MESSAGE, ARGS);
        IF (LEVEL = 'EXCEPTION') THEN
            SYSTEM$LOG_ERROR(SC_RAISE_MESSAGE);
            RAISE MY_EXCEPTION;
        ELSEIF (LEVEL = 'WARNING') THEN
            SYSTEM$LOG_WARN(SC_RAISE_MESSAGE);
            RETURN 'Warning printed successfully';
        ELSE
            SYSTEM$LOG_INFO(SC_RAISE_MESSAGE);
            RETURN 'Message printed successfully';
        END IF;
    END;
$$;

STRING_FORMAT_UDF¶

 CREATE OR REPLACE FUNCTION PUBLIC.STRING_FORMAT_UDF(PATTERN VARCHAR, ARGS VARIANT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "udf",  "convertedOn": "02/11/2025",  "domain": "test" }}'
AS
$$
	var placeholder_str = "{%}";
	var result = PATTERN.replace(/(?<!%)%(?!%)/g, placeholder_str).replace("%%","%");
	for (var i = 0; i < ARGS.length; i++)
	{
		result = result.replace(placeholder_str, ARGS[i]);
	}
	return result;
$$;

已知问题¶

没有已知问题。

RETURN¶

描述¶

RETURN 语句从存储过程返回给调用者。（Redshift SQL 语言参考 Return (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-return)）。

将 return 语句从 Amazon Redshift 转换到 Snowflake 非常简单，只需考虑在 Snowflake 的 return 语句中添加 NULL 即可。

语法¶

 RETURN;

示例源模式¶

简单 case 语句¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 ()
AS
$$
BEGIN
   RETURN;
END
$$ LANGUAGE plpgsql;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/12/2025",  "domain": "test" }}'
AS
$$
BEGIN
  RETURN NULL;
END
$$;

当过程中包含输出参数时¶

SnowConvert AI returns a variant with parameters set up as output parameters. So, for each return, SnowConvert AI will add a variant as a return value.

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 (OUT output_value VARCHAR)
AS
$$
BEGIN
   RETURN;
END
$$ LANGUAGE plpgsql;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 (output_value OUT VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/16/2025",  "domain": "no-domain-provided" }}'
AS
$$
BEGIN
   RETURN NULL;
END
$$;

已知问题¶

没有已知问题。

SECURITY (DEFINER | INVOKER)¶

描述¶

Amazon Redshift 存储过程中的 SECURITY 子句定义了该过程执行时的访问控制和权限上下文。这决定了该过程是使用所有者（创建者）还是调用者（调用过程的用户）的权限。

语法¶

 [ { SECURITY INVOKER | SECURITY DEFINER } ]  

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE SP_SECURITY_INVOKER( )
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql
SECURITY INVOKER
;

CREATE OR REPLACE PROCEDURE SP_SECURITY_DEFINER( )
AS 
$$
     BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql
SECURITY DEFINER;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE SP_SECURITY_INVOKER ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/07/2025",  "domain": "test" }}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        NULL;
    END;
$$
;

CREATE OR REPLACE PROCEDURE SP_SECURITY_DEFINER ( )
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/07/2025",  "domain": "test" }}'
EXECUTE AS OWNER
AS
$$
    BEGIN
        NULL;
    END;
$$;

已知问题¶

没有已知问题。

VARIABLE DECLARATION¶

描述¶

Declare all variables in a block, except for loop variables, in the block's DECLARE section.

(Redshift SQL Language Reference Variable Declaration (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-structure.html#r_PLpgSQL-variable-declaration))

Note

Snowflake 完全支持变量声明。

语法¶

 DECLARE
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

在 Redshift 中，CONSTANT 关键字用于防止执行过程中的变量重赋值。由于 Snowflake 不支持此关键字，因此在转换过程中会将其删除。这不会影响功能，因为逻辑不应尝试对常量变量重赋值。

Redshift 中的 NOT NULL 约束条件可确保变量不能被赋值为空值，并且需要一个非空的默认值。由于 Snowflake 不支持此约束条件，因此在转换过程中会将其删除。但会保留默认值以确保功能正常。

带有 Refcursor 的变量声明会转换为 Resultset 类型，详见信息。

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE VARIABLE_DECLARATION()
LANGUAGE plpgsql
AS $$
DECLARE
    v_simple_int INT;	
    v_default_char CHAR(4) DEFAULT 'ABCD';
    v_default_float FLOAT := 10.00;
    v_constant_char CONSTANT CHAR(4) := 'ABCD';
    v_notnull VARCHAR NOT NULL DEFAULT 'Test default';
    v_refcursor REFCURSOR;
BEGIN
-- Procedure logic
END;
$$;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE VARIABLE_DECLARATION ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
        DECLARE
            v_simple_int INT;
            v_default_char CHAR(4) DEFAULT 'ABCD';
            v_default_float FLOAT := 10.00;
            v_constant_char CHAR(4) := 'ABCD';
            --** SSC-FDM-PG0012 - NOT NULL CONSTRAINT HAS BEEN REMOVED. ASSIGNING NULL TO THIS VARIABLE WILL NO LONGER CAUSE A FAILURE. **
            v_notnull VARCHAR DEFAULT 'Test default';
            v_refcursor RESULTSET;
BEGIN
            NULL;
-- Procedure logic
END;
$$;

已知问题 ¶

未发现任何问题。

TRANSACTIONS¶

COMMIT¶

描述¶

将当前事务提交到数据库。此命令使事务中的数据库更新永久生效。（Redshift SQL 语言参考 COMMIT (https://docs.aws.amazon.com/redshift/latest/dg/r_COMMIT.html)）

语法

COMMIT [WORK | TRANSACTION]

示例源模式¶

设置数据¶

Redshift¶

Query¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Snowflake¶

Query¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

带 TRANSACTION 关键字的 COMMIT¶

Snowflake 不支持 TRANSACTION 关键字。但是，由于它不会对功能产生影响，因此只会将其删除。

Redshift¶

Query¶

 COMMIT TRANSACTION;

Snowflake¶

Query¶

 COMMIT;

默认事务行为过程（无 NONATOMIC 子句）中的 COMMIT¶

To avoid out of scope transaction exceptions in Snowflake, the usages of COMMIT will be matched with BEGIN TRANSACTION.

当过程中有多个 COMMIT 语句时，将在每个 COMMIT 语句之后生成多个 BEGIN TRANSACTION 语句以模拟 Redshift 事务行为。

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test VALUES (a);
    COMMIT;
    INSERT INTO transaction_values_test VALUES (a + 1);
    COMMIT;
END
$$;

CALL transaction_test(120);

SELECT * FROM transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 120  |
| 121  |
+------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a);
    COMMIT;
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a + 1);
    COMMIT;
END
$$;

CALL transaction_test(120);

SELECT * FROM
    transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 120  |
| 121  |
+------+

带有 NONATOMIC 行为的过程中的 COMMIT¶

通过使用设置为 true 的会话参数 AUTOCOMMIT，在 Snowflake 中模拟 Redshift 中的 NONATOMIC 行为。

Since the AUTOCOMMIT session parameter is assumed to be true by SnowConvert AI, the COMMIT statement inside NONATOMIC procedures is left as is.

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE nonatomic_procedure(a int)
    NONATOMIC
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a + 2);
    INSERT INTO transaction_values_test values (a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 12   |
| 13   |
+------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE nonatomic_procedure (a int)
RETURNS VARCHAR
--    --** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--    NONATOMIC
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a + 2);
    INSERT INTO transaction_values_test
    values (:a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM
transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 12   |
| 13   |
+------+

已知问题¶

1. 嵌套过程调用中的 COMMIT

在 Redshift 中，如果在嵌套过程调用中指定了 COMMIT 语句，则该命令将提交当前和父作用域中先前语句中的所有待处理工作。Snowflake 不支持提交父作用域操作，当检测到这种情况时，会生成 FDM。

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test VALUES (a);
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    INSERT INTO transaction_values_test values (a + 2);
    CALL transaction_test(a + 3);
END
$$;

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a);
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    INSERT INTO transaction_values_test
    values (:a + 2);
    --** SSC-FDM-RS0006 - CALLED PROCEDURE CONTAINS USAGES OF COMMIT/ROLLBACK, MODIFYING THE CURRENT TRANSACTION IN CHILD SCOPES IS NOT SUPPORTED IN SNOWFLAKE **
    CALL transaction_test(:a + 3);
END
$$;

已知问题¶

没有已知问题。

ROLLBACK¶

描述¶

停止当前事务并丢弃该事务所做的所有更新。（Redshift SQL 语言参考 ROLLBACK (https://docs.aws.amazon.com/redshift/latest/dg/r_ROLLBACK.html)）

语法

ROLLBACK [WORK | TRANSACTION]

示例源模式¶

设置数据¶

Redshift¶

Query¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Snowflake¶

Query¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

带 TRANSACTION 关键字的 ROLLBACK¶

Snowflake 不支持 TRANSACTION 关键字。但是，由于它不会对功能产生影响，因此只会将其删除。

Redshift¶

Query¶

 ROLLBACK TRANSACTION;

Snowflake¶

Query¶

 ROLLBACK;

默认事务行为过程（无 NONATOMIC 子句）中的 ROLLBACK¶

To avoid out of scope transaction exceptions in Snowflake, the usages of ROLLBACK will be matched with BEGIN TRANSACTION.

当过程中存在多个事务控制语句时，将在每个语句后生成多个 BEGIN TRANSACTION 语句，以模拟 Redshift 事务行为。

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    COMMIT;
    insert into transaction_values_test values (80);
    insert into transaction_values_test values (55);
    ROLLBACK;
END
$$;

CALL transaction_test(120);

SELECT * FROM transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 120  |
+------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test values (:a);
    COMMIT;
    BEGIN TRANSACTION;
    insert into transaction_values_test values (80);
    insert into transaction_values_test values (55);
    ROLLBACK;
END
$$;

CALL transaction_test(120);

SELECT * FROM
    transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 120  |
+------+

带有 NONATOMIC 行为的过程中的 ROLLBACK¶

通过使用设置为 true 的会话参数 AUTOCOMMIT，在 Snowflake 中模拟 Redshift 中的 NONATOMIC 行为。

Since the AUTOCOMMIT session parameter is assumed to be true by SnowConvert AI, the ROLLBACK statement inside NONATOMIC procedures is left as is.

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE nonatomic_procedure(a int)
    NONATOMIC
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test values (a + 2);
    INSERT INTO transaction_values_test values (a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 10   |
| 11   |
| 12   |
| 13   |
+------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE nonatomic_procedure (a int)
RETURNS VARCHAR
--    --** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--    NONATOMIC
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test
    values (:a + 2);
    INSERT INTO transaction_values_test
    values (:a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM
transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 10   |
| 11   |
| 12   |
| 13   |
+------+

已知问题¶

1. 嵌套过程调用中的 ROLLBACK

在 Redshift 中，如果在嵌套过程调用中指定了 ROLLBACK 语句，则该命令将提交当前和父作用域中先前语句中的所有待处理工作。Snowflake 不支持提交父作用域操作，当检测到这种情况时，会生成 FDM。

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    ROLLBACK;
    INSERT INTO transaction_values_test values (a + 1);
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    CALL transaction_test(a + 3);
    COMMIT;
END
$$;

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test (a int)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    ROLLBACK;
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a + 1);
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test (a int)
RETURNS VARCHAR
    LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    --** SSC-FDM-RS0006 - CALLED PROCEDURE CONTAINS USAGES OF COMMIT/ROLLBACK, MODIFYING THE CURRENT TRANSACTION IN CHILD SCOPES IS NOT SUPPORTED IN SNOWFLAKE **
    CALL transaction_test(:a + 3);
    COMMIT;
END
$$;

2. DDL 语句的 ROLLBACK

In Snowflake, DDL statements perform an implicit commit whenever they are executed inside a procedure, making effective all the work before executing the DDL as well as the DDL itself. This causes the ROLLBACK statement to not be able to discard any changes before that point, this issue will be informed using an FDM.

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE rollback_ddl(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    CREATE TABLE someRollbackTable
    (
        col1 INTEGER
    );

    INSERT INTO someRollbackTable values (a);
    ROLLBACK;
END
$$;

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE rollback_ddl (a int)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    CREATE TABLE someRollbackTable
    (
        col1 INTEGER
    );
    BEGIN TRANSACTION;
    INSERT INTO someRollbackTable
    values (:a);
    --** SSC-FDM-RS0007 - DDL STATEMENTS PERFORM AN AUTOMATIC COMMIT IN SNOWFLAKE. ROLLBACK WILL NOT UNDO DDL-COMMITTED CHANGES. **
    ROLLBACK;
END
$$;

已知问题¶

没有已知问题。

TRUNCATE¶

描述¶

在不进行表扫描的情况下从表中删除所有行（Redshift SQL 语言参考 TRUNCATE (https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNCATE.html)）

语法

TRUNCATE [TABLE] table_name

示例源模式¶

设置数据¶

Redshift¶

Query¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Snowflake¶

Query¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

默认事务行为过程（无 NONATOMIC 子句）中的 TRUNCATE¶

由于 TRUNCATE 语句在执行时会自动提交其所在的事务，因此在 Snowflake 中，为模拟此行为，任何使用该语句的地方都会生成 COMMIT 语句。

由于生成了 COMMIT 语句，因此相同的 BEGIN TRANSACTION 语句生成将应用于 TRUNCATE。有关更多信息，请查看 COMMIT 转换规范。

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE truncate_in_procedure(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test VALUES (a);
    TRUNCATE TABLE transaction_values_test;
    INSERT INTO transaction_values_test VALUES (a + 12);
    COMMIT;
END
$$;

CALL truncate_in_procedure(10);

SELECT * FROM transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 22   |
+------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE truncate_in_procedure (a int)
RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a);
    TRUNCATE TABLE transaction_values_test;
    COMMIT;
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a + 12);
    COMMIT;
END
$$;

CALL truncate_in_procedure(10);

SELECT * FROM
    transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 22   |
+------+

带有 NONATOMIC 行为的过程中的 TRUNCATE¶

通过使用设置为 true 的会话参数 AUTOCOMMIT，在 Snowflake 中模拟 Redshift 中的 NONATOMIC 行为。

Since the AUTOCOMMIT session parameter is assumed to be true by SnowConvert AI, the TRUNCATE statement inside NONATOMIC procedures is left as is, there is no need to generate a COMMIT statement because every statement is automatically committed when executed.

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE nonatomic_procedure(a int)
    NONATOMIC
    LANGUAGE plpgsql
    AS $$
BEGIN
    TRUNCATE TABLE transaction_values_test;
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test values (a + 2);
    INSERT INTO transaction_values_test values (a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 10   |
| 11   |
| 12   |
| 13   |
+------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE nonatomic_procedure (a int)
RETURNS VARCHAR
--    --** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--    NONATOMIC
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    TRUNCATE TABLE transaction_values_test;
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test
    values (:a + 2);
    INSERT INTO transaction_values_test
    values (:a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM
transaction_values_test;

Result¶

+------+
| col1 |
+------+
| 10   |
| 11   |
| 12   |
| 13   |
+------+

已知问题¶

1. 嵌套过程调用中的 TRUNCATE

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test VALUES (a);
    TRUNCATE TABLE transaction_values_test;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    INSERT INTO transaction_values_test values (a + 2);
    CALL transaction_test(a + 3);
END
$$;

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    VALUES (:a);
    TRUNCATE TABLE transaction_values_test;
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    INSERT INTO transaction_values_test
    values (:a + 2);
    --** SSC-FDM-RS0006 - CALLED PROCEDURE CONTAINS USAGES OF COMMIT/ROLLBACK, MODIFYING THE CURRENT TRANSACTION IN CHILD SCOPES IS NOT SUPPORTED IN SNOWFLAKE **
    CALL transaction_test(:a + 3);
END
$$;

已知问题¶

没有已知问题。

CONDITIONS¶

CASE¶

描述¶

Redshift 中的 CASE 语句允许您根据条件返回值，从而在查询中启用条件逻辑。它有两种形式：简单形式和搜索形式。（Redshift SQL 语言参考 Conditionals:Case (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-conditionals-case)）。

简单 case 语句¶

简单 CASE 语句根据操作数的相等性来提供条件执行。

Note

Snowflake 完全支持简单 case 语句。

语法¶

 CASE search-expression
WHEN expression [, expression [ ... ]] THEN
  statements
[ WHEN expression [, expression [ ... ]] THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE proc1(x INT)
LANGUAGE plpgsql
AS $$
BEGIN
  CASE x
WHEN 1, 2 THEN
  NULL;
ELSE
  NULL;
END CASE;                  
END;
$$;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE proc1 (x INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/14/2025",  "domain": "test" }}'
AS $$
BEGIN
  CASE x
    WHEN 1 THEN
      NULL;
    WHEN 2 THEN
      NULL;
   ELSE
     NULL;
  END CASE;
END;
$$;

搜索 case 语句¶

Note

Snowflake 完全支持搜索 Case 语句。

语法¶

 CASE
WHEN boolean-expression THEN
  statements
[ WHEN boolean-expression THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

示例源模式¶

输入代码：¶

Redshift¶

 CREATE PROCEDURE PROC1 (paramNumber int)
LANGUAGE plpgsql
AS $$
DECLARE
    result VARCHAR(100);	
BEGIN
CASE
  WHEN paramNumber BETWEEN 0 AND 10 THEN
    result := 'value is between zero and ten';
  WHEN paramNumber BETWEEN 11 AND 20 THEN
    result := 'value is between eleven and twenty';
  END CASE;  
END;
$$;

输出代码：¶

Redshift¶

 CREATE PROCEDURE PROC1 (paramNumber int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
    DECLARE
      result VARCHAR(100);
      case_not_found EXCEPTION (-20002, 'Case not found.');
BEGIN
CASE
  WHEN paramNumber BETWEEN 0 AND 10 THEN
    result := 'value is between zero and ten';
  WHEN paramNumber BETWEEN 11 AND 20 THEN
    result := 'value is between eleven and twenty';
  ELSE
    RAISE case_not_found;
  END CASE;
END;
$$;

CASE（无 ELSE）¶

在 Redshift 中，当执行 CASE 表达式时，如果所有验证条件均不满足且未定义 ELSE，则会触发异常“CASE NOT FOUND”。在 Snowflake 中，代码会执行但不返回任何结果。在此场景中，为了在 Snowflake 中保持相同的功能，如果不满足任何 CASE 条件，将声明并执行同名异常。

Note

Snowflake 完全支持不含 ELSE 的 CASE。

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 (input_value INT)
AS $$
BEGIN
  CASE input_value
  WHEN 1 THEN
   NULL;
  END CASE;
END;
$$ LANGUAGE plpgsql;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 (input_value INT)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
    DECLARE
      case_not_found EXCEPTION (-20002, 'Case not found.');
BEGIN
  CASE input_value
  WHEN 1 THEN
   NULL;
  ELSE
   RAISE case_not_found;
  END CASE;
END;
$$;

已知问题¶

没有已知问题。

IF¶

描述¶

此语句允许您根据特定条件做出决定。（Redshift SQL 语言参考 Conditionals: IF (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-conditionals-if)）。

SnowConvert AI will add the parenthesis in the conditions and change the keyword ELSIF by ELSEIF since Redshift does not require the parenthesis in the conditions and ELSIF is the keyword.

语法¶

 IF boolean-expression THEN
  statements
[ ELSIF boolean-expression THEN
  statements
[ ELSIF boolean-expression THEN
  statements
    ...] ]
[ ELSE
  statements ]
END IF;

示例源模式¶

输入代码：¶

Redshift¶

 CREATE PROCEDURE PROC1 (paramNumber int)
LANGUAGE plpgsql
AS $$
DECLARE
    result VARCHAR(100);	
BEGIN
    IF paramNumber = 0 THEN
      result := 'zero';
    ELSIF paramNumber > 0 THEN
      result := 'positive';
    ELSIF paramNumber < 0 THEN
      result := 'negative';
    ELSE
      result := 'NULL';
    END IF;
END;
$$;

输出代码：¶

Redshift¶

 CREATE PROCEDURE PROC1 (paramNumber int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            result VARCHAR(100);
BEGIN
            IF (:paramNumber = 0) THEN
                result := 'zero';
            ELSEIF (:paramNumber > 0) THEN
                result := 'positive';
            ELSEIF (:paramNumber < 0) THEN
                result := 'negative';
              ELSE
                result := 'NULL';
            END IF;
END;
$$;

已知问题¶

没有已知问题。

LOOPS¶

描述¶

这些语句用于重复代码块直到达到指定条件。（Redshift SQL 语言参考 Loops (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-loops)）。

CONTINUE FOR LOOP WHILE EXIT

CONTINUE¶

描述¶

如果 CONTINUE 条件为 true，循环可以继续执行，如果为 false，则停止循环。（Redshift SQL 语言参考 Conditionals: CONTINUE (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-loops)）。

警告

Snowflake 对 CONTINUE 提供部分支持。

语法¶

 CONTINUE [ label ] [ WHEN expression ];

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 (x INT)
    LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := 0;
BEGIN
    <<simple_loop_when>>
    LOOP
        i := i + 1;
        CONTINUE WHEN i = 5;
        RAISE INFO 'i %', i;
        EXIT simple_loop_when WHEN (i >= x);
    END LOOP;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure11 (x INT)
    LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := 0;
BEGIN
    LOOP
        i := i + 1;
		IF (I = 5) THEN 
        	CONTINUE;
		END IF;
        RAISE INFO 'i %', i;
        EXIT WHEN (i >= x);
    END LOOP;
END;
$$;

Results¶


控制台输出
1
2
3
4
6
7

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE procedure1 (x INT)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
    		DECLARE
    			i INTEGER := 0;
BEGIN
    			--** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    LOOP
        i := i + 1;
        IF (:i = 5) THEN
        	CONTINUE;
        END IF;
        CALL RAISE_MESSAGE_UDF('INFO', 'i %', array_construct(:i));
        IF ((:i >= : x)) THEN
        	EXIT simple_loop_when;
        END IF;
    END LOOP simple_loop_when;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure11 (x INT)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
    		DECLARE
    			i INTEGER := 0;
BEGIN
    			--** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
    LOOP
        i := i + 1;
		IF (:I = 5) THEN
        	CONTINUE;
		END IF;
        CALL RAISE_MESSAGE_UDF('INFO', 'i %', array_construct(:i));
        IF ((:i >= : x)) THEN
        	EXIT;
        END IF;
    END LOOP;
END;
$$;

Results¶


控制台输出
1
2
3
4
6
7

已知问题¶

没有已知问题。

EXIT¶

描述¶

当 WHEN 语句中定义的条件为 true 时，停止循环执行（Redshift SQL 语言参考 Conditionals: EXIT (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-loops)）。

警告

Snowflake 对 EXIT 提供部分支持。

语法¶

 EXIT [ label ] [ WHEN expression ];

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE simple_loop_when(x int)
LANGUAGE plpgsql
AS $$
DECLARE i INTEGER := 0;
BEGIN
  <<simple_loop_when>>
  LOOP
    RAISE INFO 'i %', i;
    i := i + 1;
    EXIT simple_loop_when WHEN (i >= x);
  END LOOP;
END;
$$;   

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE simple_loop_when (x int)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
    DECLARE
      i INTEGER := 0;
BEGIN
      --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
  LOOP
        CALL RAISE_MESSAGE_UDF('INFO', 'i %', array_construct(:i));
    i := i + 1;
        IF ((:i >= : x)) THEN
          EXIT simple_loop_when;
        END IF;
  END LOOP simple_loop_when;
END;
$$;

已知问题¶

没有已知问题。

FOR¶

语法¶

整数变体

 [<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
  statements
END LOOP [ label ];

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 ()
AS $$
BEGIN
  FOR i IN 1..10 LOOP
    NULL;
  END LOOP;

  FOR i IN REVERSE 10..1 LOOP
    NULL;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
BEGIN
  FOR i IN 1 TO 10
                   --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                   LOOP
    NULL;
  END LOOP;

  FOR i IN REVERSE 10 TO 1
                           --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                           LOOP
    NULL;
  END LOOP;
END;
$$;

已知问题¶

没有已知问题。

LOOP¶

描述¶

一个简单的循环定义了一个无条件循环，该循环会无限期重复，直到被 EXIT 或 RETURN 语句终止。（Redshift SQL 语言参考 Conditionals:Simple Loop (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-loops)）。

警告

Snowflake 对简单循环提供部分支持。

语法¶

 [<<label>>]
LOOP
  statements
END LOOP [ label ];

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE simple_loop()
LANGUAGE plpgsql
AS $$
BEGIN
  <<simple_while>>
  LOOP
    RAISE INFO 'I am raised once';  
    EXIT simple_while;
    RAISE INFO 'I am not raised';
  END LOOP;
  RAISE INFO 'I am raised once as well';
END;
$$;   

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE simple_loop ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
BEGIN
  --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
  LOOP
    CALL RAISE_MESSAGE_UDF('INFO', 'I am raised once');
    EXIT simple_while;
    CALL RAISE_MESSAGE_UDF('INFO', 'I am not raised');
  END LOOP simple_while;
  CALL RAISE_MESSAGE_UDF('INFO', 'I am raised once as well');
END;
$$;

已知问题¶

没有已知问题。

WHILE¶

语法¶

 [<<label>>]
WHILE expression LOOP
  statements
END LOOP [ label ];

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE simple_loop_when()
    LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := 0;
BEGIN
    WHILE I > 5 AND I > 10 LOOP
        NULL;
    END LOOP;   
END;
$$;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE simple_loop_when ()
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
            DECLARE
                i INTEGER := 0;
BEGIN
                WHILE (:I > 5 AND : I > 10)
                                            --** SSC-PRF-0008 - PERFORMANCE REVIEW - LOOP USAGE **
                                            LOOP
        NULL;
    END LOOP;
END;
$$;

已知问题¶

没有已知问题。

CURSORS¶

CLOSE CURSOR¶

描述¶

关闭与 Open cursor 关联的所有空闲资源。（Redshift SQL 语言参考 Close Cursor (https://docs.aws.amazon.com/redshift/latest/dg/close.html)）。

Note

Snowflake 完全支持此语法。

语法¶

 CLOSE cursor

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE cursor_test()
AS $$
BEGIN
   CLOSE cursor1;
END;
$$;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE cursor_test ()
RETURNS VARCHAR
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/05/2025",  "domain": "test" }}'
AS $$
BEGIN
   CLOSE cursor1;
END;
$$;

已知问题¶

没有已知问题。

FETCH CURSOR¶

描述¶

使用游标检索行。（Redshift SQL 语言参考 Fetch (https://docs.aws.amazon.com/redshift/latest/dg/fetch.html)）

转换信息

 FETCH [ NEXT | ALL | {FORWARD [ count | ALL ] } ] FROM cursor

FETCH cursor INTO target [, target ...];

示例源模式¶

设置数据¶

Redshift¶

Query¶

 CREATE TABLE cursor_example
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

INSERT INTO cursor_example VALUES (10, 'hello');

Snowflake¶

Query¶

 CREATE TABLE cursor_example
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

INSERT INTO cursor_example VALUES (10, 'hello');

Fetch into¶

Redshift 中的 FETCH into 语句在 Snowflake 中完全等效

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE fetch_into_example()
LANGUAGE plpgsql
AS $$
DECLARE my_cursor CURSOR FOR
        SELECT col1, col2
        FROM cursor_example;
        some_id INT;
        message VARCHAR(20);
BEGIN
    OPEN my_cursor;
    FETCH my_cursor INTO some_id, message;
    CLOSE my_cursor;
    INSERT INTO cursor_example VALUES (some_id * 10, message || ' world!');
END;
$$;

CALL fetch_into_example();

SELECT * FROM cursor_example;

Result¶

+------+-------------+
| col1 | col2        |
+------+-------------+
| 10   | hello       |
| 100  | hello world!|
+------+-------------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE fetch_into_example ()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
    my_cursor CURSOR FOR
    SELECT col1, col2
    FROM
    cursor_example;
    some_id INT;
    message VARCHAR(20);
BEGIN
    OPEN my_cursor;
    FETCH my_cursor INTO some_id, message;
    CLOSE my_cursor;
    INSERT INTO cursor_example
			VALUES (:some_id * 10, :message || ' world!');
END;
$$;

CALL fetch_into_example();

SELECT * FROM
	cursor_example;

Result¶

+------+-------------+
| col1 | col2        |
+------+-------------+
| 10   | hello       |
| 100  | hello world!|
+------+-------------+

已知问题¶

1.不支持不带目标变量的 Fetch

Snowflake 需要 FETCH 语句指定 INTO 子句，该子句中包含将要存储提取的行值的变量。如果在代码中找到未带 INTO 子句的 FETCH 语句，将生成 EWI。

输入代码：

 FETCH FORWARD FROM cursor1;

输出代码：

 !!!RESOLVE EWI!!! /*** SSC-EWI-PG0015 - FETCH CURSOR WITHOUT TARGET VARIABLES IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FETCH FORWARD FROM cursor1;

已知问题¶

没有已知问题。

OPEN CURSOR¶

描述¶

在使用游标检索行之前，必须先将其打开。（Redshift SQL 语言参考 Open Cursor (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-cursors)）。

Note

Snowflake 完全支持此语法。

语法¶

 OPEN bound_cursor_name [ ( argument_values ) ];

示例源模式¶

设置数据¶

Redshift¶

Query¶

 CREATE TABLE cursor_example
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

CREATE TABLE cursor_example_results
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

INSERT INTO cursor_example VALUES (10, 'hello');

Snowflake¶

Query¶

 CREATE TABLE cursor_example
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

CREATE TABLE cursor_example_results
(
	col1 INTEGER,
	col2 VARCHAR(20)
);

INSERT INTO cursor_example VALUES (10, 'hello');

Open cursor（不带实参）¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE cursor_test()
AS $$
BEGIN
   OPEN cursor1;
END;
$$;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE cursor_test ()
RETURNS VARCHAR
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/05/2025",  "domain": "test" }}'
AS $$
BEGIN
   OPEN cursor1;
END;
$$;

Open cursor（带有实参）¶

Cursor arguments have to be bound per each one of its uses, SnowConvert AI will generate the bindings, as well as reorder and repeat the passed values to the OPEN statement as needed to satisfy the bindings.

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE cursor_open_test()
LANGUAGE plpgsql
AS $$
DECLARE
    cursor2 CURSOR (val1 VARCHAR(20), val2 INTEGER) FOR SELECT col1 + val2, col2 FROM cursor_example where val1 = col2 and val2 > col1;
    res1 INTEGER;
    res2 VARCHAR(20);
BEGIN
    OPEN cursor2('hello', 50);
    FETCH cursor2 INTO res1, res2;
    CLOSE cursor2;
    INSERT INTO cursor_example_results VALUES (res1, res2);
END;
$$;

call cursor_open_test();

SELECT * FROM cursor_example_results;

Result¶

+------+-------+
| col1 | col2  |
+------+-------+
| 60   | hello |
+------+-------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE cursor_open_test ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            cursor2 CURSOR FOR SELECT col1 + ?, col2 FROM
                cursor_example
            where
                ? = col2 and ? > col1;
            res1 INTEGER;
            res2 VARCHAR(20);
BEGIN
    OPEN cursor2 USING (50, 'hello', 50);
    FETCH cursor2 INTO res1, res2;
    CLOSE cursor2;
    INSERT INTO cursor_example_results
            VALUES (:res1, : res2);
END;
$$;

call cursor_open_test();
SELECT * FROM
cursor_example_results;

Result¶

+------+-------+
| col1 | col2  |
+------+-------+
| 60   | hello |
+------+-------+

带有过程参数或局部变量的 Open cursor¶

The procedure parameters or local variables have to be bound per each one of its uses in the cursor query, SnowConvert AI will generate the bindings and add the parameter or variable names to the OPEN statement, even if the cursor originally had no parameters.

Redshift¶

Query¶

 CREATE OR REPLACE PROCEDURE cursor_open_test(someValue iNTEGER)
LANGUAGE plpgsql
AS $$
DECLARE
    charVariable VARCHAR(20) DEFAULT 'hello';
    cursor2 CURSOR FOR SELECT col1 + someValue, col2 FROM cursor_example where charVariable = col2 and someValue > col1;
    res1 INTEGER;
    res2 VARCHAR(20);
BEGIN
    OPEN cursor2;
    FETCH cursor2 INTO res1, res2;
    CLOSE cursor2;
    INSERT INTO cursor_example_results VALUES (res1, res2);
END;
$$;

call cursor_open_test(30);

Result¶

+------+-------+
| col1 | col2  |
+------+-------+
| 40   | hello |
+------+-------+

Snowflake¶

Query¶

 CREATE OR REPLACE PROCEDURE cursor_open_test (someValue iNTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            charVariable VARCHAR(20) DEFAULT 'hello';
            cursor2 CURSOR FOR SELECT col1 + ?, col2 FROM
                cursor_example
            where
                ? = col2 and ? > col1;
            res1 INTEGER;
            res2 VARCHAR(20);
BEGIN
    OPEN cursor2 USING (someValue, charVariable, someValue);
    FETCH cursor2 INTO res1, res2;
    CLOSE cursor2;
    INSERT INTO cursor_example_results
            VALUES (:res1, : res2);
END;
$$;

call cursor_open_test(30);

Result¶

+------+-------+
| col1 | col2  |
+------+-------+
| 40   | hello |
+------+-------+

已知问题¶

没有已知问题。

DECLARE CURSOR¶

描述¶

定义新游标。使用游标一次从较大查询的结果集中检索几行。（Redshift SQL 语言参考 Declare Cursor (https://docs.aws.amazon.com/redshift/latest/dg/declare.html)）。

Note

Snowflake 完全支持此语法。

语法¶

 name CURSOR [ ( arguments ) ] FOR query 

示例源模式¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE cursor_test()
AS $$
DECLARE
   -- Declare the cursor
   cursor1 CURSOR FOR SELECT 1;
   cursor2 CURSOR (key integer) FOR SELECT 2 where 1 = key;
   
BEGIN
END;
$$;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE cursor_test ()
RETURNS VARCHAR
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
      DECLARE
         -- Declare the cursor
         cursor1 CURSOR FOR SELECT 1;
         cursor2 CURSOR FOR SELECT 2 where 1 = ?;
BEGIN
         NULL;
END;
$$;

已知问题¶

没有已知问题。

DECLARE REFCURSOR¶

描述¶

refcursor 数据类型仅包含对游标的引用。您可以通过将游标变量声明为 refcursor 类型的变量来创建游标变量

（Redshift SQL 语言参考 Refcursor Declaration (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-cursors)）

Note

Snowflake 完全支持 Refcursor 声明。

语法¶

 DECLARE
name refcursor;

由于 Snowflake 不支持 REFCURSOR 数据类型，因此可通过将 REFCURSOR 变量转换为 RESULTSET 类型来复制其功能。用于打开 REFCURSOR 的查询被分配给 RESULTSET 变量，之后会创建一个新游标并将其链接到 RESULTSET 变量。此外，游标逻辑中对原始 REFCURSOR 的所有引用都会更新为使用新游标，从而复制原始功能。

示例源模式¶

情况：单次使用¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR()
LANGUAGE plpgsql
AS $$
DECLARE
  v_curs1 refcursor;
BEGIN
  OPEN v_curs1 FOR SELECT column1_name, column2_name FROM your_table;
-- Cursor logic
  CLOSE v_curs1;
 END;
$$;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
  DECLARE
   v_curs1 RESULTSET;
BEGIN
   v_curs1 := (
    SELECT column1_name, column2_name FROM your_table
   );
   LET v_curs1_Resultset_1 CURSOR
   FOR
    v_curs1;
   OPEN v_curs1_Resultset_1;
-- Cursor logic
  CLOSE v_curs1_Resultset_1;
 END;
$$;

情况：带有动态 SQL 的游标 ¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR_DYNAMIC(min_salary NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
    cur refcursor;
    qry TEXT;
BEGIN
    qry := 'SELECT id, name FROM employees WHERE salary > ' || min_salary;

    OPEN cur FOR EXECUTE qry;
-- Cursor logic
    CLOSE cur;
END;
$$;


CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR_DYNAMIC2(min_salary NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
    cur refcursor;
BEGIN
    OPEN cur FOR EXECUTE 'SELECT id, name FROM employees WHERE salary > ' || min_salary;
-- Cursor logic
    CLOSE cur;
END;
$$;

输出代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR_DYNAMIC (min_salary NUMERIC)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
        DECLARE
            cur RESULTSET;
    qry TEXT;
BEGIN
    qry := 'SELECT id, name FROM employees WHERE salary > ' || min_salary;
            cur := (
                EXECUTE IMMEDIATE qry
            );
            LET cur_Resultset_1 CURSOR
            FOR
                cur;
            OPEN cur_Resultset_1;
-- Cursor logic
    CLOSE cur_Resultset_1;
END;
$$;


CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR_DYNAMIC2 (min_salary NUMERIC)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
        DECLARE
            cur RESULTSET;
BEGIN
            cur := (
                EXECUTE IMMEDIATE 'SELECT id, name FROM employees WHERE salary > ' || min_salary
            );
            LET cur_Resultset_2 CURSOR
            FOR
                cur;
            OPEN cur_Resultset_2;
-- Cursor logic
    CLOSE cur_Resultset_2;
END;
$$;

情况：多次使用：¶

输入代码：¶

Redshift¶

 CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR()
LANGUAGE plpgsql
AS $$
DECLARE
  v_curs1 refcursor;
BEGIN
  OPEN v_curs1 FOR SELECT column1_name, column2_name FROM your_table;
-- Cursor logic
  CLOSE v_curs1;
  OPEN v_curs1 FOR SELECT column3_name, column4_name FROM your_table2;
-- Cursor logic
  CLOSE v_curs1;
 END;
$$;

输出代码：¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE VARIABLE_REFCURSOR ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
AS $$
  DECLARE
   v_curs1 RESULTSET;
BEGIN
   v_curs1 := (
    SELECT column1_name, column2_name FROM your_table
   );
   LET v_curs1_Resultset_1 CURSOR
   FOR
    v_curs1;
   OPEN v_curs1_Resultset_1;
-- Cursor logic
  CLOSE v_curs1_Resultset_1;
   v_curs1 := (
    SELECT column3_name, column4_name FROM your_table2
   );
   LET v_curs1_Resultset_2 CURSOR
   FOR
    v_curs1;
   OPEN v_curs1_Resultset_2;
-- Cursor logic
  CLOSE v_curs1_Resultset_2;
 END;
$$;

已知问题¶

没有已知问题。

SnowConvert AI - Redshift - CREATE PROCEDURE¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

输出代码：¶

Snowflake¶

相关的 EWIs¶

ALIAS DECLARATION¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

输出代码：¶

Snowflake¶

已知问题¶

相关的 EWIs。¶

ARGUMENTS MODE¶

描述¶

IN（输入参数）¶

OUT（输出参数）¶

INOUT（输入/输出参数）¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

输出代码：¶

Snowflake¶

已知问题¶

相关的 EWIs¶

PROCEDURE BODY¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

输出代码：¶

Snowflake¶

已知问题¶

相关的 EWIs。¶

BLOCK STATEMENT¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

输出代码：¶

Snowflake¶

已知问题¶

相关的 EWIs。¶

DECLARE¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

输出代码：¶

Snowflake¶

已知问题¶

相关的 EWIs。¶

EXCEPTION¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

输出代码：¶

Snowflake¶

已知问题¶

相关的 EWIs。¶

LABEL¶

描述¶

语法¶

示例源模式¶

输入代码：¶

Redshift¶

输出代码：¶

Snowflake¶