Snowflake存储过程循环插入变量报错：无效标识符PROD_NAME

问题现象 #

编写Snowflake SQL存储过程动态生成产品记录时，循环内的prod_name变量在INSERT语句中触发错误：

statement error: invalid identifier 'PROD_NAME'

预期功能是生成2022-2028年所有月份的产品记录（格式如JAN-25），每条记录的FREQUENCY为MONTHLY。

初始代码（存在问题） #

问题根源：Snowflake存储过程中，直接在VALUES子句中引用变量名会被解析为列名，需使用变量绑定语法（:变量名）。即使修正绑定，循环内逐行插入的效率也极低。

DECLARE
    current_year INT := YEAR(CURRENT_DATE());
    start_year INT := current_year - 3;
    end_year INT := current_year + 3;
    month_names ARRAY := ARRAY_CONSTRUCT('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC');
    prod_name STRING;

BEGIN   

    CREATE TABLE IF NOT EXISTS TB_PRODUCTS (
        PRODUCT STRING PRIMARY KEY,
        FREQUENCY STRING
    );
    
    FOR i IN 0 TO ARRAY_SIZE(month_names) - 1 DO
        FOR y IN start_year TO end_year DO
            prod_name := month_names[i] || '-' || RIGHT(y, 2);
            -- 错误点：未使用变量绑定，Snowflake将prod_name识别为列名
            INSERT INTO TB_PRODUCTS (PRODUCT, FREQUENCY)
                VALUES (prod_name, 'MONTHLY');
        END FOR;
    END FOR;

    RETURN 'Completed'; 

END;

优化后的批量插入代码 #

通过数组收集所有待插入数据，再批量插入，既解决变量作用域/绑定问题，又大幅提升执行效率：

DECLARE
    current_year INT := YEAR(CURRENT_DATE());
    start_year INT := current_year - 3;
    end_year INT := current_year + 3;
    month_names ARRAY := ARRAY_CONSTRUCT('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC');
    prod_name STRING;
    insert_values ARRAY := ARRAY_CONSTRUCT();

BEGIN   

    CREATE TABLE IF NOT EXISTS TB_PRODUCTS (
        PRODUCT STRING PRIMARY KEY,
        FREQUENCY STRING
    );

    -- 清空表数据（替代TRUNCATE，保留表结构）
    DELETE FROM TB_PRODUCTS;

    FOR i IN 0 TO ARRAY_SIZE(month_names) - 1 DO
        FOR y IN start_year TO end_year DO
            prod_name := month_names[i] || '-' || RIGHT(y, 2); 
            -- 将产品名和频率拼接后存入数组
            insert_values := ARRAY_APPEND(insert_values, prod_name || ',MONTHLY');
        END FOR;
    END FOR;

    -- 批量插入：展开数组并拆分字段
    INSERT INTO TB_PRODUCTS (PRODUCT, FREQUENCY)
    SELECT 
        SPLIT_PART(VALUE, ',', 1) AS PRODUCT,
        SPLIT_PART(VALUE, ',', 2) AS FREQUENCY
    FROM TABLE(FLATTEN(input => :insert_values));

    RETURN 'Insertion completed'; 
    
END;

优化说明 #

1. 批量插入替代逐行插入：减少事务提交次数，提升大数据量下的执行效率
2. 变量绑定正确使用：通过:insert_values绑定数组变量，避免解析错误
3. 数据收集方式：用数组存储所有待插入的组合字符串，最后通过FLATTEN展开、SPLIT_PART拆分得到字段值

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