本文深入探讨了Oracle数据库中用于拼接字符串的多种方法，包括wmsys.wm_concat()和LISTAGG()函数，特别关注了在大量数据拼接场景下的性能与限制。通过对比不同版本的Oracle数据库，揭示了高版本中wmsys.wm_concat()函数的缺失及自定义实现过程。
对于一些业务，需要连接函数把内容拼接文本文件的时候，借助合适的函数，非常重要，减少很多工作。
目前常用的连接函数有 wmsys.wm_concat()和 LISTAGG( )函数，当然还有看拼接内容的长度来选。
oracle数据库中，还有一个根据版本选择。最新的两个版本中，11G中，自带有两个函数，但在12C中，
oracle不再自带 wmsys.wm_concat()，如果实际业务中需要到，需要自己创建上。当然，如何创建，会在本文的最后，
会提供两个版本的 wmsys.wm_concat()和 wmsys.wm_concat()函数沿用过来。 很多人说在12C中， )函数
就已经够用了，但是在服务过的客户的应用中，该函数是远远不够使用的，连版本1的 wmsys.wm_concat()函数都不够用，
不能满足业务应用的大量拼接业务的使用。
----创建测试表：
--测试表结构：
SQL> set lines 80
SQL> desc suxing.WMCONCAT_TAB
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
TEL VARCHAR2(20)
INSERT_DATE DATE
--测试表记录数：
SQL> select distinct count(*) from suxing.WMCONCAT_TAB;
COUNT(*)
----------
262144
--测试表内容：
SQL> select distinct * from suxing.WMCONCAT_TAB;
NAME TEL INSERT_DA
-------------------- -------------------- ---------
suxing1 18777104737 27-NOV-17
suxing7 18777104733 27-NOV-17
suxing2 18777104738 27-NOV-17
suxing3 18777104739 27-NOV-17
suxing5 18777104731 27-NOV-17
suxing0 18777104736 27-NOV-17
suxing4 18777104730 27-NOV-17
suxing6 18777104732 27-NOV-17
8 rows selected.
##该表共26w多条记录，其中是以上8条记录的重复记录。三个字段，分别为名字、11位号码和输入日期。
----使用不同函数或者不同版本查询拼接表 LISTAGG( )函数：
SELECT name, LISTAGG(TEL, ',') WITHIN GROUP (ORDER BY TEL) AS pho_lists
FROM suxing.WMCONCAT_TAB
GROUP BY name;
##直接报错，拼接内容过长，超出该函数的保存长度varchar2(4000 ) 。 
--使用版本1的 wmsys.wm_concat()函数：
SELECT name, wm_concat(TEL) AS pho_lists
FROM suxing.WMCONCAT_TAB
GROUP BY name;
##同样报内部的拼接内容的长度超出了内部类的保存长度pl/sql varchar2 （32767 ） 。
wmsys.wm_concat()函数 ：
-- wmsys.wm_concat()函数 ：
改版本的函数，拼接的内容是以varchar2(32767 )数据类型的文本形式保存。
一.解锁wmsys用户
alter user wmsys identified by "XXXXXX" account unlock;
-- 创建包、包体和函数
以wmsys用户登录数据库，执行下面的命令
CREATE OR REPLACE TYPE WM_CONCAT_IMPL AS OBJECT
-- AUTHID CURRENT_USER 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
);
/
--定义类型body:
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;
/
--自定义行变列函数:
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;
/
--创建同义词并授权：
[sql] view plain copy
create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
/
create public synonym wm_concat for wmsys.wm_concat
/
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/
版本2的 ：
该版本的函数，拼接的内容是以clob (4G <span style="white-space:normal;font-family:;" "="">)数据类型的文本形式保存。
alter user wmsys identified by "Test_2017" account unlock;
--创建包、包体和函数：
以wmsys用户登录数据库，执行下面的命令
CREATE OR REPLACE TYPE wm_concat_impl AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
CURR_STR_C CLOB,
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 WM_CONCAT_IMPL) RETURN NUMBER
);
/
--定义类型body:
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,NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL, P1 VARCHAR2) RETURN NUMBER IS
BEGIN
IF (CURR_STR_C IS NULL AND (CURR_STR IS NULL OR LENGTH(CURR_STR)<29950)) THEN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ',' || P1;
ELSE
CURR_STR := P1;
END IF;
ELSE
IF (CURR_STR_C IS NULL) THEN
CURR_STR_C := CURR_STR ;
CURR_STR := NULL ;
END IF ;
CURR_STR_C := CURR_STR_C || ',' || P1;
END IF ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF WM_CONCAT_IMPL, RETURNVALUE OUT CLOB, FLAGS NUMBER) RETURN NUMBER IS
BEGIN
IF (CURR_STR IS NOT NULL) THEN
RETURNVALUE := CURR_STR ;
ELSE
RETURNVALUE := CURR_STR_C ;
END IF ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT WM_CONCAT_IMPL, SCTX2 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;
/
--自定义行变列函数:
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)
RETURN CLOB AGGREGATE USING WM_CONCAT_IMPL ;
/
--创建同义词并授权：
[sql] view plain copy
create public synonym WM_CONCAT_IMPL for wmsys.WM_CONCAT_IMPL
/
create public synonym wm_concat for wmsys.wm_concat
/
grant execute on WM_CONCAT_IMPL to public
/
grant execute on wm_concat to public
/
扫一扫
4576
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
