ORACLE单行函数与多行函数之六：通用函数示例

主要实验了几个常用的判断NULL及DECODE的函数。 1.NVL(a,b):判断A字段的内容为null时,运算结果为指定的。A不为NULL，则显示A的。 如果COMM的为NULL，则返回显示为200. BYS@bys1Select COMM,NVL(COMM,200) From EMP where rownum3; COMM NVL(COMM,200) ------

主要实验了几个常用的判断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 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 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