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

Revisió del 20:39, 22 abr 2016

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.