综合数据类型函数_NVL使用

l   综合数据类型函数

NVL (expr1, expr2)

如果expr1为非空，就返回expr1, 如果expr1为空返回expr2，两个表达式的数据类型一定要相同。

NVL2 (expr1, expr2, expr3)

如果expr1为非空，就返回expr2, 如果expr1为空返回expr3

NULLIF (expr1, expr2)

如果expr1和 expr2相同就返回空，否则返回expr1

COALESCE (expr1, expr2, ..., exprn)

返回括号内第一个非空的值。

 

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

 

ENAME                 COMM NVL(COMM,-1)

-------------------- ----- ------------

SMITH                                -1

ALLEN                  300          300

WARD                   500          500

JONES                                -1

MARTIN                1400         1400

BLAKE                                -1

CLARK                                -1

KING                                 -1

TURNER                   0            0

JAMES                                -1

FORD                                 -1

MILLER                               -1

有奖金就返回奖金，奖金为空就返回-1。

 

select sal+comm ,sal+nvl(comm,0),nvl2(comm,'工资加奖金','纯工资') "收入类别" from emp;

有奖金就返回‘工资加奖金‘，奖金为空就返回‘纯工资’。

SQL> select sal+comm,sal+nvl(comm,0),nvl2(comm,'g+j','cun')"收入类别" from emp;

  SAL+COMM SAL+NVL(COMM,0) ???
---------- --------------- ---
                       800 cun
      1900            1900         g+j
      1750            1750 g+j
                      2975 cun
      2650            2650 g+j
                      2850 cun
                      2450 cun
                      3000 cun
                      5000 cun
      1500            1500 g+j
                      1100 cun

 SAL+COMM SAL+NVL(COMM,0) ???
---------- --------------- ---
                       950 cun
                      3000 cun
                      1300 cun

14 rows selected.