Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis conceptes fundamentals UF3: PL-SQL»
Línia 108: | Línia 108: | ||
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. | 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. | ||
<!-- | <!-- | ||
+ | DECLARE | ||
+ | CURSOR c_dept_cursor IS | ||
+ | SELECT department_id, department_name | ||
+ | FROM departments | ||
+ | WHERE manager_id IS NOT NULL; | ||
+ | v_total_salari NUMBER(8,2):=0; | ||
+ | BEGIN | ||
+ | FOR v_dept_record IN c_dept_cursor | ||
+ | LOOP | ||
+ | v_total_salari:=total_salari_dept(v_dept_record.department_id); | ||
+ | INSERT INTO pressupost | ||
+ | VALUES (v_dept_record.department_name, v_total_salari); | ||
+ | END LOOP; | ||
+ | COMMIT; | ||
+ | END; | ||
--> | --> | ||
+ | f) Mostreu el contingut de la taula PRESSUPOST. | ||
+ | <!-- | ||
+ | <pre> | ||
+ | SELECT * | ||
+ | FROM PRESSUPOST; | ||
+ | </pre> | ||
+ | --> | ||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | kkkk) | ||
<!-- | <!-- | ||
DECLARE | DECLARE |
Revisió del 13:08, 21 març 2019
Esquema HR
1.- Crea una 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
kkkk)
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