⾏列转换之字符串拼接（⼀）、WM_CONCAT函数
字符串拼接和分离（String Aggregation Techniques）是数据处理时经常需要⽤到⼀个技术，⽐如需要按时间顺序拼装⼀个快递的运输记录，或者将流程中各个环节的处理⼈拼装为⼀个字符串。
Oracle中有多种⽅法来实现这个功能，这⾥罗列⼏种，详细⽤法可以参考下⾯的⽂章：
-
-
-
0.测试样例
这⾥介绍第⼀种：WM_CONCAT，这个函数是Oracle内部函数，在官⽅⽂档⾥是没有说明（undocumented function），并且在Oracle12.2开始的版本⾥已经取消了WM_CONCAT函数。
从all_objects视图中取4个表记录和3个视图记录作为测试数据：
SQL> CREATE TABLE T_STRAGG AS
2select OBJECT_TYPE,CREATED,OBJECT_NAME from ALL_OBJECTS WHERE OBJECT_TYPE='TABLE'AND rownum<5
3UNION ALL
4select 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-0918:23:43 DUAL
TABLE 2013-10-0918:23:44 SYSTEM_PRIVILEGE_MAP
TABLE 2013-10-0918:23:45 TABLE_PRIVILEGE_MAP
TABLE 2013-10-0918:23:47 STMT_AUDIT_OPTION_MAP
VIEW 2013-10-0918:23:53 ALL_XML_SCHEMAS
VIEW 2013-10-0918:23:56 ALL_XML_SCHEMAS2
VIEW 2013-10-0918: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.返回值只能⽤逗号分隔的
这⼀点⽆法改变
2.返回值是CLOB类型
在11g之前返回的是VARCHAR2类型。
这有个优点，能⽀持很长的字符串拼接，短了可以TO_CHAR展⽰
但也有缺点，就是⼤⼤增⼤了临时段的读写，数据量⼤时可能会出现错误
ORA-01652:unable to extend temp segment by 128 in tablespace name
( ⽆法通过 128 (在表空间 TEMP 中) 扩展 temp 段)
3.没法排序
⽐如，我想按照created先后进⾏排序，产⽣这样的结果:
DUAL创建于2013-10-09 18:23:43，SYSTEM_PRIVILEGE_MAP创建于2013-10-09
18:23:44…..
虽然WM_CONCAT函数本⾝不⽀持排序，但是还是有变通的办法来解决排序问题：
object to⽹上有些⽂章并没有使⽤row_number()来取数，⽽是⽤MAX函数取最⼤值
事实上在oracle11g⾥会报ORA-00932错:
原因是clob字段不⽀持max函数，⽹上的⽂章是基于oracle11g之前的环境，那时WM_CONCAT函数返回的是VARCHAR2类型。
4.对DISTINCT 的部分⽀持在sql环境中，WM_CONCAT是⽀持DISTINCT的，⽐如：
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
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
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
5 FROM T_STRAGG
6 ) GROUP BY OBJECT_TYPE;
ORA-00932: inconsistent datatypes: expected - got CLOB
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/91 DUAL
TABLE 2013/10/91 DUAL
SQL> select object_type,WM_CONCAT(DISTINCT OBJECT_NAME) AS TXT
2FROM T_STRAGG
3GROUP 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
4select WM_CONCAT(DISTINCT OBJECT_NAME) AS TXT
5
6INTO FunctionResult
7FROM T_STRAGG
8WHERE OBJECT_TYPE=V_OBJTYPE;
9
10return(FunctionResult);
11end 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
6FROM T_STRAGG
7WHERE OBJECT_TYPE='''||V_OBJTYPE||''''
8INTO FunctionResult;
9
10return(FunctionResult);
11end 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 建议
1. Oracle官⽅并不推荐使⽤WM_CONCAT函数，因此尽量少⽤
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论