Vaak zul je loops samen met SELECT queries moeten gebruiken om de door de SELECT geretourneerde gegevensverzameling te kunnen verwerken. Om dit te kunnen doen heeft men een cursor nodig.

Cursor in een database (*wikipedia): De term cursor wordt ook gebruikt om een deelverzameling rijen aan te geven, die met een SQL-statement uit een database zijn geselecteerd. Deze cursor gedraagt zich min of meer als een zelfstandige tabel, die bijvoorbeeld sequentieel kan worden verwerkt.

Cursor bij een datacontainer (*wikipedia): Een cursor is een iterator waarvan de doorlooplogica in de container opgeslagen ligt.

Cursors zijn een relatief nieuwe toevoeging aan MySQL en als zodanig, zijn ze nog steeds onderworpen aan enkele belangrijke beperkingen:

  • MySQL cursors zijn forward-only; ze kunnen ze niet worden gebruikt om naar een vorige record terug te keren.
  • MySQL cursors zijn alleen-lezen; ze kunnen alleen worden gebruikt om waarden uit een reeks resultaten te lezen, ze kunnen worden gebruikt om te schrijven naar bestaande waarden of bestaande waarden bij te werken.
  • Bij gebruik in transacties, worden MySQL cursors automatisch worden gesloten na een COMMIT.

Cursors worden geïnitialiseerd met een DECLARE statement, net als variabelen (hoewel cursor aangiften moeten komen nadat variabele declaraties). Elke cursor wordt geïdentificeerd met een unieke naam en verbonden met een bepaalde SELECT-instructie. Hier is een voorbeeld:

DECLARE mycur CURSOR FOR
SELECT AirportName, NumTerminals
FROM airport;

Zodra de cursor is gedeclareerd, biedt MySQL de OPEN, FETCH en CLOSE commando’s om de reseultaatset te doorlopen die geretourneerd wordt door de SELECT-instructie van de cursor.

  • Het OPEN commando opent de cursor voor het lezen.
  • Het FETCH commando leest de inhoud van het huidige record in één of meer variabelen en verplaatst de cursor dan naar het volgende record. Om een hele reeks resultaten te verwerken, is het noodzakelijk om de FETCH opdracht aan te roepen zo lang er records in het resultaatset zijn. Dit wordt typisch bereikt met een lus.
  • Het CLOSE commando sluit de cursor. Cursors worden ook automatisch gesloten als de opgeslagen routine waarin ze werden geïnitialiseerd ook wordt beëindigt.

Hier is een voorbeeld van een loop-en-cursor combinatie in een opgeslagen procedure: met behulp van een cursor die de lijst doorloopt en markeert de luchthaven van elke luchthaven als ‘groot’ of ‘klein’, afhankelijk van het aantal terminals dat het heeft. Deze procedure declareert een cursor, die actief is op het resultaatset die geretourneerd is door de SELECT-instructie. Deze cursor is geopend voor het lezen met het OPEN-commando, en vervolgens wordt een LOOP gebruikt om te itereren over de resultaatset. Met de FETCH-instructie wordt elke record uit de collectie op een sequentiële wijze geretourneerd. Een voorwaardelijk IF-test wordt vervolgens gebruikt om het aantal terminals te controleren en elke luchthaven respectievelijk te markeren als ‘groot’ of ‘klein’. Zodra de lus eindigt, wordt het CLOSE commando gebruikt om de cursor te sluiten.

MSSQL

CREATE PROCEDURE get_airport_size
AS
DECLARE @a VARCHAR(255), @b INT, @x INT
DECLARE c CURSOR FOR
SELECT AirportName, NumTerminals FROM airport;
OPEN c;    
    size: 
    FETCH c INTO @a, @x;
    IF @x > 2
        BEGIN
            SELECT @a AS Name, 'big' AS Size;
        END
    ELSE 
        BEGIN
            SELECT @a AS Name, 'small' AS Size;
        END
    GOTO size;
CLOSE c;
DEALLOCATE x;

Command(s) completed successfully.
EXEC get_airport_size;

(Endless loop!)

MySQL

DELIMITER //
CREATE PROCEDURE get_airport_size()
BEGIN
    DECLARE a VARCHAR(255);
    DECLARE b,x INT;
    DECLARE c CURSOR FOR
    SELECT AirportName, NumTerminals FROM airport;
    OPEN c;
        size: LOOP
        FETCH c INTO a,x;
        IF x > 2
            THEN SELECT a AS Name, 'big' AS Size;
            ELSE SELECT a AS Name, 'small' AS Size;
        END IF;
        END LOOP size;
    CLOSE c;
END //

(0 row(s) affected)
CALL get_airport_size();

(Error Code: 1329 No data - zero rows fetched, selected, or processed)

Met MySQL krijgt men een foutmelding aan het einde. Deze fout treedt op, omdat de lus zoals getoond, geen uitgangsconditie bevat. Als gevolg daarvan bereikt de cursor het einde van de gegevensset en blijft het proberen records te benaderen die niet bestaan. MSSQL blijft in een oneindige lus zitten!

Het volgende voorbeeld toont hetzelfde script waarin een uitgangsconditie is verwerkt.

MSSQL

CREATE PROCEDURE [dbo].[get_airport_size]
AS
DECLARE @a VARCHAR(255), @b INT, @x INT
DECLARE c CURSOR FOR
SELECT AirportName, NumTerminals FROM airport;
OPEN c;        
FETCH c INTO @a, @x;
WHILE @@FETCH_STATUS = 0 
BEGIN
    FETCH c INTO @a, @x;
    IF @x > 2
        BEGIN
            SELECT @a AS Name, 'big' AS Size;
        END
    ELSE 
        BEGIN
            SELECT @a AS Name, 'small' AS Size;
        END
END
CLOSE c;
DEALLOCATE c;

Command(s) completed successfully.
EXEC get_airport_size;

(1 row(s) affected)

MySQL

DELIMITER //
CREATE PROCEDURE get_airport_size()
BEGIN
    DECLARE a VARCHAR(255);
    DECLARE b,x INT;
    DECLARE cursor_finished INTEGER DEFAULT 0;
    DECLARE c CURSOR FOR
        SELECT AirportName, NumTerminals FROM airport;
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET cursor_finished = 1;
    OPEN c;
        size: LOOP
        FETCH c INTO a,x;
        IF cursor_finished = 1 THEN 
            LEAVE size;
        END IF;
        IF x > 2
            THEN SELECT a AS Name, 'big' AS Size;
            ELSE SELECT a AS Name, 'small' AS Size;
        END IF;
        END LOOP size;
    CLOSE c;
END //

(0 row(s) affected)
CALL get_airport_size();

(1 row(s) returned)