SnowConvert：Redshift CREATE PROCEDURE 语句¶

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)

语法¶

以下 SQL 语法可用于在 Amazon Redshift 中创建过程。点击此处 (https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html) 查看该语法的 Redshift 规范。

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

Copy

示例源模式¶

输入代码：¶

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

Copy

输出代码：¶

 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;
$$;

Copy

ALIAS DECLARATION¶

描述¶

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

Snowflake 不支持此功能。

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

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

语法¶

 name ALIAS FOR $n;

Copy

示例源模式¶

输入代码：¶

 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;
$$;

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

ARGUMENTS MODE¶

描述¶

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

IN（输入参数）¶

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

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

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

OUT（输出参数）¶

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

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

INOUT（输入/输出参数）¶

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

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

语法¶

 [ argname ] [ argmode ] argtype

Copy

示例源模式¶

输入代码：¶

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

Copy

输出代码：¶

 CREATE OR REPLACE PROCEDURE SP_PARAMS (PARAM1 INTEGER, PARAM2 INTEGER, PARAM3 INTEGER)
RETURNS VARIANT
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
        NULL;
        RETURN OBJECT_CONSTRUCT('PARAM2', :PARAM2, 'PARAM3', :PARAM3);
    END;
$$;

Copy

不支持的场景¶

输入代码：¶

CREATE OR REPLACE PROCEDURE SP_PARAMS(
OUT PARAM2 REFCURSOR
)
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

Copy

输出代码：¶

 CREATE OR REPLACE PROCEDURE SP_PARAMS (PARAM2 REFCURSOR !!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!)
RETURNS VARIANT
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
        NULL;
        RETURN OBJECT_CONSTRUCT('PARAM2', :PARAM2);
    END;
$$;

Copy

已知问题¶

没有已知问题。

PROCEDURE BODY¶

描述¶

与 Redshift 一样，Snowflake 支持使用 $$ procedure_logic $$ 作为主体的 CREATE PROCEDURE。Redshift 语法有所不同，其中单词可以像 $word$ 一样位于 $$ 内部，也可以像 $word$ procedure_logic $word$ 一样用作分隔符主体。SnowConvert 在对其进行转换时会删除 word，保留 $$。

语法¶

 AS
$Alias$
  procedure_body
$Alias$ 

Copy

示例源模式¶

输入代码：¶

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

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

BLOCK STATEMENT¶

描述¶

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

语法¶

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

Copy

示例源模式¶

输入代码：¶

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

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

DECLARE¶

描述¶

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

语法¶

 [ DECLARE declarations ]

Copy

示例源模式¶

输入代码：¶

 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;
$$;

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

EXCEPTION¶

描述¶

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

语法¶

 EXCEPTION
  WHEN OTHERS THEN
    statements

Copy

示例源模式¶

输入代码：¶

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

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

LABEL¶

描述¶

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

警告

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

语法¶

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

Copy

示例源模式¶

输入代码：¶

 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;
$$;

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

NONATOMIC¶

描述¶

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

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

语法¶

 NONATOMIC

Copy

示例源模式¶

输入代码：¶

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

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

POSITIONAL ARGUMENTS¶

描述¶

Redshift 通过使用 $ 按参数的位置引用参数来支持无名参数。Snowflake 不支持此行为。为确保功能等效，如果定义中存在参数名称，则SnowConvert 可以按参数名称转换这些引用。否则，SnowConvert 将为参数生成一个名称，并将用到该参数的地方替换为新名称。

语法¶

$n

Copy

示例源模式¶

输入代码：¶

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

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

RAISE¶

描述¶

