Własne funkcje w klauzuli WITH

Klauzula With nie jest niczym nowym w świecie SQL-a. Do mojego “ulubionego” systemu baz danych, czyli tego z logo Oracle’a, została wprowadzona już w wersji 9i, czyli niemal dekadę wcześniej niż piszącemu te słowa dane było popełnić pierwszego SELECT-a. Tym niemniej osobiście zacząłem korzystać z tej klauzuli jeszcze później, czyli dopiero po kilku latach od moich pierwszych sqlowych wprawek –  na początku głównie jako dość poręcznego narzędzia do refaktoryzacji własnego kodu w skryptach, które czasami potrzebowałem komuś udostępnić.

Z czasem – nierzadko z lenistwa – zacząłem używać tego rozwiązania coraz częściej, a praktycznie zawsze wtedy, kiedy stawałem przed koniecznością tworzenia zagnieżdżonych zapytań, choć oczywiście nie tylko wówczas. Po prostu w miarę puchnięcia skryptu coraz częściej nachodziła mnie potrzeba podzielenia całości na pewne mniej lub bardziej logiczne zorganizowane jednostki właśnie za pomocą klauzuli WITH. Z tego powodu można mnie nazwać więcej niż umiarkowanym fanem tego rozwiązania.

Tym niemniej dopiero stosunkowo niedawno – mówimy o okresie czasu liczonym w miesiącach od popełnienia niniejszego wpisu – dowiedziałem się o pewnych “smaczkach”, które jeszcze bardziej wywindowały moją sympatię dla tej klauzuli. Otóż najpierw w trybie przyśpieszonym musiałem dokształcić się w temacie rekurencji dla zapytań hierarchicznych. Wprawdzie twórcy Oracle’a na taką okoliczność przygotowali autorskiego rozwiązanie, ale korzystanie z CONECTED BY jest dla mnie szalenie mniej czytelne niż rekurencyjne użycie klauzuli WITH. Co więcej to drugie rozwiązanie jest dostępne chyba w każdym obecnie popularnym systemie zarządzania relacyjną bazą danych (rzecz jasna w odróżnieniu od pomysłu made in Oracle).

Zostawmy jednak na boku kwestię wyższości jednego nad drugim, ponieważ nie temu zagadnieniu poświęcony jest dzisiejszy wpis. W kontekście właściwego tematu ważniejsze jest, iż poniekąd przy okazji poszukiwania mądrości odnośnie zapytań hierarchicznych dowiedziałem się o zastosowaniach klauzuli WITH, które jeszcze bardziej mnie zadziwiły i zachwyciły jednocześnie.

Otóż dowiedziałem się wówczas, że od wersji Oracle 12.1 pojawiła się możliwość umieszczania kodu PL/SQL bezpośrednio w  klauzuli WITH. Od razu zaznaczę, że w praktyce sprowadza się to do możliwości powoływania do życia funkcji, które dostępne będą na czas wykonywania naszego zapytania. Oczywiście  nic nie stoi na przeszkodzie, by w ten sposób pisać również “tymczasowe” procedury, ale ponieważ tych ostatnich – w odróżnieniu od funkcji – nie da się wywołać bezpośrednio z poziomu kodu SQL, ich przydatność jest mocno ograniczona. Tym niemniej sama taka możliwość jest, do czego wrócę jeszcze za chwilę parę.

Coby dalszej sztucznie nie nabijać wierszówki, za którą nikt mi i tak nie zapłaci, warto zobaczyć jak całość wygląda w akcji. Od razu zaznaczę, że sam przykład, na którym zademonstrowane zostanie tworzenie własnej funkcji w klauzuli WITH jest banalny i w zasadzie pozbawiony praktycznego sensu, ponieważ wykonanie tak prostego działania arytmetycznego, jakim jest zliczenie obwodu koła na podstawie długości jego promienia, nie wymaga w żaden sposób opakowania całości w kod PL/SQL.

WITH FUNCTION f_pole_kola (

    p_promien IN number

  ) return number  

  is

BEGIN

  RETURN round(3.14*power(p_promien,2), 2);

