Oracle-defined aggregate Functions

-- Create Function Type create type strcat_type as object (cat_string varchar2 (4000), -- set the initialization of the custom aggregate function. From here, a Aggregate Function static function odciaggresponinitialize (cs_ctx in out strcat_type) is created) return number, -- user-defined aggregate function, the main step. This function defines the specific operations of our aggregate function. In the following example, we take the maximum value, minimum value, and average value, or do the connection operation. self is the pointer to the current aggregate function, which is used to associate with the previous calculation results. member function odciaggregateiterate (self in out strcat_type, value in varchar2) return number, -- It is used to merge the results corresponding to two different pointers of two Aggregate functions. You can merge the data with 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 the aggregate function, and return the result of the processing of the aggregate function, 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;/* function call */create or replace function strcat (input varchar2) return varchar2parallel_enable aggregate using strcat_type;