自定义一个聚合函数

1.创建类型

create or replace type strcat_type as object (
cat_string varchar2(4000),
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return
number,
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type)
return number,
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out
varchar2,flags in number) return number
);

2. 创建类型体

create or replace type body strcat_type is
static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number
is
begin
      cs_ctx := strcat_type( null );
      return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT strcat_type,
                                       value IN varchar2 )
return number
is
begin
      self.cat_string := self.cat_string || ';'|| value;
      return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN Out strcat_type,
                                         returnValue OUT varchar2,
                                         flags IN number)
return number
is
begin
      returnValue := ltrim(rtrim(self.cat_string,';'),';');
      return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT strcat_type,
                                     ctx2 IN Out strcat_type)
return number
is
begin
      self.cat_string := self.cat_string || ';' || ctx2.cat_string;
      return ODCIConst.Success;
end;

end;

3.创建函数
CREATE OR REPLACE FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;

4、select id, strcat(name) from users group by id;