如何在Snowflake存储过程中遍历特定名称表更新列并统计更新行数

实现逻辑 #

• 从INFORMATION_SCHEMA.TABLES视图拉取所有名称匹配规则的表清单，避免手动枚举表
• 用循环遍历表列表，拼接动态SQL执行更新操作，兼容不同表结构的同规则更新
• 每次更新后直接读取Snowflake内置的SQLROWCOUNT变量拿到本次更新的行数，按要求格式化输出结果

完整存储过程代码 #

CREATE OR REPLACE PROCEDURE BATCH_UPDATE_SP_TABLES()
RETURNS ARRAY
LANGUAGE SQL
AS
DECLARE
    -- 定义游标：查询匹配名称规则的所有表，可替换为你实际的库、Schema
    cur_tables CURSOR FOR 
        SELECT TABLE_CATALOG || '.' || TABLE_SCHEMA || '.' || TABLE_NAME AS FULL_TABLE_NAME
        FROM YOUR_DATABASE.YOUR_SCHEMA.INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
          AND TABLE_NAME ILIKE '%SP%'; -- 表名匹配规则，可自定义
    v_full_table_name STRING;
    v_update_sql STRING;
    v_updated_rows INT;
    v_result ARRAY DEFAULT ARRAY_CONSTRUCT();
BEGIN
    -- 遍历所有匹配的表
    FOR record IN cur_tables DO
        v_full_table_name := record.FULL_TABLE_NAME;
        -- 拼接更新SQL，替换为你实际的列更新逻辑、过滤条件
        v_update_sql := 'UPDATE ' || v_full_table_name || ' SET YOUR_COLUMN = ''YOUR_NEW_VALUE'' WHERE 1=1'; -- 可自定义WHERE条件
        -- 执行更新
        EXECUTE IMMEDIATE v_update_sql;
        -- 获取更新行数
        v_updated_rows := SQLROWCOUNT;
        -- 按要求格式拼接结果存入数组
        ARRAY_APPEND(v_result, v_full_table_name || ' - ' || v_updated_rows || ' Rows updated');
    END FOR;
    -- 返回所有表的更新结果
    RETURN v_result;
END;

使用步骤 #

• 替换代码中YOUR_DATABASE、YOUR_SCHEMA为你要操作的数据库和Schema名称
• 替换v_update_sql里的更新逻辑，将YOUR_COLUMN = ''YOUR_NEW_VALUE''修改为你实际要执行的列更新规则，有过滤条件也可以直接加在SQL末尾
• 执行存储过程创建语句后，调用CALL BATCH_UPDATE_SP_TABLES();即可得到结果

输出示例 #

MY_DB.MY_SCHEMA.USER_SP_INFO - 128 Rows updated
MY_DB.MY_SCHEMA.ORDER_SP_DETAIL - 0 Rows updated
MY_DB.MY_SCHEMA.GOODS_SP_STOCK - 342 Rows updated

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