5. Cursors

Els cursors s'utilitzen per a recórrer un a un els registres del resultat d'una SELECT per a fer alguna operació amb eixos valors. El mode d'operació és:

Ordre de les declaracions

Cal respectar l'ordre de les declaracions:

1. Variables i condicions
2. Cursors
3. Handlers

La SELECT del cursor no pot contindre la instrucció INTO.

5.1. Mode d'operació

5.1.1. Declarar el cursor

Amb un nom i una select associada:

DECLARE c_alumnes CURSOR FOR
    SELECT camp1, camp2, camp3
    FROM alumnes
    WHERE curs = '1DAM';

5.1.2. Declarar un handler

Per a saber quan eixim del cursor.

Amb la variable @acabat farem la condició per a eixir del bucle que recorrerà el cursor.

DECLARE CONTINUE HANDLER FOR
NOT FOUND
SET @acabat = TRUE;

5.1.3. Recórrer els elements del cursor

a) Obrir el cursor:

OPEN c_alumnes;

Executa la SELECT del cursor (sense mostrar res) i deixa preparades les dades obtingudes en la consulta per a ser processades posteriorment.

b) Bucle on, en cada iteració, obtindrem els valors dels camps de la SELECT en cada registre:

Si el cursor pot retornar més d'una fila, hem de recórrer-ho amb un WHILE-DO, REPEAT-UNTIL, etc., on la condició d'acabar serà la que haurà activat el handler quan ja no queden files per llegir en el cursor.

-- INICI BUCLE
FETCH c_alumnes INTO var1, var2, var3
-- Ací faríem operacions amb eixes variables
-- FI BUCLE

FETCH obté el següent registre disponible. Si no existeix, donarà l'error de "sense dades", amb el valor SQLSTATE 02000 (zero files seleccionades o processades).

c) Tancar el cursor:

CLOSE c_alumnes;

Serveix per a alliberar recursos. Si no el tanquem nosaltres, es tanca automàticament quan s'arriba al final del bloc BEGIN...END on està declarat. Però és convenient tancar-lo.

Els cursors de MySQL són només de lectura (no podem modificar els valors de la taula ni esborrar-los) i només es mouen cap endavant (cap al registre següent, seguint l'ORDER BY de la SELECT del cursor).

5.2. Exercici resolt: fer_quiniela

Crea la taula QUINIELES = equipc + equipf + jornada + resultat, on el camp "resultat" serà un char(1) per a guardar '1', 'x' o '2'. A continuació, crea el procediment fer_quiniela, al qual li passes com a paràmetre un número de jornada i ha d'omplir la taula de quinieles amb el resultat d'eixa jornada. Caldrà fer un cursor que recórrega els partits d'eixa jornada.

CREATE TABLE quinieles (...);

DELIMITER //

CREATE PROCEDURE fer_quiniela(j INT)
BEGIN
    -- 1r) DECLARACIÓ DE VARIABLES --------------------------
    DECLARE ec, ef CHAR(3);
    DECLARE gf, gc INT DEFAULT 0;
    DECLARE resul CHAR(1);
    DECLARE fi_cursor INT DEFAULT 0;

    -- 2n) DECLARACIÓ DEL CURSOR -----------------------------
    DECLARE c_partits CURSOR FOR
        SELECT equipc, equipf, golsc, golsf
        FROM partits
        WHERE jornada = j;

    -- 3r) DECLARACIÓ DEL HANDLER PER A EIXIR DEL CURSOR
    DECLARE CONTINUE HANDLER FOR
        NOT FOUND             -- O bé: SQLSTATE '02000'
        SET fi_cursor = 1;

    -- 4t) RECORREGUT DEL CURSOR -----------------------------
    OPEN c_partits;    -- Obrim cursor
    REPEAT
        FETCH c_partits INTO ec, ef, gc, gf;  -- Recuperem dades
        IF NOT fi_cursor THEN    -- Comprovem que no s'han acabat les files
            CASE
                WHEN gc > gf THEN SET resul = '1';
                WHEN gc = gf THEN SET resul = 'x';
                ELSE SET resul = '2';
            END CASE;
            INSERT INTO quinieles (equipc, equipf, jornada, resultat)
            VALUES (ec, ef, j, resul);
        END IF;
    UNTIL fi_cursor END REPEAT;
    CLOSE c_partits;    -- Tanquem cursor
END //

5.3. Exercicis de cursors

8. Fes la funció llistaJugadors, a la qual li passes un codi d'equip i un lloc de jugador ('porter', 'defensa', ...) i retorna una cadena de caràcters amb els noms dels jugadors d'eixe equip en eixa posició, separats per comes. Recorda que pots usar la funció concat.

Per exemple, si cridem la funció amb...

SELECT llistaJugadors('rma', 'porter');

... això mostrarà la següent línia:

9. Usant la funció anterior, mostra de cada equip els seus davanters, defenses, mitjos i porters, de forma que isca així: