Snowflake 的 Structured Types 如何作为参数传给存储过程?
结论
建议先在 CALL 中直接传入显式转换后的数组,或传入构造数组的子查询,跳过中间变量 A。使用 INTO :R 接收返回值。
Snowflake 的 SQL 存储过程参数支持使用 Structured Types,不能仅凭这个错误认定结构化数组参数不受支持。根据题目中的现象,应重点检查结构化数组通过 Snowflake Scripting 变量绑定后传入 CALL 的过程;是否属于产品缺陷,仍需复现确认。Structured data types 官方文档
为什么参数声明正确,调用仍然失败
SHOW PROCEDURES 展示的是存储过程保存的参数签名。它可以确认参数声明为 ARRAY(MVE_RANGE),但不能证明调用时的实参已正确完成类型解析和绑定。
原代码中的数组先被构造并赋值给局部变量,随后再次转换并绑定到 CALL。错误中的 [UNKNOWN] 与 [STRUCTURED_OBJECT] 表明编译器在某一步无法匹配类型,但这条消息不足以说明整个 ARRAY 已变成无类型数组。
接下来可以比较直接传表达式和通过变量传参的结果,先不要继续叠加相同的 ::ARRAY(MVE_RANGE) 转换。
先直接传入数组
以下示例假设题目中的类型和存储过程已在当前数据库的 MVE schema 中创建。
EXECUTE IMMEDIATE $$
DECLARE
R INTEGER;
BEGIN
CALL MVE.TEST_MVE_ARRAY(
[{'RANGE_START':1, 'RANGE_END':2}]::ARRAY(MVE.MVE_RANGE)
) INTO :R;
RETURN R;
END;
$$;
数组常量本身是半结构化 ARRAY,示例将整个数组显式转换为 ARRAY(MVE.MVE_RANGE);这个转换需要保留。Structured data types 官方文档
如果调用成功,根据 ARRAY_SIZE 计算,单元素数组的预期返回值为 1。实际结果需要在你的账户中确认。
保留 ARRAY_APPEND 时,直接传入子查询
如果想沿用原来的数组构造方式,可以将生成数组的查询直接传给 CALL,省去 SELECT ... INTO :A 和后续变量传递:
EXECUTE IMMEDIATE $$
DECLARE
R INTEGER;
BEGIN
CALL MVE.TEST_MVE_ARRAY(
SELECT ARRAY_APPEND(
ARRAY_CONSTRUCT()::ARRAY(MVE.MVE_RANGE),
{'RANGE_START':1, 'RANGE_END':2}::MVE.MVE_RANGE
)
) INTO :R;
RETURN R;
END;
$$;
Snowflake 的 CALL 参数允许使用表达式和子查询,INTO :R 用于把存储过程的返回值写入 Snowflake Scripting 变量。CALL 官方文档
这两种写法都跳过了通过局部结构化数组变量传参的步骤。如果直接传入成功,而 CALL MVE.TEST_MVE_ARRAY(:A::ARRAY(MVE.MVE_RANGE)) INTO :R; 仍然失败,可以进一步将排查范围缩小到变量传参路径。
排查时需要注意什么
- 使用一致的 schema 限定名称,例如
MVE.MVE_RANGE和MVE.TEST_MVE_ARRAY,避免当前 schema 影响对象解析。 - 原代码同时使用了
ARRAY(MVE_RANGE NOT NULL)和ARRAY(MVE_RANGE),其中NOT NULL约束的是数组元素。排查时可以先统一类型声明,减少差异,但不能据此认定它就是当前错误的原因。结构化 ARRAY 类型语法 - 如果实际业务必须沿存储过程调用链传递已有变量,直接构造参数只能作为局部绕过方案。保留直接传入和变量传入两组最小示例,附上 Query ID 和错误信息,提交给 Snowflake Support 确认。
备注:内容仅供参考。
