M2 - Bases de dades / Exercicis UF3: Llenguatges SQL / Solucions PL-SQL-2 T8

Soluciones a la Práctica 8-1: Creación de Disparadores de Sentencia y de Fila

En esta práctica, creará disparadores de sentencia y de fila. También creará procedimientos que se llamarán desde los disparadores.

1) Las filas de la tabla JOBS almacenan los salarios mínimos y máximos permitidos para los distintos valores de JOB_ID. Le piden que escriba un código para garantizar que el salario de los empleados esté dentro del rango permitido por su tipo de trabajo, para operaciones de inserción y actualización.

a) Cree un procedimiento denominado CHECK_SALARY, de la siguiente forma:

i) El procedimiento acepta dos parámetros, uno para la cadena del identificador de trabajo del empleado y el otro para el salario.

ii) El procedimiento utiliza el identificador de trabajo para determinar el salario mínimo y máximo para el trabajo especificado.

iii) Si el parámetro del salario, mínimo y máximo incluidos, no está dentro del rango de salarios, aparecerá una excepción de aplicación con el mensaje “Invalid salary <sal>. Salaries for job <jobid> must be between <min> and <max>”. Sustituya los distintos elementos del mensaje por los valores que proporcionan los parámetros y las variables rellenados con consultas. Guarde el archivo.

Abra el script /home/oracle/labs/plpu/solns/sol_08_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 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
        SELECT min_salary, max_salary INTO v_minsal, v_maxsal
        FROM jobs
        WHERE job_id = 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

b) Cree un disparador denominado CHECK_SALARY_TRG en la tabla EMPLOYEES que arranque ante una operación INSERT o UPDATE en cada fila:

i) El disparador debe llamar al procedimiento CHECK_SALARY para ejecutar la lógica de negocio.

ii) El disparador debe transferir el nuevo identificador de trabajo y salario a los parámetros de procedimiento.

Abra el script /home/oracle/labs/plpu/solns/sol_08_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 check_salary_trg
    BEFORE INSERT OR UPDATE OF job_id, salary
    ON employees
    FOR EACH ROW
    BEGIN
       check_salary(:new.job_id, :new.salary);
    END;
    /
    SHOW ERRORS

2) Pruebe el disparador CHECK_SAL_TRG utilizando los siguientes casos:

a) Utilice el procedimiento EMP_PKG.ADD_EMPLOYEE para agregar a la empleada Eleanor Beh al departamento 30. ¿Qué sucede? ¿Por qué?

Abra el script /home/oracle/labs/plpu/solns/sol_08_02_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('Eleanor', 'Beh', 30)

El disparador produce una excepción porque el procedimiento EMP_PKG.ADD_EMPLOYEE llama a una versión sobrecargada de sí mismo, que utiliza un salario por defecto de 1.000 dólares y un identificador de trabajo por defecto de SA_REP. Sin embargo, la tabla JOBS almacena un salario mínimo de 6.000 dólares para el tipo de trabajo SA_REP.

b) Actualice el salario del empleado 115 a 2.000 dólares. En otra operación de actualización, cambie el identificador de trabajo del empleado a HR_REP. ¿Qué sucede en cada caso?

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

    UPDATE employees
      SET salary = 2000
    WHERE employee_id = 115;
    UPDATE employees
      SET job_id = 'HR_REP'
    WHERE employee_id = 115;

La primera sentencia de actualización no consigue definir el salario en 2.000 dólares. La regla del disparador de comprobación del salario no consigue realizar la operación de actualización, porque el nuevo salario para el empleado 115 es menor que el mínimo que permite el identificador de trabajo PU_CLERK.

La segunda actualización no consigue cambiar el trabajo del empleado, porque el salario actual de éste es de 3.100 dólares, menor que el mínimo para un nuevo identificador de trabajo HR_REP.

c) Actualice el salario del empleado 115 a 2.800 dólares. ¿Qué sucede?

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

La operación de actualización se ha realizado correctamente, porque el nuevo salario está dentro del rango aceptable para el identificador de trabajo actual.

