迁移时遍历两Schema表执行COUNT(*)批量对比的游标报错问题

错误原因 #

Snowflake的DECLARE关键字仅支持在存储过程、用户定义函数（UDF）或任务的代码块内部使用，直接在交互式SQL会话中执行包含DECLARE的脚本会触发语法错误，这就是你遇到"Statement expected, got Declare"的核心原因。

批量表行数对比的正确实现 #

基于你的单表对比逻辑，我们可以通过编写Snowflake存储过程来实现批量遍历指定表并对比行数的需求：

CREATE OR REPLACE PROCEDURE compare_table_rows()
RETURNS TABLE(table_name STRING, prod_row_count NUMBER, dev_row_count NUMBER)
LANGUAGE SQL
AS
$$
DECLARE
  results RESULTSET DEFAULT (SELECT table_name FROM db.information_schema.tables WHERE table_schema = 'BASE');
  c1 CURSOR FOR results;
  v_table_name STRING;
  v_prod_count NUMBER;
  v_dev_count NUMBER;
  result_table TABLE(table_name STRING, prod_row_count NUMBER, dev_row_count NUMBER);
BEGIN
  FOR record IN c1 DO
    v_table_name := record.table_name;
    
    -- 生产库统计行数
    USE SCHEMA db.base;
    SELECT COUNT(*) INTO v_prod_count FROM IDENTIFIER(:v_table_name);
    
    -- 测试库统计行数
    USE SCHEMA db_dev.test_base;
    SELECT COUNT(*) INTO v_dev_count FROM IDENTIFIER(:v_table_name);
    
    -- 插入结果表
    INSERT INTO result_table VALUES (:v_table_name, :v_prod_count, :v_dev_count);
  END FOR;
  
  RETURN TABLE(result_table);
END;
$$;

执行存储过程查看结果 #

CALL compare_table_rows();

替代方案（无需存储过程） #

如果你不想创建存储过程，也可以用Snowflake的动态SQL结合临时表实现：

1. 生成所有表的对比SQL语句：

CREATE OR REPLACE TEMP TABLE sql_stmts AS
SELECT 
  CONCAT(
    'SELECT ''', table_name, ''' AS table_name, ',
    '(SELECT COUNT(*) FROM db.base.', table_name, ') AS prod_row_count, ',
    '(SELECT COUNT(*) FROM db_dev.test_base.', table_name, ') AS dev_row_count'
  ) AS sql_stmt
FROM db.information_schema.tables 
WHERE table_schema = 'BASE';

2. 拼接并执行所有对比SQL：

SET combined_sql = (SELECT LISTAGG(sql_stmt, ' UNION ALL ') FROM sql_stmts);
EXECUTE IMMEDIATE $combined_sql;

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