The Oracle (tm) Users' Co-Operative FAQ
How do you write an aggregate function that can be used in SQL?
|
Author's name: Keith_Jamieson Author's Email: Keih_Jamieson@hotmail.com |
Date written: 22 Sep 2003 Oracle version(s): 9.2.0.1.0 |
|
If you work in a Financial, Statistical, Mathematical or Scientific
Industry, you may have a requirement that the built-in Aggregate functions do
not support. Oracle now gives you the ability to write functions that operate
on an entire dataset. |
In order to write your own Aggregate function, you need to perform 3 essential steps.
Create an Object Type.
Create a Type Body
Create a Function
When you create the object type you need to define 1 Static (ODCIAggregateInitialize) and 3 Member (ODCIAggregateIterate, ODCIAggregateMerge, ODCIAggregateTerminate) Functions.
The structure of the object type is as follows:
CREATE TYPE function_name (
STATIC FUNCTION ODCIAggregateInitialize( ... ) ...,
MEMBER FUNCTION ODCIAggregateIterate(...) ... ,
MEMBER FUNCTION ODCIAggregateMerge(...) ...,
MEMBER FUNCTION ODCIAggregateTerminate(...) );
The
definitions of these functions according to the manual are as follows:
ODCIAggregateInitialize
“This routine is invoked by Oracle to initialize the computation of the user-defined aggregate. The initialized aggregation context is passed back to Oracle as an object type instance”.
ODCIAggregateIterate
“This routine is repeatedly invoked by Oracle. On each invocation, a new value (or a set of new values) is passed as input. The current aggregation context is also passed in. The routine processes the new value(s) and returns the updated aggregation context back to Oracle. This routine is invoked for every non-NULL value in the underlying group. (NULL values are ignored during aggregation and are not passed to the routine.)”
ODCIAggregateMerge
“This routine is invoked by Oracle to combine two aggregation contexts. This routine takes the two contexts as inputs, combines them, and returns a single aggregation context”.
ODCIAggregateTerminate
“This routine is invoked by Oracle as the final step of aggregation. The routine takes the aggregation context as input and returns the resulting aggregate value”.
I have decided to write an Aggregate Function MyAverage which will mimic the Oracle Function. The main reason for doing this is that it will give a simple example of how aggregate functions can be written and it can be compared to the oracle supplied avg function. When we create the type, we are creating our equivalent of the package header. Any “Function Prototype” declared in the type must also be declared in the type Body.
create or replace type MyAverageImpl as object
(
total NUMBER, -- total of all numbers
iteration NUMBER, -- total of all iterations
static function ODCIAggregateInitialize(sctx IN OUT MyAverageImpl)
return number,
member function ODCIAggregateIterate(self IN OUT MyAverageImpl,
value IN number) return number,
member function ODCIAggregateTerminate(self IN MyAverageImpl,
returnValue OUT number, flags IN number) return number
member function ODCIAggregateMerge(self IN OUT MyAverageImpl,
ctx2 IN MyAverageImpl) return number
);
The name of our user-defined aggregate object type is MyAverageImpl. The variables total and iteration will be used in the type body to perform the computation. From what I can determine the ODCIAggregateMerge is necessary, even if it just returns ODCIConst.Success as are the parameters.
create or replace type body MyAverageImpl is
static function ODCIAggregateInitialize(sctx IN OUT MyAverageImpl)
return number is
begin
sctx := MyAverageImpl(0, 0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT MyAverageImpl, value IN number)
return number is
begin
self.total := self.total + value;
self.iteration := self.iteration + 1;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN MyAverageImpl, returnValue OUT
number, flags IN number) return number is
begin
returnValue := self.total/self.iteration;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT MyAverageImpl, ctx2 IN
MyAverageImpl) return number is
begin
return ODCIConst.Success;
end;
end;
ODCIAggregateInitialize is where we initialize any variables which we have declared in the TYPE. In the above example we have initialised both the total and the iteration variable to 0.
ODCIAggregateIterate is where all the work is done. Each time the function is called, we add the value passed in to the total, and we increment the number of iterations by 1.
We do not need to use ODCIAggregateMerge in this example, so we merely return Success.
Finally, we return the result of our calculation in an output parameter, returnvalue.
So we have now created our TYPE and our TYPE Body. It only remains for us to create and test our function.
CREATE OR REPLACE FUNCTION MyAverage (input NUMBER) RETURN NUMBER
AGGREGATE USING MyAverageImpl;
You can add the Key Word PARALLEL_ENABLE immediately before AGGREGATE in order to make use of Parallel Slaves. Also note that there is no end for this function.
Using the Scott Schema we can see that SELECT MyAverage(Sal) from emp returns the same result as using SELECT AVG(Sal) from emp.
MYAVERAGE(SAL)
--------------
2073.21429
Further reading: Oracle9i Data Cartridge Developer's Guide Release 2 (9.2) (Part Number A96595-01)
