Oracle中Function与Procedure返回关联数组的性能差异咨询

核心性能差异原因 #

PL/SQL中函数返回集合和存储过程OUT参数返回集合的底层值传递逻辑不同，是性能差距的核心来源：

• 函数的返回值强制按值传递：你的函数实现中，首先需要把查询结果BULK COLLECT写入内部局部变量l_aaToRet，返回时会完整拷贝整个集合到返回值缓冲区，调用方接收时还要再把缓冲区的集合拷贝赋值给目标变量l_aaDummy，全程产生2次额外的全集合拷贝操作。
• 普通OUT参数存储过程仅产生1次额外拷贝：OUT参数默认也是按值传递，但过程内部直接把查询结果BULK COLLECT写入形参，仅在过程执行结束时，把形参的集合拷贝回调用方的实参变量，比函数少了1次全集合拷贝。
• NOCOPY修饰的OUT参数是按引用传递：直接把调用方实参的内存地址传给过程形参，查询结果直接写入调用方的变量内存，没有额外拷贝操作，因此性能最优。

你的测试结果完全符合拷贝次数对应的性能损耗比例：NOCOPY耗时最少，普通存储过程耗时约为NOCOPY的2.2倍，函数耗时约为普通存储过程的5.3倍，和额外拷贝次数的差异完全匹配。

测试有效性说明 #

你的测试设计没有明显缺陷：

• 提前执行了预热查询，确保SQL执行计划已缓存，排除了硬解析的影响
• 用10%采样数据的多次循环测试，结果取平均，排除了单次执行的波动干扰
• 多环境复现一致，排除了特定实例的配置影响

优化建议 #

如果需要返回大集合，优先使用NOCOPY修饰的OUT参数存储过程，Oracle不支持函数返回值加NOCOPY修饰，因此返回大集合时函数天然存在性能劣势。

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