oracle中的vm_concat和LISTAGG

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;
1
2
(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;
1
2
3
4
（5）使用该函数进行查询

SQL> select id,wm_concat(name) as name from test group by id;
​ ID NAME
---------- --------------------
​ 1 a,b
​ 2 c