DAM - DAW - MP 0484 Bases de Dades - NF4

En una base de dades, una de les sentències més utilitzades és SELECT. Després d'una sentència de selecció de files el que necessitem és anar recorrent i tractant cada una d'aquestes files. L'eina que ens permet fer-ho és el CURSOR.

Un cursor és una estructura que ens permet recórrer tots els registres de forma seqüencial i processar cada una de les files retornades per una SELECT.

Declaració de cursors

Els Cursors podem declarar-los, de forma explícita, amb la clàusula DECLARE CURSOR.

Per recórrer tots els registres que retorna el CURSOR, el més senzill és utilitzar l'estrutura ...

FOR pels Cursors

La sentència FOR ens permet recórrer tots els registres d'una consulta SELECT de la forma més snezilla.

Els cursors poden ser explícits o implícits i encara se simplifica més el seu ús.

Cursor explícit

[begin_label:]
FOR record_name IN cursor_name [(cursor_actual_parameter_list)]
DO statement_list
END FOR [ end_label ];

Cursor implícit

[begin_label:]
FOR record_name IN ( select_statement )
DO statement_list
END FOR [ end_label ];

Començarem per la forma més simple.

FOR amb cursor implícit

BEGIN NOT ATOMIC
    FOR r_jobs IN (SELECT * FROM empresa.jobs) DO
        SELECT r_jobs.job_title;
    END FOR;
END;

En aquest cas no cal declarar res de res.

S'executa la SELECT i tenim una variable de tipus ROW TYPE anomenada r_jobs on a cada iteració hi haurà un nou registre de la SELECT.

No hem hagut de declarar la variable r_jobs.

Per accedir a cadascun dels camps de la fila ho farem seguint el següent exemple: r_jobs.job_title.

FOR amb cursor explícit

BEGIN NOT ATOMIC
    DECLARE c_jobs CURSOR FOR SELECT * FROM empresa.jobs;

    FOR r_jobs IN c_jobs DO
        SELECT r_jobs.job_title;
    END FOR;
END;

En aquest cas només hem hagut de declarar el cursor c_jobs que conté la consulta.

L'avantatge dels cursors és que podem passar-li paràmetres.

No hem hagut de declarar la variable r_jobs.

Exemple amb iteracions i cursors anidats

Anem a veure un exemple on utilitzarem dos cursors. EL primer ens permetrà anar mostrants els departaments. Amb el segon, accedirem als empleats del departament. Farem l'exemple amb CURSORS explícits i implícits.

Cursors explícits

DELIMITER //
BEGIN NOT ATOMIC
    DECLARE v_quants INTEGER;
    DECLARE c_departments CURSOR FOR SELECT * FROM departments ORDER BY department_name;
    DECLARE c_employees CURSOR (p_department_id TYPE OF departments.department_id)
            FOR (SELECT * FROM employees WHERE department_id = p_department_id
                          ORDER BY LAST_NAME, FIRST_NAME, SALARY);

    FOR r_departments IN c_departments DO
        SET v_quants = 0;
        SELECT r_departments.department_name;
        SELECT REPEAT('=', LENGTH(r_departments.department_name));
        FOR r_employees IN c_employees(r_departments.department_id) DO
            SELECT CONCAT('    ', r_employees.last_name, ', ', r_employees.first_name, ' - ', r_employees.salary);
            SET v_quants = v_quants + 1;
        END FOR;
        SELECT CONCAT(v_quants, ' empleat', CASE v_quants WHEN 1 THEN '' ELSE 's' END);
        SELECT '';
    END FOR;
END;
//
DELIMITER ;

Cursors implícits

DELIMITER //
BEGIN NOT ATOMIC
    DECLARE v_quants INTEGER;
    FOR r_departments IN (SELECT * FROM departments ORDER BY department_name) DO
        SET v_quants = 0;
        SELECT r_departments.department_name;
        SELECT REPEAT('=', LENGTH(r_departments.department_name));
        FOR r_employees IN (SELECT * FROM employees
            WHERE department_id = r_departments.department_id ORDER BY LAST_NAME, FIRST_NAME, SALARY)
        DO
            SELECT CONCAT('    ', r_employees.last_name, ', ', r_employees.first_name, ' - ', r_employees.salary);
            SET v_quants = v_quants + 1;
        END FOR;
        SELECT CONCAT(v_quants, ' empleat', CASE v_quants WHEN 1 THEN '' ELSE 's' END);
        SELECT '';
    END FOR;

END;
//
DELIMITER ;

SELECT sense FOR

En el cas de no utilitzar FOR, caldrà:

• declarar les variables sobre els que recuperarem les dades de cada fila, que podran ser variables simples o compostes,

• declarar el cursor amb la consulta SELECT,

• obrir el cursor,

• anar agafant les files una a una amb la instrucció FETCH,

• controlar quan s’arriba al final de les files,

• tancar el cursor

Per controlar el final de les files només podrem fer-ho deixant que la sentència FETCH doni error, capturant aquest error, DECLARE CONTINUE HANDLER, i executant una comanda que ens permeti saber que s’ha arribat al final de les files, IF v_fi THEN.

Veurem l’exemple i més endavant explicarem el tractament dels errors amb els handlers.

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET v_fi = TRUE;

Exemple de SELECT sense FOR

No ho utilitzarem.

BEGIN NOT ATOMIC
    DECLARE v_fi BOOLEAN DEFAULT FALSE; -- Controla final files
    DECLARE r_jobs ROW TYPE OF empresa.jobs; -- On rebem les files
    DECLARE c_jobs CURSOR FOR SELECT * FROM empresa.jobs; -- SELECT
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
        SET v_fi = TRUE; -- Captura l'error ERROR 1329 (02000):
        -- No data - zero rows fetched, selected, or processed

    OPEN c_jobs;
    FETCH c_jobs INTO r_jobs;
    label_jobs: LOOP
        IF v_fi THEN
            LEAVE label_jobs;
        END IF;
        SELECT r_jobs.job_title; -- Tractem les dades
        FETCH c_jobs INTO r_jobs;
    END LOOP label_jobs;
    CLOSE c_jobs;
END;

Fixeu-vos que, per fer la mateixa feina, sense el FOR, necessitem moltes més instruccions i estructures.

Cal dir que l’estructura FOR és força nova i abans calia utilitzar la fórmula que acabem de veure.