Wykład 13

Transact-SQL – kursory

1. Wstęp

Pisząc kod Transact-SQL możemy spotkać się z zadaniem, którego nie jesteśmy w stanie rozwiązać przy pomocy wielu pojedynczych instrukcji SQL. Przykładem może być sytuacja, w której dla każdego wiersza jednej tabeli musimy wykonać kilka aktualizacji na innych tabelach. W tym przypadku jedynym rozwiązaniem jest użycie kursora.

Kursor jest buforem, do którego są zapisywane kolejno sprowadzane z bazy danych wiersze z wynikami zapytania. Umożliwi on nam więc przejrzenie wiersz po wierszu wyniku działania instrukcji SELECT i dla każdego sprowadzonego wiersza wykonanie zadanej operacji. Kursor jest konstrukcją programistyczną, która może być użyta w dowolnym kodzie Transact-SQL, a więc w bloku anonimowym, procedurze składowanej czy wyzwalaczu.

Kursorów nie należy nadużywać. Są one mało wydajne. Jeśli więc zadanie da się wykonać bez użycia kursora, powinniśmy preferować takie rozwiązanie.

2. Składnia

Aby skorzystać z kursora musimy wykonać kilka kroków. Dla początkującego programisty mogą się one wydać bardzo skomplikowane. W praktyce jednak, jest to schemat, który jest powtarzany zawsze, gdy korzystamy z kursorów.

Na początku musimy kursor zadeklarować. Deklarujemy kursor zawsze dla instrukcji SELECT. To jej wyniki będziemy sprowadzać kolejno, wiersz po wierszu.

DECLARE nazwa_kursora CURSOR FOR instrukcja_SELECT

Następnie otwieramy kursor.

OPEN nazwa_kursora

Instrukcją która pozwala na pobranie kolejnego wiersza i zapisanie go w zmiennych jest FETCH. Pamiętajmy, że instrukcja SELECT zawarta w deklaracji kursora może zwracać wiele pól. Musimy więc wcześniej zadeklarować tyle zmiennych, ile zwróci pól instrukcja SELECT.

FETCH NEXT FROM nazwa_kursora INTO zmienne

Z kursora korzystamy zwykle w pętli. Pozwoli nam to na przejście po wszystkich rekordach. Do sprawdzenia, czy ostatnie wywołanie FETCH zwróciło wiersz, służy zmienna systemowa @@FETCH_STATUS. Jeśli zwraca wartość 0 oznacza to, że pobranie wiersza odbyło się z powodzeniem. W przeciwnym przypadku oznacza to wystąpienie błędu, lub częściej, osiągnięcie końca zbioru rekordów.

WHILE @@FETCH_STATUS = 0

Po zakończeniu korzystania z kursora należy jeszcze wykonać:

CLOSE nazwa_kursora
DEALLOCATE nazwa_kursora

Instrukcja CLOSE zamyka kursor i zwalnia blokady, które zakładał kursor na danych, na których operował, jednak jest on jeszcze dostępny po ponownym otwarciu. Instrukcja DEALLOCATE powoduje całkowite usunięcie kursora i zwolnienie zarezerwowanej dla niego pamięci.

3. Przykłady

W poniższym przykładzie wypiszemy na ekranie nazwiska i pensje wszystkich pracowników, których pensja jest wyższa niż 200.

DECLARE kursor CURSOR FOR
SELECT Ename, Sal FROM Emp WHERE Sal > 200
DECLARE @nazwisko VARCHAR(50), @pensja INT
PRINT 'Pracownicy o pensji wyższej niż 200:'
OPEN kursor 
FETCH NEXT FROM kursor INTO @nazwisko, @pensja 
WHILE @@FETCH_STATUS = 0
   BEGIN 
      PRINT @nazwisko + ' ' + Cast(@pensja As Varchar)
      FETCH NEXT FROM kursor INTO @nazwisko, @pensja 
   END 
CLOSE kursor 
DEALLOCATE kursor

Zwróćmy uwagę na to, że instrukcja FETCH występuje w kodzie dwukrotnie. Pierwsze jej wystąpienie jest konieczne, aby pobrać pierwszy wiersz i ustawić zmienną @@FETCH_STATUS. Bez tego wykonanie nie przeszłoby do wnętrza pętli WHILE. Drugie wystąpienie powinno pojawić się zawsze na końcu pętli, a więc tuż przed END, aby pobrać kolejny wiersz dla kolejnej iteracji pętli i zmienić wartość zmiennej @@FETCH_STATUS w przypadku, gdyby osiągnięty został koniec zbioru rekordów. Niewłaściwe umiejscowienie lub brak tych instrukcji może spowodować niewłaściwe działanie programu lub wejście w nieskończoną pętlę.

W kolejnym przykładzie kursor zostanie użyty w celu przeprowadzenia modyfikacji pensji pracowników w tabeli „emp”. Osoby zarabiające poniżej 1000 będą miały podniesioną pensję o 10%, natomiast osoby zarabiające powyżej 1500, obniżoną o 10%. Każda przeprowadzona zmiana zostanie wypisana na ekran. Gdyby nie wypisywanie komunikatów, zadanie dałoby się wykonać bez użycia kursora, przy pomocy dwóch instrukcji UPDATE.

