oracle自定义聚集函数实现列合并

原帖:http://www.cnblogs.com/changhai-xuri/archive/2006/11/05/550811.html

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