字符串拼接和分离（String Aggregation Techniques）是数据处理时经常需要用到一个技术，比如需要按时间顺序拼装一个快递的运输记录，或者将流程中各个环节的处理人拼装为一个字符串。
Oracle中有多种方法来实现这个功能，这里罗列几种，详细用法可以参考下面的文章：
WM_CONCAT函数
LISTAGG函数
自定义聚合函数
0.测试样例
这里介绍第一种：WM_CONCAT，这个函数是Oracle内部函数，在官方文档里是没有说明（undocumented function），并且在Oracle12.2开始的版本里已经取消了WM_CONCAT函数。
从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字符串的语法如下：
SQL> select object_type,WM_CONCAT(OBJECT_NAME) FROM T_STRAGG group by object_type; OBJECT_TYPE WM_CONCAT(OBJECT_NAME) ------------------- -------------------------------------------------------------------------------- TABLE DUAL,STMT_AUDIT_OPTION_MAP,TABLE_PRIVILEGE_MAP,SYSTEM_PRIVILEGE_MAP VIEW ALL_XML_SCHEMAS,V_$ADVISOR_CURRENT_SQLPLAN,ALL_XML_SCHEMAS2
这里我们发现了WM_CONCAT函数的几个特征（或问题）：
1.返回值只能用逗号分隔的
这一点无法改变<喎? f/ware/vc/”=”” target=”_blank” class=”keylink”>vcD4NCjxoMj4yLre1u9jWtcrHQ0xPQsDg0M08L2gyPg0KPHA+1NoxMWfWrsewt7W72LXEysdWQVJDSEFSMsDg0M2hozwvcD4NCjxwPtXi09C49tPFteOjrMTc1qez1rrcs6S1xNfWt/u0rsa0vdOjrLbMwcu/ydLUVE9fQ0hBUtW5yr48L3A+DQo8cD61q9Ky09DIsbXjo6y+zcrHtPO089T2tPPBy8HZyrG2zrXEtsHQtKOsyv2+3cG/tPPKsb/JxNy74bP2z9a07c7zPC9wPg0KPHA+T1JBLTAxNjUyOnVuYWJsZSB0byBleHRlbmQgdGVtcCBzZWdtZW50IGJ5IDEyOCBpbiB0YWJsZXNwYWNlIG5hbWU8L3A+DQo8cD4oIM7et6jNqLn9IDEyOCAo1Nqx7b/VvOQgVEVNUCDW0CkgwKnVuSB0ZW1wILbOKTwvcD4NCjxwPjxpbWcgYWx0PQ==”这里写图片描述” src=”/uploadfile/Collfiles/20180324/20180324090629241.png” title=”\” />
3.没法排序
比如，我想按照created先后进行排序，产生这样的结果:
DUAL创建于2013-10-09 18:23:43，SYSTEM_PRIVILEGE_MAP创建于2013-10-09
18:23:44…..
虽然WM_CONCAT函数本身不支持排序，但是还是有变通的办法来解决排序问题：
SQL> select object_type,TXT from 2 ( 3 select object_type 4 ,WM_CONCAT(OBJECT_NAME||'创建于'||TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS')) OVER (PARTITION BY OBJECT_TYPE ORDER BY CREATED) AS TXT 5 ,ROW_NUMBER() OVER (PARTITION BY OBJECT_TYPE ORDER BY CREATED DESC) RN 6 FROM T_STRAGG 7 ) WHERE RN=1; OBJECT_TYPE TXT ------------------- -------------------------------------------------------------------------------- TABLE DUAL创建于2013-10-09 18:23:43,SYSTEM_PRIVILEGE_MAP创建于2013-10-09 18:23:44,TABLE_PRIV VIEW ALL_XML_SCHEMAS创建于2013-10-09 18:23:53,V_$ADVISOR_CURRENT_SQLPLAN创建于2013-10-09 18
网上有些文章并没有使用row_number()来取数，而是用MAX函数取最大值
事实上在oracle11g里会报ORA-00932错:
SQL> select object_type,MAX(TXT) from 2 ( 3 select object_type 4 ,WM_CONCAT(OBJECT_NAME||'创建于'||TO_CHAR(CREATED,'YYYY-MM-DD HH24:MI:SS')) OVER (PARTITION BY OBJECT_TYPE ORDER BY CREATED) AS TXT 5 FROM T_STRAGG 6 ) GROUP BY OBJECT_TYPE; ORA-00932: inconsistent datatypes: expected - got CLOB
原因是clob字段不支持max函数，网上的文章是基于oracle11g之前的环境，那时WM_CONCAT函数返回的是VARCHAR2类型。
4.对DISTINCT的部分支持
在sql环境中，WM_CONCAT是支持DISTINCT的，比如：
SQL> insert into t_stragg select * from t_stragg where OBJECT_NAME='DUAL'; 1 row inserted SQL> select * from t_stragg where OBJECT_NAME='DUAL'; OBJECT_TYPE CREATED OBJECT_NAME ------------------- ----------- ------------------------------ TABLE 2013/10/9 1 DUAL TABLE 2013/10/9 1 DUAL SQL> select object_type,WM_CONCAT(DISTINCT OBJECT_NAME) AS TXT 2 FROM T_STRAGG 3 GROUP BY OBJECT_TYPE; OBJECT_TYPE TXT ------------------- -------------------------------------------------------------------------------- TABLE DUAL,STMT_AUDIT_OPTION_MAP,SYSTEM_PRIVILEGE_MAP,TABLE_PRIVILEGE_MAP VIEW ALL_XML_SCHEMAS,ALL_XML_SCHEMAS2,V_$ADVISOR_CURRENT_SQLPLAN
但是在PLSQL环境中，WM_CONCAT使用distinct会报错
ORA-30482: DISTINCT option not allowed for this function
这就是我所说的部分支持。
SQL> create or replace function F_WMCONCAT(V_OBJTYPE VARCHAR2) return clob is 2 FunctionResult clob; 3 begin 4 select WM_CONCAT(DISTINCT OBJECT_NAME) AS TXT 5 6 INTO FunctionResult 7 FROM T_STRAGG 8 WHERE OBJECT_TYPE=V_OBJTYPE; 9 10 return(FunctionResult); 11 end F_WMCONCAT; 12 / Warning: Function created with compilation errors SQL> SHOW ERR Errors for FUNCTION DONGFENG.F_WMCONCAT: LINE/COL ERROR -------- ---------------------------------------------------------------- 4/10 PL/SQL: ORA-30482: DISTINCT option not allowed for this function 4/3 PL/SQL: SQL Statement ignored
当然，这个也有办法解决：
1. 解决办法之一是先做distinct，再wm_concat；
2. 解决办法之二是用动态SQL方式，规避PLSQL编译。
比如：
SQL> create or replace function F_WMCONCAT(V_OBJTYPE VARCHAR2) return clob is
2 FunctionResult clob;
3 begin
4 execute immediate
5 'select WM_CONCAT(DISTINCT OBJECT_NAME) AS TXT
6 FROM T_STRAGG
7 WHERE OBJECT_TYPE='''||V_OBJTYPE||''''
8 INTO FunctionResult;
9
10 return(FunctionResult);
11 end F_WMCONCAT;
12 /
Function created
SQL> select F_WMCONCAT('TABLE') from DUAL;
F_WMCONCAT('TABLE')
--------------------------------------------------------------------------------
DUAL,STMT_AUDIT_OPTION_MAP,SYSTEM_PRIVILEGE_MAP,TABLE_PRIVILEGE_MAP
建议
Oracle官方并不推荐使用WM_CONCAT函数，因此尽量少用