Oracle custom Aggregate functions

Custom Aggregate functions
Introduction to custom aggregate function Interfaces
Oracle provides many predefined Aggregate functions, such as Max (), Sum (), AVG (), however, these predefined Aggregate functions are basically applicable to scalar data. For complex data types, such as custom Object type and Clob, is not supported.

Fortunately, you can create a user-defined aggregate function by implementing the ODCIAggregate interface in Oracle's Extensibility Framework. The usage of the user-defined aggregate function is no different from that of the built-in aggregate function.

Create a custom aggregate function by implementing ODCIAggregate rountines. You can define an Object Type and implement the ODCIAggregate interface function (routines) within this Type. These interface functions can be implemented in any language supported by Oracle, for example, C/C ++, JAVA, PL/SQL. After the Object Type is defined, the corresponding interface functions are implemented within the Object Type Body. Then, you can use the create function statement to CREATE a custom aggregate FUNCTION.

Each custom aggregate function needs to implement four ODCIAggregate interface functions, which define the operations to be implemented in any aggregate function, including initialization, iteration, merging, and termination.

A. static function odciaggresponinitialize (sctx IN OUTstring_agg_type) return number

The initialization operation of the User-Defined aggregation function starts from here. The initialized aggregation context is sent back to oracle as an object type instance.

B. member function ODCIAggregateIterate (self IN OUT string_agg_type, value IN varchar2) return number

The main step for customizing a clustering function is to define the specific operations of our clustering function. The following example shows whether to take the maximum value, minimum value, average value, or perform a connection operation. self is the pointer to the current aggregate function, used to associate with the previous calculation results

This function is used to traverse the data to be processed and is repeatedly called by oracle. Each call, the current aggreation context and new (Group) values are used as input parameters. This function will process these incoming values and then return the updated aggregation context. This function will be executed once for each NON-NULL value. NULL values are not passed into Aggregate functions.

C. member function ODCIAggregateMerge (self IN string_agg_type, returnValue OUT varchar2, flags IN number) return number

It is used to merge the results of two different pointers of two Aggregate functions. You can combine the data of different results, especially when processing parallel (parallel) queries of Aggregate functions.

This function is used to integrate two aggregation contexts and is generally used for parallel Computing (when a function is set to enable parallel for processing ).

D. member function ocdiaggresponterminate (self IN string_agg_type, returnValue OUT varchar2, flags IN number)

Terminate the processing of the aggregate function and return the processing result of the aggregate function.

This function is the last function called by Oracle. It receives the aggregation context as the parameter and returns the final aggregate value.

Application Scenario 1: String Aggregation
Create or replace type typ_concatenate_impl AS OBJECT

(

Retstr VARCHAR2 (30000), -- Concatenates the intermediate string used

SEPARATORFLAG VARCHAR2 (64), -- delimiter, which is defined freely by default |, which can be modified here

Static function odciaggresponinitialize (sctx in out typ_concatenate_impl) return number,

Member function odciaggregateiterate (self in out typ_concatenate_impl, value IN VARCHAR2) return number,

Member function odciaggresponterminate (self IN typ_concatenate_impl, returnvalue OUT VARCHAR2, flags in number) return number,

Member function odciaggregatemerge (self in out typ_concatenate_impl, ctx2 IN typ_concatenate_impl) RETURN NUMBER

)

/

Create or replace type body typ_concatenate_impl IS

-- Initialization of custom Aggregate functions

Static function odciaggresponinitialize (sctx in out typ_concatenate_impl) RETURN NUMBER IS

BEGIN

Sctx: = typ_concatenate_impl (,,);

Return odciconst. SUCCESS;

END;

-- Define the function to concatenate strings

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;

-- Define to terminate the processing of the aggregate function and return the processing result of the aggregate function.

Member function odciaggresponterminate (self IN typ_concatenate_impl, returnvalue OUT VARCHAR2, flags in number)

RETURN NUMBER IS

BEGIN

IF returnvalue IS NOT NULL THEN

Returnvalue: = SUBSTR (self. retstr, 1, LENGTH (self. retstr)-1 );

ELSE

Returnvalue: = self. retstr;

End if;

Return odciconst. SUCCESS;

END;

-- Used to merge the results of two different pointers of two Aggregate functions. The default value is here.

Member function odciaggregatemerge (self in out typ_concatenate_impl, ctx2 IN typ_concatenate_impl) RETURN NUMBER IS

BEGIN

Return odciconst. SUCCESS;

END;

END;

/

-- Create a UDF

Create or replace function f_concatenate_str (I _str VARCHAR2) RETURN VARCHAR2

Aggregate using typ_concatenate_impl;

/
 

Create and test tables and data

Create table test (id number, NAME VARCHAR2 (20 ));

Insert into test values (1, AAA );

Insert into test values (2, BBB );

Insert into test values (1, ABC );

Insert into test values (3, CCC );

Insert into test values (2, DDD );

COMMIT;
 

View the execution result and compare it with the execution result of the WMSYS. WM_CONCAT function.

SQL> SELECT id, f_concatenate_str (name) name FROM test GROUP BY id;

ID NAME

----------------------------------------------------------------------------

1 AAA, ABC,

2 BBB, DDD,

3 CCC,

SQL> SELECT id, wmsys. wm_concat (name) name FROM test GROUP BY id;

ID NAME

----------------------------------------------------------------------------

1 AAA, ABC

2 BBB, DDD

3 CCC

SQL> SELECT id, f_concatenate_str (name) OVER (PARTITION BY id) name FROM test;

ID NAME

----------------------------------------------------------------------------

1 AAA, ABC,

1 AAA, ABC,

2 DDD, BBB,

2 DDD, BBB,

3 CCC,

SQL> SELECT id, wmsys. wm_concat (name) OVER (PARTITION BY id) name FROM te