Oracle中为何选用标量SQL宏而非带PRAGMA UDF的函数？

在实际使用中我发现，标量宏（SQL_MACRO(scalar)）和添加PRAGMA UDF编译指令的自定义函数，都能提升SQL中函数逻辑的执行速度，两种写法都可以正常运行。我个人更倾向使用带PRAGMA UDF的函数，因为这种写法可以明确指定函数的返回值类型，因此想了解标量宏相比这类UDF函数有哪些独有的优势。

测试用到的两组对照代码如下：

-- 标量宏实现版本
WITH
 FUNCTION fc 
        RETURN VARCHAR2 SQL_MACRO(scalar)
    IS
    BEGIN
        RETURN q'{
      1
  }';
    END;
SELECT fc()
  FROM dual
 
-- PRAGMA UDF实现版本
WITH
 FUNCTION fc 
        RETURN integer
    IS PRAGMA UDF;
    BEGIN
        RETURN      1;
    END;
SELECT fc()
  FROM dual

标量宏相比PRAGMA UDF的核心优势主要有以下几点：

• 零函数调用开销：PRAGMA UDF只是减少了SQL引擎和PL/SQL引擎上下文切换的成本，执行阶段仍然存在独立的函数调用流程。标量宏是在SQL解析优化阶段就直接把宏定义的表达式片段替换到调用位置，执行阶段完全没有函数调用环节，性能和直接把计算逻辑写在SQL语句中完全一致，处理千万级以上大数据量时，性能优势会非常明显。
• 对SQL优化器完全透明：宏逻辑内联后就是原生SQL的一部分，Oracle优化器可以对这部分逻辑应用常量折叠、谓词下推、索引匹配、执行计划重排等所有常规SQL优化规则。比如在WHERE条件中调用宏做判断，优化器可以直接识别条件关联的列，匹配对应索引生成高效执行计划；但PRAGMA UDF对优化器来说是黑盒，无法解析内部逻辑，很多优化规则都无法触发，成本估算也容易出现偏差，经常会生成低效执行计划。
• 没有调用场景限制：带PRAGMA UDF的函数是专门为SQL调用场景做的编译优化，如果在普通PL/SQL块中调用这类函数，性能反而会低于普通自定义函数。标量宏不存在这个问题，所有支持SQL表达式的位置都可以正常使用，不需要根据调用场景做特殊适配。
• 支持动态逻辑拼接：标量宏的本质是返回可执行的SQL文本片段，开发时可以根据传入的参数动态生成不同的计算逻辑，比如根据入参选择不同的统计列、切换不同的计算规则，这种SQL解析阶段动态拼接逻辑的能力，是编译期就固定执行逻辑的PRAGMA UDF无法实现的。

关于你提到的返回值类型问题：标量宏不需要显式指定固定返回类型，是因为它完全遵循内联后表达式的类型推导规则，和直接在SQL中编写表达式的类型判定逻辑完全一致，正常使用不会出现类型不匹配的问题。

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud