Snowflake存储过程：循环读取列值生成并执行资源监控SQL

要实现从all_resource_monitor_master表读取多行数据，逐条构造并执行资源监控器创建语句，最直接的方式是通过Snowflake存储过程结合游标（Cursor）完成循环逻辑。以下是完整实现方案：

1. 创建存储过程 #

这个存储过程会遍历目标表的每一行，动态构造CREATE RESOURCE MONITOR语句并执行，同时支持异常处理和日志记录：

CREATE OR REPLACE PROCEDURE CREATE_RESOURCE_MONITORS()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    -- 定义游标，读取所需列并提前构造资源监控器名称
    cur CURSOR FOR 
        SELECT 
            'RM_' || rm_type || '_' || env AS lv_rm_name,
            credit_quota AS lv_credit_quota,
            frequency AS lv_frequency,
            start_timestamp AS lv_timestamp,
            notify_users AS lv_notify_users,
            trigger1 AS lv_trigger1,
            action1 AS lv_action1,
            trigger2 AS lv_trigger2,
            action2 AS lv_action2,
            trigger3 AS lv_trigger3,
            action3 AS lv_action3
        FROM all_resource_monitor_master;
    rec RECORD; -- 存储游标每行的数据
    rm_setup VARCHAR; -- 存储构造好的SQL语句
BEGIN
    -- 循环遍历游标中的每一行数据
    FOR rec IN cur DO
        -- 动态构造CREATE RESOURCE MONITOR语句
        rm_setup := 'CREATE RESOURCE MONITOR IF NOT EXISTS "' || rec.lv_rm_name || '" WITH '
                    || 'CREDIT_QUOTA = ' || rec.lv_credit_quota || ' '
                    || 'FREQUENCY = "' || rec.lv_frequency || '" '
                    || 'START_TIMESTAMP = "' || rec.lv_timestamp || '" '
                    || 'NOTIFY_USERS = ("' || rec.lv_notify_users || '") '
                    || 'TRIGGERS ON ' || rec.lv_trigger1 || ' PERCENT DO ' || rec.lv_action1 || ' '
                    || 'ON ' || rec.lv_trigger2 || ' PERCENT DO ' || rec.lv_action2 || ' '
                    || 'ON ' || rec.lv_trigger3 || ' PERCENT DO ' || rec.lv_action3;
        
        -- 执行动态SQL
        EXECUTE IMMEDIATE :rm_setup;

        -- 可选：将执行记录写入日志表（需提前创建日志表）
        INSERT INTO resource_monitor_exec_log (rm_name, execute_sql, status)
        VALUES (rec.lv_rm_name, :rm_setup, 'SUCCESS');
    END FOR;

    RETURN '所有资源监控器创建任务执行完成';
EXCEPTION
    WHEN OTHERS THEN
        -- 捕获异常并记录错误信息
        INSERT INTO resource_monitor_exec_log (rm_name, execute_sql, status, error_msg)
        VALUES (rec.lv_rm_name, :rm_setup, 'FAILED', SQLERRM);
        RETURN '执行出错：' || SQLERRM;
END;
$$;

2. 提前创建日志表（可选） #

如果需要记录执行结果，先创建日志表：

CREATE TABLE IF NOT EXISTS resource_monitor_exec_log (
    rm_name VARCHAR(100),
    execute_sql TEXT,
    status VARCHAR(20),
    error_msg TEXT,
    execute_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. 调用存储过程 #

执行存储过程开始批量创建资源监控器：

CALL CREATE_RESOURCE_MONITORS();

关键注意事项 #

• 权限要求：执行存储过程的角色需要拥有CREATE RESOURCE MONITOR权限，以及读取all_resource_monitor_master表的权限。
• 多用户通知处理：如果notify_users字段包含多个用户（用逗号分隔），需要调整拼接逻辑，比如：

  'NOTIFY_USERS = ("' || REPLACE(rec.lv_notify_users, ',', '","') || '") '
  

• SQL注入风险：如果表中的字段内容不可信，建议使用QUOTE_IDENT或QUOTE_STRING函数对变量进行转义，避免SQL注入。

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