使用 RAISE level 语句报告消息并引发错误。（Redshift SQL 语言参考 RAISE (https://docs.aws.amazon.com/es_es/redshift/latest/dg/c_PLpgSQL-statements.html#r_PLpgSQL-messages-errors)）

Snowflake 完全支持变量声明。

Grammar Syntax

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

Copy

在 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 中那样显示。要查看自定义消息，必须直接访问日志。

Sample Source Patterns

Input Code:

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

Copy

Output Code:

 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;
$$;

Copy

UDFs

 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;
$$;

Copy

 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;
$$;

Copy

Known Issues

没有已知问题。

Related EWIs.

无相关的 EWIs。

RETURN

Description

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 即可。

Grammar Syntax

 RETURN;

Copy

Sample Source Patterns

简单 case 语句

Input Code:

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

Copy

Output Code:

 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
$$;

Copy

当过程中包含输出参数时

SnowConvert 返回将参数设置为输出参数的变体。因此，对于每次返回，Snowconvert 都会添加一个变体作为返回值。

Input Code:

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

Copy

Output Code:

 CREATE OR REPLACE PROCEDURE procedure1 (output_value VARCHAR)
RETURNS VARIANT
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 OBJECT_CONSTRUCT('output_value', :output_value);
END
$$;

Copy

Known Issues

没有已知问题。

Related EWIs.

无相关的 EWIs。

SECURITY (DEFINER | INVOKER)

Description

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

Grammar Syntax

 [ { SECURITY INVOKER | SECURITY DEFINER } ]  

Copy

Sample Source Patterns

Input Code:

 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;

Copy

Output Code:

 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;
$$;

Copy

Known Issues

没有已知问题。

Related EWIs.

无相关的 EWIs。

VARIABLE DECLARATION

Description

在块的 DECLARE 段中，声明块中的所有变量（循环变量除外）。（Redshift SQL 语言参考变量声明 (https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-structure.html#r_PLpgSQL-variable-declaration)）

Snowflake 完全支持变量声明。

语法¶

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

Copy

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

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

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

示例源模式¶

输入代码：¶

 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;
$$;

Copy

输出代码：¶

 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;
$$;

Copy

已知问题 ¶

未发现任何问题。

TRANSACTIONS¶

COMMIT¶

描述¶

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

语法

COMMIT [WORK | TRANSACTION]

Copy

示例源模式¶

设置数据¶

Redshift¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Copy

Snowflake¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Copy

带 TRANSACTION 关键字的 COMMIT¶

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

Redshift¶

 COMMIT TRANSACTION;

Copy

Snowflake¶

 COMMIT;

Copy

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

为了避免 Snowflake 中出现超出范围的事务异常，COMMIT 的使用将与 BEGIN TRANSACTION 相匹配。

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

Redshift¶

 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;

Copy

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

Copy

Snowflake¶

 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;

Copy

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

Copy

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

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

由于 SnowConvert 假定 AUTOCOMMIT 会话参数为 true，因此 NONATOMIC 过程中的 COMMIT 语句将保持原样。

Redshift¶

 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;

Copy

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

Copy

Snowflake¶

 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;

Copy

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

Copy

已知问题¶

1. 嵌套过程调用中的 COMMIT

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

Redshift¶

 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
$$;

Copy

Snowflake¶

 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
$$;

Copy

已知问题¶

没有已知问题。

ROLLBACK¶

描述¶

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

语法

ROLLBACK [WORK | TRANSACTION]

Copy

示例源模式¶

设置数据¶

Redshift¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Copy

Snowflake¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Copy

带 TRANSACTION 关键字的 ROLLBACK¶

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

Redshift¶

 ROLLBACK TRANSACTION;

Copy

Snowflake¶

 ROLLBACK;

Copy

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

为了避免 Snowflake 中出现超出范围的事务异常，ROLLBACK 的使用将与 BEGIN TRANSACTION 相匹配。

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

Redshift¶

 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;

Copy

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

Copy

Snowflake¶

 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;

Copy

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

Copy

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

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

由于 SnowConvert 假定 AUTOCOMMIT 会话参数为 true，因此 NONATOMIC 过程中的 ROLLBACK 语句将保持原样。

Redshift¶

 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;

Copy

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

Copy

Snowflake¶

 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;

Copy

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

Copy

已知问题¶

1. 嵌套过程调用中的 ROLLBACK

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

Redshift¶

 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
$$;

Copy

Snowflake¶

 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
$$;

Copy

2. DDL 语句的 ROLLBACK

在 Snowflake 中，每当在过程中执行 DDL 语句时，该语句都会进行隐式提交，从而使执行 DDL 之前的所有工作以及 DDL 本身都生效。这会导致 ROLLBACK 语句无法撤销在此之前的任何更改，该问题将通过 FDM 进行通知。

Redshift¶

 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
$$;

Copy

Snowflake¶

 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, ROLLBACK WILL NOT WORK AS EXPECTED **
    ROLLBACK;
END
$$;

Copy

已知问题¶

没有已知问题。

TRUNCATE¶

描述¶

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

语法

TRUNCATE [TABLE] table_name

Copy

示例源模式¶

设置数据¶

Redshift¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Copy

Snowflake¶

 CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Copy

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

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

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

Redshift¶

 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;

Copy

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

Copy

Snowflake¶

 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;

Copy

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

Copy

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

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

由于 SnowConvert 假定 AUTOCOMMIT 会话参数为 true，因此 NONATOMIC 过程中的 TRUNCATE 语句将保持原样，无需生成 COMMIT 语句，因为每条语句在执行时都会自动提交。

Redshift¶

 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;

Copy

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

Copy

Snowflake¶

 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;

Copy

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

Copy

已知问题¶

1. 嵌套过程调用中的 TRUNCATE

Redshift¶

 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
$$;

Copy

Snowflake¶

 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
$$;

Copy

已知问题¶

没有已知问题。

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 语句根据操作数的相等性来提供条件执行。

Snowflake 完全支持简单 case 语句。

Grammar Syntax

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

Copy

Sample Source Patterns

Input Code:

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

Copy

Output Code:

 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;
$$;

Copy

搜索 case 语句

Snowflake 完全支持搜索 Case 语句。

语法¶

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

Copy

示例源模式¶

输入代码：¶

 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;
$$;

Copy

输出代码：¶

 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": "02/10/2025",  "domain": "test" }}'
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;
$$;

Copy

CASE（无 ELSE）¶

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

Snowflake 完全支持不含 ELSE 的 CASE。

Input Code:

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

Copy

Output Code:

 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;
$$;

Copy

Known Issues

没有已知问题。

Related EWIs.

无相关的 EWIs。

IF

Description

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

由于 Redshift 不要求条件中包含括号，并且 ELSIF 是关键字，因此 SnowConvert 将在条件中添加括号并将关键字 ELSIF 更改为 ELSEIF。

Grammar Syntax

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

Copy

Sample Source Patterns

Input Code:

 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;
$$;

Copy

Output Code:

 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": "02/10/2025",  "domain": "test" }}'
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;
$$;

Copy

Known Issues

没有已知问题。

Related EWIs.

无相关的 EWIs。

LOOPS

Description

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

CONTINUE FOR LOOP WHILE EXIT

CONTINUE

Description

如果 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 ];

Copy

示例源模式¶

输入代码：¶

 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;
$$;

Copy

控制台输出
1
2
3
4
6
7

输出代码：¶

 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": "02/17/2025",  "domain": "test" }}'
AS $$
            DECLARE
                i INTEGER := 0;
BEGIN
                !!!RESOLVE EWI!!! /*** SSC-EWI-0094 - LABEL DECLARATION FOR A STATEMENT IS NOT SUPPORTED BY SNOWFLAKE SCRIPTING <<simple_loop_when>> ***/!!!
    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;
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": "02/17/2025",  "domain": "test" }}'
AS $$
            DECLARE
                i INTEGER := 0;
BEGIN
    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;
$$;

Copy

控制台输出
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 ];

