DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica 9 – Procediments

En aquesta pràctica, creareu procediments que contenen consultes DML.

Per fer aquests exercicis cal tenir les següents taules creades (Compte que li deixem les PKs):

CREATE OR REPLACE TABLE EMPLEATS AS SELECT * FROM empresa.EMPLOYEES;
CREATE OR REPLACE TABLE FEINES LIKE empresa.JOBS;
INSERT INTO FEINES SELECT * FROM empresa.JOBS;

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 el procediment ADD_FEINA per afegir un nou treball a la taula FEINES.

   1. Creeu un procediment anomenat ADD_FEINA amb dos paràmetres: job_id i job_title.

   2. Executeu el procediment amb 'IT_DBA' com a job_id i 'Database Administrator' com a job_title. Consulteu la taula FEINES per veure els resultats.

   3. Torneu a executar el procediment, passant un job_id 'ST_MAN' i job_title 'Stock Manager'. Què passa i per què?

   Possible solució

   DELIMITER //
   CREATE OR REPLACE PROCEDURE ADD_FEINA(p_job_id TYPE OF feines.job_id,
                                       p_job_title TYPE OF feines.job_title)
   MODIFIES SQL DATA
   BEGIN
       INSERT INTO FEINES(job_id, job_title)
           VALUES(p_job_id , p_job_title);
   END //
   DELIMITER ;
   START TRANSACTION;
   CALL ADD_FEINA('IT_DBA', 'Database Administrator');
   SELECT * FROM FEINES;
   CALL ADD_FEINA('ST_MAN', 'Stock Manager'); -- Dona ERROR per repetir la PK
   COMMIT;
   

2. Creeu un procediment anomenat UPD_FEINA per modificar un treball a la taula FEINES.

   1. Creeu un procediment anomenat UPD_FEINA per actualitzar el job_title. Proporcioneu el job_id i un nou job_title, utilitzant dos paràmetres. Cal incloure la gestió d'excepcions necessària per si no es produeix cap actualització.

   2. Executeu el procediment per canviar el job_title del job_id IT_DBA a 'Data Administrator'.

   3. Consulteu la taula FEINES per veure els resultats.

   Possible solució

   DELIMITER //
   CREATE OR REPLACE PROCEDURE UPD_FEINA(p_job_id TYPE OF feines.job_id,
                                       p_job_title TYPE OF feines.job_title)
   MODIFIES SQL DATA
   BEGIN
   DECLARE v_q INT;
   DECLARE v_missatge VARCHAR(200);
   
   SELECT COUNT(*) INTO v_q FROM FEINES
       WHERE job_id = p_job_id;
   IF v_q = 0 THEN
       SET v_missatge = CONCAT('Feina no trobada: ', p_job_id);
       SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = v_missatge;
   END IF;
   UPDATE FEINES SET job_title = p_job_title
       WHERE job_id = p_job_id;
   END //
   DELIMITER ;
   START TRANSACTION;
   CALL UPD_FEINA('IT_DBA', 'Data Administrator');
   CALL UPD_FEINA('IT_WEB', 'Web Developer');
   COMMIT;
   

3. Creeu un procediment anomenat DEL_FEINA per eliminar un treball de la taula FEINES.

   1. Creeu un procediment anomenat DEL_FEINA per eliminar treball, amb un únic paràmetre el JOB_ID. Incloeu l'excepció necessària per si no es troba la feina a esborrar.

   2. Executeu el procediment amb IT_DBA com a JOB_ID. Consulteu la taula FEINES.

   3. Comproveu la gestió d'excepcions intentant suprimir un JOB_ID que no existeix (per exemple IT_WEB). Hauríeu d'obtenir el missatge que heu utilitzat a la secció de gestió d'excepcions.

   Possible solució

   DELIMITER //
   CREATE OR REPLACE PROCEDURE DEL_FEINA(p_job_id TYPE OF feines.job_id)
   MODIFIES SQL DATA
   BEGIN
   DECLARE v_q INT;
   DECLARE v_missatge VARCHAR(200);
   
   DELETE FROM FEINES
       WHERE job_id = p_job_id;
   IF ROW_COUNT() = 0 THEN
       SET v_missatge = CONCAT('Feina no trobada: ', p_job_id);
       SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = v_missatge;
   END IF;
   END //
   DELIMITER ;
   START TRANSACTION;
   CALL DEL_FEINA('IT_DBA');
   CALL DEL_FEINA('IT_WEB');
   COMMIT;
   

4. Creeu un procediment anomenat QUERY_EMP per consultar la taula EMPLEATS, recuperant el salary i el job_id d'un empleat.

   1. Creeu un procediment que retorni un valor de les columnes salary i job_id per a un determinat identificador d'empleat (employee_id).

   b) Aquest procediment rebrà tres paràmetres. Un d’entrada (IN) i dos de sortida (OUT).

   c) Per a provar-ho necessitarem 2 o 3 variables d’usuari. Almenys per les variables de sortida (OUT)

   d) Executeu el procediment per mostrar el salary i job_id per a l'empleat 107.

   Possible solució

   DELIMITER //
   CREATE OR REPLACE PROCEDURE QUERY_EMP(
       p_employee_id TYPE OF empleats.employee_id,
       OUT p_salary TYPE OF empleats.salary,
       OUT p_job_id TYPE OF empleats.job_id)
       CONTAINS SQL
   BEGIN
       SELECT salary, job_id
           INTO p_salary, p_job_id
           FROM EMPLEATS
           WHERE employee_id = p_employee_id;
   END //
   CALL QUERY_EMP(107, @sou, @job) //
   SELECT @sou, @job //