通用函数（学习笔记）

--***************通用函数
--NVL处理NULL
--NVL(数字|列,默认值),如果显示数字是NULL的话，则使用默认值
--NVL2(数字|列,默认值1,默认值2) 如果列不是NULL则返回结果1，如果为空返回2
--NULLIF（表达式1，表达2）比较表达式1，和表达2，的结果是否相等，如果相等返回NULL，如果不相等返回表达式1
--DECODE(列|值,判断值1，显示结果1，判断值2，显示结果2.............)
--CASE 列|数值 WHEN 表达式1 THEN 显示结果1，else表达式n..end
--COALESCE （表达1，表达2，。。。。表达n)
--使用NVL处理NULL
--要求查询员工的编号，姓名，职位，入职日期和年薪(月薪+comm)*12
--验证NVL

SELECT NVL(NULL,0),NVL(3,0) FROM dual;

结果:0,3

SELECT e.empno,e.ename,e.job,e.hiredate,
(e.sal+NVL(e.comm,0))*12 年薪
FROM emp e;

 

--使用NVL2函数

SELECT e.empno,e.ename,e.job,e.hiredate,
(e.sal+NVL2(e.comm,comm,0))*12 年薪
from emp e;

 

SELECT e.empno,e.ename,e.job,e.hiredate,e.comm,
NVL2(e.comm,e.sal+e.comm,e.sal)*12 年薪
from emp e;

 

--NULLIF函数比较2个表达式，如果相等返回NULL，不相等返回表达式1

SELECT NULLIF(1,1),NULLIF(1,2) FROM dual;

结果:null,1
--比较名字长度和职位长度

SELECT e.empno,e.ename,e.job,LENGTH(e.ename),LENGTH(e.job),
NULLIF(LENGTH(e.ename),LENGTH(e.job)) 长度是否相同
FROM emp e;

--decode使用DECODE要全部判断，如果没有判断的内容就会显示NULL

SELECT e.ename,e.sal,
DECODE(job,'CLERK','业务员',
'SALESMAN','销售员',
'MANAGER','经理',
'ANALYST','分析师',
'PRESIDENT','总裁') 职位
FROM emp e;

 

--CASE表达式
--显示每个员工的姓名，工资，职位，同时显示新的工资（新工资为业务员增长10%，销售员增加20%，经理增长30%，其他职位的人增长50%

SELECT e.ename,e.sal,e.job,
CASE e.job WHEN 'CLERK' THEN e.sal*1.1
WHEN 'SALESMAN' THEN e.sal*1.2
WHEN 'SALSEMAN' THEN e.sal*1.3
ELSE e.sal*1.5
END 新工资
FROM emp e;

---coalesce （表达1，表达2，。。。。表达n)依次判断，如果表达式1为NULL，则显示表达式2，如果表达式2是NULL，则显示表达式3.。。。。。

SELECT e.ename,e.sal,e.comm,
COALESCE(e.comm,100,2000),COALESCE(e.comm,NULL,NULL)
FROM emp e;