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';

La actualización de la columna min_salary del trabajo 'IT_PROG' falla porque el disparador UPD_MINSALARY_TRG de la tabla JOBS intenta actualizar los salarios de los empleados llamando al procedimiento EMP_PKG.SET_SALARY. El procedimiento SET_SALARY hace que el disparador CHECK_SALARY_TRG arranque (efecto en cascada). El disparador CHECK_SALARY_TRG llama al procedimiento CHECK_SALARY, que intenta leer los datos de la tabla JOBS. Mientras se lee la tabla JOBS, el procedimiento CHECK_SALARY detecta la excepción de tabla mutante.

2) Para resolver el problema de la tabla mutante, cree JOBS_PKG para mantener en memoria una copia de las filas de la tabla JOBS. A continuación, modifique el procedimiento CHECK_SALARY para utilizar los datos del paquete en vez de emitir una consulta en una tabla mutante para evitar la excepción. Sin embargo, debe crear un disparador de sentencia BEFORE INSERT OR UPDATE en la tabla EMPLOYEES para inicializar el estado del paquete JOBS_PKG antes de que arranque el disparador de fila CHECK_SALARY.

a) Cree un nuevo paquete denominado JOBS_PKG con la siguiente especificación:

    PROCEDURE initialize;
    FUNCTION get_minsalary(jobid VARCHAR2) RETURN NUMBER;
    FUNCTION get_maxsalary(jobid VARCHAR2) RETURN NUMBER;
    PROCEDURE set_minsalary(jobid VARCHAR2,min_salary NUMBER);
    PROCEDURE set_maxsalary(jobid VARCHAR2,max_salary NUMBER);

Abra el archivo sol_09_02_a.sql de la carpeta /home/oracle/labs/plpu/solns o copie y pegue el código siguiente en el área SQL Worksheet.

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 PACKAGE jobs_pkg IS
      PROCEDURE initialize;
      FUNCTION get_minsalary(p_jobid VARCHAR2) RETURN NUMBER;
      FUNCTION get_maxsalary(p_jobid VARCHAR2) RETURN NUMBER;
      PROCEDURE set_minsalary(p_jobid VARCHAR2, p_min_salary NUMBER);
      PROCEDURE set_maxsalary(p_jobid VARCHAR2, p_max_salary NUMBER);
    END jobs_pkg;
    /
    SHOW ERRORS

b) Implante el cuerpo de JOBS_PKG, como se detalla a continuación:

i. Declare una tabla de índice PL/SQL privada denominada jobs_tab_type indexada por un tipo de cadena basada en JOBS.JOB_ID%TYPE.

ii. Declare una variable privada denominada jobstab basada en jobs_tab_type.

iii. El procedimiento INITIALIZE lee las filas en la tabla JOBS con un bucle de cursor y utiliza el valor JOB_ID para el índice jobstab que se le asigne a la fila correspondiente.

iv. La función GET_MINSALARY utiliza un parámetro p_jobid como índice para jobstab y devuelve min_salary para dicho elemento.

v. La función GET_MAXSALARY utiliza un parámetro p_jobid como índice para jobstab y devuelve max_salary para dicho elemento.

vi. El procedimiento SET_MINSALARY utiliza su p_jobid como índice para jobstab con el fin de definir el campo min_salary de su elemento en el valor del parámetro min_salary.

vii. El procedimiento SET_MAXSALARY utiliza su p_jobid como índice para jobstab con el fin de definir el campo max_salary de su elemento en el valor del parámetro max_salary.

Abra el script /home/oracle/labs/plpu/solns/sol_09_02_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. Para compilar el cuerpo del paquete, haga clic con el botón derecho en el nombre o el cuerpo del paquete en el árbol Object Navigator y, a continuación, seleccione Compile.

    CREATE OR REPLACE PACKAGE BODY jobs_pkg IS
    TYPE jobs_tab_type IS TABLE OF jobs%rowtype
      INDEX BY jobs.job_id%type;
    jobstab jobs_tab_type;

      PROCEDURE initialize IS
      BEGIN
        FOR rec_job IN (SELECT * FROM jobs)
        LOOP
          jobstab(rec_job.job_id) := rec_job;
        END LOOP;
      END initialize;

      FUNCTION get_minsalary(p_jobid VARCHAR2) RETURN NUMBER IS
      BEGIN
        RETURN jobstab(p_jobid).min_salary;
      END get_minsalary;

      FUNCTION get_maxsalary(p_jobid VARCHAR2) RETURN NUMBER IS
      BEGIN
        RETURN jobstab(p_jobid).max_salary;
      END get_maxsalary;

      PROCEDURE set_minsalary(p_jobid VARCHAR2, p_min_salary NUMBER) IS
      BEGIN
        jobstab(p_jobid).max_salary := p_min_salary;
      END set_minsalary;

      PROCEDURE set_maxsalary(p_jobid VARCHAR2, p_max_salary NUMBER) IS
      BEGIN
        jobstab(p_jobid).max_salary := p_max_salary;
      END set_maxsalary;
    END jobs_pkg;
    /
   SHOW ERRORS

