PL/SQL: Как да напишем собствена агрегираща/аналитична функция?

Често създаването на собствена агрегираща или аналитична функция се възприема като задача за много напреднали потребители, но надявам се, че тази статия ще ви убеди в това, че подобна задача може спокойно да се постави в категорията обичайни.

За да напишете собствена агрегираща функция са необходими четири елемента:

• състояние
• функция за обработка на следващ ред
• функция която връща резултата
• указание, че предишните три точки са агрегираща функция

В сървърите на Oracle първите три се реализират с интерфейса Data Cartridge, с помощта на който лесно могат да се създават разширения към съществуващата в сървъра функционалност, а четвъртият е стандартна DDL заявка с малко допълнение.

Да разгледаме прост пример, в който ще напишем агрегираща функция PRODUCT(), която умножава данните в група – такава няма сред стандартните. След подробните стъпки, вижте и видеото как да напишете собствена агрегираща функция на PL/SQL, която изчислява средна претеглена стойност.

Стъпка 1: Създаване на собствен обектен тип

Трябва да създадете собствен тип, който на Стъпка 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 и учатват в различните моменти от работата на агрегиращата функция.

• ODCIAggregateInitialize() извършва началната инициализация на контекста.
• ODCIAggregateIterate() е функцията, в която извършва основното изчисление. Изпълнява се за всеки ред от групата като параметъра value е текущата стойност, с която трябва да умножим.
• ODCIAggregateMerge() се използва в случите, когато сървъра изпълнява действията паралелно в няколко нишки. Функцията обединява резултатите от паралелните изчисления
• ODCIAggregateTerminate() връща резултата

В четирите функции параметъра self е референция към текущия контекст.

Стъпка 2: Дефиниране на функциите в тялото на типа

Функциите от типа 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 или някоя от другите константи.

Стъпка 3: Създаване на функция, която ще се използва в заявките

CREATE OR REPLACE FUNCTION Product(value NUMBER) RETURN NUMBER
PARALLEL_ENABLE -- посочва, че може да се използва в паралелно работещи потоци 
AGGREGATE USING ProductImpl;

С това сме готови да тестваме работата на агрегиращата функция.

Стъпка 4: Тест

Ще използваме заявка с данни директно въведени в скрипта за да може по-лесно да променяме стойностите и да изследваме поведението на функцията при 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;

Полученият резултат е:

Опитайте да промените част от стойностите с 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;

Полученият резултат е:

Пример с видео: Как да напишем агрегираща функция, която изчислява претеглена средна стойност?

Цялостен пример показващ стъпка по стъпка как да напишете собствена агрегираща функция на PL/SQL, която изчислява средна претеглена стойност

Искате да научите повече за PL/SQL?

Включете се в курсовете ни по PL/SQL за Oracle разработчици, програмисти и администратори на бази данни.

Автор: Дикран Хачикян