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、简单的合并（合并不需要排序）
原文：
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、简单的合并（合并的字符串要排序）
原文：
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
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、简单的合并（合并不需要排序）
原文：
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、简单的合并（合并的字符串要排序）
原文：
除了用以下方法，还可以用上面1中的聚集函数以分析函数的方式使用，在over子句中排序来实现，可以参考
itpub上的文章：
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