Oracle_行转列之listagg及wmconcat用法

最新推荐文章于 2024-09-18 15:02:39 发布

国民小废物

最新推荐文章于 2024-09-18 15:02:39 发布

阅读量882

点赞数 12

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_53560046/article/details/141816269

版权

1.功能简介

对于将Oracle的多行转化为一列问题，Oracle目前提供的listagg及wmconcat可以很好的实现。

实现结果如下：

表study_detail中存储的是每个学生学习的科目：

student	subject
涵涵	语文
乐乐	语文
乐乐	数学
东东	语文
涵涵	数学
涵涵	英语

欲统计每个学生都学习了哪些科目，可利用上述两个函数进行计算，结果如下：

student	subjects
涵涵	语文、数学、英语
乐乐	语文、数学
东东	语文

2.使用方式

2.1 wmconcat

SELECT sd.student,   --分组依据
       wm_concat(DISTINCT sd.subject) subjects  --转换的行
  FROM study_detail sd
 group by sd.student

该方式分割符为'，',若需要转化为其他字符，可使用replace函数

replace(wm_concat(DISTINCT jpwr.wo_num), ',', '、 ')

值得注意的是，该函数转化后格式为clob，使用中，一般需转化为varchar2，如下

 to_char(replace(wm_concat(DISTINCT jpwr.wo_num), ',', ' / '))

2.2 listagg

SELECT sd.student,
        listagg( sd.subject || '、 ')) within GROUP(ORDER BY sd.subject) subjects,
  FROM study_detail sd
 group by sd.student

在上述方法中，分隔符号可以自己指定，返回数据类型为varchar2。

3.优缺点对比

3.1版本适用

wm_concat在12c版本中被取消； listagg是在11.2的版本引入

3.2 数据类型

listagg 返回varchar2类型、不支持distinct且有长度限制（4000）；

wm_concat 返回clob类型，依赖WMSYS用户，可能存在兼容性问题

3.3 弊端

wm_concat：

1.非公开函数风险

用户是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()进行转换，否则会出现不兼容现象

listagg：