oracle数据库中常用的几个非主流函数

今天我向大家介绍oracle中几个常用但是非主流的函数，有以下几个：

COALESCE , DECODE , WM_CONCAT , LISTAGG , ROLLUP ,   开窗函数(分析函数) 。

使用的例子，是oracle数据库中的 scott 用户中的 四张表，EMP , DEPT , SALGRADE , BONUS 

下面分别介绍一下：

1. COALESCE 

说道这个函数，首先需要说一个其他类似的函数： NVL(exp1,exp2) 

这个函数的意义是，如果exp1不为空，就返回 exp1 的值；如果 exp1 为空，就返回 exp2 的值 。

那么问题来了，如果我在想判断 exp2 的值为空的时候，在返回一个其他的值怎么办呢？或者exp3 为空的时候，在返回一个其他的值，怎么办，很明显NVL这个函数无能为力

COALESCE 函数 就能够实现这个功能 ， 用法是  COALESCE(exp1 , exp2 , exp3 ....)

这个函数的意义是 , 遇到非null值即停止并返回该值，如果所有的表达式都是空值，最终将返回一个空值

2. DECODE

主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；
使用方法：
Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）
From talbename
Where …
其中 columnname 为要选择的table中所定义的column,
如果 columnname 的值等于 值1 ，那么返回 翻译值1；
如果 columnname 的值等于 值2 ，那么返回 翻译值2...
如果 一直到 值n 都不相等，那么就返回 缺省值 

3. WM_CONCAT

SELECT T.DEPTNO,WM_CONCAT(T.ENAME) FROM EMP T 
GROUP BY T.DEPTNO;

返回的结果如下：

10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

也就是说 WM_CONCAT 就相当于一个聚合函数，中间使用  , 连接 

4. LISTAGG 

这个函数是从 ORACLE 11G 才有的一个函数！！

我们有时候会遇到这样的需求：“对员工列表进行操作，将每个部门的员工名称横向排列，并且按照姓名排序，以逗号进行分割”
SELECT T.DEPTNO,LISTAGG(T.ENAME,',') WITHIN GROUP (ORDER BY T.ENAME) FROM EMP T 
GROUP BY T.DEPTNO;

5.ROLLUP 函数

这个函数也是一个分析函数，至于分析函数都有哪一些，主要查看 第6部分

本函数主要适用于 在分组求和之后，在结果集后面在添加行，用于显示总计

语法1：

SELECT T.DEPTNO,SUM(T.SAL) FROM 
EMP T GROUP BY ROLLUP(T.DEPTNO) ;

显示的结果集：

10 8750
20 10875
30 9400
29025

如果将每个部门的员工工资显示出来，并且显示每个部门的合计工资，那么就需要这么写：

SELECT T.DEPTNO,T.ENAME,SUM(T.SAL) FROM 
EMP T GROUP BY ROLLUP(T.DEPTNO,T.ENAME) ;

结果集：

10 KING 5000
10 CLARK 2450
10 MILLER 1300
10 8750
20 FORD 3000
20 ADAMS 1100
20 JONES 2975
20 SCOTT 3000
20 SMITH 800
20 10875
篇幅原因，剩下的结果集省略.... ....

还可以在进一步学习一下跟 ROLLIUP 有关的 GROUPING函数

6. 开窗函数【分析函数】

开窗函数又叫做分析函数 ，主要由以下几个常用的分析函数

a. 自动汇总函数rollup,cube,
b. rank 函数, rank,dense_rank,row_number
c. lag,lead函数
lag  函数 “取到上个月的销售额”
lead 函数 “获取下一个月销售量”
d. sum,avg,的移动增加,移动平均数
e. ratio_to_report报表处理函数
f. first,last取基数的分析函数

有了上面的介绍，剩下的可以在通过查找百度解决了，这篇博客主要起到抛砖引玉的作用！