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.  

Back to index of questions


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)


Back to top

Back to index of questions