Oracle大师Thomas Kyte在他的经典著作中,反复强调过一个实现需求方案选取顺序:
“如果你可以使用一句SQL解决的需求,就使用一句SQL;如果不可以,就考虑PL/SQL是否可以;如果PL/SQL实现不了,就考虑Java存储过程是否可以;如果这些都不可能实现,那么就需要考虑你是否真的需要实现这个需求。”
各个关系型DBMS产品都在遵守关系型数据库模型的基本体系架构,遵循通用的SQL国际规范。同时,为了更好地配合自身数据库实现的特征,以及提供更加丰富的功能,各个DBMS纷纷在标准SQL上进行了一系列语法、语义以及功能上的拓展实现。Oracle从商用化开始,就不断的在这条道路上不断探索。借助Oracle提供的很多拓展SQL语句和函数,可以帮助我们将过去很复杂的需求,通过简单的一句SQL加以实现。
本篇就介绍三个日常开发中有用的函数方法。
1、listagg
我们有时候会遇到这样的需求:“对员工列表进行操作,将每个部门的员工名称横向排列,以逗号进行分割”。
员工表我们使用scott用户schema下的emp表。
SQL> select * from emp;
EMPNO ENAMEJOBMGR HIREDATESALCOMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITHCLERK7902 1980-12-17800.0020
7876 ADAMSCLERK7788 1987-5-231100.0020
7900 JAMESCLERK7698 1981-12-3950.0030
7902 FORDANALYST7566 1981-12-33000.0020
7934 MILLERCLERK7782 1982-1-231300.0010
(篇幅原因,有省略……)
14 rows selected
这个需求的关键在于如何将ename员工名称列压扁为一行数据。如果不使用SQL解决,最直观的想法就是使用PL/SQL进行迭代遍历,获取到所有的数据行记录。
此时,我们就可以求助Oracle 11g中的函数listagg。首先我们来看一下listagg的函数描述(摘自Oracle SQL Reference)。