ITPub博客

首页 > 应用开发 > IT综合 > 使用自定义聚集函数来实现分组合并字符串

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

原创 IT综合 作者:yaanzy 时间:2006-03-22 16:01:43 0 删除 编辑

oracle自定义聚集函数接口简介
a. static function ODCIAggregateInitialize(sctx IN OUTstring_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)
终止聚集函数的处理,返回聚集函数处理的结果

以下例子来自asktom网站

[@more@]

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3898/viewspace-822115/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    108
  • 访问量
    761539