Oracle: Своя агрегатная функция

Хотел бы рассказать как в Oracle создать свою агрегатную функцию наподобие SUM или AVG.

Делается это с помощью интерфейса ODCIAggregate.
Он имеет 4 метода:

• Initialize
• Iterate
• Merge
• Terminate

Нам необходимо реализовать 4 эти метода:
1. Initialize - Данный метод выполняет определенные действия перед началом вычислений.
2. Iterate - Собственно метод, выполняющий операцию над очередным значением из массива.
3. Merge - Метод, необходимый в случае распараллеливания выполнения расчета, когда весь массив бьется на части, а потом рассчитанные по отдельности они объединяются. Так вот этот метод выполняет объединение пары результатов.
4. Terminate - Метод, заканчивающий расчеты и выдающий результат.

Кроме того необходимо объявить функцию, которая будет вызывать расчет агрегатной функции.

Разберем на примере агрегатной функции перемножения значений, назовем ее MULT.

1. Создаем новый объект с набором необходимых свойств и методов:

create type MultImpl as object
(
  mult_value NUMBER, --свойство, где хранится результат

  static function ODCIAggregateInitialize(sctx IN OUT MultImpl) 
    return number,
  member function ODCIAggregateIterate(self IN OUT MultImpl, 
    value IN number) return number,
  member function ODCIAggregateTerminate(self IN MultImpl, 
    returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT MultImpl, 
    ctx2 IN MultImpl) return number
);
/

2. Описываем реализации объекта.

create or replace type body MultImpl is 
static function ODCIAggregateInitialize(sctx IN OUT MultImpl) 
return number is 
begin
  --кладем в начальное значение NULL (если положить 0, то результатом перемножения всегда будет 0)
  sctx := MultImpl(NULL);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT MultImpl, value IN number) return number is
begin
  --перемножаем наше свойство с новым значением из выборки
  self.mult_value := nvl(self.mult_value,1) * NVL(value,0);
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN MultImpl, 
    returnValue OUT number, flags IN number) return number is
begin
  --возвращаем результат из свойства
  returnValue := self.mult_value;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT MultImpl, ctx2 IN MultImpl) return number is
begin
  --в случае объединения - также перемножаем результаты 2 потоков
  self.mult_value := nvl(self.mult_value,0) * nvl(ctx2.mult_value,0);
  return ODCIConst.Success;
end;
end;
/

3. Создаем pl/sql функцию, вызывающую наш объект.

CREATE FUNCTION mult (input NUMBER) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING MultImpl;

4. Вызываем нашу новую агрегатную функцию.

with t as (
select 2 as n from dual union all
select 3 from dual union all
select 4 from dual)
SELECT SUM(n), mult(n) FROM t;

5. Смотрим результат.

    SUM(N)    MULT(N)
---------- ----------
         9         24 

Подробное описание можно почитать на хабре: https://habrahabr.ru/company/postgrespro/blog/351008/