Snowflake存储过程中变量与整数拼接返回NULL值求助

我正尝试从Information Schema中获取数据，并基于条件动态加载到目标表中。但DEFAULT_VAL始终为NULL，导致无法正确构造插入SQL语句。请告知如何将变量与SQL执行返回的值进行拼接。

原代码如下：

var tbl_qry = snowflake.execute({ sqlText: `select TABLE_NAME, ORDINAL_POSITION, case WHEN DATA_TYPE LIKE '%INT%' THEN 'INT' 
                                            WHEN DATA_TYPE LIKE '%TIME%' OR DATA_TYPE LIKE '%DATE%'THEN 'DATE' AS ACTION
                                            from INFORMATION_SCHEMA.COLUMNS;`})

while tbl_query.next()
{
    if name == 'EMP'
    {
        DEFAULT_VAL = -1
    }
    else if name == 'SAL'
    {
        DEFAULT_VAL = -2
    }
    else
    {
        DEFAULT_VAL=-3
    }
    
    var COL_LIST = ' (' ;
    var VAL_LIST = ' VALUES (' ;
    
    var TABLE_NAME = tbl_query.getColumnValue(1);
    var ORDINAL_POSITION = tbl_query.getColumnValue(2);
    var ACTION = tbl_query.getColumnValue(3);
    
    if (ORDINAL_POSITION > 1)
    {
        var COL_LIST = COL_LIST + ',' ;
        var VAL_LIST = VAL_LIST + ',' ;
    }
    if (ACTION_FLAG == 'DATE')
    {
        VAL_LIST = VAL_LIST + `CONCAT('"','9999-12-31','"')`;
    }
    else
    {
        VAL_LIST = VAL_LIST + `DEFAULT_VAL`;
    }
    
    var COL_LIST = COL_LIST + COLUMN_NAME;

    
    INSERT_SQL_CMD := 'INSERT INTO VAL_TBL'|| COL_LIST || VAL_LIST ;
    
    snowflake.execute ({sqlText: INSERT_SQL_CMD});
}

问题分析 #

原代码存在几个核心问题导致DEFAULT_VAL拼接失败：

• 变量名拼写错误：定义的是tbl_qry，循环里误用tbl_query，导致无法遍历结果集
• 未获取关键字段：name和COLUMN_NAME未从查询结果中取值，默认值赋值逻辑完全失效
• SQL拼接错误：直接将DEFAULT_VAL作为字符串写入SQL，没有引用变量的实际数值
• 逻辑结构错误：每次循环都重置COL_LIST和VAL_LIST，无法构建多列的INSERT语句
• 未定义变量：ACTION_FLAG未声明，应该使用查询返回的ACTION字段

修复后的代码 #

DECLARE
    tbl_qry RESULTSET;
    TABLE_NAME VARCHAR;
    ORDINAL_POSITION INTEGER;
    ACTION VARCHAR;
    COLUMN_NAME VARCHAR;
    DEFAULT_VAL INTEGER;
    COL_LIST VARCHAR := ' (';
    VAL_LIST VARCHAR := ' VALUES (';
    INSERT_SQL_CMD VARCHAR;
BEGIN
    -- 补充COLUMN_NAME字段，获取完整列信息
    tbl_qry := snowflake.execute({ 
        sqlText: `SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME,
                        CASE WHEN DATA_TYPE LIKE '%INT%' THEN 'INT' 
                             WHEN DATA_TYPE LIKE '%TIME%' OR DATA_TYPE LIKE '%DATE%' THEN 'DATE' 
                        END AS ACTION
                  FROM INFORMATION_SCHEMA.COLUMNS;`
    });

    -- 遍历结果集拼接字段和值
    WHILE (tbl_qry.next()) DO
        TABLE_NAME := tbl_qry.getColumnValue(1);
        ORDINAL_POSITION := tbl_qry.getColumnValue(2);
        COLUMN_NAME := tbl_qry.getColumnValue(3);
        ACTION := tbl_qry.getColumnValue(4);

        -- 根据列名设置对应默认值
        IF COLUMN_NAME = 'EMP' THEN
            DEFAULT_VAL := -1;
        ELSIF COLUMN_NAME = 'SAL' THEN
            DEFAULT_VAL := -2;
        ELSE
            DEFAULT_VAL := -3;
        END IF;

        -- 多列时添加分隔逗号
        IF ORDINAL_POSITION > 1 THEN
            COL_LIST := COL_LIST || ',';
            VAL_LIST := VAL_LIST || ',';
        END IF;

        COL_LIST := COL_LIST || COLUMN_NAME;

        -- 根据字段类型拼接对应值
        IF ACTION = 'DATE' THEN
            VAL_LIST := VAL_LIST || `'9999-12-31'`;
        ELSE
            VAL_LIST := VAL_LIST || DEFAULT_VAL;
        END IF;
    END WHILE;

    -- 闭合括号，完成语句拼接
    COL_LIST := COL_LIST || ')';
    VAL_LIST := VAL_LIST || ')';
    INSERT_SQL_CMD := 'INSERT INTO VAL_TBL' || COL_LIST || VAL_LIST;

    -- 执行插入语句
    snowflake.execute({sqlText: INSERT_SQL_CMD});
END;

关键修复说明 #

1. 统一变量名，修正拼写错误，确保结果集遍历正常
2. 在查询中添加COLUMN_NAME字段，作为默认值判断的依据
3. 直接拼接DEFAULT_VAL变量的数值，而非字符串"DEFAULT_VAL"
4. 将列列表和值列表的初始化放在循环外，实现多列累积拼接
5. 简化日期值的写法，直接使用字符串常量即可，无需额外CONCAT函数
6. 使用Snowflake Scripting标准的DECLARE/BEGIN/END结构，保证语法合规

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