有几篇文章都写过了关于ORACLE中的自制聚集函数的例子,这里再加一篇自已写的对多行数据在a 字段group by 后,对别的字段值进行字串合并的例子.
-----------------------------------------------------------------------------------------------------------
--以下请在pl/sql里依次执行,
--功能说明:有几篇文章都写过了关于ORACLE中的自制聚集函数的例子,这里再加一篇自已写的对多行数据在a 字段group by 后,对别的字段值进行字串合并的例子.
--NO.1
create or replace type string_merge_type as object
(
-- author : administrator
-- created : 12-九月-06 10:36:27
-- purpose : 对多行字串进行合并,用指定的字符作间隔符
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- attributes
str varchar2(30000),
static function odciaggregateinitialize(sctx in out string_merge_type)
return number,
member function odciaggregateiterate(self in out string_merge_type,
value in varchar2) return number,
member function odciaggregateterminate(self in string_merge_type,
returnvalue out varchar2,
flags in number)
return number,
member function odciaggregatemerge(self in out string_merge_type,
ctx2 in string_merge_type)
return number
) ;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--No.2
create or replace type body string_merge_type is
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
static function odciaggregateinitialize(sctx in out string_merge_type)
return number is
begin
sctx := string_merge_type(null);
return odciconst.success;
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function odciaggregateiterate(self in out string_merge_type,
value in varchar2) return number is
begin
self.str := self.str || value || ',';
return odciconst.success;
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function odciaggregateterminate(self in string_merge_type,
returnvalue out varchar2,
flags in number) return number is
begin
returnvalue := substr(self.str, 1, length(self.str) - 1);
return odciconst.success;
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function odciaggregatemerge(self in out string_merge_type,
ctx2 in string_merge_type)
return number is
begin
self.str:=self.str||ctx2.str; --可直接用null;
return odciconst.success;
end;
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--另:如果添加对于相同值在合并后的字串中只出现一次的处理 ---
--No.2
create or replace type body string_merge_type is
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
static function odciaggregateinitialize(sctx in out string_merge_type)
return number is
begin
sctx := string_merge_type(null);
sctx.str:=',';
return odciconst.success;
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function odciaggregateiterate(self in out string_merge_type,
value in varchar2) return number is
begin
if instr(self.str,','||value||',',1,1)=0 then
self.str := self.str || value || ',';
end if;
return odciconst.success;
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function odciaggregateterminate(self in string_merge_type,
returnvalue out varchar2,
flags in number) return number is
begin
returnvalue :=ltrim(rtrim(self.str,','),',');
return odciconst.success;
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
member function odciaggregatemerge(self in out string_merge_type,
ctx2 in string_merge_type)
return number is
begin
self.str:=self.str||ctx2.str; --可直接用null;
return odciconst.success;
end;
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--No.3
CREATE or replace FUNCTION merger(input varchar2 ) RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_merge_type;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--函数功能测试
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*
TODO: owner="Administrator" created="12-四月-06"
text="测试函数特性"
*/
drop table test_xxm;
create table test_xxm (a_s varchar2(5),b_s varchar2(5),c_n number(4,2),d_n number(5,0));
insert into test_xxm (a_s,b_s,c_n,d_n) values('aa','a1',2,5);
insert into test_xxm (a_s,b_s,c_n,d_n) values('bb','a4',3,4);
insert into test_xxm (a_s,b_s,c_n,d_n) values('cc','a2',4,6);
insert into test_xxm (a_s,b_s,c_n,d_n) values('aa','a2',2,1);
insert into test_xxm (a_s,b_s,c_n,d_n) values('bb','a3',5,3);
insert into test_xxm(a_s,b_s,c_n,d_n)values('bb','a3',6,2);
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--不同查询方式下的测试结果 ,查询表test_xxm的结果
select * from test_xxm ;
aa a1 2.00 5
bb a4 3.00 4
cc a2 4.00 6
aa a2 2.00 1
bb a3 5.00 3
bb a3 6.00 2 ;
select merger(b_s) from test_xxm ;
a1,a4,a2,a2,a3,a3;
select merger(b_s) from test_xxm group by b_s;
1 a1
2 a2,a2
3 a3,a3
4 a4
select merger(B_S) OVER (ORDER BY b_s) from test_xxm ;
a1
a1,a2,a2
a1,a2,a2
a1,a2,a2,a3
a1,a2,a2,a3,a3
a1,a2,a2,a3,a3,a4 ;
select merger(B_S) OVER (ORDER BY b_s desc) from test_xxm ;
a4
a4,a3,a3
a4,a3,a3
a4,a3,a3,a2,a2
a4,a3,a3,a2,a2
a4,a3,a3,a2,a2,a1 ;
select merger(B_S) OVER (PARTITION BY a_s ORDER BY b_s desc) from test_xxm ;
a2
a2,a1
a4
a4,a3,a3
a4,a3,a3
a2 ;
select merger(B_S) from test_xxm group by a_s ;
a1,a2
a4,a3,a3
a2
select merger(B_S),merger(c_n) from test_xxm group by a_s ;
a1,a2 2
a4,a3 3,5
a2 4
select a_s, max(b_s)
from (select a_s, merger(b_s) over(partition by a_s order by b_s) b_s
from test_xxm)
group by a_s
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
本来是可以在pl/sql中用创建type的方式来写和编辑,更为方便,但我发现在pl/sql中self是不被允许的,而聚合函数在这里要用到的地方是不可改为他名的,所在如果编译成功后如果在pl/sql中打开,请不要用spec&body的方式打开,否则什么也不动再编译也会报错,这可能算是pl/sql的一个bug吧!