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普通字符串,为区分真空值(根本没值)和伪空值(实际字符串)。