自定义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