如何在Snowflake存储过程中正确将参数传入游标？

问题描述 #

尝试在Snowflake存储过程中传入参数并用于游标创建，但参数未被正确解析，而是直接执行了字符串' ||:TABLE2PROFILE|| '。硬编码参数可正常运行，简化后的存储过程代码如下：

CREATE OR REPLACE PROCEDURE PROC_MODEL_CREATE_TABLE_PROFILE(TABLE2PROFILE string)
RETURNS TABLE()
LANGUAGE SQL
AS
$$
declare
    sql string;
    final_sql string;
    c1 cursor for (
        SELECT
        TABLE_NAME as TABLENAME
        from TABLE_OF_TABLES
        WHERE tablename LIKE ANY (' ||:TABLE2PROFILE|| ')
        ORDER BY TABLENAME);
    res resultset;
begin
    final_sql := '';
    
    for record in c1 do
        sql := 'SELECT COUNT(*) AS Number_Of_Rows FROM '||record.tablename||';';
        final_sql := final_sql || sql;
    end for;
    
    final_sql := 'create or replace table data_profiles_LATEST as (' || final_sql || ')';
    res := (execute immediate :final_sql);
    return table(res);
$$;

调用语句：

CALL PROC_MODEL_CREATE_TABLE_PROFILE('TABLE_OF_INTEREST');

解决方案 #

问题出在静态游标不支持字符串拼接式的参数传递，需要改用动态游标，通过构造SQL语句并绑定参数的方式实现。修改后的代码如下：

CREATE OR REPLACE PROCEDURE PROC_MODEL_CREATE_TABLE_PROFILE(TABLE2PROFILE string)
RETURNS TABLE()
LANGUAGE SQL
AS
$$
declare
    sql string;
    final_sql string;
    -- 构造动态SQL语句，用?作为参数占位符
    cursor_sql string := 'SELECT TABLE_NAME as TABLENAME FROM TABLE_OF_TABLES WHERE tablename LIKE ANY (?) ORDER BY TABLENAME';
    -- 定义动态游标，绑定存储过程参数
    c1 cursor for execute immediate :cursor_sql using :TABLE2PROFILE;
    res resultset;
begin
    final_sql := '';
    
    for record in c1 do
        sql := 'SELECT COUNT(*) AS Number_Of_Rows FROM '||record.tablename||';';
        final_sql := final_sql || sql;
    end for;
    
    -- 修复多个SELECT语句拼接的语法问题，用UNION ALL连接
    final_sql := 'create or replace table data_profiles_LATEST as (' || REPLACE(final_sql, ';', ' UNION ALL ') || ')';
    res := (execute immediate :final_sql);
    return table(res);
$$;

关键修改点说明 #

1. 动态游标定义：先构造包含占位符?的SQL字符串，再通过execute immediate ... using语法绑定存储过程参数，确保参数被正确解析而非当成字符串字面量。
2. 查询语句拼接修复：原代码中多个SELECT直接拼接会触发SQL语法错误，通过REPLACE将分号替换为UNION ALL，保证多查询拼接的合法性。

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