Користувальницькі функції аггрегаціі в Oracle

Зустрів сьогодні питання на SQL.RU "Як запитом отримати добуток значень всіх попередніх рядків?", І вирішив спробувати створити власну функцію аггрегаціі. Завдання насправді простіше простого, але рідко коли можна вирішити. Отже …

Вступ.


Всі використовують функції аггрегаціі, такі як MAX, MIN, AVG, SUM і інші, які виконують дію над деякими масивом даних. Проте час від часу виникає необхідність, скажімо, не скласти, а перемножити всі значення поля у вибірці. У даному випадку нам на допомогу приходить інтерфейс ODCIAggregate.


Короткий опис.


Інтерфейс ODCIAggregate має 4 методу:



Чим же вони займаються? По порядку:
1. Initialize. Даний метод виконує певні дії перед початком обчислень.
2. Iterate. Власне метод, що виконує операцію над черговим значенням з масиву.
3. Merge. Метод, необхідний у випадку розпаралелювання виконання розрахунку, коли весь масив б'ється на частини, а потім розраховані окремо вони об'єднуються. Так ось цей метод виконує об'єднання пари результатів.
4. Terminate. Метод, що закінчує розрахунки і видає результат.

Крім того необхідно оголосити функцію, яка буде викликати розрахунок аггрегатной функції.

Приклад.


Розглянемо випадок, коли нам життєво необхідна функція, перемножуються всі значення поля у вибірці.

– Створюємо тип для обчислень
create type MultiImpl as object
(
multires NUMBER, – результат
static function ODCIAggregateInitialize(sctx IN OUT MultiImpl)
return number,
member function ODCIAggregateIterate(self IN OUT MultiImpl,
value IN number) return number,
member function ODCIAggregateTerminate(self IN MultiImpl,
returnValue OUT number, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT MultiImpl,
ctx2 IN MultiImpl) return number
);
/

– Створюємо тіло типу

create or replace type body MultiImpl is
– Функція, що виконує ініціалізацію об'єкта
static function ODCIAggregateInitialize(sctx IN OUT MultiImpl)
return number is
begin
sctx := MultiImpl(1);
return ODCIConst.Success;
end;

– Функція, що виконує розрахунок
member function ODCIAggregateIterate (self IN OUT MultiImpl, value IN number)
return number is
begin
– Множимо поточний результат на чергове значення
self.multires := self.multires*value;
return ODCIConst.Success;
end;

– Функція, закінчує розрахунок
member function ODCIAggregateTerminate (self IN MultiImpl, returnValue OUT
number, flags IN number) return number is
begin
returnValue: = self.multires; – Передаємо результат у вихідний параметр
return ODCIConst.Success;
end;

– На випадок розпаралелювання
member function ODCIAggregateMerge (self IN OUT MultiImpl, ctx2 IN MultiImpl) return number IS
begin
– Просто перемножуємо результати
self.multires := self.multires*ctx2.multires;
return ODCIConst.Success;
end;
end;
/

– Ну і створюємо функцію, яка буде викликати аггрегацію
CREATE FUNCTION Multi (input NUMBER) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING MultiImpl;
/



Підсумок.
Тепер пробуємо використовувати те, що вийшло:
SQL> SELECT multi(col)
2 FROM (SELECT 2 AS col
3 FROM dual
4 UNION ALL
5 SELECT 3 AS col
6 FROM dual
7 UNION ALL
8 SELECT 4 AS col
9 FROM dual
10 UNION ALL
11 SELECT NULL AS col
12 FROM dual
13 UNION ALL
14 SELECT 7 AS col
15 FROM dual
16 UNION ALL
17 SELECT 8 AS col FROM dual) t;

MULTI(COL)
———-
1344


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*