Copy

示例源模式¶

输入代码：¶

 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;
$$;   

Copy

输出代码：¶

 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": "03/03/2025",  "domain": "test" }}'
AS $$
    DECLARE
      i INTEGER := 0;
BEGIN
  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;
$$;

Copy

已知问题¶

没有已知问题。

FOR¶

语法¶

整数变体

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

Copy

示例源模式¶

输入代码：¶

 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;

Copy

输出代码：¶

 CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
BEGIN
  FOR i IN 1 TO 10 LOOP
    NULL;
  END LOOP;

  FOR i IN REVERSE 10 TO 1 LOOP
    NULL;
  END LOOP;
END;
$$;

Copy

已知问题¶

没有已知问题。

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

Copy

示例源模式¶

输入代码：¶

 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;
$$;   

Copy

输出代码：¶

 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": "03/03/2025",  "domain": "test" }}'
AS $$
BEGIN
  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;
$$;

Copy

已知问题¶

没有已知问题。

WHILE¶

语法¶

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

Copy

示例源模式¶

输入代码：¶

 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;
$$;

Copy

输出代码：¶

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

Copy

已知问题¶

没有已知问题。

CURSORS¶

CLOSE CURSOR¶

描述¶

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

Snowflake 完全支持此语法。

Grammar Syntax

 CLOSE cursor

Copy

Sample Source Patterns

Input Code:

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

Copy

Output Code:

 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;
$$;

Copy

