Oracle custom Aggregate functions

-- create a function type
Create type strcat_type as object (
cat_string varchar2 (4000),
-- set the initialization of a UDF, start a clustering function
static function odciaggregateinitialize (cs_ctx in out strcat_type) return number,
-- custom clustering function, the main step, this function defines what the aggregate function performs. In the following example, the maximum value, minimum value, average value, or connection operation is performed. self is the pointer to the current aggregate function, used to associate with the previous calculation results
member function odciaggregateiterate (self in out strcat_type, value in varchar2) return number,
-- used to merge the results of two different pointers of two Aggregate functions. You can merge the data of different results, especially when processing parallel (parallel) queries of Aggregate functions.
member function odciaggregatemerge (self in out strcat_type, ctx2 in out strcat_type) return number,
-- terminate the processing of clustering functions, returns the result of processing the aggregate function.
member function odciaggresponterminate (self in out strcat_type, returnvalue out varchar2, flags in number) return number
)
--/*
-- function subject

*/
Create or replace type body strcat_type is
Static function odciaggresponinitialize (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;
-- 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 odciaggresponterminate (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;

/*
Function call

*/

Create or replace
Function strcat (input varchar2)
Return varchar2
Parallel_enable aggregate using strcat_type;