M2 - Bases de dades / Solucions 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 

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 anònim 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;

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!!'.

CREATE OR REPLACE FUNCTION Total_Salari_Dept2 (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;
v_emp_dept_record c_emp_dept_cursor%ROWTYPE;

BEGIN
OPEN c_emp_dept_cursor;
FETCH c_emp_dept_cursor INTO v_emp_dept_record;
IF c_emp_dept_cursor%NOTFOUND THEN
  RAISE_APPLICATION_ERROR(-20215,'No existeix el departament '||p_dept_num);
END IF;  
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;    
   FETCH c_emp_dept_cursor INTO v_emp_dept_record;
   EXIT WHEN c_emp_dept_cursor%NOTFOUND;
END LOOP;
CLOSE c_emp_dept_cursor;
 return v_total_salari;
END;

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

BEGIN
 dbms_output.put_line('Total Salari Departament 10: ' || Total_Salari_Dept2(10));
 dbms_output.put_line('Total Salari Departament 50: ' || Total_Salari_Dept2(50));
 dbms_output.put_line('Total Salari Departament 75: ' || Total_Salari_Dept2(75));
END;

Informe de error -
ORA-20215: No existeix el departament 75
ORA-06512: at "HR.TOTAL_SALARI_DEPT2", line 13
ORA-06512: at line 4

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

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
ORDER BY department_name;
v_total_salaris NUMBER(8,2):=0;

BEGIN
FOR v_dept_record IN c_dept_cursor
LOOP
  v_total_salaris:=total_salari_dept(v_dept_record.department_id);
  INSERT INTO pressupost
  VALUES (v_dept_record.department_name, v_total_salaris);
END LOOP;
COMMIT;
END;

f) Mostreu el contingut de la taula PRESSUPOST.

  SELECT *
  FROM PRESSUPOST;

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

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.

CREATE OR REPLACE PROCEDURE Afegir_Pressupost_Dept (
p_manager_id departments.manager_id%type, 
p_dept_name departments.department_name%type, 
p_total_salaris pressupost2.total_salaris%type) IS

v_first_name EMPLOYEES.FIRST_NAME%type;
v_last_name EMPLOYEES.LAST_NAME%type;

BEGIN
  SELECT first_name, last_name INTO v_first_name, v_last_name
  FROM employees
  WHERE employee_id = p_manager_id;
  
  INSERT INTO pressupost2
  VALUES (p_dept_name, v_first_name, v_last_name, p_total_salaris);

END;

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 ('Error: No existeix el manager d'aquest departament') i qualsevol altre excepció que es pugui produir (codi d'error i missatge d'oracle).

DECLARE
CURSOR c_dept_cursor IS
SELECT department_id, department_name, manager_id
FROM departments
WHERE manager_id IS NOT NULL;
v_total_salaris NUMBER(8,2):=0;
v_err_num NUMBER;
v_err_msg VARCHAR2(100);

BEGIN
FOR v_dept_record IN c_dept_cursor
LOOP
  v_total_salaris:=total_salari_dept(v_dept_record.department_id);
  afegir_pressupost_dept(v_dept_record.manager_id, v_dept_record.department_name, v_total_salaris);
END LOOP;
COMMIT;
EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Error: No existeix el manager d''aquest departament');
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE ('Codi Error: ' || v_err_num || ' Missatge: '|| v_err_msg);

END;

j) Mostreu el contingut de la taula PRESSUPOST2.

SELECT * 
FROM pressupost2;

   NOM_DEPARTAMENT    FIRST_NAME   LAST_NAME   TOTAL_SALARIS
   Administration     Jennifer	   Whalen	        4532
   Marketing	      Michael	   Hartstein	       20210
   Purchasing	      Den	   Raphaely            26087
   Human Resources    Susan        Mavris               6825
   Shipping           Adam         Fripp              123600
   IT	              Alexander    Hunold              30240
   Public Relations   Hermann      Baer                10500
   Sales              John         Russell            316075
   Executive	      Steven       King                63220
   Finance            Nancy        Greenberg        54428,56
   Accounting         Shelley      Higgins          21563,56 

2.- Fes un bloc PL/SQL per omplir una taula de registres indexada per VARCHAR2, que crei una taula indexada per job_id 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.

a) En un bloc PL/SQL declara una taula de registres per recollir el job_title, quants departaments tenen aquest ofici i quants empleats tenen aquest ofici. La taula estarà indexada per VARCHAR2 (job_id). Declara també les variables respectives.

DECLARE
TYPE t_job IS RECORD
(job_title jobs.job_title%type,
 num_depts number(2),
 num_emps number(2));
 v_job t_job;

TYPE job_table_type IS TABLE OF t_job INDEX BY VARCHAR2(10);
job_table job_table_type;

b) Mitjançant un cursor recupera el job_id i job_title de la taula JOBS i inicialitza les dades de la taula de l'apartat a) convenientment (numdepts = 0, num_emps=0). Mostra el resultat.

DECLARE
TYPE t_job IS RECORD
(job_title jobs.job_title%type,
 num_depts number(2),
 num_emps number(2));
 v_job t_job;

TYPE job_table_type IS TABLE OF t_job INDEX BY VARCHAR2(10);
job_table job_table_type;

CURSOR c_job_cursor IS
SELECT job_id, job_title
FROM jobs;

v_job_id jobs.job_id%type;

BEGIN

FOR v_job_record IN c_job_cursor
LOOP
  v_job.job_title:= v_job_record.job_title;
  v_job.num_depts:=0;
  v_job.num_emps:=0;
  job_table(v_job_record.job_id):=v_job;
