oracle添加字符串连接聚合函数wm_concat

自从oracle新版废弃了wm_concat函数后，各种不方便，网上搜索到的自定义聚合函数也是问题多多，例如用varchar2(32767)定义返回值类型，4000会超，32767不一样会超吗？所以最终用clob类型返回才是最终解决方案，你说会慢？慢就慢点，总比无法实现的好，用xmlagg替代的方案更要慢死人。

废话不多说了，上代码

create or replace type wm_concat_impl as object
(
  join_string clob,
  static function ODCIAggregateInitialize(sctx IN OUT wm_concat_impl) 
    return number,
  member function ODCIAggregateIterate(self IN OUT wm_concat_impl, 
    value IN varchar2) return number,
  member function ODCIAggregateTerminate(self IN wm_concat_impl, 
    returnValue OUT clob, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT wm_concat_impl, 
    ctx2 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);
	dbms_lob.createtemporary(sctx.join_string, true);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT wm_concat_impl, value IN varchar2) 
return number is
begin
  if(dbms_lob.getlength(self.join_string) > 0)then
	  dbms_lob.append(self.join_string,',');
	end if;
	dbms_lob.append(self.join_string,value);
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN wm_concat_impl, returnValue OUT 
clob, flags IN number) return number is
begin
  returnValue := self.join_string;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT wm_concat_impl, ctx2 IN 
wm_concat_impl) return number is
begin
  if(dbms_lob.getlength(self.join_string) > 0 and dbms_lob.getlength(ctx2.join_string) > 0) then
    dbms_lob.append(self.join_string,',');
	  dbms_lob.append(self.join_string,ctx2.join_string);
	elsif(dbms_lob.getlength(ctx2.join_string) > 0) then
	  self.join_string := ctx2.join_string;
	end if;
  return ODCIConst.Success;
end;
end;
/
create or replace FUNCTION wm_concat (input varchar2) RETURN clob 
PARALLEL_ENABLE AGGREGATE USING wm_concat_impl;
/