oracle 定义聚合函数,Oracle 自定义聚合函数

create or replace type str_concat_type as object (

cat_string varchar2(4000),

static function ODCIAggregateInitialize(cs_ctx In Out str_concat_type) return number,

member function ODCIAggregateIterate(self In Out str_concat_type,value in varchar2) return number,

member function ODCIAggregateMerge(self In Out str_concat_type,ctx2 In Out str_concat_type) return number,

member function ODCIAggregateTerminate(self In Out str_concat_type,returnValue Out varchar2,flags in number) return number

)

/

create or replace type body str_concat_type is

static function ODCIAggregateInitialize(cs_ctx IN OUT str_concat_type) return number

is

begin

cs_ctx := str_concat_type( null );

return ODCIConst.Success;

end;

member function ODCIAggregateIterate(self IN OUT str_concat_type,

value IN varchar2 )

return number

is

begin

--1. concat string

self.cat_string := self.cat_string || ‘,‘|| value;

-- 2.get union set

-- if instr(self.cat_string, value ) = 0 or self.cat_string is null then

-- self.cat_string := self.cat_string || ‘,‘ || value ;

-- else

-- self.cat_string := self.cat_string ||‘‘ ;

-- end if ;

return ODCIConst.Success;

end;

member function ODCIAggregateTerminate(self IN Out str_concat_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 str_concat_type,

ctx2 IN Out str_concat_type)

return number

is

begin

self.cat_string := self.cat_string || ‘,‘ || ctx2.cat_string;

return ODCIConst.Success;

end;

end;

/

CREATE OR REPLACE FUNCTION str_concat(input varchar2) RETURN varchar2

PARALLEL_ENABLE

AGGREGATE USING str_concat_type;

/

实现的功能和wm_concat一样。

原文：https://www.cnblogs.com/ayumie/p/10002486.html