END LOOP;

v_job_id := job_table.FIRST;
WHILE v_job_id IS NOT NULL LOOP
 dbms_output.put_line('OFICI: '|| rpad(job_table(v_job_id).job_title,40,' ') || '  DEPARTAMENTS: ' || job_table(v_job_id).num_depts || '  EMPLEATS: ' || job_table(v_job_id).num_emps);
v_job_id := job_table.NEXT(v_job_id);
END LOOP;

END;

OFICI: Public Accountant                         DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Accounting Manager                        DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Administration Assistant                  DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: President                                 DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Administration Vice President             DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Accountant                                DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Finance Manager                           DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Human Resources Representative            DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Programmer                                DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Marketing Manager                         DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Marketing Representative                  DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Public Relations Representative           DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Purchasing Clerk                          DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Purchasing Manager                        DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Sales Manager                             DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Sales Representative                      DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Shipping Clerk                            DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Stock Clerk                               DEPARTAMENTS: 0  EMPLEATS: 0
OFICI: Stock Manager                             DEPARTAMENTS: 0  EMPLEATS: 0

c) Utilitzant un cursor que recuperi tots els departaments amb els seus oficis i el número de treballadors que fan cada ofici, i actualitza la taula convenientment. Mostra el resultat.

DECLARE
TYPE t_job IS RECORD
(job_title jobs.job_title%type,
 num_depts number(2),
 num_emps number(2));
 v_job t_job;

TYPE job_table_type IS TABLE OF t_job INDEX BY VARCHAR2(10);
job_table job_table_type;

CURSOR c_job_cursor IS
SELECT job_id, job_title
FROM jobs;

v_job_id jobs.job_id%type;

CURSOR c_dept_job_cursor IS
SELECT department_id, job_id, COUNT(employee_id) num_emps
FROM employees
GROUP BY department_id, job_id;


BEGIN

FOR v_job_record IN c_job_cursor
LOOP
  v_job.job_title:= v_job_record.job_title;
  v_job.num_depts:=0;
  v_job.num_emps:=0;
  job_table(v_job_record.job_id):=v_job;
END LOOP;


FOR v_dept_job_record IN c_dept_job_cursor
LOOP
  job_table(v_dept_job_record.job_id).num_depts :=job_table(v_dept_job_record.job_id).num_depts+1;
  job_table(v_dept_job_record.job_id).num_emps :=job_table(v_dept_job_record.job_id).num_depts+v_dept_job_record.num_emps;
END LOOP;

v_job_id := job_table.FIRST;
WHILE v_job_id IS NOT NULL LOOP
    dbms_output.put_line('OFICI: '|| rpad(job_table(v_job_id).job_title,40,' ') || '  DEPARTAMENTS: ' || job_table(v_job_id).num_depts || '  EMPLEATS: ' || job_table(v_job_id).num_emps);
    v_job_id := job_table.NEXT(v_job_id);
END LOOP;

END;

OFICI: Public Accountant                         DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Accounting Manager                        DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Administration Assistant                  DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: President                                 DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Administration Vice President             DEPARTAMENTS: 1  EMPLEATS: 3
OFICI: Accountant                                DEPARTAMENTS: 1  EMPLEATS: 6
OFICI: Finance Manager                           DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Human Resources Representative            DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Programmer                                DEPARTAMENTS: 1  EMPLEATS: 6
OFICI: Marketing Manager                         DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Marketing Representative                  DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Public Relations Representative           DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Purchasing Clerk                          DEPARTAMENTS: 1  EMPLEATS: 6
OFICI: Purchasing Manager                        DEPARTAMENTS: 1  EMPLEATS: 2
OFICI: Sales Manager                             DEPARTAMENTS: 1  EMPLEATS: 6
OFICI: Sales Representative                      DEPARTAMENTS: 2  EMPLEATS: 3
OFICI: Shipping Clerk                            DEPARTAMENTS: 1  EMPLEATS: 21
OFICI: Stock Clerk                               DEPARTAMENTS: 1  EMPLEATS: 21
OFICI: Stock Manager                             DEPARTAMENTS: 1  EMPLEATS: 6

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>

DECLARE

CURSOR c_dept_cursor IS
SELECT department_id, department_name, manager_id
FROM departments
WHERE manager_id IS NOT NULL;

CURSOR c_emp_cursor (p_dept_id departments.department_id%type) IS
SELECT first_name, last_name
FROM employees
WHERE department_id = p_dept_id;

v_first_cap employees.first_name%type;
v_last_cap employees.last_name%type;

BEGIN
  DBMS_OUTPUT.PUT_LINE('- EMPLEATS PER DEPARTAMENT -');
 
  
  FOR v_dept_record IN c_dept_cursor
  LOOP

     SELECT first_name, last_name INTO v_first_cap, v_last_cap
     FROM employees
     WHERE employee_id = v_dept_record.manager_id;
     DBMS_OUTPUT.PUT_LINE('Department: '||v_dept_record.department_name);
     DBMS_OUTPUT.PUT_LINE('Cap: '||v_first_cap||' '||v_last_cap);
     DBMS_OUTPUT.PUT_LINE('Empleats');
     DBMS_OUTPUT.PUT_LINE('--------');
     FOR v_emp_record IN c_emp_cursor(v_dept_record.department_id)
      LOOP
        IF NOT (v_first_cap = v_emp_record.first_name AND v_last_cap = v_emp_record.last_name) THEN
           DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name || ' ' || v_emp_record.last_name);
        END IF;
      END LOOP;  
   DBMS_OUTPUT.PUT_LINE('***************************');
  END LOOP;


END;