Snowflake存储过程嵌套循环中全局变量更新不生效问题问询

问题场景 #

在Snowflake SQL存储过程中，出现以下异常行为：

• 在DECLARE块声明全局变量WORKING_DT，用于记录当前迭代日期
• 外层WHILE循环按日期递增更新WORKING_DT，内层FOR循环遍历数组/表记录，将当前WORKING_DT追加到结果数组
• 实际执行后，结果数组仅包含第一次WHILE循环的日期值，第二次WHILE循环的内层FOR循环未执行

示例1：遍历数组的嵌套循环 #

存储过程代码 #

CREATE OR REPLACE PROCEDURE DBNAME.SCHEMANAME.PROCEDURENAME(
FIELD1 DATE,
FIELD2 DATE
)
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  COUNTER := 0;
  START_DT DATE;
  END_DT DATE;  
  WORKING_DT DATE;
  WORKING_DT_ARRAY := [];
  WITHIN_FORLOOP_ARRAY := [];
  ELEMENTS_FOR_FORLOOP_ARRAY := [1,2,3,4,5];
BEGIN
  WORKING_DT := $FIELD1;
  START_DT := $FIELD1;
  END_DT := $FIELD2;
  
  WHILE (WORKING_DT <= END_DT) DO
    FOR ELEMENT IN ELEMENTS_FOR_FORLOOP_ARRAY DO
      WITHIN_FORLOOP_ARRAY := ARRAY_APPEND(WITHIN_FORLOOP_ARRAY, WORKING_DT);
    END FOR;

    WORKING_DT_ARRAY := ARRAY_APPEND(WORKING_DT_ARRAY, WORKING_DT);
    WORKING_DT := DATEADD(DAY, 1, WORKING_DT);
    COUNTER := COUNTER+1;
  END WHILE;
  RETURN WITHIN_FORLOOP_ARRAY;
END;

调用结果 #

-- 返回仅包含第一次日期的数组
-- ['2024-01-01','2024-01-01','2024-01-01','2024-01-01','2024-01-01']
CALL DBNAME.SCHEMANAME.PROCEDURENAME('2024-01-01', '2024-01-02');

示例2：遍历表记录的嵌套循环 #

存储过程代码 #

CREATE OR REPLACE PROCEDURE DBNAME.SCHEMANAME.PROCEDURENAME(
FIELD1 DATE,
FIELD2 DATE
)
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  COUNTER := 0;
  START_DT DATE;
  END_DT DATE;  
  WORKING_DT DATE;
  WORKING_DT_ARRAY := [];
  WITHIN_FORLOOP_ARRAY := [];
  TABLEA RESULTSET DEFAULT (SELECT FIELDA, FIELDB FROM DBNAME.SCHEMANAME.TABLEA);
BEGIN
  WORKING_DT := $FIELD1;
  START_DT := $FIELD1;
  END_DT := $FIELD2;
  
  WHILE (WORKING_DT <= END_DT) DO
    FOR RECORDS IN TABLEA DO
      WITHIN_FORLOOP_ARRAY := ARRAY_APPEND(WITHIN_FORLOOP_ARRAY, WORKING_DT);
    END FOR;

    WORKING_DT_ARRAY := ARRAY_APPEND(WORKING_DT_ARRAY, WORKING_DT);
    WORKING_DT := DATEADD(DAY, 1, WORKING_DT);
    COUNTER := COUNTER+1;
  END WHILE;
  RETURN WITHIN_FORLOOP_ARRAY;
END;

调用结果 #

-- 返回仅包含第一次日期的数组，与预期的10个元素不符
-- ['2024-01-01','2024-01-01','2024-01-01','2024-01-01','2024-01-01']
CALL DBNAME.SCHEMANAME.PROCEDURENAME('2024-01-01', '2024-01-02');

用户疑问 #

1. 为何WHILE循环内更新的WORKING_DT值无法传递到后续的FOR循环中？
2. 为何WHILE循环仅触发1次内层FOR循环，而非预期的2次？

问题根因 #

Snowflake SQL存储过程中，基于数组或预定义结果集的FOR循环，其迭代源的游标/迭代器只能被遍历一次：

