Procedury składowane w PostgreSQL/Język PLpgSQL/Kursory
Typem danych kursora jest REFCURSOR. Podstawowe operacje na kursorach:
- otwieranie (OPEN),
- wczytywanie jednego lub więcej wierszy (FETCH),
- zmiana pozycji (MOVE),
- zamknięcie (CLOSE).
Zobacz też:
Nazwa kursora
edytujNazwa kursora jest domyślnie ustawiana na <unnamed cursor n> lub <unnamed portal n>, gdzie n jest liczbą całkowitą wybieraną przez serwer. Nie ma większego znaczenia, jeśli kursor jest używany jedynie wewnątrz procedury składowanej, gdy jednak ma zostać zwrócony w celu wykonywania na nim jakiś instrukcji SQL, sensowna nazwa staje się istotna.
Przypisanie napisu do zmiennej kursorowej ustawia jego nazwę. Należy to zrobić przed otwarciem kursora; można także po, ale dalsze działania mogą stać się niemożliwe.
DECLARE
kursor REFCURSOR;
BEGIN
kursor := 'dane';
OPEN kursor FOR ...
END;
Otwieranie kursorów
edytujKursor otwierany jest instrukcją OPEN. Składnia dla kursorów ograniczonych:
OPEN kursor [ ( argumenty ) ]
Gdzie kursor jest nazwą zmiennej, a argumenty listą opcjonalnych parametrów. Dla kursorów nieograniczonych składnia jest podobna do tej, używanej przy deklaracji:
OPEN kursor [ NO [ SCROLL ] ] FOR zapytanie OPEN kursor [ NO [ SCROLL ] ] FOR EXECUTE napis [ USING lista wartości ]
Słowo SCROLL decyduje, że można będzie przesuwać kursor wstecz, np. MOVE PRIOR FROM kursor. Domyślnie jest to zabronione - NO SCROLL.
Zapytanie jest albo zapytaniem statycznym, albo dynamicznym.
Przykład 1 - kursory nieograniczone
edytujDECLARE
kursor1 REFCURSOR;
kursor2 REFCURSOR;
BEGIN
OPEN kursor1 FOR
SELECT * FROM tabela WHERE id = elem_id;
...
OPEN kursor2 FOR
EXECUTE 'SELECT * FROM ' || nazwa::regclass || ' WHERE id=$1' USING elem_id;
Przykład 2 - kursory ograniczone
edytujDECLARE
kursor1 CURSOR FOR SELECT * FROM tabela ORDER BY kolumna;
kursor2 CURSOR (argument integer) FOR SELECT * FROM tabela WHERE id=argument;
BEGIN
OPEN kursor1;
OPEN kursor2(elem_id);
Zamykanie kursorów
edytujZamknięcie kursora umożliwia instrukcja CLOSE kursor. Kursor musi być już otwarty, inaczej zostanie zgłoszony błąd.
Zwracanie kursorów
edytujPrzykład
edytuj-- procedura otwiera i zwraca kursor
CREATE OR REPLACE FUNCTION kursor(nazwa_kursora text) RETURNS refcursor AS $$
DECLARE
c refcursor := nazwa_kursora;
BEGIN
OPEN c FOR SELECT * FROM pracownicy;
RETURN c;
END;
$$ LANGUAGE 'plpgsql';
Użycie:
BEGIN; SELECT kursor('nasz kursor'); -- uwaga, nazwa kursora to nazwa kolumny, dlatego w cudzysłowie! FETCH ALL IN "nasz kursor"; COMMIT;
Kursory, jako argumenty funkcji
edytujFunkcja musi przyjmować argument typu REFCURSOR, natomiast kursor musi zostać wcześniej zadeklarowany instrukcją SQL DECLARE. Funkcji musi zostać przekazana nazwa kursora, jako napis.
W ciele procedury można już korzystać z kursora bez ograniczeń.
Przykład
edytujProcedura zwracająca co 4-ty wiersz wyniku:
CREATE OR REPLACE FUNCTION co_czwarty(K REFCURSOR) RETURNS SETOF text AS $$
DECLARE
nazwa text;
BEGIN
LOOP
FETCH NEXT FROM K INTO nazwa;
EXIT WHEN NOT FOUND;
RETURN NEXT nazwa;
MOVE +3 FROM K;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';
I przykładowa sesja
-- kursory mogą być tworzone tylko w obrębie transakcji BEGIN; -- stworzenie kursora DECLARE kursor CURSOR FOR SELECT relname FROM pg_class ORDER BY relname; -- i wywołanie funkcji SELECT co_czwarty('kursor'); ROLLBACK;