自定义函数替代WMSYS.WM_CONCAT

参考文章：

http://docs.oracle.com/cd/B13789_01/appdev.101/b10800/dciaggfns.htm#i1005028

http://www.eygle.com/archives/2012/10/wmsys_wm_concat.html

 

提供如下完整代码，经测试可以运行：

create or replace type MyConcatImpl as object
(
  str VARCHAR2(32767), -- second highest value seen so far
  static function ODCIAggregateInitialize(sctx IN OUT MyConcatImpl) 
    return number,
  member function ODCIAggregateIterate(self IN OUT MyConcatImpl, 
    value IN VARCHAR2) return number,
  member function ODCIAggregateTerminate(self IN MyConcatImpl, 
    returnValue OUT VARCHAR2, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT MyConcatImpl, 
    ctx2 IN MyConcatImpl) return number
);
/

create or replace type body MyConcatImpl is 
static function ODCIAggregateInitialize(sctx IN OUT MyConcatImpl) 
return number is 
begin
  sctx := MyConcatImpl(NULL);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT MyConcatImpl, value IN VARCHAR2) return number is
begin
  if self.str is not null then
    self.str := self.str || ';' || value;
  else
    self.str := value;
  end if;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN MyConcatImpl, returnValue OUT VARCHAR2, flags IN number) return number is
begin
  returnValue := self.str;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT MyConcatImpl, ctx2 IN MyConcatImpl) return number is
begin
  if ctx2.str is not null then
    self.str := self.str || ';' || ctx2.str;
  end if;
  return ODCIConst.Success;
end;
end;
/
create or replace FUNCTION MyConcat(input VARCHAR2) RETURN VARCHAR2 
PARALLEL_ENABLE AGGREGATE USING MyConcatImpl;
/

如执行如下SQL：

select MyConcat(to_char(tt.user_id || ',' || tt.username )) as concat_char from dba_users tt

注：如果要处理的字符串很长，则可将VARCHAR2替换成Clob类型。