M2 - Bases de dades / Apunts UF3 : Exercicis OnLine paquets i trigers
Exercicis OnLine – Part 2
Un cop acabada aquesta part de la prova pengeu un document (PL_SQL_Part2_Nom_i_PrimerCognom) en l’apartat respectiu:
PL/SQL
1. Crea un paquet amb els següents recursos:
1.1. Públics:
1.1.1 Una matriu associativa indexada per PLS_INTEGER (idproveedor) per emmagatzemar la següent informació:
- - Nombre de productes.
- - Nombre de productes suspesos.
- - Nombre de comandes que no es poden atendre (unidadesenexistencia < unidadesenpedido)
- Cal crear els tipus corresponents.
1.1.2 Un procediment que rebrà com a paràmetre un identificador de categoria i actualitzarà la matriu associativa amb la informació corresponent a aquesta categoria.
- - Per comprovar l’existència de la categoria utilitzarà una funció privada.
- - En cas de que no existeixi la categoria el procediment generarà l’excepció: -20111 amb el text ‘Codi Categoria Incorrecte’.
1.1.3 Un procediment que recorrerà la matriu associativa i mostrarà el seu contingut segons l’informe mostrat al final.
1.2. Privats:
1.2.1 Una funció que rebrà com a paràmetre un identificador de categoria retornarà si aquesta categoria existeix o no.
1.3 Fes un bloque anònim que utilitzi els procediments del paquet i a continuació elimini tots els elements de la matriu associativa.
Informe per la categoria 3: ID PROVEEDOR: 7 ------------------ Num. productes: 1 Num. productes suspesos: 0 Num. productes stock insuficient: 0 ------------------ ID PROVEEDOR: 8 ------------------ Num. productes: 4 Num. productes suspesos: 0 Num. productes stock insuficient: 2 ------------------ ID PROVEEDOR: 11 ------------------ Num. productes: 3 Num. productes suspesos: 0 Num. productes stock insuficient: 0 ------------------ ID PROVEEDOR: 22 ------------------ Num. productes: 2 Num. productes suspesos: 0 Num. productes stock insuficient: 1 ------------------ ID PROVEEDOR: 23 ------------------ Num. productes: 2 Num. productes suspesos: 0 Num. productes stock insuficient: 1 ------------------ ID PROVEEDOR: 29 ------------------ Num. productes: 1 Num. productes suspesos: 0 Num. productes stock insuficient: 0 ------------------
2. Triguers
2.1 Fes un triguer per a fer un seguiment de totes les operacions DML que es facin sobre la taula PEDIDOS. En concret, volem afegir una fila a la taula AUDITPED amb la següent informació: Usuari que a fet la operació (USER), data en que la fet (SYSDATE), Moment (hora, minuts i segons) i quina operació a fet (INSERT, UPDATE, DELETE).
- Feu un joc de proves oportú.
- Creeu la taula AUDITPED
CREATE TABLE AUDITPED( Usuari VARCHAR2(10), Dia DATE, Hora VARCHAR2(8), Operacio VARCHAR2(6));
2.2 Feu un triguer que actualitzi la taula productes atenent als INSERTS que es produeixen en la taula DETALLES_PEDIDOS. En concret:
- Si hi ha unitats suficients (unidadesenexistencia) per atendre la comanda, actualitzarà les unitats en existència. I si aquestes queden per sota del nivell per fer una nova comanda (nivellnuevopedido) escriurà un missatge indicant que el producte està per sota del nivell per fer una comanda i que cal comprar tantes unitats (el triple del que indiqui el nivell de nova comanda).
- Si no hi ha unitats suficients (unidadesenexistencia) per atendre la comanda, generarà l’excepció -20202 amb un missatge adient.
- Feu un joc de proves oportú.