oracle NVL、NVL2,、NULLIF、COALESCE函數的用法__靜態函數

        Oracle中函數中除了字元函數、日期函數、數學函數、以及轉換函式等等，還有一些函數是通用函數，比如：NVL、NVL2,、NULLIF、COALESCE。
       下面就來看一下它們的用法：

1.NVL函數
       NVL(expr1,expr2)

如果expr1和expr2的資料類型一致，則：
如果expr1為空白(null),那麼顯示expr2，
如果expr1的值不為空白，則顯示expr1。

例如：
SQL> select ename,NVL(comm,-1) from emp;

ENAME                NVL(COMM,-1)
-------------------- ------------
SMITH                          -1
ALLEN                         300
WARD                          500
JONES                          -1
MARTIN                       1400
BLAKE                          -1
CLARK                          -1
SCOTT                          -1
KING                           -1
TURNER                          0
ADAMS                          -1
JAMES                          -1
FORD                           -1
MILLER                         -1

14 rows selected.

當兩個參數資料類型不同時，oracle會將兩個參數進行隱式轉換，如果不能隱式轉換剛會報錯，隱式轉換規則如下：
        1.如果參數1為字元型，則把參數2轉換為參數1的類型，傳回值為VARCHAR2
        2.如果參數1為數值型，則判斷兩個參數的最高數值優先順序（如雙精實數比單精實數優先順序高），然後轉換成高優先順序的數值，返回該類型的值.

--第一個參數是number類型，第二個參數是varchar類型，無法隱式轉換，報錯。
SQL> select ename,NVL(comm, 'no comm') from emp;
select ename,NVL(comm, 'no comm') from emp
                       *
ERROR at line 1:
ORA-01722: invalid number


--將第一個參數變成字元類型，第二個參數是number類型，那麼第二個參數自動轉成字元類型，返回字串。
SQL> select ename,NVL(comm || '',-1) from emp;

ENAME                NVL(COMM||'',-1)
-------------------- -------------------------
SMITH                -1
ALLEN                300
WARD                 500
JONES                -1
MARTIN               1400
BLAKE                -1
CLARK                -1
SCOTT                -1
KING                 -1
TURNER               0
ADAMS                -1
JAMES                -1
FORD                 -1
MILLER               -1

14 rows selected.

2.NVL2函數
   NVL2(expr1,expr2, expr3)

如果expr1不為NULL，返回expr2； expr1為NULL，返回expr3。
expr2和expr3類型不同的話，expr3會轉換為expr2的類型，轉換不了，則報錯。

SQL> select ename,nvl2(comm,comm,-1) from emp;

ENAME                NVL2(COMM,COMM,-1)
-------------------- ------------------
SMITH                                -1
ALLEN                               300
WARD                                500
JONES                                -1
MARTIN                             1400
BLAKE                                -1
CLARK                                -1
SCOTT                                -1
KING                                 -1
TURNER                                0
ADAMS                                -1
JAMES                                -1
FORD                                 -1
MILLER                               -1

14 rows selected.

3. NULLIF函數
       NULLIF(expr1,expr2)
如果expr1和expr2相等則返回空(NULL)，否則返回expr1。

-- 顯示出那些換過工作的人員原工作、現工作，沒有換工作的，顯示為null。
SQL> col last_name for a15
SQL> SELECT e.last_name, e.job_id,j.job_id,NULLIF(e.job_id, j.job_id) "Old Job ID"
  2  FROM employees e, job_history j
  3  WHERE e.employee_id = j.employee_id
  4  ORDER BY last_name;

LAST_NAME       JOB_ID               JOB_ID               Old Job ID
--------------- -------------------- -------------------- --------------------
De Haan         AD_VP                IT_PROG              AD_VP
Hartstein       MK_MAN               MK_REP               MK_MAN
Kaufling        ST_MAN               ST_CLERK             ST_MAN
Kochhar         AD_VP                AC_MGR               AD_VP
Kochhar         AD_VP                AC_ACCOUNT           AD_VP
Raphaely        PU_MAN               ST_CLERK             PU_MAN
Taylor          SA_REP               SA_REP
Taylor          SA_REP               SA_MAN               SA_REP
Whalen          AD_ASST              AC_ACCOUNT           AD_ASST
Whalen          AD_ASST              AD_ASST

10 rows selected.

4.coalesce函數
     coalesce(expr1, expr2, expr3….. exprn)
返回運算式中第一個非空運算式，如果都為空白則返回空值。
所有運算式必須是相同類型，或者可以隱式轉換為相同的類型，否則報錯。

Coalese函數和NVL函數功能類似，只不過選項更多。
-
-這裡，和nvl的效果是一樣的
SQL> select ename,coalesce(comm,-1) from emp;

ENAME                COALESCE(COMM,-1)
-------------------- -----------------
SMITH                               -1
ALLEN                              300
WARD                               500
JONES                               -1
MARTIN                            1400
BLAKE                               -1
CLARK                               -1
SCOTT                               -1
KING                                -1
TURNER                               0
ADAMS                               -1
JAMES                               -1
FORD                                -1
MILLER                              -1

14 rows selected.

SQL> select ename,coalesce(comm,null,-2,-5) from emp;

ENAME                COALESCE(COMM,NULL,-2,-5)
-------------------- -------------------------
SMITH                                       -2
ALLEN                                      300
WARD                                       500
JONES                                       -2
MARTIN                                    1400
BLAKE                                       -2
CLARK                                       -2
SCOTT                                       -2
KING                                        -2
TURNER                                       0
ADAMS                                       -2
JAMES                                       -2
FORD                                        -2
MILLER                                      -2

14 rows selected.