Oracle存储过程如何同时返回游标结果集并统计返回行数

完全可以在单个存储过程中同时实现返回结果集、统计总行数用于日志记录的需求。你之前整合失败的核心原因是：将SYS_REFCURSOR转为DBMS_SQL游标编号后逐行遍历计数会将游标指针移动到结果集末尾，计数完成后游标已经没有可读取的数据，自然无法再正常返回给调用方，且逐行遍历计数的方式在结果集较大时性能极差。

以下是两种生产环境可用的实现方案，可根据Oracle版本、查询复杂度选择：

方案1：结果集缓存到PL/SQL集合（推荐，无重复查询开销，性能最优） #

核心逻辑是先把查询结果一次性批量拉取到PL/SQL嵌套表集合中，集合的COUNT属性就是结果总行数，可直接用该值写入日志，之后再基于集合打开游标返回结果集，全程仅执行一次查询，性能损耗最低。
示例代码：

CREATE OR REPLACE PROCEDURE GETCUR(PARAM1 VARCHAR2)
AS
  -- 定义和查询返回字段匹配的记录、集合类型
  TYPE result_rec IS RECORD(
    f3_t1 T1.F3%TYPE,
    f3_t2 T2.F3%TYPE
  );
  TYPE result_tab IS TABLE OF result_rec;
  v_results result_tab;
  cur SYS_REFCURSOR;
  cnt INTEGER;
BEGIN
  -- 批量拉取查询结果到内存集合
  SELECT T1.F3, T2.F3 
  BULK COLLECT INTO v_results
  FROM T1 JOIN T2 ON T1.F1 = T2.F2 
  WHERE T2.F9 = PARAM1;

  -- 从集合属性直接获取总行数，执行日志写入逻辑
  cnt := v_results.COUNT;
  -- 替换为实际业务中的日志表写入逻辑
  INSERT INTO OP_LOG(proc_name, exec_time, return_rows) 
  VALUES('GETCUR', SYSDATE, cnt);
  COMMIT;

  -- 基于内存集合打开游标返回给调用方
  OPEN cur FOR SELECT * FROM TABLE(v_results);
  DBMS_SQL.RETURN_RESULT(cur);
END;
/

注意事项：

• 若使用Oracle 11g及更早版本，需要将示例中的result_rec、result_tab创建为SQL级别的自定义类型，否则TABLE(v_results)会抛出类型不存在的错误；
• 若单次查询结果集超过10万行，建议在批量拉取时增加LIMIT子句分批加载，避免PGA内存占用过高。

方案2：独立COUNT统计（实现最简单，适合低开销查询场景） #

如果查询本身执行速度快、关联逻辑简单，不想额外定义类型，可以使用和原查询完全一致的关联、过滤条件单独执行一次COUNT统计拿到总行数，之后再打开游标返回结果集，代码改动量最小，逻辑最直观。
示例代码：

CREATE OR REPLACE PROCEDURE GETCUR(PARAM1 VARCHAR2)
AS
  cur SYS_REFCURSOR;
  cnt INTEGER;
BEGIN
  -- 先统计符合条件的总行数
  SELECT COUNT(*)
  INTO cnt
  FROM T1 JOIN T2 ON T1.F1 = T2.F2 
  WHERE T2.F9 = PARAM1;

  -- 写入操作日志
  INSERT INTO OP_LOG(proc_name, exec_time, return_rows) 
  VALUES('GETCUR', SYSDATE, cnt);
  COMMIT;

  -- 打开游标返回业务结果集
  OPEN cur FOR
  SELECT T1.F3, T2.F3 FROM T1 JOIN T2 ON T1.F1 = T2.F2 WHERE T2.F9 = PARAM1;
  DBMS_SQL.RETURN_RESULT(cur);
END;
/

注意事项：该方案会执行两次结构相似的查询，如果原查询关联表多、数据量大、过滤条件无合适索引，会导致存储过程执行时间翻倍，不适合高并发、大结果集的生产场景。

避坑说明 #

你之前尝试的SYS_REFCURSOR转DBMS_SQL游标逐行计数的方式存在两个硬伤，不建议生产使用：

• 逐行单行拉取数据的IO、上下文切换开销远高于批量拉取或者单独COUNT查询
• 计数完成后游标指针位于结果集末尾，Oracle默认服务端游标不支持回滚到第一行，即使转回SYS_REFCURSOR返回，调用方也无法读取到有效数据；如果强制开启可滚动游标，会带来额外的内存、性能开销。

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