Oracle自定义聚合函数-分析函数.docxVIP

自定义聚合函数，分析函数--from GTA Aaron最近做一数据项目要用到连乘的功能，而Oracle数据库里没有这样的预定义聚合函数，当然利用数据库已有的函数进行数学运算也可以达到这个功能，如：selectexp(sum(ln(field_name))) from table_name;不过今天的重点不是讲这个数学公式，而是如何自己创建聚合函数，实现自己想要的功能。很幸运Oracle 允许用户自定义聚合函数，提供了相关接口，LZ研究了下，留贴共享。首先介绍聚合函数接口：用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregate interface来创建自定义聚合函数，而且自定义的聚合函数跟内建的聚合函数用法上没有差别。通过实现 ODCIAggregaterountines来创建自定义的聚合函数。可以通过定义一个对象类型（Object Type），然后在这个类型内部实现ODCIAggregate 接口函数(routines)，可以用任何一种Oracle支持的语言来实现这些接口函数，比如C/C++, JAVA, PL/SQL等。在这个Object Type定义之后，相应的接口函数也都在该Object Type Body内部实现之后，就可以通过CREATE FUNCTION语句来创建自定义的聚合函数了。每个自定义的聚合函数需要实现4个ODCIAggregate 接口函数， 这些函数定义了任何一个聚合函数内部需要实现的操作：1. 自定义聚合函数初始化操作,从这儿开始一个聚合函数。初始化的聚合环境(aggregation context)会以对象实例(object type instance)传回给oracle.static function ODCIAggregateInitialize(varIN OUTagg_type ) return number 2. 自定义聚合函数,最主要的步骤,这个函数定义我们的聚合函数具体做什么操作，self 为当前聚合函数的指针,用来与前面的计算结果进行关联。这个函数用来遍历需要处理的数据，被oracle重复调用。每次调用的时候，当前的aggreation context 和 新的（一组）值会作为传入参数。 这个函数会处理这些传入值，然后返回更新后的aggregation context. 这个函数对每一个NON-NULL的值都会被执行一次。NULL值不会被传递个聚合函数。member function ODCIAggregateIterate(self IN OUT agg_type ,value IN varchar2) return number 3. 用来合并两个聚合函数的两个不同的指针对应的结果,用户合并不同结果结的数据,特别是处理并行(parallel)查询聚合函数的时候. 这个函数用来把两个aggregation context整合在一起，一般用来并行计算中（当一个函数被设置成enable parallel 处理的时候）。member functionODCIAggregateMerge(self IN OUTagg_type, value IN agg_type) return number4.终止聚合函数的处理,返回聚合函数处理的结果. 这个函数是Oracle调用的最后一个函数。它接收aggregation context作为参数，返回最后的aggregate value.member function OCDIAggregateTerminate(self IN agg_type,returnValue OUT varchar2,flags IN number) 下一步我们就根据这些接口来创建连乘聚合函数， 首先创建OBJECT TYPE:CREATE OR REPLACE TYPE sermult_context AS OBJECT(multvalue NUMBER, -- 保存连乘后的值 --（该步骤是必须的）初始化函数，必须要实现的方法，用于在聚合运算的最开始部分，初始化上下文环境 static FUNCTION ODCIAggregateInitialize(serm IN OUT sermult_context) RETURN NUMBER, --（该步骤是必须的）迭代运算函数，oracle依据该函数进行迭代运算，第一个参数为聚合运算的上下文， --第二个参数为当前需要处理的值,可以为number varchar2等类型， --在迭代过程中，如果当前值为null，则忽略该次迭代 member FUNCTION ODCIAggregateIterate(sel