行列转换之字符串拼接(一)、WM_CONCAT函数

字符串拼接和分离(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.返回值只能用逗号分隔的

这一点无法改变

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函数本身不支持排序,但是还是有变通的办法来解决排序问题:

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

建议

  1. Oracle官方并不推荐使用WM_CONCAT函数,因此尽量少用
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值