oracle 使用nullif解决除数为零的问题

川长思鸟来 2021-09-26 17:50 751阅读 0赞

先来说一下**nullif**的语法。  
![这里写图片描述][70]

> NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.  
>   
> If both arguments are numeric data types, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that data type, and returns that data type. If the arguments are not numeric, then they must be of the same data type, or Oracle returns an error.  
>   
> The NULLIF function is logically equivalent to the following CASE expression:  
> CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

如果两个参数相等，返回null，否则返回第一个。第一个参数不可指定为空。对于非数字类型参数，数据类型必须一致。对于数值数据类型，会隐式的转化为更高优先级的数据类型。（这个理解可能有误，我测试了int，integer，float。但是最终都转化为number类型）。

一般来说，我们处理“除数为零”的问题会用到decode（当然也可以用case，但是写起来代码更多些）。比如

dividend / decode(divisor, 0, null, divisor)

但是在除数divisor非常复杂的时候，就需要把这一大串代码写两遍，或者是再套一层view。无论是哪种，都是极其不利于维护的。

1 / 
    decode((sum(t.val) over(order by t.c) +
           nvl(lead(val) over(partition by b order by c), 0)) /
          sum(val) over(partition by b order by c),
          0,
          null,
          (sum(t.val) over(order by t.c) +
           nvl(lead(val) over(partition by b order by c), 0)) / sum(val)
          over(partition by b order by c))

对于这种复杂表达式的除数，每回修改都要改两遍，很容易出错。

利用**nullif**，可以让除数只写一次。  
因为 `decode(divisor, 0, null, divisor)` 与 `nullif(divisor, 0)` 是等效的。

[70]: /images/20210923/e34fdb94c43c420baa660436d68ccb1a.png