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