Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T9»

Revisió del 09:55, 24 abr 2016

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