oracle更新到12c之後WM_CONCAT函數不存在的解決方法

之前一直用的是oracle 11.2.0.4版本的資料庫， 其中有一個行變列的函數是 WM_CONCAT.

最近很多系統資料庫改為oracle 19c了， 這個函數從12c開始就不再提供了， 當時我們很多程式員在程式中确使用了該函數，導緻程式出現錯誤

此處分享一下解決的辦法。

方法1： 使用 listagg

-- 原寫法：
SELECT to_char(WMSYS.WM_CONCAT(a)) FROM (select 1 a from dual union all select 2 from dual);
-- 新寫法：
SELECT listagg(a,',') within group (order by a) FROM (select 1 a from dual union all select 2 from dual);

-- 原方案
select to_char(wm_concat(status)),to_char(wm_concat(countstatus))
from (
      select '優' status ,30  countstatus from dual
      union all
      select '良', 70 from dual
      union all
      select '污染', 265 from dual
      )
-- 替代方案
select listagg(status,',') within group (order by decode(status,'優',1,'良',2,'污染',3)) status,
       listagg(countstatus,',') within group (order by decode(status,'優',1,'良',2,'污染',3)) status
from (
      select '優' status ,30  countstatus from dual
      union all
      select '良', 70 from dual
      union all
      select '污染', 265 from dual
      )           

方法二： 參照11g手動建立WM_CONCAT 函數

-- 使用sysdba解鎖内置的帳号
conn / as sysdba
alter user wmsys account unlock;
alter user wmsys identified by Abcd_123456;
grant connect,resource to wmsys;

-- 使用内置的帳号，建立類型和函數
conn wmsys/Abcd_123456
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
); 
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 ;  

-- 再次使用sysdba建立同義詞并賦權給所有人
conn / as sysdba
create public synonym WM_CONCAT_IMPL for WMSYS.WM_CONCAT_IMPL;
create public synonym WM_CONCAT for WMSYS.WM_CONCAT;
grant execute on WMSYS.WM_CONCAT_IMPL to public

--  現在所有帳号都可以使用了， 測試如下
select id,to_char(wmsys.wm_concat(name)) name ,to_char(wm_concat(name)) name2
  from (
        select '1' id,'蘋果' name from dual
         union all
        select '1','西瓜' from dual
         union all
        select '2','香蕉' from dual
       )
 group by id;