ABC Kursor-ów w SQL Server

O tym dlaczego używamy kursorów, oraz dlaczego powinniśmy ich unikać pisałem tu:

Kursory – dlaczego tak je lubimy i dlaczego ich lepiej nie używajmy….

Dziś bardziej przyziemnie, bardziej praktycznie, bardziej w SSMS:

Jak napisać kursor ?

Zdefiniujmy wpierw tabelę na której będziemy pracować:

;WITH wartosci AS
(
SELECT 1 AS id, ‘pierwszy wpis’ AS opisUNION
SELECT 2 AS id, ‘drugi wpis’ AS opisUNION
SELECT 3 AS id, ‘trzeci wpis’ AS opisUNION
SELECT 4 AS id, ‘czwarty wpis’ AS opisUNION
SELECT 5 AS id, ‘pi¹ty wpis’ AS opisUNION
SELECT 6 AS id, ‘szósty wpis’ AS opis
)
SELECT id, opis
INTO dbo.przyklad
FROM wartosci

Przykład najprostszego kursora:

/*deklarujemy kursor*/
DECLARE moj_kursor CURSOR FOR
/*deklarujemy zrodlo kursora*/
SELECT id, opis FROM dbo.przyklad
/*otwieramy kursor*/
OPEN moj_kursor
/*przypisujemy pierwszy wiersz do zmiennych o typach zgodnych z kolumnami w zrodle*/
FETCH NEXT FROM moj_kursor INTO @my_id, @my_opis
/*jezeli przypisanie sie powiodlo wchodzimy w petle*/
WHILE @@FETCH_STATUS = 0
BEGIN
/*przykladowa czynnosc na danych*/
  SET @liczba_wystapien += 1;
/*przypisanie kolejnego wiersza*/ 
  FETCH NEXT FROM moj_kursor INTO @my_id, @my_opis
END
/*zamkniecie kursora*/
CLOSE moj_kursor
/*zwolnienie kursora*/
DEALLOCATE moj_kursor

Na co należy zwrócić uwagę?

Dla tak banalnego przykładu ważne jest aby zamknąć kursor.
Przyjrzyjmy się kursorowi po drobnej modyfikacji:

DECLARE moj_kursor CURSOR FOR
SELECT id, opis FROM dbo.przyklad
OPEN moj_kursor
FETCH NEXT FROM moj_kursor INTO @my_id, @my_opis
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @liczba_wystapien += 1;
  FETCH NEXT FROM moj_kursor INTO @my_id, @my_opis
END
      return;
CLOSE moj_kursor
DEALLOCATE moj_kursor

Za pierwszym razem kursor wykonał się poprawnie:
Command(s) completed successfully.
Wykonajmy jeszcze raz ten sam kuror:
Msg 16915, Level 16, State 1, Line 6
A cursor with the name ‘moj_kursor’ already exists.
Msg 16905, Level 16, State 1, Line 7
The cursor is already open.

Co tak właściwie się stało? Przy pierwszym wywołaniu kursora wyszliśmy z niego szybciej, niż powinniśmy. Generalnie dla SQL Server nie jest to problem. Problem pojawił się gdy ponownie chcieliśmy otworzyć kursor. SQL zaprotestował bo w pamięci wciąż ma niezwolnioną poprzednią instancję tego samego kursora.

Niezamkniety kursor będzie bruździł w ramach sesji, która go nie zamknęła. Nie pozwoli na otworzenie kolejnego kursora o tej samej nazwie. To ważne, b programiści mają w zwyczaju używać CTRL+C i CTRL+V do sprawdzonych i przetestowanych bloków kodu. Może się więc okazać że wszystkie kursory w bazie nazywają się „moj_kursor” i wszystkie one nie zadziałają w omawianym przpadku.

Trzeba też zwrócić uwagę że o ile podany przykład jest mocno akademicki, o tyle w pętli mogą występować złożone operacje, które spowodują wyjątek. Nieobsłuzony wyjątek zawędruje aż do użytkownika, obsłuzony w sekcję CATCH. NIezależnie jaki przypadek nastąpi to niewykonanie

CLOSE moj_kursor
DEALLOCATE moj_kursor

spowoduje problemy w bazie danych.

Co zrobić jeśli już mamy taki przypadek?

Właściwie z własnego doświadczenia znam dwa przypadki. Pierwszy to zwolnienie kursora, ale co ważne, z sesji która tego do tej pory nie zrobiła. Druga metoda, bardzo brutalna, to restart serwera.

EDIT: jeśli ktoś zna inne sposoby proszę o podanie w komentarzu. Nobody`s perfect!

Jak chronić siebie i bazę przed probelemem niezwolnionego kursora?

Po pierwsze przyjąć zasadę że nie ma returnów wewnątrz kursora.
Po drugie otoczyć kursor blokiem TRY a w CATCH niezleżnie od obslugi samego błędu dopisać CLOSE i DEALLOCATE niezwolnionego kursora.

Sądzę, że wiedza tu zawarta stanowi przyzwoitą podstawę do pracy z kursorami. Oczywiście to absolutne ABC tematu. Osobny temat to usprawnienie kursorów tak, by były one szybsze i mniej utrudnialy pracę innym obiektom w bazie danych. Przypuszczam nawet, że to dobry temat na inny artykuł:”DEF Kursor-ów w SQL Server”

pozdrawiam,

Podziel się na:
  • Google Bookmarks
  • RSS
  • Dodaj do ulubionych
  • email
  • Facebook
  • Twitter
  • Blogger.com
  • LinkedIn
  • Gadu-Gadu Live