Oracle的行转列函数的使用及其局限性_oracle wellformed

一. 前言

• Oracle10可以使用vm_concat列转行函数
• Oracle11不兼容vm_concat列转行函数
• Oracle12没有vm_concat这个函数

二. listagg()函数

1. Oracle11提供的另外一个函数：listagg();
   语法：listagg(参数，‘分隔符’) within group(order by 参数id)
   规范写法：LISTAGG(字段, 连接符) WITHIN GROUP (ORDER BY 字段)
   列子：to_char(listagg(ur.user_role,’,’ ) within GROUP (order by (ur.user_role))) userrole,

2. listagg()用法
   使用 listagg() WITHIN GROUP () 将多行合并成一行(比较常用)

SELECT
	T.DEPTNO,
	listagg (T.ENAME, ',') WITHIN GROUP (ORDER BY T.ENAME) names
FROM
	EMP T
WHERE
	T.DEPTNO = '20'
GROUP BY
	T.DEPTNO
1
2
3
4
5
6
7
8
9

查询结果：

特殊情况：

问题：这段sql在运行的时候突然报错，提示“字符串连接的结果过长”；

问题分析： 通常情况下，LISTAGG是满足需要的，LISTAGG 返回的是一个varchar2类型的数据，最大字节长度为4000。

解决方法：所以，在实际开发中，我们可能会遇到一个问题，连接长度过长。在这个时候，我们需要将LISTAGG函数改成XMLAGG函数。XMLAGG返回的类型为CLOB，最大字节长度为32767。

三. xmlagg()函数

1. 下面是XMLAGG函数规划写法：
   XMLAGG(XMLPARSE(CONTENT 字段 || 字符串 WELLFORMED) ORDER BY 字段).GETCLOBVAL()
2. xmlagg()用法

SELECT
	T.DEPTNO,
     	xmlagg(xmlparse(content T.ENAME||',' wellformed)  order by T.ENAME).getclobval()
	listagg (T.ENAME, ',') WITHIN GROUP (ORDER BY T.ENAME) names
FROM
	EMP T
WHERE
	T.DEPTNO = '20'
GROUP BY
	T.DEPTNO
1
2
3
4
5
6
7
8
9
10

特殊情况：

问题： 用xmlagg还是满足不了字符长度需求，并且会很消耗性能和耗时间

问题分析：字段拼接过长太影响数据库性能

解决方法：当需要多表关联后多行合并成一行，最后采用分次查询（可结合jdk8的stream运算（包括去重）解决）。