![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
a. static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
自定义聚集函数初始化设置,从这儿开始一个聚集函数
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT varchar2,flags IN number)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create or replace type string_agg_type as object
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
total varchar2(4000),
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
static function
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ODCIAggregateIterate(self IN OUT string_agg_type ,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
value IN varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ODCIAggregateTerminate(self IN string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue OUT varchar2,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
flags IN number)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ODCIAggregateMerge(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ctx2 IN string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
);
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Type created.
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create or replace type body string_agg_type
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sctx := string_agg_type( null );
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateIterate(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
value IN varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
self.total := self.total || ',' || value;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateTerminate(self IN string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue OUT varchar2,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
flags IN number)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue := ltrim(self.total,',');
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateMerge(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ctx2 IN string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
self.total := self.total || ctx2.total;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Type body created.
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE or replace
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FUNCTION stragg(input varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
RETURN varchar2
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
scott@ORA9I.WORLD> select deptno, stragg(ename)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2 from emp
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3 group by deptno
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4 /
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DEPTNO
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
----------
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
STRAGG(ENAME)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
---------------------------------------------------------------------------------
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--------------------------------------------------
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CLARK,KING,MILLER
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SMITH,FORD,ADAMS,SCOTT,JONES
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create or replace type body string_agg_type
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sctx := string_agg_type( vcArray() );
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateIterate(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
value IN varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
data.extend;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
data(data.count) := value;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateTerminate(self IN string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue OUT varchar2,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
flags IN number)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
l_data varchar2(4000);
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
for x in ( select column_value from TABLE(data) order by 1 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
loop
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
l_data := l_data || ',' || x.column_value;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end loop;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue := ltrim(l_data,',');
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateMerge(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ctx2 IN string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin -- not really tested ;)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
for i in 1 .. ctx2.data.count
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
loop
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
data.extend;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
data(data.count) := ctx2.data(i);
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end loop;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE or replace
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FUNCTION stragg(input varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
RETURN varchar2
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ops$tkyte@ORA9IR2> column ename format a40ops$tkyte@ORA9IR2> select deptno, stragg(ename) ename
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2 from emp
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3 group by deptno
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4 /
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DEPTNO ENAME
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
---------- ----------------------------------------
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10 CLARK,KING,MILLER
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20 ADAMS,FORD,JONES,SCOTT,SMITH
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
oracle自定义聚集函数接口简介
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
a. static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
自定义聚集函数初始化设置,从这儿开始一个聚集函数
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
b. member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
自定义聚集函数,最主要的步骤,这个函数定义我们的聚集函数具体做什么操作,后面的例子,是取最大值,最小值,平均值,还是做连接操作.self 为当前聚集函数的指针,用来与前面的计算结果进行关联
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
c. member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT varchar2,flags IN number)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
用来合并两个聚集函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚集函数的时候.
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create or replace type string_agg_type as object
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
total varchar2(4000),
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
static function
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ODCIAggregateIterate(self IN OUT string_agg_type ,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
value IN varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ODCIAggregateTerminate(self IN string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue OUT varchar2,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
flags IN number)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ODCIAggregateMerge(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ctx2 IN string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
);
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Type created.
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create or replace type body string_agg_type
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sctx := string_agg_type( null );
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateIterate(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
value IN varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
self.total := self.total || ',' || value;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateTerminate(self IN string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue OUT varchar2,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
flags IN number)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue := ltrim(self.total,',');
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateMerge(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ctx2 IN string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
self.total := self.total || ctx2.total;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Type body created.
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE or replace
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FUNCTION stragg(input varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
RETURN varchar2
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
scott@ORA9I.WORLD> select deptno, stragg(ename)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2 from emp
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3 group by deptno
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4 /
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DEPTNO
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
----------
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
STRAGG(ENAME)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
---------------------------------------------------------------------------------
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--------------------------------------------------
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CLARK,KING,MILLER
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SMITH,FORD,ADAMS,SCOTT,JONES
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create or replace type body string_agg_type
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sctx := string_agg_type( vcArray() );
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateIterate(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
value IN varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
data.extend;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
data(data.count) := value;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateTerminate(self IN string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue OUT varchar2,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
flags IN number)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
l_data varchar2(4000);
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
for x in ( select column_value from TABLE(data) order by 1 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
loop
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
l_data := l_data || ',' || x.column_value;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end loop;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
returnValue := ltrim(l_data,',');
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function ODCIAggregateMerge(self IN OUT string_agg_type,
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ctx2 IN string_agg_type)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return number
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
is
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin -- not really tested ;)
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
for i in 1 .. ctx2.data.count
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
loop
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
data.extend;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
data(data.count) := ctx2.data(i);
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end loop;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return ODCIConst.Success;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE or replace
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FUNCTION stragg(input varchar2 )
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
RETURN varchar2
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
ops$tkyte@ORA9IR2> column ename format a40ops$tkyte@ORA9IR2> select deptno, stragg(ename) ename
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2 from emp
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3 group by deptno
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4 /
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
DEPTNO ENAME
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
---------- ----------------------------------------
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10 CLARK,KING,MILLER
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20 ADAMS,FORD,JONES,SCOTT,SMITH
![(转载)使用自定义聚集函数来实现分组合并字符串 - abao8_111 - abao8_111的博客](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD