DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica 7 – Cursors amb paràmetres

En aquesta pràctica, aplicareu els vostres coneixements sobre l’ús de cursors amb paràmetres per processar diverses files sobre múltiples taules.

Per fer aquests exercicis cal tenir les següents taules creades:

CREATE OR REPLACE TABLE EMPLEATS AS SELECT * FROM empresa.EMPLOYEES;
CREATE OR REPLACE TABLE DEPARTAMENTS AS SELECT * FROM empresa.DEPARTMENTS;

ALTER TABLE EMPLEATS ADD STARS VARCHAR(50);

Canvieu el delimitador per a treballar millor (si ho feu des de la shell)

DELIMITER //

Es recomana, per possibles errades, treballar amb una transacció

START TRANSACTION //
SAVEPOINT P1 //

i així, en qualsevol moment poder desfer el que s’ha fet

ROLLBACK TO P1 //

1. Utilitzeu un cursor per recuperar, de la taula DEPARTAMENTS, el department_id i el department_name d’aquells departaments que el department_id sigui inferior a 100. Passeu el número de departament a un altre cursor per recuperar els empleats de la taula EMPLEATS: last_name, job_id, hire_date i salary d'aquells empleats amb employee_id menor de 120 i que treballen en aquest departament.

   Si un departament no té empleats que compleixen aquestes condicions, també ha d’aparèixer en el llistat.

       Dept Núm: 10 Dept Nom: Administration
       Dept Núm: 20 Dept Nom: Marketing
       Dept Núm: 50 Dept Nom: Shipping
       Dept Núm: 60 Dept Nom: IT
       Dept Núm: 80 Dept Nom: Sales
           Hunold IT_PROG 1990-01-03 9000.00
           Ernst IT_PROG 1991-05-21 6000.00
           Lorentz IT_PROG 1999-02-07 4200.00
       Dept Núm: 90 Dept Nom: Executive
           Hunold IT_PROG 1990-01-03 9000.00
           Ernst IT_PROG 1991-05-21 6000.00
           Lorentz IT_PROG 1999-02-07 4200.00
   

   Possible solució amb FOR

   --
   -- Exercici 1 amb FOR
   --
   BEGIN NOT ATOMIC
       DECLARE c_depts CURSOR FOR
           SELECT * FROM DEPARTAMENTS WHERE department_id < 100;
       DECLARE c_emps CURSOR(p_dept TYPE OF DEPARTAMENTS.department_id) FOR
           SELECT * FROM EMPLEATS
           WHERE employee_id < 120 AND department_id < p_dept;
   
       FOR r_dept IN c_depts DO
           SELECT CONCAT_WS(' ', 'Dept Núm:', r_dept.department_id,
                               'Dept Nom:', r_dept.department_name);
           FOR r_emp IN c_emps(r_dept.department_id) DO
               SELECT CONCAT_WS(' ', '    ', r_emp.last_name, r_emp.job_id,
                                   r_emp.hire_date, r_emp.salary);
           END FOR;
       END FOR;
   END //
   

   Possible solució amb LOOP i CURSOR

   --
   -- Exercici 1 amb LOOP i CURSOR
   --
   BEGIN NOT ATOMIC
       DECLARE v_fiD, v_fiE BOOLEAN DEFAULT FALSE; 
       DECLARE c_depts CURSOR FOR
           SELECT * FROM DEPARTAMENTS WHERE department_id < 100;
       DECLARE c_emps CURSOR(p_dept TYPE OF DEPARTAMENTS.department_id) FOR
           SELECT * FROM EMPLEATS
           WHERE employee_id < 120 AND department_id < p_dept;
       DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
                       SET v_fiD = TRUE;
       BEGIN
           DECLARE r_dept ROW TYPE OF c_depts;
           DECLARE r_emp ROW TYPE OF c_emps;
           OPEN c_depts;
           FETCH c_depts INTO r_dept;
           l_depts: LOOP
               IF v_fiD THEN LEAVE l_depts; END IF;
               SELECT CONCAT_WS(' ', 'Dept Núm:', r_dept.department_id,
                                   'Dept Nom:', r_dept.department_name);
               BEGIN
               DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
                       SET v_fiE = TRUE;
               SET v_fiE = FALSE;
               OPEN c_emps(r_dept.department_id);
               FETCH c_emps INTO r_emp;
               l_emps: LOOP
                   IF v_fiE THEN LEAVE l_emps; END IF;
                   SELECT CONCAT_WS(' ', '    ', r_emp.last_name, r_emp.job_id,
                                       r_emp.hire_date, r_emp.salary);
                   FETCH c_emps INTO r_emp;
               END LOOP;
               CLOSE c_emps;
               END;
           FETCH c_depts INTO r_dept;
           END LOOP;
           CLOSE c_depts;
       END;
   END //
   

2. Modifiqueu el codi de la pràctica 4 exercici 4 per tractar tots els empleats de la taula EMPLEATS. Incorporeu un cursor amb la funcionalitat FOR UPDATE en la SELECT.

   1. Ara sí que et deixem utilitzar la funció REPEAT o LPAD o RPAD.

   2. Executeu el bloc anònim modificat.

   3. Comproveu que el bloc anònim ha funcionat correctament.

   4. Recordeu-vos d’afegir el camp STARS, VARCHAR(50) si no el teniu a la taula EMPLEATS.

      ALTER TABLE EMPLEATS ADD STARS VARCHAR(50);
      

   Possible solució amb FOR

   DELIMITER //
   --
   -- Exercici 2 amb FOR
   --
   BEGIN NOT ATOMIC
   DECLARE v_asteriscs TYPE OF EMPLEATS.stars;
   FOR r_emp IN (SELECT * FROM EMPLEATS FOR UPDATE) DO
       SET v_asteriscs = REPEAT('*', TRUNCATE(r_emp.salary / 1000, 0));
       UPDATE EMPLEATS
       SET stars = v_asteriscs
       WHERE employee_id = r_emp.employee_id;
   END FOR;
   END //
   

   Possible solució amb LOOP i CURSOR

   --
   -- Exercici 2 amb LOOP i CURSOR
   --
   BEGIN NOT ATOMIC
   DECLARE v_asteriscs TYPE OF EMPLEATS.stars;
   DECLARE c_emps CURSOR FOR SELECT * FROM EMPLEATS FOR UPDATE;
   BEGIN
       DECLARE v_fi BOOLEAN DEFAULT FALSE;
       DECLARE r_emp ROW TYPE OF c_emps;
       DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_fi = TRUE;
       OPEN c_emps;
       FETCH c_emps INTO r_emp;
       l_emps: LOOP
       IF v_fi THEN LEAVE l_emps; END IF;
       SET v_asteriscs = REPEAT('*', TRUNCATE(r_emp.salary / 1000, 0));
       UPDATE EMPLEATS
           SET stars = v_asteriscs
           WHERE employee_id = r_emp.employee_id;
       FETCH c_emps INTO r_emp;
       END LOOP;
       CLOSE c_emps;
   END;
   END //