Oracle Custom Aggregate functions

Introduction to custom aggregate function interfaces

Oracle provides a number of predefined aggregate functions, such as Max (), Sum (), AVG (), but these predefined aggregate functions are basically adapted to scalar data (scalar), for complex data types, such as user-defined object type, Clob and so on, is not supported.

Fortunately, however, users can create custom aggregate functions by implementing the Odciaggregate interface in the Oracle Extensibility Framework, and there is no difference between the custom aggregate functions and the built-in aggregate function usage.

Create a custom aggregate function by implementing Odciaggregate Rountines. You can implement these interface functions in any Oracle-supported language, by defining an object type and then implementing the Odciaggregate interface function (routines) within that type, such as C + +, JAVA, pl/ SQL, and so on. After the object type is defined, the corresponding interface function is also implemented inside the object type body, and a custom aggregate function can be created by creating a functions statement.

Each custom aggregate function needs to implement 4 Odciaggregate interface functions that define the operations that need to be implemented within any one of the aggregate functions, namely initialization, iteration, merging, and termination.

A. Static function Odciaggregateinitialize (Sctx in Outstring_agg_type) return number

Custom aggregate function initialization operation, starting from here a clustered function. The initialized clustered environment (aggregation context) is passed back to Oracle as an object instance (type instance).

B. member function odciaggregateiterate (self in Out string_agg_type, value in VARCHAR2) return number

Custom aggregate function, the most important step, this function defines what our aggregate function does, and the following example is the maximum, the minimum, the average, or the connection operation. Self is a pointer to the current aggregate function that is used to correlate the previous calculation results

This function is used to traverse the data that needs to be processed and repeated by Oracle. Each time it is invoked, the current aggreation context and the new (set) values are passed in as parameters. This function processes these incoming values and returns the updated aggregation context. This function will be executed once for each non-null value. Null values are not passed a clustered function.

C. member function odciaggregatemerge (self into string_agg_type,returnvalue out varchar2,flags in number) return number

The results of two different pointers for merging two aggregate functions, the user merges data from different result knots, especially when processing parallel (parallel) query aggregation functions.

This function is used to integrate two aggregation context, which is commonly used in parallel computations (when a function is set to enable parallel processing).

D. member function ocdiaggregateterminate (self into string_agg_type,returnvalue out varchar2,flags in number)

Terminates the processing of the aggregate function and returns the result of the aggregate function processing.

This function is the last function of an Oracle call. It receives the aggregation context as a parameter and returns the last aggregate value.

Application Scenario One: String aggregation

CREATE OR REPLACE TYPE Typ_concatenate_impl as OBJECT (Retstr VARCHAR2 (30000)--a patchwork of intermediate string SEP Aratorflag VARCHAR2 (64),--delimiter, default with free definition |, you can modify this STATIC FUNCTION odciaggregateinitialize (sctx in Out typ_concatenate _impl) return number, member FUNCTION odciaggregateiterate (self in Out Typ_concatenate_impl, value in VARCHAR2) RE TURN number, member FUNCTION odciaggregateterminate (self into Typ_concatenate_impl, returnvalue out VARCHAR2, flags In number, the member FUNCTION Odciaggregatemerge (self in Out Typ_concatenate_impl, ctx2 in Typ_concate
    
   Nate_impl)/CREATE OR REPLACE TYPE Body Typ_concatenate_impl--Custom aggregate function initialization operation
    
       STATIC FUNCTION odciaggregateinitialize (sctx in Out Typ_concatenate_impl) return number is BEGIN
    
       Sctx: = Typ_concatenate_impl (', ', ', '); Return Odciconst.
    
   SUCCESS;
    
   End; --Define function, implement string concatenation member function OdciaggRegateiterate (self in Out Typ_concatenate_impl, value in VARCHAR2) return number is BEGIN self.retstr : = Self.retstr | | value| | Self.
    
       Separatorflag; Return Odciconst.
    
   SUCCESS;
    
   End; --Defines the processing of the terminating aggregate function, returns the result of the aggregate function processing member function odciaggregateterminate (self-typ_concatenate_impl, returnvalue out VAR
    
           CHAR2, FLAGS in number) return # is BEGIN IF returnvalue be not NULL THEN
    
       ReturnValue: = SUBSTR (Self.retstr,1,length (SELF.RETSTR)-1);
    
       ELSE returnvalue: = Self.retstr;
    
       End IF; Return Odciconst.
    
   SUCCESS;
    
   End; --the result of two different pointers that are used to merge two aggregate functions, where the member FUNCTION Odciaggregatemerge is the default (self in Out Typ_concatenate_impl, ctx2 in Typ_ CONCATENATE_IMPL) return number are BEGIN return odciconst.
    
   SUCCESS;
    
End;
    
End; /--Create a custom function created OR REPLACE function f_concatenate_str (i_str VARCHAR2) return VARCHAR2 AGGREGATE USING Typ_concatenate_impl; /