ORACLE單行函式與多行函式之六：通用函式示例

主要實驗了幾個常用的判斷NULL值及DECODE的函式。

1.NVL(a,b):判斷A欄位的內容為null時,運算結果為指定的值。A不為NULL，則顯示A的值。

如果COMM的值為NULL，則返回顯示為200.
BYS@bys1>Select COMM,NVL(COMM,200) From EMP where rownum<3;
      COMM NVL(COMM,200)
---------- -------------
                     200
       300           300

2.NVL2(a,b,c):如果第一個表示式a的值不為null,顯示錶達式2的值;如果a為null，顯示錶達式C的值。
如果comm值不為NULL，返回678；如果為NULL，返回999.
BYS@bys1>select sal,comm,nvl2(comm,678,999) from emp where rownum<5;
       SAL       COMM NVL2(COMM,678,999)
---------- ---------- ------------------
       800                           999
      1600        300                678
      1250        500                678
      2975                           999

3.NULLIF:如果前後兩個表示式的內容相等的,那就返回null,否則,返回第一個表示式的值

如果工資sal=3000，則返回NULL值。
BYS@bys1>select sal,ename,nullif(sal,3000) from emp order by 1 desc;
       SAL ENAME      NULLIF(SAL,3000)
---------- ---------- ----------------
      5000 KING                   5000
      3000 FORD
      3000 SCOTT
      2975 JONES                  2975
      2850 BLAKE                  2850
      2450 CLARK                  2450
      1600 ALLEN                  1600
      1500 TURNER                 1500
      1300 MILLER                 1300
      1250 WARD                   1250
      1250 MARTIN                 1250
      1100 ADAMS                  1100
       950 JAMES                   950

4.COALESCE函式:用來匹配多個欄位的值,如果表示式1的值為null,顯示錶達式2的值,如果表示式2也為空,顯示錶達式3的值,依次類推

如下語句是，如果COMM是空，則顯示MGR。如果COMM也為空，則顯示EMPNO。如果EMPNO也為空，則顯示886.

KING的COMM是NULL值，MGR也是NULL，所以返回顯示了工號7839。

SMITH的COMM是NULL值，MGR為值,所以返回顯示了MGR的號碼：7902

BYS@bys1>select ename,empno,mgr,comm,coalesce(comm,mgr,empno,886) from emp;

ENAME           EMPNO        MGR       COMM COALESCE(COMM,MGR,EMPNO,886)
---------- ---------- ---------- ---------- ----------------------------
SMITH            7369       7902                                    7902
ALLEN            7499       7698        300                          300
WARD             7521       7698        500                          500
JONES            7566       7839                                    7839
MARTIN           7654       7698       1400                         1400
BLAKE            7698       7839                                    7839
CLARK            7782       7839                                    7839
SCOTT            7788       7566                                    7566
KING             7839                                               7839
TURNER           7844       7698          0                            0
ADAMS            7876       7788                                    7788
JAMES            7900       7698                                    7698
FORD             7902       7566                                    7566

MILLER           7934       7782                                    7782

5.decode函式，DECODE函式可以直接對NULL做等值判斷

decode(條件,值1,返回值1,值2,返回值2,...值n,返回值n,預設值) 該函式的含義如下：
IF 條件=值1 THEN
　　　　RETURN(翻譯值1)
ELSIF 條件=值2 THEN
　　　　RETURN(翻譯值2)
　　　　......
ELSIF 條件=值n THEN
　　　　RETURN(翻譯值n)
ELSE
　　　　RETURN(預設值)
END IF

如下兩條語句，decode(5,1,'ok',2)中，如5等於1，返回OK。不等於1，返回2

BYS@bys1>select decode(5,1,'ok',2) from dual;
D
-
2
BYS@bys1>select decode(1,1,'ok',2) from dual;
DE
--
ok
如下的表：decode(xx,9,1,0),即XX行的值為9，則顯示1，不為9，則顯示0.

BYS@bys1>select * from aa;
        XX
----------
         9
         5
         5
         9
         5
         9
         9
7 rows selected.
BYS@bys1>select decode(xx,9,1,0),decode(xx,5,1,0) from aa;
DECODE(XX,9,1,0) DECODE(XX,5,1,0)
---------------- ----------------
               1                0
               0                1
               0                1
               1                0
               0                1
               1                0

可用於統計等於某個數值的列總共有多少行。分組函式不統計NULL值

BYS@bys1>select count(decode(xx,9,1,null)),count(decode(xx,5,1,0)) from aa;
COUNT(DECODE(XX,9,1,NULL)) COUNT(DECODE(XX,5,1,0))
-------------------------- -----------------------
                         4                       7