M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T9
Soluciones a la Práctica 9-1: Gestión de Reglas de Integridad de Datos y de Excepciones de Tablas Mutantes
En esta práctica, implantará una regla de negocio sencilla para garantizar la integridad de los datos de los salarios de los empleados con respecto al rango de salarios válidos para sus trabajos. Cree un disparador para esta regla. Durante este proceso, los nuevos disparadores darán lugar a un efecto en cascada con disparadores creados en la sección práctica de la lección anterior. El efecto en cascada originará una excepción de tabla mutante en la tabla JOBS. A continuación, cree un paquete PL/SQL y disparadoresadicionales para resolver el problema de la tabla mutante.
1) Los empleados reciben un aumento de sueldo automáticamente si el salario mínimo de un trabajo se aumenta a un valor superior a sus salarios actuales. Implante este requisito con un procedimiento empaquetado al que llame el disparador de la tabla JOBS. Cuando intenta actualizar el salario mínimo en la tabla JOBS e intenta actualizar los salarios de los empleados, el disparador CHECK_SALARY intenta leer la tabla JOBS, que está sujeta a cambios y obtendrá una excepción de tabla mutante que se resuelve creando un nuevo paquete y disparadores adicionales.
- a) Actualice el paquete EMP_PKG (que actualizó por última vez en la práctica 8), de la siguiente forma:
- i. Agregue un procedimiento denominado SET_SALARY, que actualice los salarios de los empleados.
- ii. El procedimiento SET_SALARY acepta los dos parámetros siguientes: el identificador de trabajo de aquellos salarios que puede que haya que actualizar y el nuevo salario mínimo para el identificador del trabajo.
- Abra el script /home/oracle/labs/plpu/solns/sol_09_01_a.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para ejecutar el script. El código y el resultado se muestran de la siguiente forma. El código que se acaba de agregar se resalta en negrita en el cuadro de código siguiente.
-- Package SPECIFICATION CREATE OR REPLACE PACKAGE emp_pkg IS TYPE emp_tab_type IS TABLE OF employees%ROWTYPE; PROCEDURE add_employee( p_first_name employees.first_name%TYPE, p_last_name employees.last_name%TYPE, p_email employees.email%TYPE, p_job employees.job_id%TYPE DEFAULT 'SA_REP', p_mgr employees.manager_id%TYPE DEFAULT 145, p_sal employees.salary%TYPE DEFAULT 1000, p_comm employees.commission_pct%TYPE DEFAULT 0, p_deptid employees.department_id%TYPE DEFAULT 30); PROCEDURE add_employee( p_first_name employees.first_name%TYPE, p_last_name employees.last_name%TYPE, p_deptid employees.department_id%TYPE); PROCEDURE get_employee( p_empid IN employees.employee_id%TYPE, p_sal OUT employees.salary%TYPE, p_job OUT employees.job_id%TYPE); FUNCTION get_employee(p_emp_id employees.employee_id%type) return employees%rowtype; FUNCTION get_employee(p_family_name employees.last_name%type) return employees%rowtype; PROCEDURE get_employees(p_dept_id employees.department_id%type); PROCEDURE init_departments; PROCEDURE print_employee(p_rec_emp employees%rowtype); PROCEDURE show_employees; /* New set_salary procedure */ PROCEDURE set_salary(p_jobid VARCHAR2, p_min_salary NUMBER); END emp_pkg; / SHOW ERRORS
-- Package BODY CREATE OR REPLACE PACKAGE BODY emp_pkg IS TYPE boolean_tab_type IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER; valid_departments boolean_tab_type; emp_table emp_tab_type; FUNCTION valid_deptid(p_deptid IN departments.department_id%TYPE) RETURN BOOLEAN; PROCEDURE add_employee( p_first_name employees.first_name%TYPE, p_last_name employees.last_name%TYPE, p_email employees.email%TYPE, p_job employees.job_id%TYPE DEFAULT 'SA_REP', p_mgr employees.manager_id%TYPE DEFAULT 145, p_sal employees.salary%TYPE DEFAULT 1000, p_comm employees.commission_pct%TYPE DEFAULT 0, p_deptid employees.department_id%TYPE DEFAULT 30) IS PROCEDURE audit_newemp IS PRAGMA AUTONOMOUS_TRANSACTION; user_id VARCHAR2(30) := USER; BEGIN INSERT INTO log_newemp (entry_id, user_id, log_time,name) VALUES (log_newemp_seq.NEXTVAL, user_id,sysdate,p_first_name||' '||p_last_name); COMMIT; END audit_newemp; BEGIN -- add_employee IF valid_deptid(p_deptid) THEN audit_newemp; INSERT INTO employees(employee_id, first_name, last_name, email, job_id, manager_id, hire_date, salary, commission_pct, department_id) VALUES (employees_seq.NEXTVAL, p_first_name,p_last_name, p_email, p_job, p_mgr, TRUNC(SYSDATE), p_sal, p_comm,p_deptid); ELSE RAISE_APPLICATION_ERROR (-20204, 'Invalid department ID. Try again.'); END IF; END add_employee; PROCEDURE add_employee( p_first_name employees.first_name%TYPE, p_last_name employees.last_name%TYPE, p_deptid employees.department_id%TYPE) IS p_email employees.email%type; BEGIN p_email := UPPER(SUBSTR(p_first_name, 1, 1)||SUBSTR(p_last_name, 1, 7)); add_employee(p_first_name, p_last_name, p_email, p_deptid => p_deptid); END; PROCEDURE get_employee( p_empid IN employees.employee_id%TYPE, p_sal OUT employees.salary%TYPE, p_job OUT employees.job_id%TYPE) IS BEGIN SELECT salary, job_id INTO p_sal, p_job FROM employees WHERE employee_id = p_empid; END get_employee; FUNCTION get_employee(p_emp_id employees.employee_id%type) return employees%rowtype IS rec_emp employees%rowtype; BEGIN SELECT * INTO rec_emp FROM employees WHERE employee_id = p_emp_id; RETURN rec_emp; END; FUNCTION get_employee(p_family_name employees.last_name%type) return employees%rowtype IS rec_emp employees%rowtype; BEGIN SELECT * INTO rec_emp FROM employees WHERE last_name = p_family_name; RETURN rec_emp; END; PROCEDURE get_employees(p_dept_id employees.department_id%type) IS BEGIN SELECT * BULK COLLECT INTO emp_table FROM EMPLOYEES WHERE department_id = p_dept_id; END; PROCEDURE init_departments IS BEGIN FOR rec IN (SELECT department_id FROM departments) LOOP valid_departments(rec.department_id) := TRUE; END LOOP; END; PROCEDURE print_employee(p_rec_emp employees%rowtype) IS BEGIN DBMS_OUTPUT.PUT_LINE(p_rec_emp.department_id ||' '|| p_rec_emp.employee_id||' '|| p_rec_emp.first_name||' '|| p_rec_emp.last_name||' '|| p_rec_emp.job_id||' '|| p_rec_emp.salary); END; PROCEDURE show_employees IS BEGIN IF emp_table IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('Employees in Package table'); FOR i IN 1 .. emp_table.COUNT LOOP print_employee(emp_table(i)); END LOOP; END IF; END show_employees; FUNCTION valid_deptid(p_deptid IN departments.department_id%TYPE) RETURN BOOLEAN IS v_dummy PLS_INTEGER; BEGIN RETURN valid_departments.exists(p_deptid); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN FALSE; END valid_deptid; /* New set_salary procedure */ PROCEDURE set_salary(p_jobid VARCHAR2, p_min_salary NUMBER) IS CURSOR cur_emp IS SELECT employee_id FROM employees WHERE job_id = p_jobid AND salary < p_min_salary; BEGIN FOR rec_emp IN cur_emp LOOP UPDATE employees SET salary = p_min_salary WHERE employee_id = rec_emp.employee_id; END LOOP; END set_salary; BEGIN init_departments; END emp_pkg; / SHOW ERRORS
- b) Cree un disparador de fila denominado UPD_MINSALARY_TRG en la tabla JOBS, que llame al procedimiento EMP_PKG.SET_SALARY cuando el salario mínimo de la tabla JOBS se actualice para un identificador de trabajo especificado.
- Abra el script /home/oracle/labs/plpu/solns/sol_09_01_b.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para ejecutar el script. El código y el resultado se muestran a continuación.
CREATE OR REPLACE TRIGGER upd_minsalary_trg AFTER UPDATE OF min_salary ON JOBS FOR EACH ROW BEGIN emp_pkg.set_salary(:new.job_id, :new.min_salary); END; / SHOW ERRORS
- c) Escriba una consulta para mostrar el identificador de empleado, el apellido, el identificador de trabajo, el salario actual y el salario mínimo para los empleados que sean programadores, es decir, su JOB_ID es 'IT_PROG'. A continuación, actualice el salario mínimo en la tabla JOBS para aumentarlo en 1.000 dólares. ¿Qué sucede?
- Abra el script /home/oracle/labs/plpu/solns/sol_09_01_c.sql.
- Haga clic en el icono Run Script (F5) de la barra de herramientas de SQL Worksheet para ejecutar el script. El código y el resultado se muestran a continuación.
SELECT employee_id, last_name, salary FROM employees WHERE job_id = 'IT_PROG'; UPDATE jobs SET min_salary = min_salary + 1000 WHERE job_id = 'IT_PROG';