DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica 8 – Excepcions

En aquesta pràctica, treballarem les excepcions per a situacions específiques.

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

CREATE OR REPLACE TABLE MISSATGES (missatge VARCHAR(2000));
CREATE OR REPLACE TABLE EMPLEATS AS SELECT * FROM empresa.EMPLOYEES;
CREATE OR REPLACE TABLE DEPARTAMENTS AS SELECT * FROM empresa.DEPARTMENTS;

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 //

Escriviu un bloc anònim per seleccionar el nom de l'empleat amb un valor salarial determinat.

Utilitzeu una variable local, @sou, per enregistrar el sou.
Si el sou introduït retorna més d'una fila, gestionar l'excepció amb un controlador d'excepcions adequat i inseriu a la taula MISSATGES el missatge “Més d'un empleat amb un sou de <salari>".
Si el salari introduït no retorna cap fila, gestioneu l'excepció amb un corresponent gestor d'excepcions i inseriu a la taula MISSATGES el missatge "Cap empleat amb un sou de <salari>". MariaDB no genera cap error en aquest cas i per tant caldrà que el generem nosaltres, comprovant si el valor d’alguna variable (no qualsevol, penseu quina) continua sense valor (si és que abans de la SELECT no tenia valor). '02000' és el NOT FOUND.
Si el salari introduït només retorna una fila, inseriu a la taula MISSATGES el missatge "<nom_empleat> <cognomEmpleat> té un sou de <salari>".
Gestioneu qualsevol altra excepció amb un controlador d'excepcions adequat i inseriu-lo al fitxer MISSATGES a la taula el missatge "Ha Succeït Un Error".
Proveu el bloc per a una varietat de casos de prova. Mostreu les files de la taula MISSATGES per comprovar si el bloc anònim s'ha executat correctament. A continuació es mostra un exemple de sortida.

+--------------------------------------+
| missatge                             |
+--------------------------------------+
| Cap empleat amb un sou de 17100      |
| Més d'un empleat amb un sou de 17000 |
| Steven King té un sou de 24000       |
+--------------------------------------+

Possible solució

DELIMITER //
SET @sou=24000 //
BEGIN NOT ATOMIC
    DECLARE v_first_name TYPE OF empleats.first_name;
    DECLARE v_last_name TYPE OF empleats.last_name;
    DECLARE v_salary TYPE OF empleats.salary;
    DECLARE EXIT HANDLER FOR NOT FOUND INSERT INTO MISSATGES(missatge) 
        VALUES (CONCAT('Cap empleat amb un sou de ', @sou));
    DECLARE EXIT HANDLER FOR SQLSTATE '42000' INSERT INTO MISSATGES(missatge) 
        VALUES (CONCAT('Més d''un empleat amb un sou de ', @sou));
    DECLARE EXIT HANDLER FOR SQLWARNING INSERT INTO MISSATGES(missatge) 
        VALUES ('Ha Succeït Un Error');
    DECLARE EXIT HANDLER FOR SQLEXCEPTION INSERT INTO MISSATGES(missatge) 
        VALUES ('Ha Succeït Un Error');

    SELECT first_name, last_name, salary
        INTO v_first_name, v_last_name, v_salary
        FROM empleats WHERE salary = @sou;
    IF v_salary IS NULL THEN -- Només podem comprovar el sou o el last_name
        SIGNAL SQLSTATE '02000';
    END IF;
    INSERT INTO MISSATGES(missatge) 
        VALUES (CONCAT_WS(' ', v_first_name, v_last_name,
                        'té un sou de', @sou));
END //
SELECT * FROM MISSATGES //

Una altra opció amb DECLARE CONTINUE HANDLER

DELIMITER //
SET @sou=24000 //
BEGIN NOT ATOMIC
    DECLARE v_missatge TYPE OF MISSATGES.missatge;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
            SET v_missatge = CONCAT('Cap empleat amb un sou de ', @sou);
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' 
            SET v_missatge = CONCAT('Més d''un empleat amb un sou de ', @sou);
    DECLARE CONTINUE HANDLER FOR SQLWARNING 
            SET v_missatge = 'Ha Succeït Un Error';
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  
            SET v_missatge = 'Ha Succeït Un Error';

    SELECT CONCAT_WS(' ', first_name, last_name, 'té un sou de', @sou)
        INTO v_missatge
        FROM empleats WHERE salary = @sou;
    INSERT INTO MISSATGES(missatge) 
        VALUES (v_missatge);
