使用cx_Oracle读取Oracle包内游标并返回SYS_REFCURSOR的优化咨询

你当前采用的“遍历游标存自定义临时表类型再转SYS_REFCURSOR”的实现，本质是做了两次无意义的全量数据拷贝，性能损耗非常大。实际上完全可以通过游标句柄直接传递的方式，实现零中间存储的结果集返回，cx_Oracle也可以直接消费这类游标，全程不需要额外做数据转换。

原实现效率低的核心原因 #

• 逐行遍历把包内游标的全量结果集拉取到PGA内存的自定义集合中，彻底失去了游标流式传输、惰性拉取的特性，客户端必须等所有数据加载完成才能拿到第一行响应
• 再通过table()函数把内存集合转成SQL可访问的结果集，又做了一次数据格式转换与拷贝，大结果集下PGA内存占用会线性上涨，很容易触发内存不足的报错
• 整个流程没有任何业务逻辑价值，纯做数据搬运，通常会带来10~100倍的性能损耗

零搬运的优化实现方案 #

1. Oracle端简化逻辑（无需自定义集合、无需循环遍历） #

不管用哪个受支持的Oracle版本，都可以去掉临时集合的逻辑，直接把打开的包内游标的句柄传递给调用方，不存在任何数据拷贝：

-- 12c及以上版本支持直接游标赋值，写法最简单
FUNCTION my_func(p_1 in number) return SYS_REFCURSOR as
  p_result_cur SYS_REFCURSOR;
begin
  -- 直接打开包内带参游标，将句柄直接赋值给返回变量，无fetch、无循环
  OPEN MY_PACKAGE.my_cur(p_1);
  p_result_cur := MY_PACKAGE.my_cur;
  return p_result_cur;
end;

-- 11gR2及以上版本可通过DBMS_SQL转换游标句柄，同样无任何数据搬运
FUNCTION my_func(p_1 in number) return SYS_REFCURSOR as
  p_result_cur SYS_REFCURSOR;
  cur_handle NUMBER;
begin
  OPEN MY_PACKAGE.my_cur(p_1);
  cur_handle := DBMS_SQL.to_cursor_number(MY_PACKAGE.my_cur);
  p_result_cur := DBMS_SQL.to_refcursor(cur_handle);
  return p_result_cur;
end;

这种实现和原生open for的SYS_REFCURSOR性能完全一致，全程只有指针传递，没有内存拷贝。

2. 直接通过cx_Oracle访问包内游标（连自定义函数都不需要写） #

实际上根本不需要在数据库端额外写my_func这类包装函数，只要包内的游标是公开声明的，就可以直接在PL/SQL块中打开游标、绑定参数，将句柄直接作为输出参数传给cx_Oracle使用，示例代码：

import cx_Oracle

# 建立数据库连接
conn = cx_Oracle.connect("用户名/密码@本地服务名")
# 初始化输出游标对象
out_cursor = conn.cursor()

# 执行PL/SQL块，直接打开包内带参游标，绑定参数
conn.cursor().execute("""
BEGIN
  OPEN MY_PACKAGE.my_cur(:input_p1);
  :output_cur := MY_PACKAGE.my_cur;
END;
""", input_p1=1001, output_cur=out_cursor)

# 直接遍历游标拉取数据，和普通ref cursor用法完全一致
for row in out_cursor:
    print(row)

极端老版本的兼容方案 #

如果使用的是10g及更早的Oracle版本，不支持游标句柄转ref cursor的能力，最优的做法是把包游标对应的复杂SELECT逻辑封装为包内的公共SQL常量，返回ref cursor时直接通过动态SQL执行该常量，不需要重复维护长SQL逻辑，也完全不需要存储临时集合。

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