Snowflake存储过程中动态SQL与游标迭代实现问题求助

需求说明 #

周末一直在研究Snowflake存储过程块中游标迭代的实现方法，目标是创建一个动态存储过程：调用时检查指定数据库schema下所有表中指定ACCOUNT_ID的记录是否存在，以此作为数据控制测试，验证数据删除流程是否正确执行。希望在声明块中为所有查询使用参数，以便轻松适配具有相同表结构但存储在不同schema的数据库（例如SOURCE_DATABASE.LOM_US_DB_PUBLIC与SOURCE_DATABASE.LOM_EU_DB_PUBLIC）。

当前实现逻辑 #

该存储过程接收三个参数：DB_NAME、SCHEMA_NAME和ACCOUNT_ID，核心逻辑如下：

• 切换到指定的数据库和schema
• 遍历目标schema下的所有表
• 动态构建SQL查询，统计每个表中目标ACCOUNT_ID的记录数
• 若发现记录则将结果累积到汇总字符串，最终返回该字符串

当前遇到的问题 #

在实现For循环或使用EXECUTE IMMEDIATE语句时持续报错，常见错误信息包括：

• Syntax error: unexpected 'IMMEDIATE'. (line 22)
• syntax error line 22 at position 35 unexpected 'INTO'. syntax error line 25 at position 8 unexpected 'IF'. (line 22)
• Syntax error: unexpected 'FOR'. (line 21)

问题根源与修复方案 #

Snowflake的SQL存储过程语法对游标遍历、动态SQL执行有严格规范，原代码的核心问题包括：

1. 切换数据库/schema时，EXECUTE IMMEDIATE后的语句无需加分号
2. 动态SQL拼接未处理表名特殊字符，且未使用参数绑定（存在SQL注入风险）
3. EXECUTE IMMEDIATE INTO的变量类型不匹配（原代码用STRING接收COUNT(*)的数值结果）
4. 遍历表时未指定数据库，可能导致跨库查询错误

修复后的完整代码 #

CREATE OR REPLACE PROCEDURE DATA_DELETION_TEST(
    DB_NAME STRING, -- 自定义目标数据库参数
    SCHEMA_NAME STRING, -- 自定义目标Schema参数
    ACCOUNT_ID STRING -- 待检查的账号ID
)
RETURNS STRING 
LANGUAGE SQL 
EXECUTE AS CALLER
AS
$$
DECLARE
    CURSOR_RESULT STRING DEFAULT '';
    TABLE_NAME STRING;
    SQL_TEXT STRING;
    QUERY_RESULT NUMBER; -- 改为NUMBER类型匹配COUNT(*)的数值结果
    -- 预定义游标，绑定参数避免硬编码
    TABLE_CURSOR CURSOR FOR 
        SELECT TABLE_NAME 
        FROM IDENTIFIER(:DB_NAME).INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = :SCHEMA_NAME;
BEGIN
    -- 切换数据库和Schema，EXECUTE IMMEDIATE后无需分号
    EXECUTE IMMEDIATE 'USE DATABASE ' || DB_NAME;
    EXECUTE IMMEDIATE 'USE SCHEMA ' || SCHEMA_NAME;

    -- 遍历游标中的表
    FOR RECORD IN TABLE_CURSOR DO
        TABLE_NAME := RECORD.TABLE_NAME;

        -- 使用参数绑定构建动态SQL，避免SQL注入并简化语法
        SQL_TEXT := 'SELECT COUNT(*) FROM IDENTIFIER(:SCHEMA_NAME).IDENTIFIER(:TABLE_NAME) WHERE ACCOUNT_ID = :ACCOUNT_ID';
        
        -- 执行动态SQL并将结果存入变量
        EXECUTE IMMEDIATE SQL_TEXT 
        USING SCHEMA_NAME, TABLE_NAME, ACCOUNT_ID
        INTO QUERY_RESULT;

        -- 记录存在数据的表信息
        IF QUERY_RESULT > 0 THEN
            -- 用换行符分隔记录，提升可读性
            CURSOR_RESULT := CURSOR_RESULT || '表: ' || TABLE_NAME || ' 存在 ' || QUERY_RESULT || ' 条账号ID为 ' || ACCOUNT_ID || ' 的记录。' || CHAR(10);
        END IF;
    END FOR;

    -- 处理无匹配记录的场景
    IF CURSOR_RESULT = '' THEN
        RETURN '未找到账号ID为 ' || ACCOUNT_ID || ' 的任何记录。';
    ELSE
        RETURN CURSOR_RESULT;
    END IF;
END;
$$;

-- 调用示例
CALL DATA_DELETION_TEST('SOURCE_DATABASE', 'LOM_US_DB_PUBLIC', 'your_account_id');

汇总格式优化建议 #

1. 可读性优化：使用换行符CHAR(10)分隔每条表记录，避免结果挤成一团
2. 友好提示：增加无匹配记录时的明确返回信息
3. 结构化输出：可将返回类型改为VARIANT，返回JSON格式的结构化结果，方便后续程序解析，示例如下：

-- 修改存储过程返回类型
RETURNS VARIANT
-- 替换原返回逻辑
IF CURSOR_RESULT = '' THEN
    RETURN PARSE_JSON('{"status": "success", "message": "未找到指定账号的记录", "account_id": "' || ACCOUNT_ID || '"}');
ELSE
    -- 将字符串结果转为结构化数组
    RETURN PARSE_JSON('{"status": "warning", "account_id": "' || ACCOUNT_ID || '", "tables_with_data": [' || 
        REPLACE(REPLACE(CURSOR_RESULT, '表: ', '"table_name": "'), ' 存在 ', '", "record_count": ') || 
        ']}');
END IF;

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