Over het statement CURSOR

Gepubliceerd op 29/3/2013

Door gebruik te maken van een CURSOR in SQL kun je rij voor rij door een data set lopen. Maar als je denkt dat je een CURSOR nodig hebt, stop dan even en denk er nog eens goed over na. Meestal is het gebruiken van een cursor namelijk niet de meest efficiënte oplossing. Lees bijvoorbeeld eens de volgende specificatie: "doorloop de verhuistabel per klant gesorteerd op invoerdatum, zolang de status 'P' is, moet het veld X leeggemaakt worden. De eerste keer dat de status 'A' wordt, moet het veld X ongewijzigd blijven en in alle daarop volgende records stel je X gelijk aan 10".

 

Dat klinkt als een prima kandidaat voor een cursor (sorteer de records op invoerdatum, doorloop ze één voor één en bekijk elke keer de waarde van het statusveld). Maar als er erg veel records in de tabel staan, is één voor één doorlopen een enorm tijdrovende klus. Je kunt dit ook oplossen met twee UPDATE statements:

UPDATE Klant
SET X = NULL
WHERE Status = 'P'

en

UPDATE Verhuizing
SET X = 10
WHERE Status = 'A'
AND Invoerdatum <> (SELECT MIN(Invoerdatum)                    FROM Verhuizing sub                    WHERE sub.KlantID = Verhuizing.KlantID
                  
AND Status = 'A')

Vooral als er een index staat op Status en Invoerdatum, is dit veel efficiënter en het levert hetzelfde resultaat op!

 

Je moet altijd proberen "set based" te blijven denken. Dus niet één voor één de records bekijken maar een set proberen te definiëren van "gelijkvormige" records waarop dezelfde actie verricht moet worden. Ook al heb je dan meerdere statements nodig, het is efficiënter dan rij voor rij de data bekijken. SQL is nu eenmaal geoptimaliseerd voor het werken met data sets.

 

Maar soms is het werken met cursors toch nodig en/of efficiënter, vandaar dat ik hier een voorbeeldje uitwerk.

 

Stel we hebben een tabel met artikelen die elk een bepaald gewicht hebben.

We willen deze artikelen zo efficiënt mogelijk in twee postpakketten stoppen, maar elk van de pakketten heeft een maximum gewicht van 50.

 

De twee pakketten definiëren we als twee tabelvariabelen en het maximum gewicht stoppen we ook in een variabele. Verder definiëren we nog twee variabelen om de gegevens die we ophalen uit de Artikel tabel op te slaan.

DECLARE @Pakket1 TABLE(ArtikelID INT NOT NULL, Gewicht INT NOT NULL)
DECLARE @Pakket2 TABLE(ArtikelID INT NOT NULL, Gewicht INT NOT NULL)
DECLARE @MaxGewicht INT = 50
DECLARE @ArtikelID INT
DECLARE
@Gewicht INT

We definiëren als volgt een cursor om de artikelen te doorlopen:

DECLARE cArtikel CURSOR
   FOR SELECT
ArtikelID
      
, Gewicht
      
FROM Artikel
      
ORDER BY Gewicht DESC

Om een cursor te gebruiken moet je hem eerst openen:

OPEN cArtikel

De cursor is nu klaar voor gebruik, maar je hebt nog geen rij opgehaald. Dat doe je met:

 

FETCH NEXT FROM cArtikel
INTO @ArtikelID, @Gewicht

We halen met dit commando de eerste rij op en stoppen de waarden van ArtikelID en Gewicht in de variabelen @ArtikelID en @Gewicht.

 

We moeten nu door alle artikelen heen lopen totdat alle artikelen verdeeld zijn óf tot beide pakketten tot hun maximum gewicht gevuld zijn.

 

We gebruiken daarvoor een WHILE-loop. Om door de cursor heen te lopen tot alle rijen opgehaald zijn gebruik je:

WHILE @@FETCH_STATUS = 0
BEGIN
  
  
-- doe je acties

  
FETCH NEXT FROM cArtikel
  
INTO @ArtikelID, @Gewicht
END

De variabele @@FETCH_STATUS wordt -1 zodra het FETCH NEXT commando voorbij de laatste rij in de cursor komt en blijft 0 zolang er succesvol rijen opgehaald worden.

 