Known Issues

没有已知问题。

Related EWIs.

无相关的 EWIs。

FETCH CURSOR

Description

使用游标检索行。（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 ...];

Copy

Sample Source Patterns

Setup data

Redshift

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

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

Copy

Snowflake

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

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

Copy

Fetch into

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

Redshift

 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;

Copy

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

Copy

Snowflake

 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;

Copy

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

Copy

Known Issues

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

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

输入代码：

 FETCH FORWARD FROM cursor1;

Copy

输出代码：

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

Copy

Known Issues

没有已知问题。

Related EWIs

SSC-EWI-PG0015：Snowflake 不支持不带目标变量的 Fetch cursor

OPEN CURSOR

Description

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

Snowflake 完全支持此语法。

语法¶

 OPEN bound_cursor_name [ ( argument_values ) ];

Copy

示例源模式¶

设置数据¶

Redshift¶

 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');

Copy

Snowflake¶

 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');

Copy

Open cursor（不带实参）¶

输入代码：¶

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

Copy

输出代码：¶

 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;
$$;

Copy

Open cursor（带有实参）¶

Cursor 参数必须根据其每种用途进行绑定，SnowConvert 将生成绑定，并根据需要对传递给 OPEN 语句的值进行重新排序和重复，以满足绑定要求。

Redshift¶

 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;

Copy

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

Copy

Snowflake¶

 CREATE OR REPLACE PROCEDURE cursor_open_test()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
    cursor2 CURSOR FOR SELECT col1 + ?, col2 FROM
        cursor_example
    where
        RTRIM(?) = RTRIM( 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;

Copy

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

Copy

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

过程参数或局部变量必须根据其在游标查询中的每种用途进行绑定，即使游标最初没有参数，SnowConvert 也会生成绑定并将参数或变量名添加到 OPEN 语句中。

Redshift¶

 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);

Copy

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

Copy

Snowflake¶

 CREATE OR REPLACE PROCEDURE cursor_open_test (someValue iNTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
    charVariable VARCHAR(20) DEFAULT 'hello';
    cursor2 CURSOR FOR SELECT col1 + ?, col2 FROM
        cursor_example
    where
        RTRIM(?) = RTRIM( 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);

Copy

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

Copy

已知问题¶

没有已知问题。

DECLARE CURSOR¶

描述¶

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

Snowflake 完全支持此语法。

Grammar Syntax

 name CURSOR [ ( arguments ) ] FOR query 

Copy

Sample Source Patterns

Input Code:

 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;
$$;

Copy

Output Code:

 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;
$$;

Copy

Known Issues

没有已知问题。

Related EWIs.

无相关的 EWIs。

DECLARE REFCURSOR

Description

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

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

Snowflake 完全支持 Refcursor 声明。

语法¶

 DECLARE
name refcursor;

Copy

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

示例源模式¶

情况：单次使用¶

输入代码：¶

 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;
$$;

Copy

输出代码：¶

 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;
$$;

Copy

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

输入代码：¶

 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;
$$;

Copy

输出代码：¶

 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;
$$;

Copy

情况：多次使用：¶

输入代码：¶

 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;
$$;

Copy

输出代码：¶

 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;
$$;

Copy

已知问题¶

没有已知问题。

SnowConvert：Redshift CREATE PROCEDURE 语句¶

CREATE PROCEDURE¶

描述¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

相关的 EWIs¶

ALIAS DECLARATION¶

描述¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

已知问题¶

相关的 EWIs。¶

ARGUMENTS MODE¶

描述¶

IN（输入参数）¶

OUT（输出参数）¶

INOUT（输入/输出参数）¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

不支持的场景¶

输入代码：¶

输出代码：¶

已知问题¶

相关的 EWIs¶

PROCEDURE BODY¶

描述¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

已知问题¶

相关的 EWIs。¶

BLOCK STATEMENT¶

描述¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

已知问题¶

相关的 EWIs。¶

DECLARE¶

描述¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

已知问题¶

相关的 EWIs。¶

EXCEPTION¶

描述¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

已知问题¶

相关的 EWIs。¶

LABEL¶

描述¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

已知问题¶

相关的 EWIs¶

NONATOMIC¶

描述¶

语法¶

示例源模式¶

输入代码：¶

输出代码：¶

已知问题¶

相关的 EWIs。¶

POSITIONAL ARGUMENTS¶

描述¶