END //
SELECT * FROM MISSATGES //

Fareu un exercici semblant a l’exercici 3 de la pràctica 3, però, incorporant excepcions.

Utilitzeu un parell de variables locals per enregistrar l'ID del departament i la ubicació del departament que volem modificar. @deptartment_id, @location_id
En el cas de no trobar el departament especificat a @deptartment_id, escriviu el codi necessari per generar un error a l’usuari amb un missatge indicant que s'ha especificat un departament que no existeix. Utilitzeu, també, una variable per escriure el missatge a l'usuari, @missatge. Ha de generar l'error SQLSTATE '02000'.
Compte ja que ROW_COUNT() torna les files actualitzades. És a dir, si volem modificar un valor i una fila ja conté aquell valor, ROW_COUNT() no la compta, per tant, no la utilitzeu.
Executeu el bloc anònim introduint un departament que no existeix.

Possible solucio

DELIMITER //
SET @department_id = 900 // 
SET @location_id = 1700 //
BEGIN NOT ATOMIC
    DECLARE v_q INT;
    DECLARE v_missatge VARCHAR(200);

    SET @missatge = NULL;  -- Eliminem possible missatge anterior
    SELECT COUNT(*) INTO v_q
        FROM DEPARTAMENTS
        WHERE department_id = @department_id;
    IF v_q > 0 THEN
        UPDATE DEPARTAMENTS SET location_id = @location_id
            WHERE department_id = @department_id;
    ELSE
        SET v_missatge = CONCAT('Departament inexistent: ', @department_id);
        SET @missatge = v_missatge;
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = v_missatge;
    END IF;
END//
SELECT * FROM DEPARTAMENTS //
SELECT @missatge //

Escriviu un bloc anònim que imprimeixi el nombre d'empleats que donat un salari, establert en una determinada variable, @sou, guanyen un salari que està entre més o menys 100 dòlars d’aquest valor salarial.

a) Si no hi ha cap empleat dins d'aquest rang salarial, genereu un error a l'usuari indicant-ho amb un missatge personalitzat.

b) Si hi ha un o més empleats dins d'aquest rang, el missatge hauria d'indicar quants dels empleats es troben dins aquest rang salarial sense generar cap error.

c) Gestioneu qualsevol altra excepció amb un gestor d'excepcions adequat. El missatge hauria d’indicar que s'ha produït algun altre error.

d) Proveu el programa per diferents valors: @sou=7000, @sou=2500, @sou=6500

Possible solucio

DELIMITER //
SET @sou = '7000' //
BEGIN NOT ATOMIC 
    DECLARE v_quants INT;
    DECLARE v_missatge VARCHAR(200);
    DECLARE EXIT HANDLER FOR SQLWARNING INSERT INTO MISSATGES(missatge) 
        VALUES ('Ha Succeït Un Error');
    DECLARE EXIT HANDLER FOR SQLEXCEPTION INSERT INTO MISSATGES(missatge) 
        VALUES ('Ha Succeït Un Error');

    SELECT COUNT(*) INTO v_quants
        FROM EMPLEATS 
        WHERE SALARY BETWEEN @sou - 100 AND @sou + 100;

    IF v_quants = 0 THEN
        SET v_missatge = CONCAT_WS(' ', 'Cap empleat amb un sou entre', 
                                        @sou - 100, 'i', @sou + 100);
        SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = v_missatge;
    ELSEIF v_quants = 1 THEN
        SELECT CONCAT_WS(' ', v_quants, 'empleat amb un sou entre',
                                @sou - 100, 'i', @sou + 100);
    ELSE
        SELECT CONCAT_WS(' ', v_quants, 'empleats amb un sou entre',
                                @sou - 100, 'i', @sou + 100);
    END IF;
END;
//