如何创建Snowflake存储过程实现跨库表复制并解决编译错误

原代码存在多处语法错误，核心问题集中在标识符引用方式错误、动态SQL拼接不当以及游标字段引用错误，以下是具体修正和优化方案：

错误点解析 #

1. 游标查询的FROM子句错误：不能直接用IDENTIFIER(SRC_DB) || '.' || ...拼接表名，IDENTIFIER()函数需接收完整的标识符字符串作为参数。
2. 动态SQL语法错误：EXECUTE IMMEDIATE的字符串中不能嵌套使用IDENTIFIER()函数，需直接拼接合法的表名字符串。
3. 游标字段引用错误：游标查询的别名是SCHEMA_TAB_NAME，但代码中错误引用REC.TAB_NAME，该字段不存在。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE COPY_TABLES_TO_SANDBOX(USERNAME VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  SRC_DB VARCHAR := 'FIRST_DATABASE';
  SRC_SCHEMA VARCHAR := 'SCHEMA_NAME';
  TRG_DB VARCHAR := 'SECOND_DATABASE';
  -- 拼接源表的完整标识符
  SRC_TABLE_LIST VARCHAR := CONCAT(SRC_DB, '.', SRC_SCHEMA, '.', 'TABLES');
  -- 游标获取源表的模式和表名，而非直接拼接目标标识符
  CURSOR table_cursor IS
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM IDENTIFIER(:SRC_TABLE_LIST)
    WHERE TABLE_TYPE = 'BASE TABLE';
  trg_schema_name VARCHAR;
  src_full_table VARCHAR;
  trg_full_table VARCHAR;
BEGIN
  FOR rec IN table_cursor DO
    -- 构建目标模式名：USERNAME_原模式名
    trg_schema_name := CONCAT(USERNAME, '_', rec.TABLE_SCHEMA);
    -- 拼接源表和目标表的完整路径
    src_full_table := CONCAT(SRC_DB, '.', rec.TABLE_SCHEMA, '.', rec.TABLE_NAME);
    trg_full_table := CONCAT(TRG_DB, '.', trg_schema_name, '.', rec.TABLE_NAME);
    
    -- 执行动态SQL复制表
    EXECUTE IMMEDIATE CONCAT(
      'CREATE OR REPLACE TABLE ', trg_full_table, 
      ' AS SELECT * FROM ', src_full_table,
      ' COPY GRANTS'
    );
  END FOR;
  RETURN 'Tables copied successfully';
END;

-- 调用示例
CALL COPY_TABLES_TO_SANDBOX('TEST_USER');

优化增强：添加错误处理 #

为了更友好地排查复制失败的问题，可加入异常捕获逻辑：

CREATE OR REPLACE PROCEDURE COPY_TABLES_TO_SANDBOX(USERNAME VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  SRC_DB VARCHAR := 'FIRST_DATABASE';
  SRC_SCHEMA VARCHAR := 'SCHEMA_NAME';
  TRG_DB VARCHAR := 'SECOND_DATABASE';
  SRC_TABLE_LIST VARCHAR := CONCAT(SRC_DB, '.', SRC_SCHEMA, '.', 'TABLES');
  CURSOR table_cursor IS
    SELECT TABLE_SCHEMA, TABLE_NAME
    FROM IDENTIFIER(:SRC_TABLE_LIST)
    WHERE TABLE_TYPE = 'BASE TABLE';
  trg_schema_name VARCHAR;
  src_full_table VARCHAR;
  trg_full_table VARCHAR;
  error_msg VARCHAR;
BEGIN
  FOR rec IN table_cursor DO
    trg_schema_name := CONCAT(USERNAME, '_', rec.TABLE_SCHEMA);
    src_full_table := CONCAT(SRC_DB, '.', rec.TABLE_SCHEMA, '.', rec.TABLE_NAME);
    trg_full_table := CONCAT(TRG_DB, '.', trg_schema_name, '.', rec.TABLE_NAME);
    
    BEGIN
      EXECUTE IMMEDIATE CONCAT(
        'CREATE OR REPLACE TABLE ', trg_full_table, 
        ' AS SELECT * FROM ', src_full_table,
        ' COPY GRANTS'
      );
    EXCEPTION
      WHEN OTHERS THEN
        error_msg := CONCAT('Failed to copy table ', src_full_table, ': ', SQLERRM);
        RETURN error_msg;
    END;
  END FOR;
  RETURN 'All tables copied successfully';
END;

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