M2 - Bases de dades / Exercicis conceptes fundamentals UF3: PL-SQL

Esquema HR

1.- Crea la taula PRESSUPOST (CREATE TABLE) que contingui els següent camps: Nom_Department i Total_Salaris.

CREATE TABLE PRESSUPOST(
Nom_Department VARCHAR2(30) PRIMARY KEY, 
Total_Salaris NUMBER(8,2));

a) Crea la funció Total_Salari_Dept que a partir d'un número de departament ens retorni el total de salaris pressupostats per a aquell departament, tenint en compte els següents augments:

3%: AD_ASST, SA_REP, SH_CLERK, PU_CLERK, ST_CLERK
5%: AC_ACCOUNT, FI_ACCOUNT, HR_REP, PR_REP, IT_PROG, MK_REP
7%: MK_MAN, AC_MGR, FI_MGR, PU_MAN, SA_MAN, ST_MANAGER
9%: AD_PRES, AD_VP 

b) Fes un bloc PL/SQL anònim per provar la funció. Mostra el total dels salaris revisats dels departaments 10, 50 i 75.

Total Salari Departament 10: 4532
Total Salari Departament 50: 123600
Total Salari Departament 75: 0

c) Crea la funció Total_Salari_Dept2 per tal que en cas de que no existeixi el número de departament utilitzi el procediment RAISE_APPLICATION_ERROR per generar una excepció amb el codi: -20215 i missatge: 'No existeix aquest departament!!'.

d) Fes un bloc anònim PL/SQL per provar el funcionament correcte de l'excepció de la funció.

e) En un bloc anònim PL/SQL crea un cursor que recuperi tots els departaments que tenen empleats (manager_id not null) ordenats per departament i utilitza la funció de l'apartat a) per omplir la taula PRESSUPOST convenientment.

f) Mostreu el contingut de la taula PRESSUPOST.

 
   NOM_DEPARTAMENT     TOTAL_SALARIS
   Administration	        4532
   Marketing	               20210
   Purchasing	               26087
   Human Resources	        6825
   Shipping	              123600
   IT	                       30240
   Public Relations	       10500
   Sales	              316075
   Executive	               63220
   Finance	            54428,56
   Accounting	            21563,56

g) Crea la taula PRESSUPOST2 (CREATE TABLE) que contingui els següent camps: Nom_Department, First_Name, Last_Name i Total_Salaris.

CREATE TABLE PRESSUPOST2(
Nom_Department VARCHAR2(30) PRIMARY KEY, 
First_Name VARCHAR2(20),
Last_Name VARCHAR2(25),
Total_Salaris NUMBER(8,2));

h) Crea el procediment Afegir_Pressupost_Dept que rebrà com a paràmetres el nom del departament, el identificador del manager del departament i el total dels salaris revisats del departament, i obtindrà el nom i cognom del manager del departament i afegirà un nou registre a la taula PRESSUPOST2 amb la informació corresponent.

i) Refès el bloc anònim anterior per tal de que utilitzant el procediment anterior afegeixi tots els departaments a la taula PRESSUPOST2. Tracta l'excepció que es produiria si el procediment no troba el manager per a un departament ('No s'ha trobat cap manager per a aquest departament) i qualsevol altre excepció que es pugui produir (codi d'error i missatge d'oracle).

j) Mostreu el contingut de la taula PRESSUPOST2.

kkk)

2.- Fes un bloc PL/SQL que crei una taula indexada per job_title i crea un cursor que recuperi tots els empleats ordenats per departament i recorre'l per omplir la taula amb la següent informació: número d'empleats que tenen aquest ofici i el número de departaments que tenen aquest ofici. Mostra el resultat.

3.- Utilitzant dos cursors, un que recuperi els departaments i un segon parametritzat que a partir d'un department_id recupera els empleats, fes el següent informe:

                     - EMPLEATS PER DEPARTAMENT -

Departament: <nom del departament>

Cap: <nom i cognom del cap del departament>

Empleats 

<nom i cognom dels empleats>

- - - - - - - - - - - - - - - - - - - - 

Departament: <nom del departament>

Cap: <nom i cognom del cap del departament>

Empleats 

<nom i cognom dels empleats>

Esquema SCOTT ampliat