如何在Snowflake脚本中使用超长字符变量创建查询？

在Snowflake普通SQL脚本中，使用SET var_name = 'value'定义变量时，变量长度被限制为256字符，但VARCHAR类型本身支持最大16MB（VARCHAR(16777216)）。该大长度变量的声明方式在存储过程中可正常使用，但直接在普通脚本中写DECLARE会触发语法错误。

错误示例及问题 #

以下代码执行会报错（注释掉DECLARE语句则可正常运行）：

DECLARE   vParameter1 VARCHAR(16777216);

SET vParameter1 = '700,145,138,159';
--SET vParameter1 := '700,145,138,159';

SELECT TO_NUMBER(table1.VALUE) AS Locations
FROM TABLE(SPLIT_to_table($vParameter1,',')) AS table1

报错信息为第1行EOF错误，错误位置指向VARCHAR长度的最后一位字符，即使调整长度值，错误位置仅随长度值变化。

可行解决方案 #

通过EXECUTE IMMEDIATE包裹匿名块的方式，模拟存储过程环境实现大长度变量的使用，以下是验证通过的代码：

EXECUTE IMMEDIATE $$
DECLARE 
  vParameter1 VARCHAR(16777216) := '700,145,138,159';
  res RESULTSET;
BEGIN 
  -- 也可以用LET替代DECLARE声明变量
  -- LET vParameter1 VARCHAR(16777216) := '700,145,138,159'; 
 
  res := (
    SELECT TO_NUMBER(table1.VALUE) AS Locations
    FROM TABLE(SPLIT_to_table(:vParameter1,',')) AS table1
  );

  /* 如果不需要返回结果集，直接执行写入操作即可：
  INSERT INTO some_table(col)
  SELECT TO_NUMBER(table1.VALUE) AS Locations
  FROM TABLE(SPLIT_to_table(:vParameter1,',')) AS table1;
  */

  RETURN TABLE(res);
END;
$$;

关键说明 #

• 借助EXECUTE IMMEDIATE配合$$包裹的匿名块，获得支持大变量的执行环境
• 变量绑定使用:变量名语法，区别于普通脚本的$变量名
• 支持用DECLARE或LET两种方式声明大长度VARCHAR变量
• 需返回查询结果时，通过RESULTSET类型变量接收后返回；仅需执行写入操作时，直接在块内运行DML语句即可

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