• 示例1中，ELEMENTS_FOR_FORLOOP_ARRAY是DECLARE块定义的静态数组，第一次FOR循环已遍历完所有元素，迭代器移动到数组末尾；第二次WHILE循环执行时，FOR循环无剩余元素可遍历，因此不会进入循环体，WORKING_DT的更新自然无法体现。
• 示例2中，TABLEA是DECLARE块预计算的结果集，第一次FOR循环已耗尽结果集游标；第二次WHILE循环时，无法重置游标重新遍历，因此FOR循环不执行。

注：WORKING_DT_ARRAY能正确返回两个日期，说明WHILE循环确实执行了2次，问题仅出在第二次循环的内层FOR未触发。

解决方案 #

针对不同迭代源，采用以下修复方式：

1. 数组迭代源（示例1） #

每次WHILE循环内重新初始化数组，保证FOR循环能遍历完整元素：

CREATE OR REPLACE PROCEDURE DBNAME.SCHEMANAME.PROCEDURENAME(
FIELD1 DATE,
FIELD2 DATE
)
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  COUNTER := 0;
  START_DT DATE;
  END_DT DATE;  
  WORKING_DT DATE;
  WORKING_DT_ARRAY := [];
  WITHIN_FORLOOP_ARRAY := [];
BEGIN
  WORKING_DT := $FIELD1;
  START_DT := $FIELD1;
  END_DT := $FIELD2;
  
  WHILE (WORKING_DT <= END_DT) DO
    -- 每次循环重新定义数组，生成新的迭代器
    LET ELEMENTS_FOR_FORLOOP_ARRAY := [1,2,3,4,5];
    FOR ELEMENT IN ELEMENTS_FOR_FORLOOP_ARRAY DO
      WITHIN_FORLOOP_ARRAY := ARRAY_APPEND(WITHIN_FORLOOP_ARRAY, WORKING_DT);
    END FOR;

    WORKING_DT_ARRAY := ARRAY_APPEND(WORKING_DT_ARRAY, WORKING_DT);
    WORKING_DT := DATEADD(DAY, 1, WORKING_DT);
    COUNTER := COUNTER+1;
  END WHILE;
  RETURN WITHIN_FORLOOP_ARRAY;
END;

2. 结果集迭代源（示例2） #

每次WHILE循环内重新执行查询生成结果集，避免游标耗尽问题：

CREATE OR REPLACE PROCEDURE DBNAME.SCHEMANAME.PROCEDURENAME(
FIELD1 DATE,
FIELD2 DATE
)
RETURNS ARRAY
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  COUNTER := 0;
  START_DT DATE;
  END_DT DATE;  
  WORKING_DT DATE;
  WORKING_DT_ARRAY := [];
  WITHIN_FORLOOP_ARRAY := [];
BEGIN
  WORKING_DT := $FIELD1;
  START_DT := $FIELD1;
  END_DT := $FIELD2;
  
  WHILE (WORKING_DT <= END_DT) DO
    -- 每次循环重新执行查询，生成新的结果集
    LET TABLEA RESULTSET := (SELECT FIELDA, FIELDB FROM DBNAME.SCHEMANAME.TABLEA);
    FOR RECORDS IN TABLEA DO
      WITHIN_FORLOOP_ARRAY := ARRAY_APPEND(WITHIN_FORLOOP_ARRAY, WORKING_DT);
    END FOR;

    WORKING_DT_ARRAY := ARRAY_APPEND(WORKING_DT_ARRAY, WORKING_DT);
    WORKING_DT := DATEADD(DAY, 1, WORKING_DT);
    COUNTER := COUNTER+1;
  END WHILE;
  RETURN WITHIN_FORLOOP_ARRAY;
END;

验证效果 #

修改后的代码调用后，将返回预期的10个元素数组：

['2024-01-01','2024-01-01','2024-01-01','2024-01-01','2024-01-01','2024-01-02','2024-01-02','2024-01-02','2024-01-02','2024-01-02']

内容的提问来源于stack exchange，提问作者Kai