如何在外部统计未知类型sys_refcursor返回的行数？

我有一个如下的PL/SQL函数（伪代码）：

function foobar() return sys_refcursor as
  select_cursor sys_refcursor;
begin
  open select_cursor for
    select
      a, b, c, d, e, f, g, h, i, j
    from
      table_a
      left join table_b on ...
      left join table_c on ...
      left join table_d on...
      left join table_e on ...
      ...
    where
     ...
    ;

  return select_cursor;
end;

如上述代码所示，该游标并非简单的table_a%rowtype类型，其结构复杂难以直接声明（编辑：并非完全无法声明，可参考MT0的相关思路）。

需求：如何统计该函数返回的行数？

约束条件：

• 需在函数外部（调用方）进行统计。
• 统计过程中可以销毁/关闭游标。
• 仅需行数统计结果，不关心实际数据内容。

我查找了诸多解决方案，但均未涉及未知类型游标的行数统计场景。

尝试过如下代码：

function count_nodes(
  p_cursor in sys_refcursor
)
return number is
  v_node_row   ???; -- 无法确定类型
  v_count      number := 0;
begin
    loop
      fetch p_cursor into v_node_row;
      exit when p_cursor%NOTFOUND;
      v_count := v_count + 1;
    end loop;

  close p_cursor;

  return v_count;
end;

曾尝试参考建议：若函数SELECT语句首列为NUMBER类型，可直接FETCH到NUMBER类型的哑变量中（实际首列确实为NUMBER类型），但执行时报错：

ORA-06504: PL/SQL: 结果集变量或查询的返回类型不匹配

可行解决方案：使用DBMS_SQL动态处理游标 #

由于SYS_REFCURSOR是弱类型游标，无法直接用静态PL/SQL的FETCH语句处理未知结构，但可以通过DBMS_SQL包将其转换为动态游标，逐行推进并计数，无需关心具体列类型。

示例代码如下：

function count_refcursor_rows(p_cursor in out sys_refcursor) return number is
  v_cursor_id    integer;
  v_col_count    integer;
  v_desc_tbl     dbms_sql.desc_tab;
  v_count        number := 0;
  v_status       integer;
begin
  -- 将SYS_REFCURSOR转换为DBMS_SQL可处理的游标ID
  v_cursor_id := dbms_sql.to_cursor_number(p_cursor);
  
  -- 获取游标列数量与元数据，用于初始化列绑定
  dbms_sql.describe_columns(v_cursor_id, v_col_count, v_desc_tbl);
  
  -- 绑定所有列，用NULL占位无需读取实际值
  for i in 1..v_col_count loop
    dbms_sql.define_column(v_cursor_id, i, null);
  end loop;
  
  -- 逐行推进游标并计数
  loop
    v_status := dbms_sql.fetch_rows(v_cursor_id);
    exit when v_status = 0;
    v_count := v_count + 1;
  end loop;
  
  -- 关闭游标
  dbms_sql.close_cursor(v_cursor_id);
  
  return v_count;
end;

调用示例 #

declare
  v_cursor  sys_refcursor;
  v_row_num number;
begin
  v_cursor := foobar();
  v_row_num := count_refcursor_rows(v_cursor);
  dbms_output.put_line('总行数：' || v_row_num);
end;

说明 #

1. dbms_sql.to_cursor_number完成弱类型游标到DBMS_SQL游标ID的转换，实现动态处理。
2. dbms_sql.describe_columns获取游标列的数量信息，为后续列绑定提供依据。
3. 循环绑定所有列时用NULL占位，避免因列类型未知导致的匹配错误，同时无需读取实际数据。
4. dbms_sql.fetch_rows逐行推进游标，每成功读取一行则计数加1，直到游标耗尽。
5. 最终关闭游标并返回计数结果，满足“可销毁游标”的约束条件。

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