1.新建type strcat_type
create or replace type strcat_type as object
(
cat_string varchar2(4000),
--对象初始化
static function odciaggregateinitialize(cs_ctx in out strcat_type) return number,
--聚合函数的迭代方法(这是最重要的方法)
member function odciaggregateiterate(self in out strcat_type,value in varchar2) return number,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
member function odciaggregatemerge(self in out strcat_type,ctx2 in out strcat_type) return number,
--终止聚集函数的处理,返回聚集函数处理的结果
member function odciaggregateterminate(self in out strcat_type,returnvalue out varchar2,flags in number) return number
)
2.建立type body strcat_type
create or replace type body strcat_type is
static function odciaggregateinitialize(cs_ctx in out strcat_type) return number
is
begin
cs_ctx := strcat_type( null );
return odciconst.success;
end;
member function odciaggregateiterate(self in out strcat_type, value in varchar2 ) return number
is
begin
/*字符串已','分割 */
self.cat_string := self.cat_string || '|'|| value;
return odciconst.success;
end;
member function odciaggregateterminate(self in out strcat_type, returnvalue out varchar2, flags in number) return number
is
begin
/*去除空(is null)*/
returnvalue := ltrim(rtrim(self.cat_string,'|'),'|');
return odciconst.success;
end;
member function odciaggregatemerge(self in out strcat_type, ctx2 in out strcat_type) return number
is
begin
self.cat_string := self.cat_string || '|' || ctx2.cat_string;
return odciconst.success;
end;
end;
3.建立函数func_strcat
create or replace function func_strcat(input varchar2)
return varchar2 -- 返回值
parallel_enable aggregate using strcat_type; --使平行累加
4.结果
select emp.deptno,func_strcat(emp.ename) from emp group by emp.deptno;
10 CLARK|MILLER|KING
20 SMITH|FORD|ADAMS|SCOTT|JONES
30 ALLEN|JAMES|TURNER|BLAKE|MARTIN|WARD
最近发现竟然有一个系统的函数可以直接连接字符串
select emp.deptno,replace(wmsys.wm_concat(emp.ename),',','|') from emp group by emp.deptno;
结果和4一样