Oracle存储过程如何同时返回总条数及SYS_REFCURSOR结果集

你遇到的核心痛点是原有两次查询的写法需要重复维护过滤条件，而开窗函数的写法无法直接将总计数赋值给OUT参数。另外你提供的开窗函数代码存在语法错误，count(*) over()需要放在SELECT字段列表中，而非FROM子句后。

方案1：CTE（WITH子句）复用过滤逻辑（推荐） #

该方案过滤条件仅需编写一次，逻辑清晰、兼容原有出参结构，无额外性能损耗，绝大多数场景下都适用。
实现代码如下：

create or replace PACKAGE BODY PA_MYPACKAGE
IS
PROCEDURE sp_myProcedure(p_name                IN            Varchar2
                         , p_totalCount       OUT           NUMBER 
                         , p_recordset        OUT           SYS_REFCURSOR)
AS
BEGIN
  -- 所有过滤条件统一写在base_data的WHERE中，后续逻辑直接复用，仅需维护一处
  WITH base_data AS (
    SELECT id, name, Description 
    FROM myTABLE 
    WHERE name = p_name -- 所有查询条件仅在此处编写一次
  )
  SELECT COUNT(*) INTO p_totalCount FROM base_data;

  OPEN p_recordset FOR 
  SELECT * FROM base_data
  OFFSET 0 ROW
  FETCH NEXT 10 ROW ONLY;
END sp_myProcedure;
END PA_MYPACKAGE;

方案2：基于开窗函数+集合批量收集（单次查询实现） #

如果你需要仅执行一次查询完成逻辑，可以用集合先暂存结果，再赋值总计数、返回结果集，适合查询结果总数据量不大的场景。
首先需要在PACKAGE HEADER中提前声明自定义类型：

TYPE my_table_rec IS RECORD (id NUMBER, name VARCHAR2(100), description VARCHAR2(500));
TYPE my_table_tab IS TABLE OF my_table_rec;

包体实现代码如下：

create or replace PACKAGE BODY PA_MYPACKAGE
IS
PROCEDURE sp_myProcedure(p_name                IN            Varchar2
                         , p_totalCount       OUT           NUMBER 
                         , p_recordset        OUT           SYS_REFCURSOR)
AS
  v_temp_tab my_table_tab;
  v_full_count NUMBER;
BEGIN
  -- 单次查询获取分页数据+全量总计数
  SELECT id, name, Description, totalCount
  BULK COLLECT INTO v_temp_tab
  FROM (
    SELECT 
      t.id, t.name, t.Description,
      count(*) over() as totalCount
    FROM myTABLE t
    WHERE name = p_name
  )
  OFFSET 0 ROW
  FETCH NEXT 10 ROW ONLY;

  -- 赋值总记录数
  IF v_temp_tab.COUNT > 0 THEN
    -- 从开窗结果中取符合条件的全量总条数
    SELECT DISTINCT totalCount INTO v_full_count FROM TABLE(v_temp_tab);
    p_totalCount := v_full_count;
  ELSE
    p_totalCount := 0;
  END IF;

  -- 把集合转成游标返回（自动过滤掉totalCount字段，和原有返回结构一致）
  OPEN p_recordset FOR SELECT id, name, Description FROM TABLE(v_temp_tab);
END sp_myProcedure;
END PA_MYPACKAGE;

注意事项 #

1. 原代码中入参p_nam和过滤条件的p_name命名不一致，需要修正避免报错。
2. 如果单页数据量超过1万条，推荐使用方案1，避免集合占用过多内存。

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