ORACLE分析函数(7)----自定义聚合函数

　　oracle为我们提供了非常丰富的聚合函数，如SUM\AVG\MAX等。除此之外，我们还可以编写自己的聚合函数，当然，自定义聚合函数也可以作为分析函数来使用。
　　自定义聚合函数与oracle内置聚合函数并无太大区别，而且它可以使用任何oracle支持的语言来编写，如PL/SQL\C\C++\JAVA。在本文中，我们以PLSQL为开发语言，尝试编制我们自己的聚合函数。
　　编制ORACLE支持的自定义聚合函数，自然要依据oracle指定的规则来编写，我们不妨称之为ODCIAggregate规则。下面我们以编写获取组中第二大值的的聚合函数为例，看一下自定义聚合函数的编制过程。
　　1：首先创建一个OBJECT TYPE
　　

CREATE or REPLACE type secmax_context AS object(
firmax NUMBER, -- 保存最大值，这部分内容根据聚合函数操作的不同，有用户自行设置
secmax NUMBER, -- 保存第二大值，这部分内容根据聚合函数操作的不同，有用户自行设置
--（该步骤是必须的）初始化函数，必须要实现的方法，用于在聚合运算的最开始部分，初始化上下文环境
static FUNCTION ODCIAggregateInitialize(sctx IN OUT secmax_context) RETURN NUMBER,
--（该步骤是必须的）迭代运算函数，oracle依据该函数进行迭代运算，第一个参数为聚合运算的上下文，
--第二个参数为当前需要处理的值,可以为number varchar2等类型，
--在迭代过程中，如果当前值为null，则忽略该次迭代
member FUNCTION ODCIAggregateIterate(self  IN OUT secmax_context,value IN NUMBER) RETURN NUMBER,
--（该步骤是必须的，但是在执行过程中，oracle会有选择的执行该步骤）该函数用于合并两个上下文到一个上下文中，在并行和串行环境下均有可能发挥作用
member FUNCTION ODCIAggregateMerge(self IN OUT secmax_context, ctx2 IN secmax_context)RETURN NUMBER,
--（该步骤是必须的）该函数在聚合运算的最后一步运行，用于对结果进行处理并返回处理结果，
--第一个参数为上下文，第二个参数为返回值，可以为number，varchar2等类型
--第三个参数为标识位
member FUNCTION ODCIAggregateTerminate(self IN secmax_context,returnValue OUT NUMBER,flags IN NUMBER) RETURN NUMBER
);
　　
　　2：实现该OBJECT TYPE
　　

create or replace type body secmax_context is
static function ODCIAggregateInitialize(sctx IN OUT secmax_context) return number is
begin
sctx := secmax_context(0, 0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT secmax_context, value IN number) return number is
begin
if value > self.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 IN secmax_context, returnValue OUT number, flags IN number) return number is
begin
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.firmax > 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;
　　
　　3：创建聚合函数
　　

CREATE FUNCTION SecMax (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING secmax_context;
　　
　　测试结果：
　　

SQL> select secmax(rownum) from costs;
SECMAX(ROWNUM)
--------------
82111
SQL> select max(rownum) from costs;
MAX(ROWNUM)
-----------
82112

　　
　　并行执行聚合函数：
　　与内置函数一样，自定义聚合函数也可以并行执行。并指执行时，首先是各个slave执行聚合运算，然后将结果进行合并，合并是通过ODCIAggregateMerge实现的。下图展示了这个过程：

http://img.blog.csdn.net/20131018131106750?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWlkaWFuODE1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast

   注意：我们可以将自定义聚合函数作为分析函数使用，但是为了提高分析函数的执行效率，我们可以对自定义聚合函数做一些处理，详细内容可以参考《Data Cartridge Developer's Guide 》