自定义Oracle聚集函数：模拟SUM,MAX,AVG

实现三个函数，关键都在于处理ODCIAggregateIterate()，即下面几张图中的高亮部分。Oracle自定义聚集函数的功能很强大，笔者暂时只钻研到这一层。

f_sum()，模拟SUM()函数：

定义TYPE：

create or replace type f_sum_type2 as object (   num number,   static function ODCIAggregateInitialize(sctx In Out f_sum_type2) return number,   member function ODCIAggregateIterate(self In Out f_sum_type2, value In number) return number,   member function ODCIAggregateMerge(self In Out f_sum_type2, ctx2 In f_sum_type2) return number,   member function ODCIAggregateTerminate(self In Out f_sum_type2, returnValue Out number, flags In number) return number )

 定义TYPE BODY：

create or replace type body f_sum_type2 is static function ODCIAggregateInitialize (sctx In Out f_sum_type2) return number is        begin             sctx := f_sum_type2(0);             return ODCIConst.Success;        end;      member function ODCIAggregateIterate (self In Out f_sum_type2, value In number) return number is        begin             self.num := self.num + value;             return ODCIConst.Success;        end;        member function ODCIAggregateMerge(self In Out f_sum_type2, ctx2 In f_sum_type2) return number is        BEGIN            self.num := self.num;             return ODCIconst.Success;        end;        member function ODCIAggregateTerminate(self In Out f_sum_type2, returnValue Out number, flags In number) return number is        begin             returnValue := self.num;             return ODCIConst.Success;        end; end;

 定义函数：

CREATE OR REPLACE FUNCTION f_sum2(input number ) RETURN number PARALLEL_ENABLE AGGREGATE USING f_sum_type2;

 测试：

SQL> select f_sum2(id),sum(id) from "tblabc"   2  ; F_SUM2(ID)    SUM(ID) ---------- ----------       5247       5247 SQL>

f_max()，模拟MAX()函数：

定义TYPE：

CREATE OR REPLACE TYPE f_max_type AS OBJECT (  var NUMBER,  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT f_max_type) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT f_max_type, VALUE IN NUMBER) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT f_max_type, ctx2 IN OUT f_max_type) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT f_max_type, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER   )

 

 定义TYPE BODY：

CREATE OR REPLACE TYPE BODY f_max_type IS               STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT f_max_type)        RETURN NUMBER IS               BEGIN                    sctx := f_max_type(0);                    RETURN ODCIConst.Success;               END;                      MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT f_max_type, VALUE IN NUMBER)        RETURN NUMBER IS               BEGIN                    IF SELF.var < VALUE THEN                       SELF.var := VALUE;                    END IF;                    RETURN ODCIConst.Success;               END;                      MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT f_max_type, ctx2 IN OUT f_max_type)        RETURN NUMBER IS               BEGIN                    RETURN ODCIConst.Success;               END;                      MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT f_max_type, returnValue OUT NUMBER, flags IN NUMBER)        RETURN NUMBER IS               BEGIN                    returnValue := SELF.var;                    RETURN ODCIconst.success;               END;        END;

 

 

CREATE OR REPLACE FUNCTION f_max (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING f_max_type;

 

SQL> select f_max(id),max(id) from "tblabc"   2  ;  F_MAX(ID)    MAX(ID) ---------- ----------        303        303 SQL>

f_avg()，模拟AVG()函数：

定义TYPE：

CREATE OR REPLACE TYPE f_avg_type AS OBJECT (  SUM NUMBER, num NUMBER,  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT f_avg_type) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT f_avg_type, VALUE IN NUMBER) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT f_avg_type, ctx2 IN f_avg_type) RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT f_avg_type, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER )

  定义TYPE BODY：

CREATE OR REPLACE TYPE BODY f_avg_type IS STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT f_avg_type) RETURN NUMBER IS        BEGIN             sctx := f_avg_type(0, 0);             RETURN ODCICONST.Success;        END;        MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT f_avg_type, VALUE IN NUMBER) RETURN NUMBER IS        BEGIN             SELF.SUM := SELF.SUM + VALUE;             SELF.num := SELF.num + 1;             RETURN ODCIConst.Success;        END;        MEMBER FUNCTION ODCIaggregateMerge(SELF IN OUT f_avg_type, ctx2 IN f_avg_type) RETURN NUMBER IS        BEGIN             RETURN ODCIConst.Success;        END;        MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT f_avg_type, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS        BEGIN             returnValue := SELF.SUM/SELF.num;             RETURN ODCIConst.Success;        END;        END;

 定义函数：

CREATE OR REPLACE FUNCTION f_avg(input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING f_avg_type;

 测试：

SQL> select f_avg(id),avg(id) from "tblabc"   2  ;  F_AVG(ID)    AVG(ID) ---------- ---------- 276.157895 276.157895