(转载)使用自定义聚集函数来实现分组合并字符串

oracle自定义聚集函数接口简介

a. static  function  ODCIAggregateInitialize(sctx  IN  OUT string_agg_type ) 
return   number

自定义聚集函数初始化设置,从这儿开始一个聚集函数

b. member  function  ODCIAggregateIterate(self  IN  OUT string_agg_type ,value  IN   varchar2 )
return   number

自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联

c. member  function  ODCIAggregateMerge (self  IN  string_agg_type,returnValue OUT  varchar2 ,flags  IN   number ) 
return   number

用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.

d. member  function  OCDIAggregateTerminate(self  IN  string_agg_type,returnValue OUT  varchar2 ,flags  IN   number )

终止聚集函数的处理,返回聚集函数处理的结果

1、简单的合并（合并不需要排序）
原文：http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:229614022562

 

create   or   replace  type string_agg_type  as  object
(
total  varchar2 ( 4000 ),

static  function
ODCIAggregateInitialize(sctx  IN  OUT string_agg_type )
return   number ,

member  function
ODCIAggregateIterate(self  IN  OUT string_agg_type ,
value  IN   varchar2  )
return   number ,

member  function
ODCIAggregateTerminate(self  IN  string_agg_type,
returnValue OUT  varchar2 ,
flags  IN   number )
return   number ,

member  function
ODCIAggregateMerge(self  IN  OUT string_agg_type,
ctx2  IN  string_agg_type)
return   number
);
/

Type created.

create   or   replace  type body string_agg_type
is

static  function  ODCIAggregateInitialize(sctx  IN  OUT string_agg_type)
return   number
is
begin
sctx : =  string_agg_type(  null  );
return  ODCIConst.Success;
end ;

member  function  ODCIAggregateIterate(self  IN  OUT string_agg_type,
value  IN   varchar2  )
return   number
is
begin
self.total : =  self.total  ||   ' , '   ||  value;
return  ODCIConst.Success;
end ;

member  function  ODCIAggregateTerminate(self  IN  string_agg_type,
returnValue OUT  varchar2 ,
flags  IN   number )
return   number
is
begin
returnValue : =   ltrim (self.total, ' , ' );
return  ODCIConst.Success;
end ;

member  function  ODCIAggregateMerge(self  IN  OUT string_agg_type,
ctx2  IN  string_agg_type)
return   number
is
begin
self.total : =  self.total  ||  ctx2.total;
return  ODCIConst.Success;
end ;


end ;
/

Type body created.

CREATE   or   replace
FUNCTION  stragg(input  varchar2  )
RETURN   varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/


scott @ORA9I .WORLD >   select  deptno, stragg(ename)
2   from  emp
3   group   by  deptno
4   /

DEPTNO
-- --------
STRAGG(ENAME)
-- -------------------------------------------------------------------------------
-- ------------------------------------------------
10
CLARK,KING,MILLER

20
SMITH,FORD,ADAMS,SCOTT,JONES

30
ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

2、简单的合并（合并的字符串要排序）
原文：http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:15637744429336#16551777586484
除了用以下方法，还可以用上面1中的聚集函数以分析函数的方式使用，在over子句中排序来实现，可以参考
itpub上的文章：http://www.itpub.net/338337.html

 

create   or   replace  type body string_agg_type
is

static  function  ODCIAggregateInitialize(sctx  IN  OUT string_agg_type)
return   number
is
begin
sctx : =  string_agg_type( vcArray() );
return  ODCIConst.Success;
end ;

member  function  ODCIAggregateIterate(self  IN  OUT string_agg_type,
value  IN   varchar2  )
return   number
is
begin
data.extend;
data(data. count ) : =  value;
return  ODCIConst.Success;
end ;

member  function  ODCIAggregateTerminate(self  IN  string_agg_type,
returnValue OUT  varchar2 ,
flags  IN   number )
return   number
is
l_data  varchar2 ( 4000 );
begin
for  x  in  (  select  column_value  from   TABLE (data)  order   by   1  )
loop
l_data : =  l_data  ||   ' , '   ||  x.column_value;
end  loop;
returnValue : =   ltrim (l_data, ' , ' );
return  ODCIConst.Success;
end ;

member  function  ODCIAggregateMerge(self  IN  OUT string_agg_type,
ctx2  IN  string_agg_type)
return   number
is
begin   --  not really tested ;)
for  i  in   1  .. ctx2.data. count
loop
data.extend;
data(data. count ) : =  ctx2.data(i);
end  loop;
return  ODCIConst.Success;
end ;


end ;
/


CREATE   or   replace
FUNCTION  stragg(input  varchar2  )
RETURN   varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/


ops$tkyte @ORA9IR2 >   column  ename format a40ops$tkyte @ORA9IR2 >   select  deptno, stragg(ename) ename
2   from  emp
3   group   by  deptno
4   /

DEPTNO ENAME
-- -------- ----------------------------------------
10  CLARK,KING,MILLER
20  ADAMS,FORD,JONES,SCOTT,SMITH
30  ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD