Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis conceptes fundamentals UF3: PL-SQL»
| Línia 14: | Línia 14: | ||
9%: AD_PRES, AD_VP | 9%: AD_PRES, AD_VP | ||
</pre> | </pre> | ||
| − | + | <!-- | |
| + | CREATE OR REPLACE FUNCTION Total_Salari_Dept (p_dept_num departments.department_id%type) RETURN NUMBER IS | ||
| + | CURSOR c_emp_dept_cursor IS | ||
| + | SELECT job_id, salary | ||
| + | FROM employees | ||
| + | WHERE department_id = p_dept_num; | ||
| + | v_total_salari NUMBER(8,2):=0; | ||
| + | |||
| + | BEGIN | ||
| + | FOR v_emp_dept_record IN c_emp_dept_cursor | ||
| + | LOOP | ||
| + | IF v_emp_dept_record.job_id IN ('AD_ASST', 'SA_REP', 'SH_CLERK', 'PU_CLERK', 'ST_CLERK') THEN | ||
| + | v_total_salari:=v_total_salari+(v_emp_dept_record.salary * 1.03); | ||
| + | ELSIF v_emp_dept_record.job_id IN ('AC_ACCOUNT', 'FI_ACCOUNT', 'HR_REP', 'PR_REP', 'IT_PROG', 'MK_REP') THEN | ||
| + | v_total_salari:=v_total_salari+(v_emp_dept_record.salary * 1.05); | ||
| + | ELSIF v_emp_dept_record.job_id IN ('MK_MAN', 'AC_MGR', 'FI_MGR', 'PU_MAN', 'SA_MAN', 'ST_MANAGER') THEN | ||
| + | v_total_salari:=v_total_salari+(v_emp_dept_record.salary * 1.07); | ||
| + | ELSIF v_emp_dept_record.job_id IN ('AD_PRES', 'AD_VP') THEN | ||
| + | v_total_salari:=v_total_salari+(v_emp_dept_record.salary * 1.09); | ||
| + | END IF; | ||
| + | END LOOP; | ||
| + | return v_total_salari; | ||
| + | END; | ||
| + | --> | ||
| + | b) Fes un bloc PL/SQL per provar la funció. Mostra el total dels salaris revisats dels departaments 10, 50 i 75. | ||
| + | <!-- | ||
| + | SET SERVEROUTPUT ON | ||
| + | BEGIN | ||
| + | dbms_output.put_line('Total Salari Departament 10: ' || Total_Salari_Dept(10)); | ||
| + | dbms_output.put_line('Total Salari Departament 50: ' || Total_Salari_Dept(50)); | ||
| + | dbms_output.put_line('Total Salari Departament 75: ' || Total_Salari_Dept(75)); | ||
| + | END; | ||
| + | --> | ||
| + | <pre> | ||
| + | Total Salari Departament 10: 4532 | ||
| + | Total Salari Departament 50: 123600 | ||
| + | Total Salari Departament 75: 0 | ||
| + | </pre> | ||
| + | |||
| + | c) Modifica la funció 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!!'. | ||
<!-- | <!-- | ||
<pre> | <pre> | ||
Revisió del 11:53, 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(5));
a) Crea una funció 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 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) Modifica la funció 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!!'.
b) Fes un bloc PL/SQL per provar la funció.
c) En un bloc 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.
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
