自定义Oracle汇聚函数：模拟SUM,MAX,AVG-数据库教程-爱易网页

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

日期：2014-05-16　浏览次数：20502 次

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

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

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

相关资料更多>

推荐阅读更多>