通用函数

--***************通用函数
--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)
--WM_CONCAT

--使用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;

--- wm_concat

      在日常的数据查询过程中，经常遇到一条信息分多条记录存储，并以同一个ID关联的情况，比如常见的房产证权利人信息，因为共有权人可能有很多，不可能把所有的权利人都放到权利人表的权利人字段，把所有权利人的证件号都放到权利人证件号字段，所以在数据库设计时候，会采用一个权利人一条记录，并以权利ID关联的方式存放。

但是在数据查询时候，有时候又希望将所有权利人信息一起展示，这里可能就会用到Oracle的wm_concat函数

1.示例数据

 

2. 示例语句

select qlrid,wm_concat(qlr) as qlr,wm_concat(qlrzjh) as qlrzjh from qlr t group by qlrid;

 

运行后的返回结果根据oracle的版本不同而会字段类型不同，在oracle11g中返回clob型，在oracle10g中返回varchar型

3. 通用版本语句

select qlrid,to_char(wm_concat(qlr)) as qlr,to_char(wm_concat(qlrzjh)) as qlrzjh from qlr t group by qlrid;