PL/SQL中使用外部变量的游标是否合规？性能影响如何？

问题背景 #

查看旧代码时，发现一个函数中的游标在WHERE子句中使用了外部变量，变量并未在游标声明中定义，而是在打开游标前完成赋值。脱敏后的函数代码如下：

create FUNCTION VERY_STRANGE_FUNCTION(
  input_field IN VARCHAR2,
  input_field_value IN VARCHAR2
   )
RETURN VARCHAR2
IS
out_RESULT VARCHAR2(4000);

BEGIN
 DECLARE
    var_ID_ORDER   VARCHAR2(100);
    var_ID_ORDER_2 VARCHAR2(100);
    OUTPUT_VAR     VARCHAR2(4000);
    
    CURSOR cur_ORDERS IS
        SELECT ID, NAME, COMPANY
        FROM ORDERS
        WHERE ORDER_ID1 = var_ID_ORDER
          AND ORDER_ID2 = var_ID_ORDER_2;

BEGIN
    var_ID_ORDER := input_field;
    var_ID_ORDER_2 := input_field_value;

    FOR rec IN cur_ORDERS
        LOOP
            OUTPUT_VAR := rec.COMPANY;
        END LOOP;
    RETURN (OUTPUT_VAR);
end;end;

注：原代码存在笔误，var_ID_ORDER2应为var_ID_ORDER_2，且赋值语句缺少分号，已修正以便阅读。

我所学的带变量游标写法是显式参数形式：

CURSOR cur_dummy (var1 VARCHAR2, var2 NUMBER) 
IS
  select 
  ID,NAME,NUMBER 
  from ORDERS 
  where ID = var1 
    AND NUMBER=var2;

核心疑问 #

• 第一种使用外部变量的游标写法是否合法正确？
• 这类游标是否会无法被缓存或复用？考虑到函数复杂（约3000行）、包含10个多表连接的同类游标且被频繁调用（约100次），会有什么性能影响？

解答 #

1. 写法的合法性 #

这种引用外部变量的游标写法完全合法，PL/SQL允许游标引用其作用域内已声明的变量。但要注意两个关键：

• 变量必须在游标的作用域范围内（示例中变量和游标同属一个DECLARE块，符合要求）；
• 打开游标前必须完成变量赋值，否则游标会使用变量的初始默认值（示例中在FOR循环前完成了赋值，逻辑没问题，但原代码的笔误会导致运行报错）。

2. 缓存与复用问题 #

这种写法确实会对游标缓存（共享游标）产生负面影响：

• 显式带参数的游标会生成通用的SQL模板，参数通过绑定变量传递，Oracle能轻松识别并复用该游标，大幅减少硬解析的开销；
• 而引用外部PL/SQL变量的游标，Oracle在解析时无法将其识别为通用的绑定变量模板，当变量值变化时，很可能无法复用之前的游标，导致每次调用都触发硬解析。对于多表连接的复杂SQL，硬解析的CPU消耗和库缓存压力会非常显著。

结合你的场景（函数复杂、10个同类游标、单次查询调用约100次），这种写法的性能隐患会被放大：频繁的硬解析会累积大量不必要的开销，拖慢整体执行效率。

优化建议 #

• 将所有这类引用外部变量的游标改为显式带参数的形式，最大化游标共享，减少硬解析；
• 修正代码中的语法错误（比如变量名拼写、分号缺失），避免运行时异常；
• 如果函数的输出仅由输入参数决定，可以添加DETERMINISTIC关键字，帮助Oracle进一步优化执行计划和缓存。

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