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

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