感恩节，赠送oracle 12C 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()函数都不够用，不能满足业务应用的大量拼接业务的使用。

创建测试表

该表共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)。

只有当拼接长度小于4000的时候，语句中LISTAGG()函数才可以正常执行，没有报错。

使用版本1的

wmsys.wm_concat()函数‍

SELECT name,  wm_concat(TEL) AS pho_lists
FROM   suxing.WMCONCAT_TAB
GROUP BY name;

同样报内部的拼接内容的长度超出了内部类的保存长度pl/sql varchar2（32767）。                                

使用版本2的

wmsys.wm_concat()函数‍

使用版本2的wmsys.wm_concat()函数是可以将我测试表中的

26W多条记录的TEL字段中的电话号码分为8组拼接起来，效果如下图所示。读者不妨可以根据版本2的wmsys.wm_concat创建脚本，然后测试拼接效果。

SELECT name,  wm_concat(TEL) AS pho_lists
FROM   suxing.WMCONCAT_TAB
GROUP BY name;

附录-

wmsys.wm_concat()函数

创建脚本‍

❤ 版本1的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 ;  
/  
创建同义词并授权给public：
[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的wmsys.wm_concat()函数
该版本的函数，拼接的内容是以clob(4G)数据类型的文本形式保存。
alter user wmsys identified by "Test_2017" account unlock;
创建包、包体和函数：
以wmsys用户登录数据库，执行下面的命令
CREATE OR REPLACE TYPE wm_concat_impl AS OBJECT

##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 ;  
/  
创建同义词并授权给public:
[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  
/