ORACLE中的自定义聚合函数（User-DefinedAggregateFunctions）

聚合函数也叫分组函数，是对多行进行计算的一种函数，比如sum、count、avg、max、min等等。

oracle中为了增强扩展性，提供了一个自定义聚合函数的接口，odciaggregate()，用户可以用oracle支持的各类语言（plsql、c、c++、java等）定义一个聚合函数，并在程序中按照一样的语法进行使用。

实际应用中，使用到自定义聚合函数的场景极少，理由是：

1.plsql语言已经足够丰富，可以轻松实现绝大部分聚合场景；

2.自定义聚合函数的效率不见得比直接plsql实现更高。

比如itpub上有个求加权中位数的帖子，答主给出了两种方案的比较，实际上自定义聚合函数只是对plsql实现的一个包装，详细可以参考原文?

https://www.itpub.net/forum.php?mod=viewthread&tid=1377770

那么，自定义聚合函数到底有啥用呢？个人觉得只有一个：封装的需要，有时是为了改善代码可读性，有时是基于技术细节的保密。

下面把oracle官方文档的一个自定义聚合案例记录一下，该案例中的函数secondmax()是取第二大的数，实际上用分析函数分分钟就可以解决，恐怕oracle自己都编不出一个非常需要使用自定义函数的恰当的场景。

定义一个包含odciaggregate例程的对象类型secondmaximpl:

create type secondmaximpl as object
(
  max number,          -- highest value seen so far 
  secmax number,       -- second highest value seen so far
  static function odciaggregateinitialize(sctx in out secondmaximpl) 
    return number,
  member function odciaggregateiterate(self in out secondmaximpl, 
    value in number) return number,
  member function odciaggregateterminate(self in secondmaximpl, 
    returnvalue out number, flags in number) return number,
  member function odciaggregatemerge(self in out secondmaximpl, 
    ctx2 in secondmaximpl) return number
);
/

secondmaximpl类型具体实现:

create or replace type body secondmaximpl is 
static function odciaggregateinitialize(sctx in out secondmaximpl) 
return number is 
begin
  sctx := secondmaximpl(0, 0);
  return odciconst.success;
end;

member function odciaggregateiterate(self in out secondmaximpl, value in number) return number is
begin
  if value > self.max then
    self.secmax := self.max;
    self.max := value;
  elsif value > self.secmax then
    self.secmax := value;
  end if;
  return odciconst.success;
end;

member function odciaggregateterminate(self in secondmaximpl, 
    returnvalue out number, flags in number) return number is
begin
  returnvalue := self.secmax;
  return odciconst.success;
end;

member function odciaggregatemerge(self in out secondmaximpl, ctx2 in secondmaximpl) return number is
begin
  if ctx2.max > self.max then
    if ctx2.secmax > self.secmax then 
      self.secmax := ctx2.secmax;
    else
      self.secmax := self.max;
    end if;
    self.max := ctx2.max;
  elsif ctx2.max > self.secmax then
    self.secmax := ctx2.max;
  end if;
  return odciconst.success;
end;
end;
/

创建用户自定义聚合函数secondmax:

create function secondmax (input number) return number 
parallel_enable aggregate using secondmaximpl;

在sql语句中应用自定义聚合函数:

select secondmax(salary), department_id
???from myemployees
???group by department_id
???having secondmax(salary) > 9000;

ORACLE中的自定义聚合函数（User-DefinedAggregateFunctions）

相关推荐