自定义聚集函数

对于一些业务，需要连接函数把内容拼接文本文件的时候，借助合适的函数，非常重要，减少很多工作。 
目前常用的连接函数有 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   
/