和Null有關的函式

關於null相關的函式在日常的工作中還有比較實用的，可能會碰到各種和Null校驗相關的情況，大體有以下幾種。
case when
decode
nvl
nvl2
lnnvl
nullif
coalsce

--&gtcase when
case when算是這個比較通用的方法，可以支援各種複雜的邏輯判斷，對於Null的校驗也不在話下，可能相對來說程式會略顯臃腫。在效能方面還沒有做更多的測試，暫時不好評估。

SQL> select case when (1=1) then 2 end from dual;  --&gt如果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
---------------------

 
--&gtnvl
  select nvl('expr1 is not null','expr1 is  null') from dual;  --&gt如果expr1不為Null，就輸出expr1 is null
NVL('EXPR1ISNOTNU
-----------------
expr1 is not null

SQL>   select nvl(null,'expr1 is null') from dual; --&gt如果expr1為Null，就輸出expr1 is null
NVL(NULL,'EXP
-------------
expr1 is null
 
--&gtnvl2(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; --&gt可以看到,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; --&gt可以看到expr1不為Null，就輸出expr2
NVL2('1','EXPR1ISNULLRETUR
--------------------------
expr1 is null return expr3

--&gtlnnvl
這個問題可以建立一個表來測試一下,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

--&gtnullif
這個函式類似如下的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

--&gtCOALSCE
這個函式類似如下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