Kurs Oracle PL/SQL. Funkcje strumieniowe

Istnieje możliwość tworzenia w PL/SQL na których wyniku możemy operować tak jak na tabeli – mam na myśli stosowanie SELECT, warunków WHERE i sortowania. Jest możliwość zastosowania funkcji zwracającej tablicę typu obiektowego, jednak stosowanie takich typów nie należy do wygodnych. Ponadto trzeba będzie poczekać na wynik do czasu aż wygeneruje się cały. Przypuśćmy, że chcemy dostać pierwsze X wierszy jak najszybciej. Reszta może zostać pobrana nieco później. Interesuje nas działanie podobne do wykonania zapytania SELECT na bardzo dużej tabeli w narzędziu takim jak SQL Developer. Stosunkowo szybko (o ile nie zastosowaliśmy np. sortowania) dostaniemy pierwsze 50 wierszy (tylko one zostały pobrane z bazy). Kolejne zostaną zfetchowane dopiero gdy przesuniemy suwak przy wyniku. Aby uzyskać taki efekt, możemy zastosować funkcję strumieniową – pipelined. Wiersze będą zwracane z funkcji jeden po drugim w takim tempie w jakim będą pobierane / generowane. Nie będziemy musieli czekać na wygenerowanie całego wyniku. Moim zdaniem ciekawa funkcjonalność w optymalizacji PL/SQL. Nic nie stoi też na przeszkodzie by użyć funkcji strumieniowych w połączeniu z typem obiektowym.

Zaczniemy od najprostszego przykładu. Stworzyłem funkcję zwracającą kolejne potęgi liczby 2. Funkcja będzie zwracać tablicę elementów typu number element po elemencie. Na wyniku tej funkcji wykorzystamy SELECT.

W pierwszej kolejności tworzę typ tablicowy elementów typu number, widoczny w całym schemacie. Dalej tworzę funkcję która zwróci tyle kolejnych potęg liczby 2 ile podamy przez parametr.

Różnicę w stosunku do zwykłych funkcji zauważyć można w liniach 3, 7 i 9. W linii 3 zauważymy deklarację "PIPELINED", jest ona wymagana jeśli chcemy wykorzystywać funkcje w sposób opisanywcześniej. W linii 7 znajdziemy "pipe row". Oznacza on po prostu zwrot kolejnego elementu z funkcji. W linii 9 znajdziemy klauzulę return która jednak nic nie zwraca... Musi ona być z powodów formalnych. Sam zwrot danych zrealizowaliśmy już wcześniej z użyciem PIPE ROW :)

W liniach 12 i 13 zobaczymy sposób wykorzystania naszej nowej funkcji. Klauzula TABLE umożliwia nam stosowanie SELECT na wyniku funkcji. Oczywiście moglibyśmy tutaj użyć również WHERE czy ORDER BY.

Teraz nieco skomplikujemy sytuację. Funkcja nie będzie zwracała tablicy elementów typu prostego, a tablicę elementów typu rekordowego.

W pierwsze kolejności muszę zdeklarować sam zwracany typu rekordowy. Ponieważ nie mogę go zdeklarować w schemacie, deklaruję go w pakiecie. Przy okazji w tym samym pakiecie deklaruję również typ tablicowy złożony z elementów właśnie utworzonego typu rekordowego. Tutaj wymagane jest pewne wyjaśnienie z moje strony. Czemu w typie rekordowym mam same pola typu varchar2? Nie jest to w żaden sposób wymagane. Po prostu wszystkie typy proste inne niż grupa LOB czy typ LONG są konwertowalne do varchar2, a ja jeszcze nie wymyśliłem zapytania któego użyję :) To sprawia też że ten typ rekordowy będzie wielorazowego użytku na potrzeby ewentualnych innych zapytań.

Dalej tworzę funkcję wykorzystującą ten typ tablicowy. Konstrukcyjnie niewiele tutaj zmian w stosunku do poprzedniej funkcji. Tyma razem jednak przetwarzam wynik zapytania. Może rzucić się w oczy linia 29 z deklaracją pojedynczego elementu do którego wrzucam zaczytany z kursora wiersz. Po co mi on potrzebny jeśli funkcja ma zwracać tablicę? Zwracać będzie tablicę, ale wierszo po wierszu. W linii 35 wywołuję PIPE ROW która nie może przecież zwrócić nam tablicy. Dlatego potrzebuję takiego elementu jako kontenera na kolejne zwracane wiersze.