oracle 12C wmsys.wm_concat()函數(shù)

    對(duì)于一些業(yè)務(wù)，需要連接函數(shù)把內(nèi)容拼接文本文件的時(shí)候，借助合適的函數(shù)，非常重要，減少很多工作。
目前常用的連接函數(shù)有 wmsys.wm_concat()和 LISTAGG( )函數(shù)，當(dāng)然還有看拼接內(nèi)容的長(zhǎng)度來(lái)選。
oracle數(shù)據(jù)庫(kù)中，還有一個(gè)根據(jù)版本選擇。最新的兩個(gè)版本中，11G中，自帶有兩個(gè)函數(shù)，但在12C中，
oracle不再自帶 wmsys.wm_concat()，如果實(shí)際業(yè)務(wù)中需要到，需要自己創(chuàng)建上。當(dāng)然，如何創(chuàng)建，會(huì)在本文的最后，
會(huì)提供兩個(gè)版本的 wmsys.wm_concat()和 wmsys.wm_concat()函數(shù)沿用過(guò)來(lái)。 很多人說(shuō)在12C中， )函數(shù)
就已經(jīng)夠用了，但是在服務(wù)過(guò)的客戶的應(yīng)用中，該函數(shù)是遠(yuǎn)遠(yuǎn)不夠使用的，連版本1的 wmsys.wm_concat()函數(shù)都不夠用，
不能滿足業(yè)務(wù)應(yīng)用的大量拼接業(yè)務(wù)的使用。

----創(chuàng)建測(cè)試表：
--測(cè)試表結(jié)構(gòu)：
SQL> set lines 80
SQL> desc suxing.WMCONCAT_TAB
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(20)
 TEL                                                VARCHAR2(20)
 INSERT_DATE                                        DATE

--測(cè)試表記錄數(shù)：
SQL> select distinct count(*) from suxing.WMCONCAT_TAB;
  COUNT(*)
----------
    262144

--測(cè)試表內(nèi)容：
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條記錄的重復(fù)記錄。三個(gè)字段，分別為名字、11位號(hào)碼和輸入日期。

----使用不同函數(shù)或者不同版本查詢拼接表 LISTAGG( )函數(shù)：
SELECT name, LISTAGG(TEL, ',') WITHIN GROUP (ORDER BY TEL) AS pho_lists
FROM   suxing.WMCONCAT_TAB
GROUP BY name;

##直接報(bào)錯(cuò)，拼接內(nèi)容過(guò)長(zhǎng)，超出該函數(shù)的保存長(zhǎng)度varchar2(4000 ) 。


--使用版本1的 wmsys.wm_concat()函數(shù)：
SELECT name,  wm_concat(TEL) AS pho_lists
FROM   suxing.WMCONCAT_TAB
GROUP BY name;

##同樣報(bào)內(nèi)部的拼接內(nèi)容的長(zhǎng)度超出了內(nèi)部類的保存長(zhǎng)度pl/sql varchar2 （32767 ） 。

wmsys.wm_concat()函數(shù) ：
-- wmsys.wm_concat()函數(shù) ：
改版本的函數(shù)，拼接的內(nèi)容是以varchar2(32767 )數(shù)據(jù)類型的文本形式保存。
一.解鎖wmsys用戶
alter user wmsys identified by "XXXXXX" account unlock;
-- 創(chuàng)建包、包體和函數(shù)
以wmsys用戶登錄數(shù)據(jù)庫(kù)，執(zhí)行下面的命令 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;  
/  
--自定義行變列函數(shù):  
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)  
RETURN VARCHAR2 AGGREGATE USING WM_CONCAT_IMPL ;  
/  

--創(chuàng)建同義詞并授權(quán)：
[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的 ：
該版本的函數(shù)，拼接的內(nèi)容是以clob (4G <span style="white-space:normal;font-family:;" "="">)數(shù)據(jù)類型的文本形式保存。
alter user wmsys identified by "Test_2017" account unlock;
--創(chuàng)建包、包體和函數(shù)：
以wmsys用戶登錄數(shù)據(jù)庫(kù)，執(zhí)行下面的命令
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;
/

--自定義行變列函數(shù):  
CREATE OR REPLACE FUNCTION wm_concat(P1 VARCHAR2)  
RETURN CLOB AGGREGATE USING WM_CONCAT_IMPL ;  
/  

--創(chuàng)建同義詞并授權(quán)：
[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  
/