自定义聚合函数

在项目开发过程中遇到将多条记录合并为一条记录的需求，这时原来的聚合函数己经不能满足我们的要求，于是在网上找到了一个聚合函数的实例供大家使用及更改更好的函数。下面创建应用的表结构并构建相应的数据。

create table jl_test
(
name varchar2(200),
id number(2),
depno number(2)
);
insert into jl_test values ('admin',1,2);
insert into jl_test values ('root',1,2);
commit;
select strcat(name),id,depno from jl_test group by id,depno;
-- 1 admin,root 1 2

1.创建类型定义

create or replace type strcat_type as object
      (
        currentstr varchar2(4000),
        currentseprator varchar2(8),
        static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number,
        member function ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number,
        member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number,
        member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number
      );

2.创建类型体及类体函数

create or replace type body strcat_type is
      static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is
      begin
        sctx := strcat_type('',',');
        return ODCIConst.Success;
      end;
      member function ODCIAggregateIterate(self IN OUT strcat_type, value IN VARCHAR2) return number is
      begin
        if self.currentstr is null then
           self.currentstr := value;
        else
          self.currentstr := self.currentstr ||currentseprator || value;
        end if;
        return ODCIConst.Success;
      end;
      member function ODCIAggregateTerminate(self IN strcat_type, returnValue OUT VARCHAR2, flags IN number) return number is
      begin
        returnValue := self.currentstr;
        return ODCIConst.Success;
      end;
      member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN strcat_type) return number is
      begin
        if ctx2.currentstr is null then
          self.currentstr := self.currentstr;
        elsif self.currentstr is null then
          self.currentstr := ctx2.currentstr;
        else
          self.currentstr := self.currentstr || currentseprator || ctx2.currentstr;
        end if;
        return ODCIConst.Success;
      end;
      end;

3.创建实例函数

CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;

 

以上内容均为网上一些人己经写好的，我只是拿过来应用而己，哈哈