Oracle SQL中CASE条件执行顺序异常：为何代码报错PL/SQL却正常？

核心问题 #

Oracle 19c及以下版本的SQL中，CASE表达式不保证条件的短路求值，这和PostgreSQL、PL/SQL的行为存在差异，是导致代码报错的根本原因。

具体解释 #

• 你写的Oracle SQL逻辑里，预期当oopname不等于'PAY_OPERDATE'时，后续的length(paramvalue)=10和to_number(substr(paramvalue,4,2))<=12应该直接跳过，避免非数字字符串的转换错误。但Oracle的SQL优化器是基于成本的优化器，它会根据执行计划的效率需求调整条件的执行顺序——哪怕你写在后面的条件，也可能被提前执行，导致转换报错。
• PL/SQL是过程化语言，严格按照代码书写的顺序执行判断，前面的条件不满足就直接终止后续判断，所以少量修改后能正常运行。
• PostgreSQL的CASE表达式则严格遵循短路求值规则：只要前面的条件不成立，后面的表达式就不会被执行，因此相同逻辑在PostgreSQL中可以正常运行。

代码对比 #

报错的Oracle SQL代码

with data as(
select null oopname, 'fgdfhdfhdf' paramvalue from dual
)
select 
  case 
    when  oopname = 'PAY_OPERDATE' 
      and length(paramvalue)=10 
      and to_number(substr(paramvalue,4,2))<=12 
    then paramvalue 
  end
from data

正常运行的PostgreSQL代码

select case when oopname = 'PAY_OPERDATE'  and length(paramvalue)=10 
and TO_NUMBER(substr(paramvalue,4,2),'99') <= 12
then paramvalue end aa
from (
select ''::text oopname, '02.hh.2022y'::text paramvalue 
) a

解决方法 #

要在Oracle中实现短路逻辑，可以把可能报错的转换放到嵌套CASE中，确保只有前面条件满足时才执行转换：

with data as(
select null oopname, 'fgdfhdfhdf' paramvalue from dual
)
select 
  case 
    when oopname = 'PAY_OPERDATE' 
      and length(paramvalue)=10 
      then case when to_number(substr(paramvalue,4,2))<=12 then paramvalue end
  end
from data

或者先用正则判断子串是否为数字，再执行转换：

with data as(
select null oopname, 'fgdfhdfhdf' paramvalue from dual
)
select 
  case 
    when oopname = 'PAY_OPERDATE' 
      and length(paramvalue)=10 
      and regexp_like(substr(paramvalue,4,2),'^[0-9]{2}$')
      and to_number(substr(paramvalue,4,2))<=12 
    then paramvalue 
  end
from data

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