DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica 6 – Cursors

En aquesta pràctica, aplicareu els vostres coneixements sobre l’ús de cursors per processar una sèrie de files d'una taula i omplir una altra taula amb els resultats.

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 TOP_DOGS(salary DECIMAL(8,2));

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. Creeu un bloc anònim que determini els millors empleats pel que fa al seu sou.

   1. Declareu una variable @n, per indicar les n persones amb millor salari de taula EMPLEATS. Per veure els cinc empleats millor retribuïts, assignarem SET @n=5.

   2. Enregistreu, a la taula TOP_DOGS, els salaris dels n primers empleats de la taula EMPLEATS, sense duplicats en els sous. Si dos empleats cobren el mateix sou, es guarda una sola vegada.

   3. Proveu com es comportaria en els casos extrems, com ara @n = 0 o n més gran que el nombre d'empleats a la taula EMPLEATS (SET @n=30). Cal buidar la taula TOP_DOGS després de cada prova o abans de començar a omplir-la. TRUNCATE TABLE TOP_DOGS;

   4. Els cinc salaris més alts de la taula EMPLEATS (n=5) són:

      +----------+
      | salary   |
      +----------+
      | 24000.00 |
      | 17000.00 |
      | 13000.00 |
      | 12000.00 |
      | 11000.00 |
      +----------+
      

   Possible solució amb FOR

   --
   -- Exercici 1 - FOR
   --
   DELIMITER //
   SET @n = 5 //
   BEGIN NOT ATOMIC
       DECLARE v_q INT DEFAULT 0;
       TRUNCATE TABLE TOP_DOGS;
       iteracio: FOR r_sous IN
       (SELECT DISTINCT SALARY FROM EMPLEATS ORDER BY SALARY DESC)
       DO
           SET v_q = v_q + 1;
           IF v_q >= @n THEN LEAVE iteracio; END IF;
           INSERT INTO TOP_DOGS VALUES(r_sous.salary);
       END FOR;
   END //
   SELECT * FROM TOP_DOGS //
   

   Possible solució amb LOOP i CURSOR

   --
   -- Exercici 1 - CURSOR
   --
   DELIMITER //
   SET @n = 5 //
   BEGIN NOT ATOMIC
       DECLARE v_fi BOOLEAN DEFAULT FALSE;
       DECLARE v_q INT DEFAULT 0;
       DECLARE c1 CURSOR FOR
           SELECT DISTINCT SALARY FROM EMPLEATS ORDER BY SALARY DESC;
       BEGIN
           DECLARE r_c1 ROW TYPE OF c1;
           DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
                   SET v_fi = TRUE;
           TRUNCATE TABLE TOP_DOGS;
           OPEN c1;
           FETCH c1 INTO r_c1;
           l_iteracio: LOOP
               IF v_fi THEN LEAVE l_iteracio; END IF;
               INSERT INTO TOP_DOGS VALUES(r_C1.salary);
               SET v_q = v_q + 1;
               IF v_q >= @n THEN LEAVE L_iteracio; END IF;
               FETCH c1 INTO r_c1;
           END LOOP;
           CLOSE c1;
       END;
   END //
   SELECT * FROM TOP_DOGS //
   

   1. Creeu un bloc de codi anònim que faci el següent:

   2. Declareu una variable d’usuari per emmagatzemar l'ID del departament (@dep_id).

   3. Escriviu un bloc anònim, per recuperar el last_name, salary i manager_id dels empleats que treballen en aquest departament.

   4. Si el sou de l'empleat (salary) és inferior a 5.000 i si l'identificador del gerent (manager_id) és 101 o 124, mostreu el missatge: <<last_name>> tindrà un augment.

      En cas contrari, mostreu el missatge <<lastname>> no tindrà augment.

   5. Proveu el bloc de codi en els casos següents: 10, 20, 50, 80

      SET @dep_id = 10 //
          Whalen tindrà un augment
      SET @dep_id = 20 //
          Hartstein no tindrà un augment
          Fay no tindrà un augment
      SET @dep_id = 50 //
          Mourgos no tindrà un augment
          Rajs tindrà un augment
          Davies tindrà un augment
          Matos tindrà un augment
          Vargas tindrà un augment
      SET @dep_id = 80 //
          Zlotkey no tindrà un augment
          Abel no tindrà un augment
          Taylor no tindrà un augment
      

   Possible solució amb FOR

   --
   -- Exercici 2 - FOR
   --
   DELIMITER //
   SET @dep_id = 80 //
   BEGIN NOT ATOMIC
       FOR r_c1 IN (SELECT last_name, salary, manager_id
                   FROM EMPLEATS
                   WHERE department_id = @dep_id)
       DO
           IF r_c1.salary < 5000 AND r_c1.manager_id IN (101, 124) THEN
               SELECT CONCAT_WS(' ', r_c1.last_name, 'tindrà un augment');
           ELSE
               SELECT CONCAT_WS(' ', r_c1.last_name, 'no tindrà un augment');
           END IF;
       END FOR;
   END //
   

   Possible solució amb LOOP i CURSOR

   --
   -- Exercici 2 - CURSOR
   --
   DELIMITER //
   SET @dep_id = 10 //
   BEGIN NOT ATOMIC
       DECLARE v_fi BOOLEAN DEFAULT FALSE;
       DECLARE c1 CURSOR FOR
           SELECT last_name, salary, manager_id
           FROM EMPLEATS
           WHERE department_id = @dep_id;
       BEGIN
           DECLARE r_c1 ROW TYPE OF c1;
           DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
                   SET v_fi = TRUE;
   
           OPEN c1;
           FETCH c1 INTO r_c1;
           l_iteracio: LOOP
               IF v_fi THEN LEAVE l_iteracio; END IF;
               IF r_c1.salary < 5000 AND r_c1.manager_id IN (101, 124) THEN
                   SELECT CONCAT_WS(' ', r_c1.last_name, 'tindrà un augment');
               ELSE
                   SELECT CONCAT_WS(' ', r_c1.last_name, 'no tindrà un augment');
   
               END IF;
               FETCH c1 INTO r_c1;
           END LOOP;
           CLOSE c1;
       END;
   END //