如何避免在SELECT列表中调用函数 及WITH子句复用函数值的方法

你之前编写的WITH片段缺少SELECT关键字，补全后搭配无关联条件的CROSS JOIN即可复用预计算的函数值，整个查询过程中函数仅会执行1次：

WITH table_values AS (
    SELECT MYFUNCTION(param1 => 'TEST') test_val FROM dual
)
SELECT 
    CASE WHEN TO_NUMBER(t1.value) > TO_NUMBER(tv.test_val)
         THEN 'Y' 
         ELSE 'N' 
    END YES_NO
FROM TABLE1 t1
CROSS JOIN table_values tv

这个方案完全适配你需要在大型查询多位置复用函数结果的需求，不管后续在SELECT、WHERE、GROUP BY等任意位置需要调用这个值，都不会重复触发函数执行。

• 如果该函数为确定性函数（相同入参永远返回相同结果，无副作用、不依赖会话/动态系统变量），可以直接在函数定义中添加DETERMINISTIC关键字声明，Oracle会自动对相同入参的函数调用做结果缓存，不需要修改原有业务SQL即可避免重复执行：

CREATE OR REPLACE FUNCTION MYFUNCTION(param1 VARCHAR2) 
RETURN VARCHAR2 
DETERMINISTIC -- 新增确定性声明
IS
-- 原有函数逻辑
BEGIN
  -- 原有处理逻辑
END;

• 如果你使用的是Oracle 11g及以上版本，还可以用标量子查询缓存实现更简洁的写法，Oracle同样只会执行1次函数调用，后续复用缓存结果：

SELECT 
    CASE WHEN TO_NUMBER(t1.value) > TO_NUMBER((SELECT MYFUNCTION(param1 => 'TEST') FROM dual))
         THEN 'Y' 
         ELSE 'N' 
    END YES_NO
FROM TABLE1 t1

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