User-DefinedAggregateFunctions in ORACLE ),

User-DefinedAggregateFunctions in ORACLE ),

An aggregate function is also called a grouping function. It is a function used to calculate multiple rows, such as SUM, COUNT, AVG, MAX, and MIN.

To enhance scalability, Oracle provides an interface for customizing Aggregate functions, ODCIAggregate (), you can define an aggregate function using various programming languages (such as PLSQL, C, C ++, and JAVA) supported by Oracle, and use the same syntax in the program.

In practical applications, there are very few scenarios where user-defined aggregate functions are used. The reason is:

1. PLSQL is rich enough to easily implement most aggregation scenarios;

2. User-defined aggregate functions are less efficient than directly implementing PLSQL.

For example, there is a post on ITPUB that calculates the weighted median. The answer master gives a comparison between the two solutions. In fact, the custom aggregate function is only a packaging of PLSQL implementation. For details, refer to the original article?

Https://www.itpub.net/forum.php? Mod = viewthread & tid = 1377770.

So,What is the use of custom Aggregate functions? I personally think there is only one need: encapsulation needs, sometimes to improve code readability, and sometimes confidentiality based on technical details.

Next we will record a user-defined aggregation case in the Oracle official document. In this case, the function SecondMax () is the second largest number, which can be solved in minutes using the analysis function, i'm afraid Oracle cannot compile an appropriate scenario that requires user-defined functions.

 

Define an object type that contains the ODCIAggregate routine SecondMaxImpl:

create type SecondMaxImpl as object(  max NUMBER,          -- highest value seen so far   secmax NUMBER,       -- second highest value seen so far  static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl)     return number,  member function ODCIAggregateIterate(self IN OUT SecondMaxImpl,     value IN number) return number,  member function ODCIAggregateTerminate(self IN SecondMaxImpl,     returnValue OUT number, flags IN number) return number,  member function ODCIAggregateMerge(self IN OUT SecondMaxImpl,     ctx2 IN SecondMaxImpl) return number);/

SecondMaxImpl type implementation:

create or replace type body SecondMaxImpl is static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) return number is begin  sctx := SecondMaxImpl(0, 0);  return ODCIConst.Success;end;member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, value IN number) return number isbegin  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 SecondMaxImpl,     returnValue OUT number, flags IN number) return number isbegin  returnValue := self.secmax;  return ODCIConst.Success;end;member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, ctx2 IN SecondMaxImpl) return number isbegin  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;/

Create a user-defined aggregate function SecondMax:

CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;

Apply custom Aggregate functions in SQL statements:

SELECT SecondMax(salary), department_id???FROM MyEmployees???GROUP BY department_id???HAVING SecondMax(salary) > 9000;