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;