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;   
/