Create user-defined aggregate functions

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

This topic takes SecMax() as an example to show how you can create a user-defined aggregation function. SecMax() returns the second largest value in a set of numbers.

1. Implement the ODCIAggregate interface.

   ODCIAggregate routines are implemented as methods in an object type SecMaxImpl.

   /* Implement the type SecMaxImpl to contain ODCIAggregate routines. */
   CREATE OR REPLACE TYPE SecMaxImpl AS OBJECT
   (
     max NUMBER,   -- largest value
     secmax NUMBER, -- second largest 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 type SecMaxImpl. */
   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 aggregate function.

   Create 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;/