推荐订阅源

-----------------------------------------------------------------------------------------------------------
--以下请在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 : 对多行字串进行合并，用指定的字符作间隔符

-- 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
)   ;

 
--No.2

create or replace type body string_merge_type is

  static function odciaggregateinitialize(sctx in out string_merge_type)
    return number is
  begin
    sctx := string_merge_type(null);
    return odciconst.success;
  end;

  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;

  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;

  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;     

   
 
 --另：如果添加对于相同值在合并后的字串中只出现一次的处理 ---
--No.2

create or replace type body string_merge_type is

  static function odciaggregateinitialize(sctx in out string_merge_type)
    return number is
  begin
    sctx := string_merge_type(null);
    sctx.str:=',';
    return odciconst.success;
  end;

  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;

  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;

  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; 

--No.3

CREATE or replace FUNCTION merger(input varchar2 ) RETURN varchar2
      PARALLEL_ENABLE AGGREGATE USING string_merge_type;       
  

 
--函数功能测试

/*
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);

--不同查询方式下的测试结果 ,查询表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

      本来是可以在pl/sql中用创建type的方式来写和编辑，更为方便，但我发现在pl/sql中self是不被允许的，而聚合函数在这里要用到的地方是不可改为他名的，所在如果编译成功后如果在pl/sql中打开，请不要用spec&body的方式打开，否则什么也不动再编译也会报错，这可能算是pl/sql的一个bug吧！