DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica 5 – Variables ROW TYPE OF

En aquesta pràctica, creareu blocs anònims que fan servir variables de tipus fila (ROW TYPE OF).

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

CREATE OR REPLACE TABLE DEPARTAMENTS AS SELECT * FROM empresa.DEPARTMENTS;
CREATE OR REPLACE TABLE PAISOS AS SELECT * FROM empresa.COUNTRIES;
SET @rownum = 0;
CREATE OR REPLACE TABLE INDEX_DEPARTAMENTS AS
  SELECT @rownum := @rownum + 1 AS comptador,
         department_id, department_name
  FROM departaments
  ORDER BY department_id;

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. Escriviu un bloc anònim per mostrar informació sobre un determinat país.

   1. Declareu una variable tipus fila basada en l'estructura de la taula PAISOS.

   2. Utilitzeu una variable d’usuari per l'identificador del país. (@country_id = 'CA')

   3. Mostreu tota la informació sobre el país amb el següent format

   Codi País: CA Nom País: Canada Regió: 2
   

   1. Executeu i proveu el bloc anònim per als ID següents: CA, DE, UK, US.
   Possible solució

   SET @country_id = 'CA' //
   BEGIN NOT ATOMIC
       DECLARE r_paisos ROW TYPE OF paisos;
       SELECT * INTO r_paisos
           FROM paisos
           WHERE country_id = @country_id;
       SELECT CONCAT_WS(' ',
                        'Codi Pais:', r_paisos.country_id,
                        'Nom Pais:', r_paisos.country_name,
                        'Regió:', r_paisos.region_id) as "Pais";
   END //
   

2. Com que no volem treballar encara amb cursors, de moment llegim files de les taules d’una en una, crearem una taula que ens servirà per anar buscant TOTS els departaments un a un. Per això crearem una taula INDEX_DEPARTAMENTS on tindrem un camp que és seqüencial i el codi de departament corresponent.

   SET @rownum = 0;
   CREATE OR REPLACE TABLE INDEX_DEPARTAMENTS AS
   SELECT @rownum := @rownum + 1 AS comptador,
           department_id, department_name
   FROM departaments
   ORDER BY department_id;
   

   1. Buscarem i emmagatzemarem en variables locals el codi menor i el major del camp comptador de la taula INDEX_DEPARTAMENTS.

   2. Amb una iteració, començant pel valor menor recuperat a l’apartat anterior i acabant pel valor major recuperat a l’apartat anterior, buscarem el camp DEPARTMENT_NAME de la taula INDEX_DEPARTAMENTS.

   3. Mostrarem tots els noms de departament un a un i caldrà obtenir una sortida semblant a

      Administration
      Marketing
      Shipping
      IT
      Sales
      Executive
      Accounting
      Contracting
      

      Possible solució - primera part

      --
      -- Exercici 2 primera part
      --
      BEGIN NOT ATOMIC
          DECLARE v_minim, v_maxim INT;
          DECLARE r_ind_dept ROW TYPE OF index_departaments;
          SELECT MIN(comptador), MAX(comptador)
              INTO v_minim, v_maxim
              FROM INDEX_DEPARTAMENTS;
          FOR v_i IN v_minim .. v_maxim DO
              -- Anem a veure quin departament volem segon l'index
              SELECT * INTO r_ind_dept
              FROM INDEX_DEPARTAMENTS WHERE comptador = v_i;
              SELECT r_ind_dept.department_name;
          END FOR;
      END //
      

   4. Ara modificarem el codi de manera que per cadascuna d’aquestes files d'INDEX_DEPARTAMENTS, anirem a buscar totes les dades del departament corresponent a la taula DEPARTAMENTS.

   5. Per cadascun dels departaments, mostrarem totes les dades amb el següent format:

      Codi Dept: 10 Nom Dept: Administration Manager: 200 Loc Id: 1700
      

   6. Sí, cal anar a buscar informació a les dues taules. Dues SELECTs o una amb JOIN però sense ROW TYPE OF

   Possible solució amb dues SELECT

   --
   -- Exercici 2 amb dues SELECTs
   --
   BEGIN NOT ATOMIC
       DECLARE v_minim, v_maxim INT;
       DECLARE r_ind_dept ROW TYPE OF index_departaments;
       DECLARE r_dept ROW TYPE OF departaments;
       SELECT MIN(comptador), MAX(comptador)
           INTO v_minim, v_maxim
           FROM INDEX_DEPARTAMENTS;
       FOR v_i IN v_minim .. v_maxim DO
           -- Anem a veure quin departament volem segon l'index
           SELECT * INTO r_ind_dept
           FROM INDEX_DEPARTAMENTS WHERE comptador = v_i;
           -- Ara que tenim el departament, anem a buscar-lo
           SELECT * INTO r_dept
           FROM DEPARTAMENTS
           WHERE department_id = r_ind_dept.department_id;
           SELECT CONCAT_WS(' ', 'Codi Dept:', r_dept.department_id,
                            'Nom Dept:', r_dept.department_name,
                            'Manager:', r_dept.manager_id,
                            'Loc Id:', r_dept.location_id);
       END FOR;
   END //
   

   Possible solució amb JOIN

   --
   -- Exercici 2 amb JOIN
   --
   BEGIN NOT ATOMIC
       DECLARE v_minim, v_maxim INT;
       DECLARE v_department_id TYPE OF departaments.department_id;
       DECLARE v_department_name TYPE OF departaments.department_name;
       DECLARE v_manager_id TYPE OF departaments.manager_id;
       DECLARE v_location_id TYPE OF departaments.location_id ;
       SELECT MIN(comptador), MAX(comptador)
           INTO v_minim, v_maxim
           FROM INDEX_DEPARTAMENTS;
       FOR v_i IN v_minim .. v_maxim DO
           -- Anem a veure quin departament volem segon l'index
           SELECT department_id, department_name,
           manager_id, location_id
           INTO v_department_id, v_department_name,
           v_manager_id, v_location_id
           FROM INDEX_DEPARTAMENTS NATURAL JOIN DEPARTAMENTS
           WHERE comptador = v_i;
           SELECT CONCAT_WS(' ', 'Codi Dept:', v_department_id,
                            'Nom Dept:', v_department_name,
                            'Manager:', v_manager_id,
                            'Loc Id:', v_location_id);
       END FOR;
   END //