stract oracle,ORACLE 字符串聚合函数 strcat

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

);

/*------------------------------------------------------------------------------------------------------------------------------------------------*/

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;

/*------------------------------------------------------------------------------------------------------------------------------------------------*/

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