WM_CONCAT函式在11g上的變化

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預設的排序就沒有了意義。