Автор: Дикран Хачикян
Често създаването на собствена агрегираща или аналитична функция се възприема като задача за много напреднали потребители, но надявам се, че тази статия ще ви убеди в това, че подобна задача може спокойно да се постави в категорията "обичайни".
За да напишете собствена агрегираща функция са необходими четири елемента:
В сървърите на Oracle първите три се реализират с интерфейса Data Cartridge, с помощта на който лесно могат да се създават разширения към съществуващата в сървъра функционалност, а четвъртият е стандартна DDL заявка с малко допълнение.
Да разгледаме прост пример, в който ще напишем агрегираща функция PRODUCT(), която умножава данните в група - такава няма сред стандартните. След подробните стъпки, вижте и видеото как да напишете собствена агрегираща функция на PL/SQL, която изчислява средна претеглена стойност.
Трябва да създадете собствен тип, който на Стъпка 3 ще се използва при създаването на функцията, която ще участва в заявките.
CREATE OR REPLACE TYPE ProductImpl AS OBJECT(
product NUMBER, -- тук ще се натрупва резултата
--статична функция за инициализация на контекста
STATIC FUNCTION ODCIAggregateInitialize( ctx IN OUT ProductImpl )
RETURN NUMBER,
--функция, която ще се изпълнява за всеки един ред (итератор)
--velue е стойността намираща се на реда, който в момента се обработва
MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ProductImpl, value IN NUMBER)
RETURN NUMBER,
--функция, която се използва за обединяване на резултатите при паралелно
--изпълнение
--ctx2 - съдържа резултата от изпълнението в другата(-ите) нишки
MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ProductImpl, ctx2 IN ProductImpl )
RETURN NUMBER,
--функция, която има за задача да върне резултата
MEMBER FUNCTION ODCIAggregateTerminate( self IN OUT ProductImpl, returnValue OUT NUMBER, flags IN NUMBER)
RETURN NUMBER
);
Четирите функции от интерфейса на Data Cartridge и учатват в различните моменти от работата на агрегиращата функция.
В четирите функции параметъра self е референция към текущия контекст.
Функциите от типа ProductImpl трябва да се дефинират и да се разпише действието им за постигане на крайния резултат.
CREATE OR REPLACE TYPE BODY ProductImpl IS
STATIC FUNCTION ODCIAggregateInitialize( ctx IN OUT ProductImpl )
RETURN NUMBER IS
BEGIN
ctx := ProductImpl(1);--инициализация на променливата product с 1
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateIterate( self IN OUT ProductImpl, value IN NUMBER)
RETURN NUMBER IS
BEGIN
self.product := self.product * NVL(value, 1);
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateMerge( self IN OUT ProductImpl, ctx2 IN ProductImpl )
RETURN NUMBER IS
BEGIN
--умножаваме резултата от другата нишка по това, което е изчислено
--в текущата
self.product := self.product * ctx2.product;
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateTerminate( self IN OUT ProductImpl, returnValue OUT NUMBER, flags IN NUMBER)
RETURN NUMBER IS
BEGIN
--връщаме резултата
returnValue := self.product;
RETURN ODCIConst.SUCCESS;
END;
END;
В конкретния пример, функциите връщат ODCIConst.SUCCESS, но при други функции, с сложни изчисления, проверки и др. може да се използва ODCIConst.ERROR или някоя от другите константи.
CREATE OR REPLACE FUNCTION Product(value NUMBER) RETURN NUMBER PARALLEL_ENABLE -- посочва, че може да се използва в паралелно работещи потоци AGGREGATE USING ProductImpl;
С това сме готови да тестваме работата на агрегиращата функция.
Ще използваме заявка с данни директно въведени в скрипта за да може по-лесно да променяме стойностите и да изследваме поведението на функцията при 0 u NULL стойности.
WITH qry AS (
SELECT 'A' nm, 1 v FROM DUAL UNION ALL
SELECT 'A' nm, 2 v FROM DUAL UNION ALL
SELECT 'A' nm, 3 v FROM DUAL UNION ALL
SELECT 'B' nm, 2 v FROM DUAL UNION ALL
SELECT 'B' nm, 4 v FROM DUAL UNION ALL
SELECT 'B' nm, 2 v FROM DUAL UNION ALL
SELECT 'C' nm, 3 v FROM DUAL UNION ALL
SELECT 'C' nm, 2 v FROM DUAL )
SELECT nm
, PRODUCT(v) Product
FROM qry
GROUP BY nm;
Полученият резултат е:
+----+---------+ | nm | Product | +----+---------+ | A | 6 | +----+---------+ | B | 16 | +----+---------+ | C | 6 | +----+---------+
Опитайте да промените част от стойностите с 0 или NULL и вижте дали полученият резултат съответства на това, което искате да получите накрая. В нашата реализация например, при NULL стойност се умножава по 1, но в друга ситуация може да се изисква NULL да се третира като 0 или да остане NULL, което ще доведе до NULL на цялото произведение.
Хубавата новина е, че функцията може да се използва и като аналитична без да се налага да променяме каквото и да е било в нея. Нека да тестваме:
WITH qry AS (
SELECT 'A' nm, 1 v FROM DUAL UNION ALL
SELECT 'A' nm, 2 v FROM DUAL UNION ALL
SELECT 'A' nm, 3 v FROM DUAL UNION ALL
SELECT 'B' nm, 2 v FROM DUAL UNION ALL
SELECT 'B' nm, 4 v FROM DUAL UNION ALL
SELECT 'B' nm, 2 v FROM DUAL UNION ALL
SELECT 'C' nm, 3 v FROM DUAL UNION ALL
SELECT 'C' nm, 2 v FROM DUAL )
SELECT nm
, v
, PRODUCT(v) OVER( PARTITION BY nm
ORDER BY v
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) Product
FROM qry;
Полученият резултат е:
+----+---+---------+ | nm | v | Product | +----+---+---------+ | A | 1 | 1 | +----+---+---------+ | A | 2 | 2 | +----+---+---------+ | A | 3 | 6 | +----+---+---------+ | B | 2 | 2 | +----+---+---------+ | B | 2 | 4 | +----+---+---------+ | B | 4 | 8 | +----+---+---------+ | C | 2 | 2 | +----+---+---------+ | C | 3 | 6 | +----+---+---------+
Цялостен пример показващ стъпка по стъпка как да напишете собствена агрегираща функция на PL/SQL, която изчислява средна претеглена стойност
