wmsys.wm_concat函数问题

概述

在日常工作中，一些客户对于wmsys.wm_concat函数使用的非常多，一些客户在升级到12c时会碰到这个问题。因为Oracle从版本12c开始，WMSYS.WM_CONCAT()函数已被标记为不推荐使用，并在版本19c中完全移除了。因此，在Oracle 19c及更高版本中，该函数不再可用。

WMSYS.WM_CONCAT()函数用于将多行数据连接成一个具有逗号分隔符的字符串。由于该函数在早期版本中广泛使用，但存在一些限制和潜在安全风险，因此Oracle决定不再支持该函数，并鼓励用户使用其他方法来实现类似的功能。因此推荐使用LISTAGG()函数。LISTAGG()函数提供了更安全、更强大的字符串连接功能，在Oracle 11g及更高版本中就能够用到。

但是一些客户因为业务系统存留问题还需要这个函数，下文展示如何创建这个函数。

wm_concat函数创建

方法1

CREATE OR REPLACE TYPE WM_CONCAT_TYPE AS OBJECT
(
  total VARCHAR2(32767),
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT WM_CONCAT_TYPE) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT WM_CONCAT_TYPE, value IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN WM_CONCAT_TYPE, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT WM_CONCAT_TYPE, ctx2 IN WM_CONCAT_TYPE) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY WM_CONCAT_TYPE IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT WM_CONCAT_TYPE) RETURN NUMBER IS
  BEGIN
    sctx := WM_CONCAT_TYPE(NULL);
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT WM_CONCAT_TYPE, value IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    self.total := self.total || ',' || value;
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateTerminate(self IN WM_CONCAT_TYPE, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := LTRIM(self.total, ',');
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT WM_CONCAT_TYPE, ctx2 IN WM_CONCAT_TYPE) RETURN NUMBER IS
  BEGIN
    self.total := self.total || ctx2.total;
    RETURN ODCIConst.Success;
  END;
END;
/

CREATE OR REPLACE TYPE BODY WM_CONCAT_TYPE IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT WM_CONCAT_TYPE) RETURN NUMBER IS
  BEGIN
    sctx := WM_CONCAT_TYPE(NULL);
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT WM_CONCAT_TYPE, value IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    self.total := self.total || ',' || value;
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateTerminate(self IN WM_CONCAT_TYPE, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := LTRIM(self.total, ',');
    RETURN ODCIConst.Success;
  END;
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT WM_CONCAT_TYPE, ctx2 IN WM_CONCAT_TYPE) RETURN NUMBER IS
  BEGIN
    self.total := self.total || ctx2.total;
    RETURN ODCIConst.Success;
  END;
END;
/

方法2

改版本的函数，拼接的内容是以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;  
/  

方法3

--  该版本的函数，拼接的内容是以clob (4G )数据类型的文本形式保存。
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;
/  

-- 创建同义词并授权：
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;   
/