WM_CONCAT函数在11g上的变化

2019独角兽企业重金招聘Python工程师标准>>>

Oracle数据库每一个版本的提升，都伴随着一些新特性的推出和原有功能的修改。这就导致我们在进行应用开发，特别是高级特性、函数使用的时候，版本因素往往是需要考量的内容。

 

中午一个同事过来咨询一句SQL的书写，问题本身不是很复杂。但是之后没有想到就是由于Oracle版本的原因造成了诸多不便。特此记录下来，为其他朋友之鉴。

 

1、  问题表示

 

问题是这样的，对于一个数据表，结构如下：

 

 

SQL> desc ABCDE;

Name     Type     Nullable Default Comments

-------- -------- -------- ------- --------

HOSTALNE CHAR(4)  Y                        

CARCDE   CHAR(3)                           

MART1    CHAR(3)                           

MART2    CHAR(3)  Y                        

MART3    CHAR(3)  Y                        

MART4    CHAR(3)  Y                        

MART5    CHAR(3)  Y                        

MART6    CHAR(3)  Y                         

MART7    CHAR(3)  Y                        

MART8    CHAR(3)  Y                        

MART9    CHAR(3)  Y                        

MART10   CHAR(3)  Y                        

MART11   CHAR(3)  Y                        

SEQNO    CHAR(2)               

 

 

需求是这样，按照carcde进行分组，以seqno的顺序将mart1-mart11合并起来进行展现。

 

问题本身并不复杂，是一个分组排序，之后进行列转行处理。列之间的合并使用简单的||操作符就可以取到比较好的效果。

 

列转行是我们在进行报表和处理中经常遇到的问题。Oracle从9i开始，开发者就不断推出解决方法。在10g之后，一种简单的wm_concate函数基本可以解决这类型问题。

 

2、初步尝试解决

 

笔者特意询问了同事的环境版本，知道之前是使用的10g之后，打算使用wm_concate函数。

 

 

 

SQL> select * from dbcargrp;

 

HOSTALNE CARCDE MART1 MART2 MART3 MART4 MART5 MART6 MART7 MART8 MART9 MART10 MART11 SEQNO

-------- ------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ------ ------  -----

         0      AA    MU    LH    SQ                                                 3

         0      IV    MI    AF    TX    CA    WY    9W    FM    BR    AQ     JK      2

         0      PS    ZY    SC    PE    AP    PJ    CZ    VM    OH    MF     ZH      1

         43     MI    LH    US    NZ    SQ    LX    BD                               2

 （篇幅原因，有省略……）

         100    ET    SN    A3    NH    LO    TP    TK    JJ    MS    OZ     OU      1

         100    JP    CA    SK    OS    TG    UA    SA    LH    NZ    US     LX      2

         100    SQ    AC    KF                                                       3

         932 VS    SQ                                                                   1

 

 

从业务上看，相同的carcde的情况下，只有最后的seqno列中存在空置。我们进行SQL处理。

 

 

SQL> select carcde, wm_concat(a) m from (select carcde,

  2         trim(mart1) || ',' || trim(mart2) || ',' || trim(mart3) || ',' ||

  3         trim(mart4) || ',' || trim(mart5) || ',' || trim(mart6) || ',' ||

  4         trim(mart7) || ',' || trim(mart8) || ',' || trim(mart9) || ',' ||

  5         trim(mart10) || ',' || trim(mart11) a

  6    from dbcargrp

  7   order by carcde, seqno) group by carcde;

 

CARCDE M

------ ------------------------

0      PS,ZY,SC,PE,AP,PJ,CZ,VM,OH,MF,ZH,IV,MI,AF,TX,CA,WY,9W,FM,BR,AQ,JK,AA,MU,LH,SQ,,,,,,,

100    ET,SN,A3,NH,LO,TP,TK,JJ,MS,OZ,OU,JP,CA,SK,OS,TG,UA,SA,LH,NZ,US,LX,SQ,AC,KF,,,,,,,,

111    MS,CA,NH,NZ,SQ,CO,AC,BD,,,

（篇幅原因，有省略……）

666    LH,LX,MS,SQ,TK,UA,,,,,

777    SQ,OU,,,,,,,,,

8      AF,KL,SQ,,,,,,,,

932    VS,SQ,,,,,,,,,

 

