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