Salta el contingut

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è?

  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.

  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.

  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.