如何遍历PL/SQL查询结果及批量统计Oracle多表指定列值出现次数

嘿，我来帮你搞定这两个Oracle/PLSQL的实用问题，都是日常开发中经常碰到的场景，直接上干货～

这里给你三种常用的实现方式，按需选择：

方法1：隐式游标FOR循环（最简洁省心） #

这是PLSQL里最常用的遍历方式，不用手动声明、打开、关闭游标，Oracle会自动帮你处理所有游标生命周期的细节，适合简单的遍历场景。

DECLARE
BEGIN
  -- 直接用FOR循环包裹SELECT语句，隐式创建游标
  FOR emp_rec IN (SELECT employee_id, last_name FROM employees WHERE department_id = 30) LOOP
    DBMS_OUTPUT.PUT_LINE('员工ID: ' || emp_rec.employee_id || '，姓名: ' || emp_rec.last_name);
  END LOOP;
END;
/

小提示：emp_rec是自动生成的行变量，结构和SELECT结果完全匹配，直接用.访问字段即可。

方法2：显式游标（适合复杂逻辑控制） #

如果需要对游标进行精细控制（比如多次遍历、中途暂停/重启），可以用显式游标，手动管理游标状态：

DECLARE
  -- 先声明游标，定义查询逻辑
  CURSOR emp_cursor IS
    SELECT employee_id, last_name FROM employees WHERE department_id = 30;
  -- 定义和游标行结构一致的变量
  emp_rec emp_cursor%ROWTYPE;
BEGIN
  OPEN emp_cursor; -- 打开游标
  LOOP
    FETCH emp_cursor INTO emp_rec; -- 读取一行数据到变量
    EXIT WHEN emp_cursor%NOTFOUND; -- 没有更多数据时退出循环
    DBMS_OUTPUT.PUT_LINE('员工ID: ' || emp_rec.employee_id || '，姓名: ' || emp_rec.last_name);
  END LOOP;
  CLOSE emp_cursor; -- 关闭游标，释放资源
END;
/

可以用emp_cursor%ROWCOUNT查看已读取的行数，emp_cursor%FOUND判断当前是否读取到数据，灵活性更高。

方法3：BULK COLLECT批量收集（大数据量场景更高效） #

如果要处理的数据集很大，用批量收集可以减少PLSQL和SQL引擎之间的上下文切换，大幅提升性能：

DECLARE
  -- 定义和employees表行结构一致的集合类型
  TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
  emp_table emp_table_type;
BEGIN
  -- 一次性把查询结果批量加载到集合中
  SELECT employee_id, last_name BULK COLLECT INTO emp_table
  FROM employees WHERE department_id = 30;
  
  -- 遍历集合
  FOR i IN emp_table.FIRST .. emp_table.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('员工ID: ' || emp_table(i).employee_id || '，姓名: ' || emp_table(i).last_name);
  END LOOP;
END;
/

如果数据量超大，建议加LIMIT子句分批处理，避免内存溢出：比如FETCH emp_cursor BULK COLLECT INTO emp_table LIMIT 1000;

你已经拿到了目标表列表，接下来用动态SQL+游标遍历就能实现批量统计，还能处理异常情况：

DECLARE
  v_target_value VARCHAR2(100) := '1001'; -- 替换成你要统计的特定值
  v_table_name VARCHAR2(128);
  v_count NUMBER;
  v_sql VARCHAR2(4000);
  
  -- 声明游标，获取所有含EmployeeId且有数据的表
  CURSOR table_cursor IS
    SELECT table_name 
    FROM all_tab_cols 
    JOIN all_tables USING (table_name) 
    WHERE column_name = 'EMPLOYEEID' -- Oracle对象名默认大写，这里用大写更稳妥
      AND num_rows > 0
      AND owner = 'YOUR_SCHEMA'; -- 替换成你的用户名，过滤目标 schema
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== 特定值 ' || v_target_value || ' 在各表EmployeeId列的出现次数 ===');
  
  OPEN table_cursor;
  LOOP
    FETCH table_cursor INTO v_table_name;
    EXIT WHEN table_cursor%NOTFOUND;
    
    BEGIN
      -- 动态拼接统计SQL
      v_sql := 'SELECT COUNT(*) FROM ' || v_table_name || ' WHERE EmployeeId = :1';
      -- 执行动态SQL，传入目标值作为绑定变量（避免SQL注入）
      EXECUTE IMMEDIATE v_sql INTO v_count USING v_target_value;
      
      DBMS_OUTPUT.PUT_LINE('表 ' || v_table_name || ': ' || v_count || ' 次');
    EXCEPTION
      WHEN OTHERS THEN
        -- 捕获异常，避免单个表统计失败导致整个程序中断
        DBMS_OUTPUT.PUT_LINE('表 ' || v_table_name || ': 统计失败，原因: ' || SQLERRM);
    END;
  END LOOP;
  CLOSE table_cursor;
END;
/

关键注意事项： #

• 大小写匹配：Oracle创建对象时如果没加引号，默认是大写，所以column_name = 'EMPLOYEEID'能避免漏匹配表。
• 权限问题：确保当前用户有所有目标表的SELECT权限，否则会触发异常，代码里的异常处理会提示具体原因。
• 类型一致性：如果EmployeeId是数字类型，把v_target_value改成NUMBER类型，避免隐式转换导致的问题。
• 大数据量表优化：如果某些表数据量极大，用APPROX_COUNT_DISTINCT（Oracle 12c+）可以快速得到近似统计结果，提升效率。

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