Snowflake存储过程动态创建角色时遇unexpected <EOF>错误求助

问题描述 #

基于Snowflake元数据动态创建角色，计划通过存储过程批量生成并执行CREATE ROLE IF NOT EXISTS语句，角色名以PROD_DB_R_S_为前缀拼接模式名，但执行时触发语法错误：

语法错误：第16行位置0处出现意外的（文件结束符）

原代码如下：

Execute IMMEDIATE $$
DECLARE
schemanamex VARCHAR;
rolename VARCHAR;


BEGIN
 
FOR  schemarec IN (SELECT SCHEMA_NAME FROM PROD_DB.INFORMATION_SCHEMA.SCHEMATA) DO
   schemanamex := schemarec.SCHEMA_NAME;
 
   rolename := "PROD_DB_R_S_"|| schemanamex;
 
   RETURN "CREATE ROLE IF NOT EXISTS "||rolename||";";
END;
$$
;

错误原因 #

1. FOR循环未闭合：缺少END FOR;语句，SQL解析器处理到END;时认为循环逻辑未完成，提前遇到文件结束符触发EOF错误
2. 字符串常量使用错误：Snowflake中字符串必须用单引号'包裹，双引号"仅用于引用带特殊字符的标识符（如含空格的对象名），原代码用双引号包裹字符串导致语法识别异常
3. RETURN语句逻辑错误：循环内直接RETURN会在处理第一个模式后就终止程序，无法批量创建所有角色

修正后的代码 #

CREATE OR REPLACE PROCEDURE CREATE_SCHEMA_ROLES()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
DECLARE
    schemanamex VARCHAR;
    rolename VARCHAR;
BEGIN
    FOR schemarec IN (SELECT SCHEMA_NAME FROM PROD_DB.INFORMATION_SCHEMA.SCHEMATA) DO
        schemanamex := schemarec.SCHEMA_NAME;
        rolename := 'PROD_DB_R_S_' || schemanamex;
        -- 执行角色创建语句
        EXECUTE IMMEDIATE 'CREATE ROLE IF NOT EXISTS ' || rolename || ';';
    END FOR; -- 闭合FOR循环
    RETURN '所有模式对应的角色已批量创建完成';
END;
$$;

-- 调用存储过程执行批量创建
CALL CREATE_SCHEMA_ROLES();

修正说明 #

• 添加END FOR;语句闭合循环，解决EOF语法错误
• 将所有字符串常量的双引号替换为单引号，符合Snowflake SQL语法规范
• 替换RETURN为EXECUTE IMMEDIATE，实际执行角色创建操作，确保批量处理所有模式
• 封装为可重复调用的存储过程，便于后续执行和维护
• 添加存储过程调用语句，直接触发批量创建逻辑

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