因开发需要,在使用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;