15 rows selected

 

 

应该说，处理是相当完美的，完全符合业务系统要求。但是，当同事回去后，问题出现了。

 

3、11g上的诡异情况

 

不一会，同事再次联系，说回去之后数据不对了。执行结果也出现问题。笔者到机器去进行实验，也的确如此。结果集合如下：

 

 

SQL> select carcde, wm_concat(a) from (select carcde,

  2         --seqno,

  3         trim(mart1) || ',' || trim(mart2) || ',' || trim(mart3) || ',' ||

  4         trim(mart4) || ',' || trim(mart5) || ',' || trim(mart6) || ',' ||

  5         trim(mart7) || ',' || trim(mart8) || ',' || trim(mart9) || ',' ||

  6         trim(mart10) || ',' || trim(mart11) a

  7    from dbcargrp

  8  -- where carcde = '000'

  9   order by carcde, seqno) group by carcde;

 

CARCDE WM_CONCAT(A)

------ --------------------------------------------------------------------------------

0      PS,ZY,SC,PE,AP,PJ,CZ,VM,OH,MF,ZH,AA,MU,LH,SQ,,,,,,,,IV,MI,AF,TX,CA,WY,9W,FM,BR,A

100    ET,SN,A3,NH,LO,TP,TK,JJ,MS,OZ,OU,SQ,AC,KF,,,,,,,,,JP,CA,SK,OS,TG,UA,SA,LH,NZ,US,

111    MS,CA,NH,NZ,SQ,CO,AC,BD,,,

（篇幅原因，有省略……）

124    CA,CZ,MU,MF,3C,,,,,,

932    VS,SQ,,,,,,,,,

 

15 rows selected

 

 

我们发现，虽然子查询结果是按照seqno输入到上层查询语句中的，但是Oracle却没有按照默认顺序进行concate操作。数据集合相同的情况，为什么结果不同？唯一的解释就是Oracle版本的差异。

 

经过确认，同事使用的环境是Oracle 11g。在10g下可以正常执行的函数为什么在11g上失效？

 

不管如何，哪怕就是bug，首先要寻求解决方法。

 

4、问题解决

 

既然在11g上出现问题，那么可以着手在11g上看看有没有其他的解决方案，毕竟高版本的功能更加强大。笔者选择了listagg函数。

 

 

SQL> select carcde, listagg(a,',') within group (order by seqno)from (select carcde,

  2         seqno,

  3         trim(mart1) || ',' || trim(mart2) || ',' || trim(mart3) || ',' ||

  4         trim(mart4) || ',' || trim(mart5) || ',' || trim(mart6) || ',' ||

  5         trim(mart7) || ',' || trim(mart8) || ',' || trim(mart9) || ',' ||

  6         trim(mart10) || ',' || trim(mart11) a

  7    from dbcargrp

  8   order by carcde, seqno) group by carcde;

 

CARCDE LISTAGG(A,',')WITHINGROUP(ORDE

------ --------------------------------------------------------------------------------

0      PS,ZY,SC,PE,AP,PJ,CZ,VM,OH,MF,ZH,IV,MI,AF,TX,CA,WY,9W,FM,BR,AQ,JK,AA,MU,LH,SQ,,,

100    ET,SN,A3,NH,LO,TP,TK,JJ,MS,OZ,OU,JP,CA,SK,OS,TG,UA,SA,LH,NZ,US,LX,SQ,AC,KF,,,,,,

111    MS,CA,NH,NZ,SQ,CO,AC,BD,,,

（篇幅原因，有省略……）

932    VS,SQ,,,,,,,,,

 

15 rows selected

 

 

最后和同事沟通后，确认项目组运行数据库确定在11g上，可以使用listagg函数。

 

5、反思和结论

 

解决这个案例之后，笔者在不断反思。相同的数据、相同的语句，因为版本的原因，可以造成处理结果的差别。这就需要我们对开发的应用系统在不同版本上进行相对完全的测试工作，发现这些问题。

 

另一方面，笔者猜想Oracle放松wm_concate函数功能的原因，可能也就在于listagg的推出。相对于wm_concate，listagg提供了合并串排序的规范和标准（within group order by），wm_concate默认的排序就没有了意义。

转载于:https://my.oschina.net/u/2381604/blog/511311