求助：基于动态SQL生成Snowflake存储过程以重建无排序规则表

需要编写Snowflake SQL存储过程，通过动态SQL为指定SCHEMA下所有表生成包含数据类型、精度的CREATE OR REPLACE TABLE语句，用于移除列的旧排序规则（已修改SCHEMA默认排序规则，需重建表清除旧规则，后续用INSERT OVERWRITE迁移数据后删除旧表）。参考示例代码编写时，无法正确创建游标和循环语句。

需求说明 #

生成的CREATE OR REPLACE TABLE语句需从INFORMATION_SCHEMA.COLUMNS获取以下信息：

• 表标识：table_catalog（数据库名）、table_schema（模式名）、table_name（表名）
• 列信息：column_name（列名）、data_type（数据类型），以及字符类型的character_maximum_length、数值类型的numeric_precision和numeric_scale等，以此构建完整的表结构。

参考代码 #

CREATE OR REPLACE PROCEDURE test_proc(DB_NAME STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  TABLE_NAME  STRING;
  QUERY    STRING;
  OUTPUT    STRING DEFAULT '';
  c1      CURSOR FOR SELECT SCHEMA_NAME FROM TABLE(?) 
        WHERE SCHEMA_NAME != 'INFORMATION_SCHEMA';
 
BEGIN
  TABLE_NAME := CONCAT(DB_NAME, '.INFORMATION_SCHEMA.SCHEMATA');
  OPEN c1 USING (TABLE_NAME);
  FOR rec IN c1 DO
    QUERY := 'COMMENT ON SCHEMA ' || DB_NAME || '.' || rec.SCHEMA_NAME
         || ' IS ''test_comment'';';
    OUTPUT := OUTPUT || QUERY;
    EXECUTE IMMEDIATE :QUERY;
  END FOR;
RETURN :OUTPUT;
END;
$$;

我的尝试代码 #

CREATE OR REPLACE PROCEDURE test_proc2(TABLE_SCHEMA STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
   TABLE_NAME   STRING;
   QUERY        STRING;
   OUTPUT       STRING DEFAULT '';
   c1           CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA(?);
BEGIN
   TABLE_NAME := CONCAT(TABLE_CATALOG,'.TABLE_SCHEMA','.TABLE_NAME');
   
   OPEN c1 USING (TABLE_SCHEMA);

   FOR rec IN c1 DO
       QUERY := 'CREATE OR REPLACE TABLE ' || TABLE_CATALOG || '.' || TABLE_SCHEMA ||'.' || TABLE_NAME || '(' || COLUMN_NAME || DATA_TYPE || ','
                 || ' ,'|| ')'||';';
       OUTPUT := OUTPUT || QUERY;
       EXECUTE IMMEDIATE :QUERY;
   END FOR;
   
   RETURN :OUTPUT;
END;
$$;

call test_proc2('SANDBOX');

修正后的存储过程 #

CREATE OR REPLACE PROCEDURE recreate_tables_with_new_collation(TARGET_SCHEMA STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    v_db_name STRING := CURRENT_DATABASE();
    v_table_name STRING;
    v_create_stmt STRING;
    v_columns STRING;
    output_str STRING DEFAULT '';
    -- 游标：获取指定SCHEMA下的所有表（去重）
    cur_tables CURSOR FOR 
        SELECT DISTINCT TABLE_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = :TARGET_SCHEMA;
BEGIN
    -- 遍历每个表
    FOR table_rec IN cur_tables DO
        v_table_name := table_rec.TABLE_NAME;
        
        -- 拼接当前表的所有列定义（包含数据类型、精度等）
        SELECT LISTAGG(
            COLUMN_NAME || ' ' || 
            CASE 
                WHEN DATA_TYPE IN ('VARCHAR', 'CHAR') THEN DATA_TYPE || '(' || CHARACTER_MAXIMUM_LENGTH || ')'
                WHEN DATA_TYPE IN ('NUMBER', 'DECIMAL') THEN DATA_TYPE || '(' || NUMERIC_PRECISION || ',' || NUMERIC_SCALE || ')'
                ELSE DATA_TYPE
            END,
            ', '
        ) INTO v_columns
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_CATALOG = :v_db_name
          AND TABLE_SCHEMA = :TARGET_SCHEMA
          AND TABLE_NAME = :v_table_name
        ORDER BY ORDINAL_POSITION;
        
        -- 构建CREATE OR REPLACE TABLE语句
        v_create_stmt := 'CREATE OR REPLACE TABLE ' || v_db_name || '.' || TARGET_SCHEMA || '.' || v_table_name || '(' || v_columns || ');';
        
        -- 执行语句并记录输出
        EXECUTE IMMEDIATE :v_create_stmt;
        output_str := output_str || v_create_stmt || '\n';
    END FOR;
    
    RETURN output_str;
END;
$$;

-- 调用示例：替换为你的目标SCHEMA名
CALL recreate_tables_with_new_collation('SANDBOX');

错误点说明 #

• 游标定义错误：原代码中WHERE TABLE_SCHEMA(?)语法错误，正确参数化写法应为WHERE TABLE_SCHEMA = :TARGET_SCHEMA，直接使用绑定变量更简洁。
• 变量引用错误：循环中未使用游标返回的rec.TABLE_NAME，而是错误拼接了字符串常量TABLE_NAME，导致表名错误。
• 列定义未处理：原代码未按表分组获取列信息，直接引用COLUMN_NAME和DATA_TYPE会导致语法错误，需通过LISTAGG按表拼接完整的列定义，同时处理不同数据类型的精度/长度参数。
• 表标识拼接错误：原代码CONCAT(TABLE_CATALOG,'.TABLE_SCHEMA','.TABLE_NAME')中，TABLE_SCHEMA和TABLE_NAME是字符串常量而非变量，应使用实际变量值。

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