oracle函数 wm_concat 与 listagg

oracle函数 wm_concat 与 listagg

1. wm_concat

select t.type, to_char(wm_concat(t.id||'---'||t.seq)) as id_seq, to_char(wm_concat(t.seq)) as seqs
from (select 'A' type, 'A001' id,  1 seq from dual union all select 'A' type, 'A002' id,  2 seq from dual union all select 'B' type, 'B001' id,  1 seq from dual 
) t group by t.type;

查询结果

2. listagg

SELECT T.type,listagg(T.id ||'---'||T.seq, ',') WITHIN GROUP(ORDER BY T .seq) id_seq,listagg(T.seq, ',') WITHIN GROUP(ORDER BY T .seq desc) seqs
FROM (select 'A' type, 'A001' id,  1 seq from dual union all select 'A' type, 'A002' id,  2 seq from dual union all select 'B' type, 'B001' id,  1 seq from dual 
) t 
WHERE T.type in ('A', 'B') 
GROUP BY T.type;

查询结果

3. wm_concat存在问题
   1.该函数不是oracle公开的系统函数，它的用户是wmsys,而不是sys或者system，oracle很有可能在版本升级或者补丁的时候取消或者修改这个函数甚至用户，这种变化oracle是不会公开的。所有可能会由于这个变化而导致异常。
   2.大量使用这个函数也会导致临时表空间爆满，这是因为在10.2.0.5中，使用wmsys.wm_concat返回的结果格式是CLOB，CLOB占用的临时表空间只有在连接释放后才会释放，部分通过连接池连接数据库的长连接很有可能导致CLOB占用临时表空间不断累积增大，会导致临时表空间爆满的故障。
   3.如果是在程序中大量使用这个函数的话会引起enq:TT的锁，可能会导致某些对象被锁。
   4.wm_concat在11g中使用需要用to_char()进行转换，否则会出现不兼容现象
4. 建议
   oracle11g后 推荐使用 listagg 函数，也可以参考wm_concat自己建立一个函数实现相同的行列转换功能

注意：
使用listagg进行分组拼接时，拼接字符过长时会报 ora-01489 错误，造成该报错的主要原因是：oracle对字符变量的长度限制，正常情况下，oracle定义的varchar2类型变量的长度不应超过4000字节