扩展NULL加法运算

DM技术交流QQ群：940124259

1. 场景描述

经常在SQL查询中遇到某些字段可为NULL的属性，当取出某一行数据记录时，需利用该字段进行算数运算（数字），一碰见NULL空值整个运算结果为NULL，说明NULL值具有一切皆空传染性。当开发人员向我方提出两者（两个字段，值未知，可NULL可值）参与运算，当且仅当一个操作数据为NULL，可视NULL为0参与正确的算数运算，而两者都为NULL，则整个运算结果为NULL。 为处理这种两者之间的关系，即两者之间的排序组合，分四种情况，再用数据库内置函数已经无法满足当前需求，得自定义函数实现。

举例： TEST表有A，B两个字段，并且两个字段的属性可以为空，针对两者相加的情形以下 ⑴ A(NOTNULL) + B(NOTNULL) ⑵ A(NOTNULL) + B(NULL) ⑶ A(NULL) + B(NOTNULL) ⑷ A(NULL) + B(NULL)

2. 解决方法

自定义一个确定性函数，在内部通过条件判断控制NULL转换。此案例可灵活采用这种方案，适用改写乘除减法运算。

TIPS: 之所以使用NVL函数转换NULL值，是因为它对数据类型自动转换非常友好，可读性也强，即NOTNULL。

create function numadd(v1 NUMBER, v2 NUMBER) 
  return number deterministic
as
  sum number := 0;
begin
   case 
      when not (v1 is null and v2 is null) 
      then     
          sum = nvl(v1, 0) + nvl(v2, 0) ;    
      else
          sum = v1 + v2 ;
   end case;

   return sum;
end numadd;
/

3. 测试用例

本人环境测试用例

drop table if exists test;
create table test (a int, b int);
insert into test values(0, 0), 
	                   (1, 1), 
	                   (null, 2), 
	                   (3, null), 
	                   (null,null);
commit;
select a, b, numadd(a, b) from test;

注意：<>这个显示占位符是我的管理工具自定义的，表示真实的NULL值，而不是NULL普通字符串，为区分真空值（根本没值）和伪空值（实际字符串）。