Verder moeten we ook checken of er überhaupt nog ruimte is in de pakketten, dus we breiden het commando uit met:

WHILE @@FETCH_STATUS = 0
  
AND (ISNULL((SELECT SUM(Gewicht) FROM @Pakket1),0) < @MaxGewicht
      
OR ISNULL((SELECT SUM(Gewicht) FROM @Pakket2),0) < @MaxGewicht)

Binnen de WHILE-loop gaan we kijken of het opgehaalde artikel nog in een pakket past en stoppen het artikel daar eventueel in:

IF @Gewicht + ISNULL((SELECT SUM(Gewicht) FROM @Pakket1),0) <= @MaxGewicht
      
INSERT INTO @Pakket1
      
(ArtikelID, Gewicht)
      
VALUES (@ArtikelID, @Gewicht)
  
ELSE IF @Gewicht + ISNULL((SELECT SUM(Gewicht) FROM @Pakket2),0) <= @MaxGewicht
      
INSERT INTO @Pakket2
      
(ArtikelID, Gewicht)
      
VALUES (@ArtikelID, @Gewicht)

En daarna halen we dus weer de volgende rij op:

   FETCH NEXT FROM cArtikel
  
INTO @ArtikelID, @Gewicht

Vergeet niet om na de WHILE loop de cursor netjes af te sluiten:

CLOSE cArtikel
DEALLOCATE cArtikel

Anders krijg je bij het opnieuw uitvoeren van de code foutmeldingen als:

Msg 16915, Level 16, State 1, Line 12

A cursor with the name 'cArtikel' already exists.

Msg 16905, Level 16, State 1, Line 18

The cursor is already open.

Voor de volledigheid hier de hele code nog eens achter elkaar:

-- Variabelen voor de twee pakketen
DECLARE @Pakket1 TABLE(ArtikelID INT NOT NULL, Gewicht INT NOT NULL)
DECLARE @Pakket2 TABLE(ArtikelID INT NOT NULL, Gewicht INT NOT NULL)
-- Variable voor het maximum gewicht van een pakket
DECLARE @MaxGewicht INT = 50
-- Variabelen voor de gegevens van een artikel
DECLARE @ArtikelID INT
DECLARE
@Gewicht INT

-- Met deze syntax definieren we een cursor
DECLARE cArtikel CURSOR
   FOR SELECT
ArtikelID
      
, Gewicht
      
FROM Artikel
      
ORDER BY Gewicht DESC

-- Eerst een cursor altijd openen
OPEN cArtikel

-- De eerste rij ophalen en de gegevens in de variabelen stoppen
FETCH NEXT FROM cArtikel
INTO @ArtikelID, @Gewicht

-- Zolang er gegevens opgehaald kunnen worden
-- en er nog iets mee gedaan kan worden
WHILE @@FETCH_STATUS = 0
  
AND (ISNULL((SELECT SUM(Gewicht) FROM @Pakket1),0) < @MaxGewicht
      
OR ISNULL((SELECT SUM(Gewicht) FROM @Pakket2),0) < @MaxGewicht)
BEGIN
  
-- controleer of het nog in Pakket1 past
  
IF @Gewicht + ISNULL((SELECT SUM(Gewicht) FROM @Pakket1),0) <= @MaxGewicht
      
INSERT INTO @Pakket1  -- stop het artikel in Pakket1
      
(ArtikelID, Gewicht)
      
VALUES (@ArtikelID, @Gewicht)
  
-- of misschien in Pakket2?
  
ELSE IF @Gewicht + ISNULL((SELECT SUM(Gewicht) FROM @Pakket2),0) <= @MaxGewicht
      
INSERT INTO @Pakket2
      
(ArtikelID, Gewicht)
      
VALUES (@ArtikelID, @Gewicht)

  
-- Haal het volgende artikel op
  
FETCH NEXT FROM cArtikel
  
INTO @ArtikelID, @Gewicht
END -- einde van de WHILE loop

-- Toon de resultaten
SELECT ArtikelID
      
, Gewicht
      
FROM Artikel
      
ORDER BY Gewicht DESC

SELECT
* FROM @Pakket1

SELECT * FROM @Pakket2

-- Sluit de cursor en ruim hem netjes op
CLOSE cArtikel
DEALLOCATE cArtikel

Dit resulteert in:

 

Terug naar Artikelen

Stuur een e-mail