Oracle中如何查询存储过程返回的OUTPUT游标数据？求高效替代方案

Oracle本身不支持直接通过SELECT * FROM <存储过程返回的游标>这种语法查询OUT游标数据，必须通过中间转换实现。下面是几种可行方案，其中大部分都比"逐行循环插入临时表"的效率高得多：

1. 使用TABLE函数包装游标（最接近直接查询的方式） #

通过定义对应的数据类型，将存储过程的OUT游标转换为可查询的表类型，核心是用BULK COLLECT批量获取数据，避免逐行操作的性能损耗。

步骤示例： #

假设你有如下存储过程：

CREATE OR REPLACE PROCEDURE get_emp_data(p_dept_id IN NUMBER, p_out_cur OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN p_out_cur FOR
    SELECT emp_id, emp_name, salary FROM employees WHERE dept_id = p_dept_id;
END;
/

第一步：定义匹配的记录和嵌套表类型

CREATE OR REPLACE TYPE emp_rec IS OBJECT (
  emp_id NUMBER,
  emp_name VARCHAR2(100),
  salary NUMBER
);
/

CREATE OR REPLACE TYPE emp_tab IS TABLE OF emp_rec;
/

第二步：编写包装函数转换游标

CREATE OR REPLACE FUNCTION fetch_emp_data(p_dept_id NUMBER) RETURN emp_tab
IS
  v_cur SYS_REFCURSOR;
  v_emp_tab emp_tab := emp_tab();
BEGIN
  get_emp_data(p_dept_id, v_cur);
  FETCH v_cur BULK COLLECT INTO v_emp_tab; -- 批量收集游标数据，性能远高于逐行读取
  CLOSE v_cur;
  RETURN v_emp_tab;
END;
/

第三步：直接查询

SELECT * FROM TABLE(fetch_emp_data(10));

2. 全局临时表（GTT）+ 批量插入 #

如果数据量极大，TABLE函数的内存占用可能过高，可使用全局临时表配合BULK COLLECT和FORALL批量操作，这比逐行循环插入快几个数量级。

步骤示例： #

第一步：创建全局临时表

CREATE GLOBAL TEMPORARY TABLE temp_emp_data (
  emp_id NUMBER,
  emp_name VARCHAR2(100),
  salary NUMBER
) ON COMMIT DELETE ROWS; -- 事务结束自动清空，也可选ON COMMIT PRESERVE ROWS

第二步：批量插入数据

DECLARE
  v_cur SYS_REFCURSOR;
  TYPE emp_tab_type IS TABLE OF temp_emp_data%ROWTYPE;
  v_emp_tab emp_tab_type;
BEGIN
  get_emp_data(10, v_cur);
  FETCH v_cur BULK COLLECT INTO v_emp_tab; -- 批量读取游标数据
  CLOSE v_cur;
  
  -- 批量插入到临时表，避免逐行INSERT
  FORALL i IN v_emp_tab.FIRST .. v_emp_tab.LAST
    INSERT INTO temp_emp_data VALUES v_emp_tab(i);
END;
/

第三步：查询临时表

SELECT * FROM temp_emp_data;

3. 管道化TABLE函数（适合流式大场景） #

如果数据量极大且不想一次性加载所有数据到内存，可使用管道化函数，查询时会逐行返回数据，降低内存占用。

示例： #

CREATE OR REPLACE FUNCTION fetch_emp_data_piped(p_dept_id NUMBER) RETURN emp_tab PIPELINED
IS
  v_cur SYS_REFCURSOR;
  v_emp_rec emp_rec;
BEGIN
  get_emp_data(p_dept_id, v_cur);
  LOOP
    FETCH v_cur INTO v_emp_rec;
    EXIT WHEN v_cur%NOTFOUND;
    PIPE ROW(v_emp_rec); -- 逐行输出数据
  END LOOP;
  CLOSE v_cur;
  RETURN;
END;
/

查询方式和普通TABLE函数一致：

SELECT * FROM TABLE(fetch_emp_data_piped(10));

关键性能提示 #

• 绝对避免逐行循环插入临时表的写法，这种方式会导致PL/SQL与SQL引擎频繁上下文切换，性能极低。
• 优先选择BULK COLLECT+FORALL的批量操作，或直接使用TABLE函数，这两种方式的性能提升非常显著。

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