c) Copie el procedimiento CHECK_SALARY de la práctica 8, ejercicio 1a, y modifique el código sustituyendo la consulta de la tabla JOBS con sentencias para definir las variables locales minsal y maxsal con valores de los datos JOBS_PKG llamando a las funciones GET_*SALARY adecuadas. Este paso debe eliminar la excepción de disparador mutante.

Abra el script /home/oracle/labs/plpu/solns/sol_09_02_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.

    CREATE OR REPLACE PROCEDURE check_salary (p_the_job
    VARCHAR2, p_the_salary NUMBER) IS
      v_minsal jobs.min_salary%type;
      v_maxsal jobs.max_salary%type;
    BEGIN
      /*
      ** Commented out to avoid mutating trigger exception on the JOBS table
      SELECT min_salary, max_salary INTO v_minsal, v_maxsal
      FROM jobs
      WHERE job_id = UPPER(p_the_job);
      */

      v_minsal := jobs_pkg.get_minsalary(UPPER(p_the_job));
      v_maxsal := jobs_pkg.get_maxsalary(UPPER(p_the_job));
      IF p_the_salary NOT BETWEEN v_minsal AND v_maxsal THEN
        RAISE_APPLICATION_ERROR(-20100,
          'Invalid salary $' ||p_the_salary ||'. '||
          'Salaries for job '|| p_the_job ||
          ' must be between $'|| v_minsal ||' and $' ||
          v_maxsal);
      END IF;
    END;
    /
    SHOW ERRORS

d) Implante un disparador de sentencia BEFORE INSERT OR UPDATE denominado INIT_JOBPKG_TRG que utilice la sintaxis CALL para llamar al procedimiento JOBS_PKG.INITIALIZE, con el fin de garantizar que el estado del paquete sea actual antes de que se realicen las operaciones DML.

Abra el script /home/oracle/labs/plpu/solns/sol_09_02_d.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 init_jobpkg_trg
    BEFORE INSERT OR UPDATE ON jobs
    CALL jobs_pkg.initialize
    /
    SHOW ERRORS

e) Pruebe los cambios de código ejecutando la consulta para mostrar los empleados que son programadores y, a continuación, emita una sentencia de actualización para aumentar el salario mínimo del tipo de trabajo IT_PROG en 1.000 en la tabla JOBS. Después de esto, realice una consulta de los empleados con el tipo de trabajo IT_PROG para comprobar los cambios resultantes. ¿Los salarios de qué empleados se han definido en el mínimo para sus trabajos?

Abra el script /home/oracle/labs/plpu/solns/sol_09_02_e.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';

    SELECT employee_id, last_name, salary
    FROM employees
    WHERE job_id = 'IT_PROG';

A los empleados cuyos apellidos son Austin, Pataballa y Lorentz se les ha actualizado el salario. No se ha producido ninguna excepción durante este proceso y se ha implantado una solución para la excepción de disparador de tabla mutante.

3) Debido a que CHECK_SALARY_TRG arranca el procedimiento CHECK_SALARY, antes de insertar o actualizar un empleado, debe comprobar si aún funciona como se esperaba.

a) Pruébelo agregando un nuevo empleado mediante EMP_PKG.ADD_EMPLOYEE con los siguientes parámetros: (‘Steve’, ‘Morse’, ‘SMORSE’, and sal => 6500). ¿Qué sucede?

Abra el script /home/oracle/labs/plpu/solns/sol_09_03_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 a continuación.

    EXECUTE emp_pkg.add_employee('Steve', 'Morse', 'SMORSE', p_sal => 6500)

b) Para corregir el problema encontrado al agregar o actualizar un empleado:

i. Cree un disparador de sentencia BEFORE INSERT OR UPDATE denominado EMPLOYEE_INITJOBS_TRG en la tabla EMPLOYEES que llame al procedimiento JOBS_PKG.INITIALIZE.

ii. Utilice la sintaxis CALL en el cuerpo del disparador.

Abra el script /home/oracle/labs/plpu/solns/sol_09_03_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 TRIGGER employee_initjobs_trg
    BEFORE INSERT OR UPDATE OF job_id, salary ON employees
    CALL jobs_pkg.initialize
    /

c) Pruebe el disparador agregando el empleado Steve Morse de nuevo. Confirme el registro insertado en la tabla EMPLOYEES mostrando el identificador de empleado, el nombre y el apellido, el salario, el identificador de trabajo y el identificador de departamento.

Abra el script /home/oracle/labs/plpu/solns/sol_09_03_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.