oracle的concat字符串太长,自定义聚集函数，仿wm_concat，使用CLOB，可处理超过4000长度字符串...

因开发需要，在使用wm_concat函数时，在字符串长度超过4000后报错，所以自定义一个类似的聚集函数，在内部使用CLOB处理，避免超长问题。

源码如下：

先以sys用户授权：

grant execute on dbms_lob to xxx;

CREATE OR REPLACE

TYPE typ_concat_clob AS OBJECT

(

concat_str CLOB,

splitstr CLOB,

--ODCIAggregateInitialize做一些初始化操作

STATIC FUNCTION

ODCIAggregateInitialize(cs_ctx In Out typ_concat_clob) return number,

--ODCIAggregateIterate是主要的处理逻辑所在，这里定义一个迭代操作

member function ODCIAggregateIterate(self In

Out typ_concat_clob,srcvalue in VARCHAR2) return number,

-- ODCIAggregateMerge是一个合并函数，如果在使用时指定了partition enabled，就必须定义此函数，用来把并行处理的结果进行合并

member function ODCIAggregateMerge(self In

Out typ_concat_clob,ctx2 In Out typ_concat_clob) return number,

--ODCIAggregateTerminate是一个终止函数，顾名思义，在这个函数中对结果做最后处理并返回

member function ODCIAggregateTerminate(self

In Out typ_concat_clob,returnValue Out CLOB,flags IN NUMBER) return number

)

CREATE OR REPLACE

TYPE BODY typ_concat_clob IS

STATIC FUNCTION

ODCIAggregateInitialize(cs_ctx In Out typ_concat_clob) return NUMBER

IS

BEGIN

cs_ctx :=

typ_concat_clob(to_clob(','),to_clob(','));

RETURN ODCICONST.SUCCESS;

END;

member function ODCIAggregateIterate(self In

Out typ_concat_clob,srcvalue IN VARCHAR2) return NUMBER

IS

BEGIN

dbms_lob.append(self.concat_str,to_clob(srcvalue));

dbms_lob.append(self.concat_str,self.splitstr);

RETURN ODCICONST.SUCCESS;

END;

member function ODCIAggregateMerge(self In

Out typ_concat_clob,ctx2 In Out typ_concat_clob) return NUMBER

IS

BEGIN

IF ctx2 IS NOT NULL THEN

dbms_lob.append(self.concat_str,ctx2.concat_str);

END IF;

RETURN ODCICONST.SUCCESS;

END;

member function ODCIAggregateTerminate(self

In Out typ_concat_clob,returnValue Out CLOB,flags IN NUMBER) return NUMBER

IS

BEGIN

dbms_lob.trim(self.concat_str,dbms_lob.getlength(self.concat_str)-1);

self.concat_str :=

dbms_lob.substr(self.concat_str,dbms_lob.getlength(self.concat_str)-1,2);

returnValue := self.concat_str;

RETURN ODCICONST.SUCCESS;

END;

END;

/

CREATE OR REPLACE

FUNCTION fun_concat_clob(in_str VARCHAR2) RETURN CLOB

AGGREGATE USING typ_concat_clob;

/

使用和wm_concat一样，不同的是内部处理和返回值都是CLOB类型的：

SELECT

fun_concat_clob(name)FROM students

GROUP BY class;