Oracle PL/SQL：基于表数据批量调用包函数并高效返回结果

普通查询形式实现 #

直接用SQL集合操作替代逐行循环的PL/SQL，Oracle支持通过TABLE()函数调用返回表类型的包函数，关联原表即可一次性返回所有结果：

传统关联写法 #

SELECT t.*
FROM SCHEMA.COMPANY c,
     TABLE(SCHEMA.PKG_COMPANY.GET_DATA_COMPANY(c.COLUM_TABLE)) t;

ANSI标准JOIN写法 #

SELECT t.*
FROM SCHEMA.COMPANY c
CROSS JOIN TABLE(SCHEMA.PKG_COMPANY.GET_DATA_COMPANY(c.COLUM_TABLE)) t;

如果函数可能返回空结果，且需要保留原表对应行记录（即使函数无返回），可改用LEFT JOIN：

SELECT t.*
FROM SCHEMA.COMPANY c
LEFT JOIN TABLE(SCHEMA.PKG_COMPANY.GET_DATA_COMPANY(c.COLUM_TABLE)) t ON 1=1;

性能优化建议 #

1. 标记函数为确定性函数：如果相同COLUM_TABLE参数调用GET_DATA_COMPANY总是返回相同结果，给函数添加DETERMINISTIC属性，Oracle会自动缓存结果，避免重复计算：

   FUNCTION GET_DATA_COMPANY(p_param IN YOUR_PARAM_TYPE) RETURN YOUR_TABLE_TYPE
   DETERMINISTIC
   IS
   BEGIN
     -- 函数原有逻辑
   END;
   

2. 开启并行查询：针对50万条数据的大表扫描，可通过并行提示加速（数字为并行度，根据服务器CPU核心数调整）：

   SELECT /*+ PARALLEL(c, 8) PARALLEL(t, 8) */ t.*
   FROM SCHEMA.COMPANY c
   CROSS JOIN TABLE(SCHEMA.PKG_COMPANY.GET_DATA_COMPANY(c.COLUM_TABLE)) t;
   

3. 优化原表索引：给SCHEMA.COMPANY表的COLUM_TABLE列创建索引，减少原表扫描开销。

4. 摒弃行级动态SQL：你原来的PL/SQL逐行执行动态SQL属于行级处理（row-by-row），Oracle对此类操作优化极差，50万条数据会导致大量上下文切换，集合式SQL能彻底解决这个性能问题。

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