Oracle實現字符串拼接和分離功能的方法(LISTAGG函數)，oraclelistagg

字符串拼接(String Aggregation Techniques)是數據處理時經常需要用到一個技術，比如需要按時間順序拼裝一個快遞的運輸記錄，或者將流程中各個環節的處理人拼裝為一個字符串。

Oracle中有多種方法來實現這個功能，這里羅列幾種，詳細用法可以參考下面的文章：

WM_CONCAT函數

LISTAGG函數

自定義聚合函數

本文介紹第二種：LISTAGG函數，這是Oracle11gR2開始正式推出的字符串聚合函數，功能非常強大。

0.測試樣例及基本用法

從all_objects視圖中取4個表記錄和3個視圖記錄作為測試數據：

SQL> CREATE TABLE T_STRAGG AS

2 select OBJECT_TYPE,CREATED,OBJECT_NAME from ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND rownum<5

3 UNION ALL

4 select OBJECT_TYPE,CREATED,OBJECT_NAME from ALL_OBJECTS WHERE OBJECT_TYPE='VIEW' AND rownum<4;

Table created

SQL> select OBJECT_TYPE,TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS') CREATED,OBJECT_NAME from T_STRAGG;

OBJECT_TYPE CREATED OBJECT_NAME

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

TABLE 2013-10-09 18:23:43 DUAL

TABLE 2013-10-09 18:23:44 SYSTEM_PRIVILEGE_MAP

TABLE 2013-10-09 18:23:45 TABLE_PRIVILEGE_MAP

TABLE 2013-10-09 18:23:47 STMT_AUDIT_OPTION_MAP

VIEW 2013-10-09 18:23:53 ALL_XML_SCHEMAS

VIEW 2013-10-09 18:23:56 ALL_XML_SCHEMAS2

VIEW 2013-10-09 18:23:54 V_$ADVISOR_CURRENT_SQLPLAN

現在想要根據OBJECT_TYPE分組，將OBJECT_NAME按時間順序拼裝成類似物流信息的字符串：

2013-10-09 18:23:43@DUAL

2013-10-09 18:23:44@SYSTEM_PRIVILEGE_MAP

2013-10-09 18:23:45@TABLE_PRIVILEGE_MAP

實現方法很簡單：

SQL> select object_type

2 ,LISTAGG(TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS')||'@'||OBJECT_NAME,CHR(10)) WITHIN GROUP(ORDER BY CREATED) AS TXT

3 FROM T_STRAGG

4 GROUP BY object_type;

OBJECT_TYPE TXT

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

TABLE 2013-10-09 18:23:43@DUAL

2013-10-09 18:23:44@SYSTEM_PRIVILEGE_MAP

2013-10-09 18:23:45@TABLE_PRIVILEGE_MAP

2013-10-09 18:23:47@STMT_AUDIT_OPTION_MAP

VIEW 2013-10-09 18:23:53@ALL_XML_SCHEMAS

2013-10-09 18:23:54@V_$ADVISOR_CURRENT_SQLPLAN

2013-10-09 18:23:56@ALL_XML_SCHEMAS2

跟WM_CONCAT相比，LISTAGG有如下特征(優缺點)：

1.返回值的分隔符可以自定義

這一點大大方便了開發人員，只要有需要可以擺出各種姿勢^_^

2.返回值類型是VARCHAR2

跟WM_CONCAT相反，速度是快了，但如果數據量大就報錯

ORA-01489: result of string concatenation is too long

(字符串連接的結果過長)

SQL> INSERT INTO T_STRAGG select object_type,CREATED,OBJECT_NAME FROM ALL_OBJECTS where rownum<1000;

999 rows inserted

SQL> select object_type

2 ,LISTAGG(TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS')||'@'||OBJECT_NAME,CHR(10)) WITHIN GROUP(ORDER BY CREATED) AS TXT

3 FROM T_STRAGG

4 GROUP BY object_type;

ORA-01489: result of string concatenation is too long

沒辦法，用回WM_CONCAT？

還是建議用第三個方法：自定義聚合函數

3.不支持DISTINCT

直接報錯ORA-30482: DISTINCT option not allowed for this function

(DISTINCT 選項在此函數中禁用)

SQL> select object_type,LISTAGG(DISTINCT OBJECT_NAME) WITHIN GROUP(ORDER BY CREATED) AS TXT

2 FROM T_STRAGG

3 GROUP BY object_type;

ORA-30482: DISTINCT option not allowed for this function

解決方法：

1.先distinct，結果再listagg

2.自定義聚合函數來取代listagg函數

結論

listagg能滿足的情況下，用listagg，不要用wm_concat listagg無法滿足的情況下，還是網上拿一個自定義聚合函數好了

http://www.dengb.com/oracle/1315268.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/1315268.htmlTechArticleOracle實現字符串拼接和分離功能的方法(LISTAGG函數)，oraclelistagg 字符串拼接(String Aggregation Techniques)是數據處理時經常需要用到一個技...