3) Actualice el disparador CHECK_SALARY_TRG para que arranque sólo cuando los valores del identificador de trabajo o el salario hayan cambiado en realidad.

a) Implante la regla de negocio utilizando una cláusula WHEN para comprobar si los valores JOB_ID o SALARY han cambiado.

Nota: asegúrese de que la condición maneja NULL en los valores de OLD.column_name si se realiza una operación INSERT; si no es así, la operación de inserción fallará.

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

    CREATE OR REPLACE TRIGGER check_salary_trg
    BEFORE INSERT OR UPDATE OF job_id, salary
    ON employees FOR EACH ROW
    WHEN (new.job_id <> NVL(old.job_id,'?') OR
          new.salary <> NVL(old.salary,0))
    BEGIN
      check_salary(:new.job_id, :new.salary);
    END;
    /
    SHOW ERRORS

b) Compruebe el disparador ejecutando el procedimiento EMP_PKG.ADD_EMPLOYEE con los siguientes valores de parámetros:

- p_first_name: 'Eleanor'

- p_last name: 'Beh'

- p_Email: 'EBEH'

- p_Job: 'IT_PROG'

- p_Sal: 5000

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

EXECUTE emp_pkg.add_employee('Eleanor', 'Beh', 'EBEH', 'IT_PROG', p_sal => 5000)

c) Actualice a los empleados con un trabajo IT_PROG incrementando su salario en 2.000 dólares. ¿Qué sucede?

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

    UPDATE employees
      SET salary = salary + 2000
    WHERE job_id = 'IT_PROG';

El salario de un empleado en el tipo de trabajo especificado excede el salario máximo para ese tipo de trabajo. No se ha actualizado ningún salario de los empleados con tipo de trabajo IT_PROG.

d) Actualice a 9.000 dólares el salario de Eleanor Beh.

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

    UPDATE employees
      SET salary = 9000
    WHERE employee_id = (SELECT employee_id
                         FROM employees
                         WHERE last_name = 'Beh');

Indicación: utilice una sentencia UPDATE con una subconsulta en la cláusula WHERE. ¿Qué sucede?

e) Cambie el trabajo de Eleanor Beh a ST_MAN utilizando otra sentencia UPDATE con una subconsulta. ¿Qué sucede?

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

    UPDATE employees
      set job_id = 'ST_MAN'
    WHERE employee_id = (SELECT employee_id
                         FROM employees
                         WHERE last_name = 'Beh');

El salario máximo del nuevo tipo de trabajo es menor que el salario actual del empleado; por lo tanto, la operación de actualización falla.

4) Se le pide que evite que se suprima a los empleados durante las horas laborables.

a) Escriba un disparador de sentencia denominado DELETE_EMP_TRG en la tabla EMPLOYEES para evitar que las filas se supriman durante horas laborables entre semana, es decir, de las 9:00 a.m. a las 6:00 p.m.

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

    CREATE OR REPLACE TRIGGER delete_emp_trg
    BEFORE DELETE ON employees
    DECLARE
      the_day VARCHAR2(3) := TO_CHAR(SYSDATE, 'DY');
      the_hour PLS_INTEGER := TO_NUMBER(TO_CHAR(SYSDATE,'HH24'));
    BEGIN
      IF (the_hour BETWEEN 9 AND 18) AND (the_day NOT IN ('SAT','SUN')) THEN
         RAISE_APPLICATION_ERROR(-20150, 'Employee records cannot be deleted during
                                 the business hours of 9AM and 6PM');
      END IF;
    END;
    /
    SHOW ERRORS

b) Intente suprimir los empleados con JOB_ID SA_REP que no estén asignados a

un departamento.

Indicación: empleado Grant con identificador 178.

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

    DELETE FROM employees
    WHERE job_id = 'SA_REP' AND department_id IS NULL;

Nota: en función de la hora actual en la máquina host del aula, es posible que pueda o que no pueda realizar operaciones de supresión. Por ejemplo, en la captura de pantalla anterior, falla la operación de supresión, ya que se realiza fuera de las horas laborables permitidas (según la hora de la máquina host).