SQL语句 之 通用函数
点击“蓝字”关注我们
晟数学院DBA成长日记--SQL篇
通用函数
NVL
语法：NVL(数字|列，默认值)
如果现实的数字是null，则使用默认值表示。
（1）如果是null，用4代替。如果是3，就继续输出3
SCOTT@SDEDU> select nvl(null,4),nvl(3,0) from dual;
NVL(NULL,4) NVL(3,0)
------------------- ----------
4 3
（2）因为comm的部分值为null，所以导致
(sal+comm)*12的部分值也为null
SCOTT@SDEDU> select ename,job,(sal+comm)*12 from emp;
ENAME JOB (SAL+COMM)*12
-------------- --------------------- -------------------------
SMITH CLERK
ALLEN SALESMAN 22800
WARD SALESMAN 21000
JONES MANAGER
MARTIN SALESMAN 31800
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN 18000
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 rows selected.
（3）nvl(comm,0)代表如果comm列的值为null，用0代替。
SCOTT@SDEDU> select ename,job,(sal+nvl(comm,0))*12 from emp;
ENAME JOB (SAL+NVL(COMM,0))*12
------------ --------------------- --------------------------------
SMITH CLERK 9600
ALLEN SALESMAN 22800
WARD SALESMAN 21000
JONES MANAGER 35700
MARTIN SALESMAN 31800
BLAKE MANAGER 34200
CLARK MANAGER 29400
SCOTT ANALYST 36000
KING PRESIDENT 60000
TURNER SALESMAN 18000
ADAMS CLERK 13200
JAMES CLERK 11400
FORD ANALYST 36000
MILLER CLERK 15600
14 rows selected.
NVL2
语法：NVL2(数字|列，返回结果1（不为空显示），返回结果2（为空显示）)
判断指定的列是否为null，如果不为null则返回1，如果为空则返回结果2.
（4）查询每个雇员的姓名、职位、年薪，如果不为空返回comm+sal，如果为空返回sal
SCOTT@SDEDU> select ename,job,
nvl2(comm,comm+sal,sal)*12 from emp;
ENAME JOB NVL2(COMM,COMM+SAL,SAL)*12
------------ ------------------- -----------------------------------------
SMITH CLERK 9600
ALLEN SALESMAN 22800
WARD SALESMAN 21000
JONES MANAGER 35700
MARTIN SALESMAN 31800
BLAKE MANAGER 34200
CLARK MANAGER 29400
SCOTT ANALYST 36000
KING PRESIDENT 60000
TURNER SALESMAN 18000
ADAMS CLERK 13200
JAMES CLERK 11400
FORD ANALYST 36000
MILLER CLERK 15600
14 rows selected.
NULLIF
语法：NULLIF(表达式1，表达式2)
比较表达式1和表达式2的结果是否相等，如果相等返回NULL， 如果不等返回表达式1
（5）如果相等返回NULL,如果不等返回表达式1
SCOTT@SDEDU> SELECT NULLIF(1,1) , NULLIF(1,2) FROM dual ;
NULLIF(1,1) NULLIF(1,2)
-------------------- ----------------
1
DECODE
语法：DECODE（列|值，判断值1，显示结果1，判断值2，显示结果2 ，…，默认值）
如果现实的数字是null，则使用默认值表示。
（6）对job列进行职位判断。
如果是CLERK，则为job1；
如果是SALEMAN，则为job2；
如果是PRESIDENT，则为job3；
如果是MANAGER，则为job4;
如果没有，则为job5。
SCOTT@SDEDU>select ename,job,
decode(job,'CLERK','job1','SALESMAN','job2','PRESIDENT','job3','MANAGER','job4','job5') from emp;
ENAME JOB DECO
----------- ----------------- ----------------
SMITH CLERK job1
ALLEN SALESMAN job2
WARD SALESMAN job2
JONES MANAGER job4
MARTIN SALESMAN job2
BLAKE MANAGER job4
CLARK MANAGER job4
SCOTT ANALYST job5
KING PRESIDENT job3
TURNER SALESMAN job2
ADAMS CLERK job1
JAMES CLERK job1
FORD ANALYST job5
MILLER CLERK job1
14 rows selected.
CASE
语法：CASE 列 | 数值 WHEN 表达式1 THEN 显示结果1...ELSE 表 达式n...表达式n...END
用于实现多条件判断，在WHEN之后编写条件，而在THEN之后编写条件满足的显示操作，如果都不满足则使用ELSE中的表达式处理。
（7）显示每个雇员的姓名、工资、职位，同时显示新
的工资（新工资的标准为：办事员增长10%、 销
售人员增长20%、经理增长30%、其他职位的人
增长50%）
SCOTT@SDEDU> select ename,case job when 'CLERK' then sal*1.1
2 when 'SALESMAN' then sal*1.2
3 when 'MANAGER' then sal*1.3
4 else sal*1.5
5 end newtable
6 from emp;
ENAME NEWTABLE
----------- ------------------
SMITH 880
ALLEN 1920
WARD 1500
COALESCE
语法：COALESCE(表达式1，表达式2，…，表达式n)
将表达式逐个判断，如果表达式1的内容是null，则显示表达式2， 如果表达式2的内容是null，则显示表达式3，以此类推，如果表达式n的结果还是null，则返回null。
（8）COALESCE(comm,100,2000)comm 列值为
null，则用100替换；COALESCE(comm,null,
null)comm 列值为null，则用null替换。
SCOTT@SDEDU> SELECT ename, sal, comm,
COALESCE(comm,100,2000) , COALESCE(comm,null, null) FROM emp ;
ENAME SAL COMM
COALESCE(COMM,100,2000) COALESCE(COMM,NULL,NULL)
------------- --------------- --------- ------------------ ------------------
SMITH 800 100
ALLEN 1600 300 300 300
WARD 1250 500 500 500
JONES 2975 100
MARTIN 1250 1400 1400 1400
BLAKE 2850 100
CLARK 2450 100
SCOTT 3000 100
KING 5000 100
TURNER 1500 0 0 0
ADAMS 1100 100
JAMES 950 100
FORD 3000 100
MILLER 1300 100
14 rows selected.
推荐阅读
晟数学院DBA成长日记
晟数学院DBA成长日记
晟数学院DBA成长日记