oracle 定义聚合函数,Oracle自定义聚合函数

应用场景：

权限管理：同一功能对同一用户有多个不同权限设置(1-允许/0-默认/-1禁用)，取组合后的权限值。

--聚合函数就是一个对象

create or replace type permission_sign_object as object (

--对象变量

permission_sign number(1),

--对象初始化

static function ODCIAggregateInitialize(v_self in out permission_sign_object) return number,

--聚合函数的迭代方法

member function ODCIAggregateIterate(self in out permission_sign_object, value in number) return number,

--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合

member function ODCIAggregateMerge(self in out permission_sign_object, v_next in permission_sign_object) return number,

--终止聚集函数的处理,返回聚集函数处理的结果

member function ODCIAggregateTerminate(self in permission_sign_object, return_value out number ,v_flags in number) return number

);

create or replace type body permission_sign_object is

static function ODCIAggregateInitialize(v_self in out permission_sign_object) return number is

begin

v_self := permission_sign_object(0);

return ODCICONST.Success;

end;

member function ODCIAggregateIterate(self in out permission_sign_object, value in number) return number is

begin

if value < 0 then

self.permission_sign := -1;

end if;

if value > 0 then

if self.permission_sign > -1 then

self.permission_sign := 1;

end if;

end if;

return ODCICONST.Success;

end;

member function ODCIAggregateMerge(self in out permission_sign_object, v_next in permission_sign_object) return number is

begin

if v_next.permission_sign < 0 then

self.permission_sign := -1;

end if;

if v_next.permission_sign > 0 then

if self.permission_sign > -1 then

self.permission_sign := 1;

end if;

end if;

return ODCICONST.Success;

end;

member function ODCIAggregateTerminate(self in permission_sign_object, return_value out number ,v_flags in number) return number is

begin

return_value:= self.permission_sign;

return ODCICONST.Success;

end;

end;

create or replace function PermissionSign(value number) return number

parallel_enable aggregate using permission_sign_object;