Snowflake存储过程内层For循环终止报错，如何正确中断内层循环？

问题描述 #

编写包含两层FOR循环的Snowflake存储过程，触发任务时出现SQL编译错误：

SQL compilation error: error line 29 at position 16
Label 'INNER' not found

尝试使用BREAK inner_loop;仍报相同错误，需求是当result等于false时仅终止内层FOR循环。

原代码如下：

DECLARE
    response_string STRING;
    result BOOLEAN;
    stream_result resultset;
    query_result resultset;
    c1 CURSOR FOR stream_result;
    c2 CURSOR FOR query_result;
BEGIN
    IF (SYSTEM$STREAM_HAS_DATA('db.schema.stream_1') = true) THEN stream_result := (SELECT process_date FROM db.schema.stream_1 ORDER BY process_date);
    ELSEIF (SYSTEM$STREAM_HAS_DATA('db.schema.stream_2') = true) THEN
        stream_result := (SELECT process_date FROM db.schema.stream_2 ORDER BY process_date);
    END IF;

    FOR row_variable IN c1 DO
        query_result := (SELECT stg.flag FROM db.schema.status_loads stg WHERE row_variable.process_date = stg.process_date);
        FOR row_variable_2 IN c2 DO
            IF (row_variable_2.flag = 1) THEN
                result := true;
            ELSE    
                result := false;
                BREAK INNER;
            END IF;
        END FOR;
        IF (:result = true) THEN
            response_string := (call sp_1(row_variable.process_date));
            RETURN(:response_string);
        ELSE 
            RETURN(row_variable.process_date);
        END IF;
    END FOR;

解决方案 #

核心问题分析 #

报错原因是内层循环未定义标签，Snowflake中使用BREAK指定终止某层循环时，必须先给目标循环添加标签；另外原代码中游标c2的定义位置错误，无法关联每次外层循环更新后的query_result。

修改后的代码 #

DECLARE
    response_string STRING;
    result BOOLEAN := false; -- 初始化默认值，避免NULL情况
    stream_result resultset;
    query_result resultset;
    c1 CURSOR FOR stream_result;
BEGIN
    IF (SYSTEM$STREAM_HAS_DATA('db.schema.stream_1') = true) THEN 
        stream_result := (SELECT process_date FROM db.schema.stream_1 ORDER BY process_date);
    ELSEIF (SYSTEM$STREAM_HAS_DATA('db.schema.stream_2') = true) THEN
        stream_result := (SELECT process_date FROM db.schema.stream_2 ORDER BY process_date);
    END IF;

    FOR row_variable IN c1 DO
        query_result := (SELECT stg.flag FROM db.schema.status_loads stg WHERE row_variable.process_date = stg.process_date);
        -- 每次外层循环重新定义内层游标，关联最新的query_result
        DECLARE c2 CURSOR FOR query_result;
        BEGIN
            -- 给内层循环添加标签inner_loop
            inner_loop: FOR row_variable_2 IN c2 DO
                IF (row_variable_2.flag = 1) THEN
                    result := true;
                ELSE    
                    result := false;
                    BREAK inner_loop; -- 终止内层循环，不影响外层
                END IF;
            END FOR inner_loop;
        END;
        IF (:result = true) THEN
            response_string := (CALL sp_1(row_variable.process_date));
            RETURN(:response_string);
        ELSE 
            RETURN(row_variable.process_date);
        END IF;
    END FOR;
END;

关键修改点 #

• 给内层循环添加标签：在FOR语句前添加inner_loop:，BREAK时指定该标签即可精准终止内层循环。
• 调整游标c2的定义位置：将c2的声明移到外层循环内部，确保每次外层循环更新query_result后，游标能关联最新的结果集。
• 初始化result变量：给result设置默认值false，避免因query_result为空导致result为NULL的异常情况。

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