SQL*Plus运行PL/SQL统计Oracle多匹配表行数无输出问题排查

你写的代码有两个核心语法错误，和dbms_output配置本身关系不大：

• SET SERVEROUTPUT ON SIZE 100000是SQLPlus专属的客户端环境命令，不属于PL/SQL语法，写在BEGIN块内会直接触发编译错误，代码根本无法正常执行。这个命令必须在运行PL/SQL块之前，在SQLPlus交互界面单独执行。
• 动态SQL的INTO语法写错了：你把into :ctr写在了动态拼接的SQL字符串内部，这里的冒号是绑定变量占位符，并没有和你开头声明的ctr局部变量做绑定，执行后ctr变量不会被赋值，就算输出也是空值。

直接在SQL*Plus中按顺序执行以下代码即可：

-- SQL*Plus环境配置，必须在PL/SQL块外单独执行
SET SERVEROUTPUT ON SIZE UNLIMITED;
SET LINESIZE 200;

DECLARE
   ctr NUMBER;
   CURSOR to_check IS
     SELECT table_name 
     FROM user_tables
     WHERE table_name LIKE '%REF%' OR table_name LIKE '%CNF%'
     ORDER BY table_name;
BEGIN
   FOR rec IN to_check LOOP
     -- INTO子句写在EXECUTE IMMEDIATE外部，直接对接局部变量
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || rec.table_name INTO ctr;
     -- RPAD做对齐，输出更规整
     DBMS_OUTPUT.PUT_LINE(RPAD(rec.table_name, 40) || '行数: ' || ctr);
   END LOOP;
END;
/

注意最后一行单独的/不能省略，SQL*Plus需要靠这个符号触发PL/SQL块的执行。

如果不想依赖DBMS_OUTPUT，可以直接通过SQL拼接批量统计语句，执行后直接得到结构化结果，不受输出缓冲区大小限制：

1. 先执行以下查询生成统计SQL：

   SELECT 'SELECT ''' || table_name || ''' AS 表名, COUNT(*) AS 行数 FROM ' || table_name || ' UNION ALL'
   FROM user_tables
   WHERE table_name LIKE '%REF%' OR table_name LIKE '%CNF%'
   ORDER BY table_name;
   

2. 把查询结果里最后一行末尾的UNION ALL删掉，直接执行拼接好的SQL，就能直接得到所有匹配表的行数，格式为规整的两列结果。

• 如果需要统计所有有权限访问的schema下的表，而不是当前用户自己的表，把user_tables替换为all_tables，拼接表名时要带上owner字段，格式为owner.table_name，避免跨schema重名报错。
• 如果匹配的表数量超过1000张，UNION ALL拼接的方案可能触发SQL长度限制，这时候优先用修正后的PL/SQL方案。

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