Oracle Custom Aggregation Functions

Today, I saw someone write a custom aggregation function, so mindedly want to understand, on the Internet to find information. Introduction to custom aggregate function custom aggregation function interfaces

Oracle provides a number of pre-defined aggregation functions, such as Max (), Sum (), AVG (), but these predefined aggregate functions are mostly adapted to scalar data, for complex data types, such as user-defined object type, CLOB, etc., is not supported.

Fortunately, however, the user can create a custom aggregate function by implementing Odciaggregate interface in the Oracle's extensibility framework, and the custom aggregation function does not differ from the built-in aggregation function usage.

Create a custom aggregation function by implementing Odciaggregate Rountines. You can implement these interface functions in any of the Oracle-supported languages by defining an object type, and then implementing the Odciaggregate interface function (routines) within that type, such as the C/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 aggregation function can be created by creating a function statement.

Each custom aggregation function needs to implement 4 Odciaggregate interface functions that define what needs to be done within any of the aggregation 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 aggregation 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

The most important step of the custom aggregation function, this function defines what our aggregate function does, the following example is the maximum, minimum, average, or connection operation. Self is a pointer to the current aggregate function and is used to correlate with the previous calculation result

This function iterates through the data that needs to be processed and is called repeatedly by Oracle. At each invocation, the current aggreation context and the new (set) value are used as incoming parameters. This function processes the incoming values and returns the updated aggregation context. This function will be executed once for each non-null value. Null values are not passed to a clustered function.

C. member function Odciaggregatemerge (self in String_agg_type,returnvalue out varchar2,flags in number) return number

The result of two different pointers used to merge two aggregate functions, the user merges data from different result nodes, especially when processing parallel (parallel) query aggregation functions.

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

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

Terminates the processing of the aggregation function, returning the result of the aggregation function processing.

This function is the last function of the Oracle call. It receives aggregation context as a parameter, returning the last aggregate value.

Application Scenario One: String aggregation

CREATE OR REPLACE TYPE Typ_concatenate_impl as OBJECT ( Retstr VARCHAR2 (30000),--piecing together the middle string used Separatorflag VARCHAR2 (64),--delimiter, default with free definition |, can be modified here STATIC FUNCTION odciaggregateinitialize (sctx in Out Typ_concatenate_impl) RETURN number, MEMBER FUNCTION odciaggregateiterate (self in Out Typ_concatenate_impl, value in VARCHAR2) RETURN number, MEMBER FUNCTION odciaggregateterminate (self in Typ_concatenate_impl, returnvalue out VARCHAR2, flags in number) RETURN NUM BER, 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 --Custom aggregation 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 functions and 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, returning the result of the aggregation function processing MEMBER FUNCTION odciaggregateterminate (self in Typ_concatenate_impl, returnvalue out VARCHAR2, FLAGS in number) RETURN number is BEGIN IF ReturnValue is isn't 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 used to merge two aggregate functions, which is a default 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 custom function CREATE OR REPLACE FUNCTION f_concatenate_str (i_str VARCHAR2) RETURN VARCHAR2 AGGREGATE USING Typ_concatenate_impl; /

Create test tables and data, and test them

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;

Review the results of the execution and compare it with the Wmsys.wm_concat function execution effect.

sql> SELECT id,f_concatenate_str (name) name from the test GROUP by ID; ID NAME ---------- ------------------------------------------------------------------ 1 AAA,ABC, 2 bbb,ddd, 3 CCC, sql> SELECT id,wmsys.wm_concat (name) name from the 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 test; ID NAME ---------- ------------------------------------------------------------------ 1 AAA,ABC 1 AAA,ABC 2 DDD,BBB 2 DDD,BBB 3 CCC

In fact, an oracle10g function Wmsys.wm_concat () is provided in the version of the file, and the aggregation of strings can be implemented, which is similar to the two functions.

This also means that the aggregation functions provided by Oracle are strong enough to invent a non-repetitive wheel.

Oracle Custom Aggregation Functions