Oracle 19C中优化合并两个分页游标的技术问询

针对你不想全量加载两个存储过程数据、又不愿改写内部复杂查询的需求，以下是几个可行的优化方案：

方案一：按需分步调用存储过程 #

核心思路是只获取当前分页所需的最小数据量，避免全量读取。步骤如下：

1. 先获取第一个存储过程的总记录数（可新增一个计数函数，复用原存储过程的查询逻辑但只返回COUNT(*)，成本远低于全量取数）。
2. 根据当前分页的startIndex和pageSize，计算需要从两个存储过程分别读取的数量：
   • 如果startIndex大于等于第一个存储过程的总条数，直接从第二个存储过程读取startIndex - 总条数开始的pageSize条数据。
   • 如果startIndex在第一个存储过程的范围内，先读取第一个存储过程中从startIndex开始、最多pageSize条的数据；若数量不足，再从第二个存储过程读取剩余条数的数据。
3. 合并两次读取的结果，返回给前端。

示例PL/SQL代码片段：

-- 假设已实现获取Proc_First总条数的函数
v_total_first := Get_Proc_First_Total(...params);

IF p_startIndex >= v_total_first THEN
  -- 从Proc_Second取数据
  Proc_Second(...params, p_startIndex - v_total_first, p_pageSize, v_out_cursor);
ELSE
  -- 先取Proc_First的部分数据
  v_need_from_first := LEAST(v_total_first - p_startIndex, p_pageSize);
  Proc_First(...params, p_startIndex, v_need_from_first, v_cursor1);
  -- 将数据存入临时集合
  FETCH v_cursor1 BULK COLLECT INTO v_result;
  CLOSE v_cursor1;

  -- 若还需要更多数据，从Proc_Second取剩余部分
  IF v_need_from_first < p_pageSize THEN
    Proc_Second(...params, 0, p_pageSize - v_need_from_first, v_cursor2);
    FETCH v_cursor2 BULK COLLECT INTO v_temp_recs;
    -- 追加到结果集合
    FOR i IN 1..v_temp_recs.COUNT LOOP
      v_result.EXTEND;
      v_result(v_result.LAST) := v_temp_recs(i);
    END LOOP;
    CLOSE v_cursor2;
  END IF;

  -- 打开最终游标
  OPEN v_out_cursor FOR SELECT * FROM TABLE(v_result);
END IF;

方案二：用管道表函数包装存储过程，实现流式分页 #

将每个存储过程包装为管道表函数，这样可以直接在SQL中合并两个函数的结果并做分页。管道表函数是逐行返回数据的，数据库会自动优化执行计划，不会一次性加载全量数据。

步骤1：编写包装函数 #

-- 包装Proc_First的管道表函数
CREATE OR REPLACE FUNCTION Wrap_Proc_First(
  ...params,
  p_startIndex IN NUMBER,
  p_pageSize IN NUMBER
) RETURN Your_Record_Type PIPELINED AS
  v_cursor SYS_REFCURSOR;
  v_rec Your_Record_Type;
BEGIN
  Proc_First(...params, p_startIndex, p_pageSize, v_cursor);
  LOOP
    FETCH v_cursor INTO v_rec;
    EXIT WHEN v_cursor%NOTFOUND;
    PIPE ROW(v_rec);
  END LOOP;
  CLOSE v_cursor;
  RETURN;
END;

-- 同理编写Wrap_Proc_Second函数

步骤2：合并分页查询 #

SELECT * FROM (
  SELECT 
    t.*, 
    ROW_NUMBER() OVER (ORDER BY your_sort_column) AS rn -- 必须指定排序字段，保证分页顺序稳定
  FROM (
    SELECT * FROM TABLE(Wrap_Proc_First(...params, 0, 999999)) -- 传入极大pageSize，让存储过程返回所有数据（但管道函数会流式返回）
    UNION ALL -- 无需去重用UNION ALL，性能更高；需要去重则替换为UNION
    SELECT * FROM TABLE(Wrap_Proc_Second(...params, 0, 999999))
  ) t
)
WHERE rn BETWEEN :startIndex + 1 AND :startIndex + :pageSize; -- Oracle的ROW_NUMBER从1开始，注意偏移量

方案三：封装统一分页存储过程 #

将上述分步调用的逻辑封装为一个新的存储过程，对外暴露统一的startIndex和pageSize参数，内部处理两个存储过程的调用和数据合并。这种方案的好处是封装性强，前端或调用方无需关心内部逻辑，直接调用新存储过程即可。

核心逻辑示例 #

CREATE OR REPLACE PROCEDURE Proc_Combined_Pagination(
  ...params,
  p_startIndex IN NUMBER,
  p_pageSize IN NUMBER,
  p_out_cursor OUT SYS_REFCURSOR
) AS
  v_total_first NUMBER;
  v_need_from_first NUMBER;
  v_need_from_second NUMBER;
  v_result Your_Table_Type := Your_Table_Type();
  v_temp_recs Your_Table_Type;
  v_cursor1 SYS_REFCURSOR;
  v_cursor2 SYS_REFCURSOR;
BEGIN
  -- 获取Proc_First的总记录数
  v_total_first := Get_Proc_First_Total(...params);

  IF p_startIndex >= v_total_first THEN
    -- 全部从Proc_Second取
    v_need_from_second := p_pageSize;
    Proc_Second(...params, p_startIndex - v_total_first, v_need_from_second, v_cursor2);
    FETCH v_cursor2 BULK COLLECT INTO v_result;
    CLOSE v_cursor2;
  ELSE
    -- 计算从Proc_First取的数量
    v_need_from_first := LEAST(v_total_first - p_startIndex, p_pageSize);
    Proc_First(...params, p_startIndex, v_need_from_first, v_cursor1);
    FETCH v_cursor1 BULK COLLECT INTO v_result;
    CLOSE v_cursor1;

    -- 若需要补充数据，从Proc_Second取
    IF v_need_from_first < p_pageSize THEN
      v_need_from_second := p_pageSize - v_need_from_first;
      Proc_Second(...params, 0, v_need_from_second, v_cursor2);
      FETCH v_cursor2 BULK COLLECT INTO v_temp_recs;
      -- 追加数据到结果集合
      FOR i IN 1..v_temp_recs.COUNT LOOP
        v_result.EXTEND;
        v_result(v_result.LAST) := v_temp_recs(i);
      END LOOP;
      CLOSE v_cursor2;
    END IF;
  END IF;

  -- 打开输出游标
  OPEN p_out_cursor FOR SELECT * FROM TABLE(v_result);
END;

方案对比 #

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