Oracle analysis Functions (7) Custom aggregate functions

Oracle provides us with very rich aggregate functions, such as Sum\avg\max. In addition, we can write our own aggregate functions, of course, custom aggregate functions can also be used as analytic functions.

Custom aggregate functions are not much different from Oracle's built-in aggregate functions, and they can be written in any Oracle-supported language, such as Pl/sql\c\c++\java. In this article, we try to compile our own aggregate functions with plsql as the development language.

The custom aggregate functions that are supported by Oracle are naturally written according to the rules specified by Oracle, which we might call the Odciaggregate rule. Let's take a look at the process of compiling a custom aggregate function by writing an aggregate function that gets the second largest value in the group.

1: Create an object TYPE first

 CREATE or REPLACE type Secmax_context as Object (Firmax number, which saves the maximum value, which, depending on the aggregate function operation, has the user set up Secmax NUM BER,--Saves the second largest value, which, depending on the operation of the aggregate function, has the user's own set--(the step is necessary) the initialization function, the method that must be implemented to initialize the context static function at the very beginning of the aggregation operation Odciaggregateinitialize (Sctx in Out Secmax_context) return number--(this step is necessary) an iterative operational function that Oracle iterates through the function, the first parameter Is the context of the aggregation operation--the second parameter is the value that is currently being processed, can be a type such as number VARCHAR2--The iteration member FUNCTION is ignored during an iteration if the current value is null Odciaggregateit Erate (self in Out secmax_context,value in number)--(This step is required, but Oracle has a choice to perform the step during execution) the function is used to Merging two contexts into one context is likely to play a role in both parallel and serial environments member function odciaggregatemerge (self in Out secmax_context, ctx2 in Secmax_context  
  Return number,--(this step is required) The function runs in the last step of the aggregation operation to process the result and returns the processing result--the first argument is the context, the second parameter is the return value, and can be a type such as Number,varchar2 --The third parameter is the identity bit member FUNCTION odciaggregateterminate (self into secmax_context,returnvalue out number,flags in number) return number); 
 

2: Implementing this object TYPE

 Create or replace type body Secmax_context are static function Odciaggregateinitialize (Sctx in Out Secmax_cont  
  EXT) return number is begin SCTX: = Secmax_context (0, 0);  
return odciconst.success;  
      
End member function odciaggregateiterate (self into out secmax_context, value in number) return # is BEGIN if value > S  
    Elf.firmax then Self.secmax: = Self.firmax;  
  Self.firmax: = value;  
  elsif value > Self.secmax then Self.secmax: = value;  
  End If;  
return odciconst.success;  
      
End member function odciaggregateterminate (self into secmax_context, returnvalue out number, flags in number) return number is B  
  Egin returnvalue: = Self.secmax;  
return odciconst.success;  
      
End member function Odciaggregatemerge (self in Out secmax_context, ctx2 in Secmax_context) return number is begin if ctx2.fi  
          Rmax > Self.firmax Then if Ctx2.secmax > Self.firmax then Self.secmax: = Ctx2.secmax; Else SELf.secmax: = Self.firmax;  
    End If;  
  Self.firmax: = Ctx2.firmax;  
  elsif ctx2.firmax > Self.secmax then Self.secmax: = Ctx2.firmax;  
  End If;  
return odciconst.success;  
End End;