Create a user-defined aggregate function

To create a user-defined aggregate function, you must implement the ODCIAggregate interface and define the user-defined aggregate function.

This topic uses the SecMax() function as an example to describe how to create a user-defined aggregate function. The SecMax() function returns the second largest value in a set of numbers.

1. Implement the ODCIAggregate interface

   The ODCIAggregate routine is implemented as a method of the SecMaxImpl object type.

   /*Implement the SecMaxImpl type to contain the ODCIAggregate routine.*/
   CREATE OR REPLACE TYPE SecMaxImpl AS OBJECT
   (
     max NUMBER,   -- Current maximum value
     secmax NUMBER, -- Current second maximum value
     STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT SecMaxImpl) RETURN NUMBER,
     MEMBER FUNCTION ODCIAggregateIterate(self IN OUT SecMaxImpl, value IN number) RETURN NUMBER,
     MEMBER FUNCTION ODCIAggregateTerminate(self IN SecMaxImpl, returnValue OUT number, flags IN number) RETURN NUMBER,
     MEMBER FUNCTION ODCIAggregateMerge(self IN OUT SecMaxImpl, ctx2 IN SecMaxImpl) RETURN NUMBER
   );
   /
   
   /*Implement the body of the SecMaxImpl type.*/
   CREATE OR REPLACE TYPE BODY SecMaxImpl IS
   STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT SecMaxImpl)
   RETURN NUMBER IS
   BEGIN
     sctx := SecMaxImpl(0, 0);
     RETURN ODCIConst.Success;
   END;
   
   MEMBER FUNCTION ODCIAggregateIterate(self IN OUT SecMaxImpl, value IN number) RETURN NUMBER IS
   BEGIN
     IF value > self.max THEN
       self.secmax := self.max;
       self.max := value;
     ELSIF value > self.secmax THEN
       self.secmax := value;
     END IF;
     RETURN ODCIConst.Success;
   END;
   
   MEMBER FUNCTION ODCIAggregateTerminate(self IN SecMaxImpl,
       returnValue OUT number, flags IN number) RETURN NUMBER IS
   
   BEGIN
     returnValue := self.secmax;
     RETURN ODCIConst.Success;
   end;
   
   MEMBER FUNCTION ODCIAggregateMerge(self IN OUT SecMaxImpl, ctx2 IN SecMaxImpl) RETURN NUMBER IS
   BEGIN
     IF ctx2.max > self.max THEN
       IF ctx2.secmax > self.secmax THEN
         self.secmax := ctx2.secmax;
       ELSE
         self.secmax := self.max;
       END IF;
       self.max := ctx2.max;
     ELSIF ctx2.max > self.secmax THEN
       self.secmax := ctx2.max;
     END IF;
     RETURN ODCIConst.Success;
   END;
   END;
   /
   

2. Define the user-defined aggregate function

   You can define the SecMax() aggregate function by specifying its signature and the object type that implements the ODCIAggregate interface:

   CREATE FUNCTION SecMax (input NUMBER) RETURN NUMBER 
   PARALLEL_ENABLE AGGREGATE USING SecMaxImpl;/