Głównym powodem używania PL/SQL jako języka programowania jest ta jego swoista przypadłość, że naprawdę dobrze współpracuje on z SQL w bazach Oracle. W gruncie rzeczy nie mogło być inaczej skoro do tego celu został on powołany. Rzecz jasna ta współpraca działa w obie strony; możesz osadzić SQL w kodzie PL/SQL, ale też nic nie stoi na przeszkodzie, by wywoływać funkcje PL/SQL w strukturach SQL. Mając na uwadze tematykę niniejszego cyklu wpisów, dzisiaj zajmiemy się tym pierwszym zagadnieniem.
Co to są i jak działają kursory?
Zanim odpowiemy sobie na pytanie czym jest sam kursor w przypadku PL/SQL, zacznijmy od konstatacji faktu, że wyróżnia się zwyczajowo dwa jego typy, czyli kursory jawne oraz – któżby na to wpadł – niejawne. Tymi ostatnimi zajmować się nie będziemy w tym tekście, dlatego wszystko co jest napisane dalej dotyczy kursorów jawnych (dalej będę pisał po prostu o kursorach jako takich).
Zamierzając wyjaśnić czym sam kursor jest spróbujemy raczej wskazać w jakich sytuacjach i do czego jest on wykorzystywany niźli silić się na jakąś techniczną definicję. Zacznijmy od banalnej konstatacji: jak doskonale wiemy z doświadczenia każde zapytanie SQL może zwrócić – przynajmniej potencjalnie – zero, jeden bądź wiele rekordów. Aby efektywnie je przetworzyć w ramach programu PL/SQL korzystamy właśnie z kursorów. Na pierwszy rzut oka takie wyjaśnienie nie jest specjalnie pouczające, ale za moment powinno to być nieco jaśniejsze.
| W tym miejscu mała dygresja: ponieważ korzystania z kursorów sprowadza się do pracy z danymi pobranymi z jakiejś tabeli lub tabel, na końcu tego wpisu można znaleźć załączony skrypt, który pozwoli utworzyć i wypełnić danymi dwie proste tabele. |
W celu skorzystania z kursora musimy wykonać kilka kroków, w ściśle określonej kolejności. Całość składa się z 4 “etapów”, czyli na początek musimy zacząć o deklaracji kursora (przy pomocy słowa CURSOR) oraz czegoś co określane bywa definicją, którą de facto stanowi instrukcja SELECT “przypisana” do kursora przy pomocy słowa IS. Następnie musimy taki kursor otworzyć (słowa kluczowe OPEN) w naszym programie, pobrać z niego wiersze (FETCH INTO), a na końcu zamknąć go (CLOSE).
Całość została zaprezentowana na przykładzie widocznym niżej.
DECLARE
v_nazwisko varchar2(10);
v_stanowisko varchar2(9);
CURSOR c_pierwszy_kursor IS SELECT ename, job from emp where empno = 7839;
BEGIN
OPEN c_pierwszy_kursor;
FETCH c_pierwszy_kursor INTO v_nazwisko, v_stanowisko;
CLOSE c_pierwszy_kursor;
DBMS_OUTPUT.PUT_LINE(v_nazwisko || ' jest zatrudniony jako ' || v_stanowisko);
END;W przykładowym kodzie sytuacja była o tyle prosta, że w wyniku zapytania zwrócony został tylko jeden rekord. Co by jednak się stało, gdyby w wyniku działania naszego SELECT-a tych rekordów byłoby więcej (co jest de facto typowym scenariuszem w przypadku konieczności użycia kursora). Na ten przykład, gdybyśmy w klauzuli WHERE wskazali, że interesują nas wszyscy pracownicy zatrudnieni w departamencie o kodzie 30 (deptno = 30). Takich osób jest wszak “aż” szóstka. Gdybyśmy użyli dokładnie tego samego kodu z tą różnicą dotyczącą samego SELECT-a, to nasz program jak najbardziej by się wykonał, ale wówczas w naszej konsoli zobaczylibyśmy dane wyłącznie pierwszego pracownika, zwróconego w wyniku działania zapytania, czyli zdani byli byśmy na kolejność sortowania użytą przez silnik bazy danych (zapewne wg klucza głównego). Jeśli taki rezultat nas nie zadowala, ponieważ chcemy wyświetlić wszystkich pracowników tego departamentu wówczas możemy do tego celu posłużyć się pętlą.
Kursory i pętla FOR LOOP
Do iteracji takiego przypadku moglibyśmy użyć dowolnego rodzaju pętli, o których mowa była w tym wpisie.
Tym niemniej do tego konkretnego zadania, kiedy to chcemy iterować przez wszystkie elementy listy zwróconych wierszy, najlepsza będzie konstrukcja FOR LOOP, ponieważ bardzo upraszcza całą konstrukcję (nie ma chociażby potrzeby otwierania oraz zamykania kursora czy pobierania wprost danych z kursora), a także nie wymaga one (tego rodzaju pętla) posługiwania się atrybutami kursorów, o których mowa będzie w dalszej części tekstu.
DECLARE
CURSOR c_drugi_kursor IS SELECT ename, job from emp where deptno = 30;
BEGIN
FOR i IN c_drugi_kursor
LOOP
DBMS_OUTPUT.PUT_LINE(i.ename || ' jest zatrudniony jako ' || i.job);
END LOOP;
END;Jak widać w tym wypadku wystarczyło zadeklarować kursor i przypisać mu odpowiednią definicję, a następnie przekazać go do pętli FOR LOOP, w której tej kursor był iterowany do czasu, gdy zawierał on jakiekolwiek rekordy do wyświetlenia.
Atrybuty
W poprzednim paragrafie była krótko wspomniana kwestia atrybutów, więc teraz kilka zdań poświęcone zostanie temu zagadnieniu. Otóż atrybuty te są pewnymi informacjami na temat stanu naszego kursora, które mogą nam chociażby odpowiedzieć na pytanie, czy dany kursor jest nadal otwarty. Niżej znajduje się lista takich atrybutów:
%FOUND – wartością atrybutu jest TRUE, jeśli ostatnia operacja przypisania danych (FETCH) odczytała rekord z kursora. W przeciwnym wypadku (tzn. kiedy odczyt się nie udał) atrybut przyjmuje wartość FALSE. Przed pierwszym odczytem atrybut ma wartość NULL. Działanie tego atrybutu przedstawione zostanie dalej w przykładowym kodzie.
%NOTFOUND – wartością atrybutu jest FALSE jeśli ostatnia operacja FETCH odczytała krotkę z kursora. W przeciwnym wypadku (tzn. kiedy odczyt się nie udał) atrybut przyjmuje wartość TRUE. Przed pierwszym odczytem atrybut ma wartość NULL
%ROWCOUNT – wartością atrybutu jest liczba dotąd odczytanych z rekordów z kursora. Przed pierwszym odczytem atrybut ma wartość 0. Tego atrybutu również użyjemy w naszym przykładzie.
%ISOPEN – wartością atrybutu jest TRUE jeśli kursor jest otwarty i FALSE jeśli kursor jest zamknięty.
By zaprezentować w akcji ich zastosowanie niżej widać przykład kodu, gdzie zaprezentowane zostały w akcji dwa atrybuty, czyli ROWCOUNT oraz FOUND.
DECLARE
v_nazwisko varchar2(10);
v_stanowisko varchar2(9);
CURSOR c_trzeci_kursor IS SELECT ename, job from emp where deptno = 30;
BEGIN
OPEN c_trzeci_kursor;
FETCH c_trzeci_kursor INTO v_nazwisko, v_stanowisko;
WHILE c_trzeci_kursor%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE(c_trzeci_kursor%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(v_nazwisko || ' jest zatrudniony jako ' || v_stanowisko);
FETCH c_trzeci_kursor INTO v_nazwisko, v_stanowisko;
END LOOP;
CLOSE c_trzeci_kursor;
END;Powyższy kod bardzo przypomina ten z pierwszego przykładu z tą zasadniczą różnicą już na samym początku, że definicja kursora nr 3 potencjalnie może zwrócić więcej niż jeden rekord. Dlatego też po pierwszym pobraniu danych (FETCH) została uruchomiona pętla WHILE właśnie na tą okoliczność, ponieważ chcemy wyświetlić wszystkie zwrócone pary wartości.
W tym miejscu może warto przypomnieć, że pętla WHILE sprawdza prawdziwość warunku i dopóki otrzymuje w odpowiedzi TRUE jest ona wykonywana. Dlatego też pierwsze pobranie wartości z kursora musiało zostać wykonane przed uruchomieniem pętli, ponieważ – o czym mowa była wcześniej przy opisywaniu poszczególnych atrybutów – w przeciwnym razie atrybut FOUND zwróciłby NULL bez względu na to, czy są jakieś rekordy do pobrania czy nie. Tym samym pętla nie zostałby wykonana.
Zakończenie
Niestety kończę ten tekst z pewnymi wyrzutami sumienia, ponieważ w mojej opinii nie wyczerpuje on tematu kursorów w takim zakresie, jaki sobie pierwotnie zakładałem. Jednakże z powodu pewnych dość nagłych zmian w moim życiu zawodowym przez kilka najbliższych miesięcy będę zmuszony zrobić sobie kolejną przerwę od “blogowania”, ponieważ będę miał inne ważniejsze priorytety, którym poświęcić będę musiał mnóstwo czasu i uwagi. Tym niemniej przed tą pauzą chciałem zamknąć niniejszy cykl na prezentacji tych najbardziej fundamentalnych elementów, z których budowane są programy w PL/SQL. W związku z tym za jakiś czas być może wrócę do tego wpisu i co nieco go podrasuję. A tymczasem dziękuję za uwagę.
Tworzenie przykładowej bazy
W zamieszczonym niżej załączniku znajduje się prosty skrypt, który należy uruchomić w ramach jakiejś istniejącej bazy danych. W efekcie jego wykonania zostaną utworzone dwie proste tabele z danymi, które zostały wykorzystane z niniejszym tekście i zapewne będą używane w kolejnych wpisach, jeśli pojawią się w bliżej nieokreślonej przyszłości (patrz poprzedni paragraf).
