1.vm_concat函数的介绍
vm_concat可以实现将多列记录聚合为一列记录,来实现数据的压缩
我们在Oracle11.2.0.4用实验来展示下该函数的效果
首先我们创建一个测试表,并插入三行数据。
create table test (id number , name varchar2(20));
insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(2,'c');
commit;
select * from test;
使用wm_concat将多列记录聚合为一列,按id分组。
select id,wm_concat(name) as name from test group by id;
在sqlplus命令行界面中可以正常显示结果,使用PLSQL显示数据类型为CLOB类型
如果在PLSQL中使用该函数需要使用to_char进行类型转换:
select id,to_char(wm_concat(name)) as name from test group by id;
2.不同数据库版本中wm_concat的差异
10g以及11g:
在10g以及11g中该函数可以正常使用,但是在10g和11g中需要注意的是,vm_concat返回的数据类型是不同的:
在10.2.0.4/11.1.0.7/11.2.0.1 vm_concat返回的是数据类型为varchar2
SQL> desc wmsys.wm_concat;
FUNCTION wmsys.wm_concat RETURNS VARCHAR2
Argument Name Type In/Out Default?
----------------------- ------------------------ -------- ---------
P1 VARCHAR2 IN
在10.2.0.5/11.2.0.2中,vm_concat返回的数据类型为clob
SQL> desc wmsys.wm_concat;
FUNCTION wmsys.wm_concat RETURNS CLOB
Argument Name Type In/Out Default?
----------------------- ------------------------ -------- ---------
P1 VARCHAR2 IN
12c:
在12c中,vm_concat函数Oracle已经不再支持使用,Oracle推荐使用分析函数LISTAGG代替vm_concat函数的使用。
19c:
在19.3c中,vm_concat函数Oracle依旧不再支持使用
在19.7中,vm_concat函数Oracle依旧不再支持使用
3.wm_concat的继任者LISTAGG
分析函数LISTAGG介绍:
- 作为单一集合,
LISTAGG
它对所有行进行操作并返回单个输出行。 - 作为分组集合,此函数将对
GROUP
BY
子句定义的每个组进行操作并返回输出行。
分析函数LISTAGG使用方法:
listagg(measure_expr,delimiter) within group ( order by order_by_clause);
解释:
measure_expr 可以是基于任何列的表达式
delimiter 分隔符,默认为NULL
order_by_clause 决定了列值的拼接顺序
例子:
select listagg(name,’,’)within group(order by id) from test;
select id,listagg(name,’,’)within group(order by id) from test group by id;
4.oracle关于wm_concat的建议
The function WMSYS.WM_CONCAT is an internal undocumented function which is installed/uninstalled as part of the Workspace Manager feature of Oracle Database. It is internally used in a number of Workspace Manager views. It is not meant to be used by customers directly, and could be changed/updated without notice by Oracle Development. Do not use the WMSYS.WM_CONCAT view in your application.
因为vm_concat是一个内部未记录的函数,并且Oracle官方不会发布内部函数的变更信息,所以Oracle建议不要在应用程序中使用该函数,Oracle建议使用分析函数LISTAGG来代替vm_concat函数的使用,或者自己编写一个函数实现vm_concat的功能,那么如何自己编写一个函数来实现vm_concat呢
CURR_STR
5.手动编写vm_concat函数
我们可以在不支持vm_concat的环境中,手动编写一个vm_concat函数来满足开发需求。
(1)自定义类型
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,RETURNVALUE OUT VARCHAR2,FLAGS IN NUMBER)RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,SCTX2 IN WM_CONCAT_IMPL) RETURN NUMBER
);
(2)创建函数主体
CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX// IN OUT WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
SCTX// := WM_CONCAT_IMPL(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL,P1// IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR// IS NOT NULL) THEN
CURR_STR// := CURR_STR// || ',' || P1//;
ELSE
CURR_STR// := P1//;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN WM_CONCAT_IMPL,RETURNVALUE OUT VARCHAR2,FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR// ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL,SCTX2// IN WM_CONCAT_IMPL)
RETURN NUMBER
IS
BEGIN
IF(SCTX2//.CURR_STR// IS NOT NULL) THEN
SELF.CURR_STR// := SELF.CURR_STR// || ',' || SCTX2//.CURR_STR// ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
(3)创建函数
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL;
(4)创建公有同义词并赋予执行权限
create public synonym WM_CONCAT_IMPL for sys.WM_CONCAT_IMPL;
create public synonym wm_concat for sys.wm_concat;
grant execute on WM_CONCAT_IMPL to public;
grant execute on wm_concat to public;
(5)使用该函数进行查询
SQL> select id,wm_concat(name) as name from test group by id;
ID NAME
---------- --------------------
1 a,b
2 c
参考文档:
WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (Doc ID 1336219.1)
Problem with WMSYS.WM_CONCAT Function after Upgrading (Doc ID 1300595.1)
WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (Doc ID 1336219.1)
Problem with WMSYS.WM_CONCAT Function after Upgrading (Doc ID 1300595.1)
Unable To Run Custom Report Under Company Level Reporting Tab. Data View Using SQL function “wm_concat” Not Supported. (Doc ID 2149649.1)