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

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

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

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

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

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

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

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

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

Трябва да създадете собствен тип, който на Стъпка 3 ще се използва при създаването на функцията, която ще участва в заявките.

Четирите функции от интерфейса на Data Cartridge и учатват в различните моменти от работата на агрегиращата функция.

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

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

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

Функциите от типа ProductImpl трябва да се дефинират и да се разпише действието им за постигане на крайния резултат.

В конкретния пример, функциите връщат ODCIConst.SUCCESS, но при други функции, с сложни изчисления, проверки и др. може да се използва ODCIConst.ERROR или някоя от другите константи.

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

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

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

Ще използваме заявка с данни директно въведени в скрипта за да може по-лесно да променяме стойностите и да изследваме поведението на функцията при 0 u NULL стойности.

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

Опитайте да промените част от стойностите с 0 или NULL и вижте дали полученият резултат съответства на това, което искате да получите накрая. В нашата реализация например, при NULL стойност се умножава по 1, но в друга ситуация може да се изисква NULL да се третира като 0 или да остане NULL, което ще доведе до NULL на цялото произведение.

Как ще изглежда аналитичният вариант на написаната функция?

Хубавата новина е, че функцията може да се използва и като аналитична без да се налага да променяме каквото и да е било в нея. Нека да тестваме:

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

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

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

Искате да научите още за работата с бази данни?

Вижте какви обучения предстоят в сферата на SQL, PL/SQL, анализ на данни и работа с бази данни.

Научете повече
Ако Вие сте програмист или администратор на бази данни и искате да научите повече за PL/SQL и да подобрите работата си със сървърите на Oracle, този курс е за вас!