和Null有关的函数(r3笔记第48天)
关于null相关的函数在日常的工作中还有比较实用的，可能会碰到各种和Null校验相关的情况，大体有以下几种。
case when
decode
nvl
nvl2
lnnvl
nullif
coalsce
-->case when
case when算是这个比较通用的方法，可以支持各种复杂的逻辑判断，对于Null的校验也不在话下，可能相对来说程序会略显臃肿。在性能方面还没有做更多的测试，暂时不好评估。
SQL> select case when (1=1) then 2 end from dual; -->如果1=1满足，就返回2,否则返回null
CASEWHEN(1=1)THEN1END
---------------------
2
SQL> select case when (1=2) then 2 end from dual; -如果1=2成立，就返回2，否则返回null
CASEWHEN(1=2)THEN2END
----------------------->nvl
select nvl('expr1 is not null','expr1 is null') from dual; -->如果expr1不为Null，就输出expr1 is null
NVL('EXPR1ISNOTNU
-----------------
expr1 is not null
SQL> select nvl(null,'expr1 is null') from dual; -->如果expr1为Null，就输出expr1 is null
NVL(NULL,'EXP
-------------
expr1 is null-->nvl2(expr1,expr2,expr3)
If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.
SQL> select nvl2(null,'expr1 is null return expr3','expr1 is not null return expr2') from dual; -->可以看到,expr1为null,就输出了expr3
NVL2(NULL,'EXPR1ISNULLRETURNEX
------------------------------
expr1 is not null return expr2
SQL> select nvl2('1','expr1 is null return expr3','expr1 is not null return expr2') from dual; -->可以看到expr1不为Null，就输出expr2
NVL2('1','EXPR1ISNULLRETUR
--------------------------
expr1 is null return expr3-->lnnvl 这个问题可以创建一个表来测试一下,test_null，返回的结果都是相反的。
create table test_null (id number);
insert into test_null values(1);
insert into test_null values(2);
insert into test_null values(3);
SQL> select id from test_null where lnnvl(id<1); --如果id<1,就输出id>=1
ID
----------
1
2
3
SQL> select id from test_null where lnnvl(id>=1); --如果id>=1,就输出id>=1
no rows selected
SQL> select id from test_null where lnnvl(id=1);
ID
----------
2
3-->nullif 这个函数类似如下的case when形式
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
SQL> select nullif(1,1)from dual;
NULLIF(1,1)
-----------
SQL> select nullif(1,2) from dual;
NULLIF(1,2)
-----------
1
SQL> select nullif(2,1)from dual;
NULLIF(2,1)
-----------
2
SQL> select nullif(1,null)from dual;
NULLIF(1,NULL)
--------------
1
SQL> select nullif(null,1)from dual; -可以看到如果expr1是Null，会报出ora-00932的错误，如果expr2是null，还是满足条件有输出的。
select nullif(null,1)from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR
decode
SQL> select decode(1,1,3,4)from dual;
DECODE(1,1,3,4)
---------------
3
SQL> select decode(1,2,3,4)from dual;
DECODE(1,2,3,4)
---------------
4-->COALSCE 这个函数类似如下case when的形式
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END可以创建一个临时的表来测试一下。可以看到在id1,id2,id3为空的时候输出。
create table test_coalesce (id1 number,id2 number,id3 number);
insert into test_coalesce values(1,null,null);
insert into test_coalesce values(2,1,null);
insert into test_coalesce values(null,null,1);
SQL> select id1,id2,id3 from test_coalesce;
ID1 ID2 ID3
---------- ---------- ----------
1
2 1
1
SQL> select coalesce(id1,id2,id3) from test_coalesce;
COALESCE(ID1,ID2,ID3)
---------------------
1
2
1 本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2014-11-09，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