END;

SELECT
    f_pole_kola(3)
FROM
    dual

Jak widać wyżej na początku mamy użyte słowo kluczowe WITH, po którym zwyczajowo występuje nazwa tworzonej tabeli “pośredniej”. Tutaj jednak pojawia się kolejne słowo kluczowe, czyli w tym wypadku FUNCTION, które wskazuje, że tym razem mamy do czynienia z budowaniem funkcji właśnie. Od tego momentu de facto do czynienia mamy z klasyczna definicją tej ostatniej, czyli w nawiasach wskazany został jeden parametr, który ta funkcja będzie przyjmować na wejściu (tutaj p_promien typu numerycznego), a dalej następuje informacja o typie zwracanej wartości przez naszą funkcję. Potem oczywiście mamy właściwe ciało funkcji pomiędzy BEGIN oraz END. Całość rzecz jasna zakończona jest średnikiem i od tego momentu możemy używać tej funkcji w ramach naszego zapytania (tutaj wykorzystaliśmy pseudo tabelę dual).

Oczywiście nic nie stoi na przeszkodzie – podobnie jak ma to miejsce z podzapytaniami – by takich funkcji utworzyć więcej. Wystarczy wówczas  po prostu po zakończeniu deklaracji jednej z nich wstawić kolejną, tak jak to widać niżej, gdzie mamy dwie funkcje zliczająco odpowiednio obwód i pole prostokąta.

WITH FUNCTION f_obwod_prostokata (

    p_bok_a IN number,
    p_bok_b IN number

  ) return number  

  is

BEGIN

    RETURN 2 * p_bok_a + 2 * p_bok_b;

END;

FUNCTION f_pole_prostokata (

    p_bok_a IN number,
    p_bok_b IN number

) return number  

is

BEGIN

  RETURN p_bok_a * p_bok_b;

END;

SELECT
    
f_obwod_prostokata (2,5) as obwod

    ,f_pole_prostokata (3,4) as pole
FROM
    dual

Oczywiście można miksować w ten sposób różne funkcje oraz – jak wspomniałem wcześniej –  insze procedury. Tym niemniej z praktycznego punktu widzenia nie ma to większego sensu, ponieważ – o czym również napomknąłem uprzednio – procedury z poziomu SQL-a dostępnymi nie są w odróżnieniu od funkcji. Rzecz jasna z poziomu tych ostatnich (funkcji) można wywołać dowolną procedurę, więc ten problem wydaje się rozwiązany, ale mając na uwadze fakt, że w przypadku klauzuli WITH będziemy mieli do czynienia raczej z prostymi strukturami kodu, tworzenia takich zagnieżdżeń ociera się wręcz o masochizm. Tym bardziej, że pewnie bardzo rzadko natrafimy na rzeczywisty problem, którego w mniej lub bardziej skomplikowany sposób nie da się zaadresować w czystym SQL. 

Od siebie mogę dodać, że czasem zdarza mi się w pytaniach wybierających używać własnych funkcji utworzonych w obrębie klauzuli WITH, choć głównie robię to w celach “rozrywkowych”, by urozmaicić tym sposobem pisanie nudnych SELECT-ów. Natomiast muszę szczerze zaznaczyć, że nierzadko zajmuje mi to więcej czasu niż ogarnięcie tego samego w samym SQL-u tj. w ramach zwykłej struktury zapytania DQL. Jak zawsze wszystko rzecz jasna zależy od konkretnych potrzeb i zastosowań. 

Tym niemniej by nie pozostawić wrażenia, że w tym wypadku do czynienia mamy ze zwykłą ciekawostką, to dodam już na sam koniec, że mimo relatywnie krótkiej przygody z takim sposobem użycia klauzuli WITH, zdarzył mi się przypadek utworzenia pewnego raportu, gdzie osiągnięcie tego samego rezultatu w czystym SQL byłoby niepomiernie bardziej skomplikowane niż napisanie kilkunastu linijek kodu w PL/SQL, gdzie dzięki dość prostej iteracji kursora udało mi się pozyskać poszukiwaną wartość.