DECLARE kurs CURSOR FOR SELECT empno, ename, sal FROM emp
DECLARE @empno INT, @ename VARCHAR(10), @sal DECIMAL(8,2)
OPEN kurs
FETCH NEXT FROM kurs INTO @empno, @ename, @sal
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @sal < 1000 BEGIN
      SET @sal = @sal * 1.1
      UPDATE emp SET sal = @sal WHERE empno = @empno
      PRINT 'Zwiększono: ' + @ename + ' ' + CONVERT(VARCHAR,@sal)
   END
   IF @sal > 1500 BEGIN
      SET @sal = @sal * 0.9
      UPDATE emp SET sal = @sal WHERE empno = @empno
      PRINT 'Zmniejszono: ' + @ename + ' ' + CONVERT(VARCHAR,@sal)
   END
   FETCH NEXT FROM kurs INTO @empno, @ename, @sal
END
CLOSE kurs
DEALLOCATE kurs

Zwróćmy uwagę na klauzulę WHERE instrukcji UPDATE. Mogłoby się wydawać, że skoro kursor znajduje się na konkretnym wierszu, instrukcja UPDATE „wie”, że ten wiersz należy modyfikować. Tak się jednak nie stanie. UPDATE działa tutaj w typowy sposób, a więc bez warunku WHERE zmodyfikuje wszystkie rekordy. Dlatego w każdej modyfikacji wykonywanej w kursorze należy umieścić warunek, najlepiej dotyczący klucza głównego (w tym przykładzie empno = @empno).

Istnieje alternatywna składnia rozwiązująca powyższy problem. Możemy napisać:

UPDATE emp SET sal = @sal WHERE CURRENT OF kurs

CURRENT OF może być użyte zarówno w instrukcji UPDATE, jak i DELETE.

Rozwiązanie powyższego przykładu można zoptymalizować. Zauważmy, że interesują nas wyłącznie pracownicy o pensji mniejszej niż 1000 oraz większej niż 1500. Warto więc już przy deklaracji kursora wstępnie przefiltrować rekordy. Oto rozwiązanie zoptymalizowane:

DECLARE kurs CURSOR FOR SELECT empno, ename, sal FROM emp WHERE sal < 1000 OR sal > 1500
DECLARE @empno INT, @ename VARCHAR(10), @sal DECIMAL(8,2)
OPEN kurs
FETCH NEXT FROM kurs INTO @empno, @ename, @sal
WHILE @@FETCH_STATUS = 0
BEGIN
   IF @sal < 1000 BEGIN
      SET @sal = @sal * 1.1
      UPDATE emp SET sal = @sal WHERE CURRENT OF kurs
      PRINT 'Zwiększono: ' + @ename + ' ' + CONVERT(VARCHAR,@sal)
   END ELSE BEGIN
      SET @sal = @sal * 0.9
      UPDATE emp SET sal = @sal WHERE CURRENT OF kurs
      PRINT 'Zmniejszono: ' + @ename + ' ' + CONVERT(VARCHAR,@sal)
   END
   FETCH NEXT FROM kurs INTO @empno, @ename, @sal
END
CLOSE kurs
DEALLOCATE kurs

4. Kursory w PL/SQL

W serwerze Oracle kursorów używamy w bardzo podobny sposób, aczkolwiek istnieją dosyć duże różnice w składni w stosunku do Transact-SQL. Istotną różnicą i bardzo wygodną dla programisty konstrukcją są zmienne typu wierszowego, np.:

wiersz emp%ROWTYPE;

W ten sposób zadeklarujemy złożoną zmienną, posiadającą taką samą strukturę, co cały wiersz tabeli „emp”. Zamiast nazwy tabeli możemy podać nazwę zadeklarowanego uprzednio kursora. Dzięki temu nie musimy deklarować wielu zmiennych dla instrukcji FETCH.

W podobny sposób możemy deklarować zmienne takiego typu jak wybrana kolumna tabeli, np.:

pensja emp.sal%TYPE;

Oto rozwiązanie zadania modyfikującego pensje pracowników, wykonane w PL/SQL:

DECLARE
   CURSOR kurs IS SELECT empno, ename, sal FROM emp WHERE sal<1000 OR sal>1500;
   wiersz kurs%ROWTYPE;
   pensja emp.sal%TYPE;
BEGIN
   OPEN kurs;
   LOOP
      FETCH kurs INTO wiersz;
      EXIT WHEN kurs%NOTFOUND;
      IF wiersz.sal < 1000 THEN
         pensja := wiersz.sal * 1.1;
         UPDATE emp SET sal = pensja WHERE empno = wiersz.empno;
         DBMS_OUTPUT.PUT_LINE('Zwiekszono ' || wiersz.ename || ' ' || pensja);
      ELSIF wiersz.sal > 1500 THEN
         pensja := wiersz.sal * 0.9;
         UPDATE emp SET sal = pensja WHERE empno = wiersz.empno;
         DBMS_OUTPUT.PUT_LINE('Zmniejszono ' || wiersz.ename || ' ' || pensja);
     END IF;
   END LOOP;
   CLOSE kurs;
END;