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.