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

Solución 8-1: Manejo de Excepciones Predefinidas

En esta práctica, escribirá un bloque PL/SQL que aplique una excepción predefinida para procesar un único registro a la vez. El bloque PL/SQL seleccionará el nombre del empleado con un valor de salario determinado.

1) Ejecute el comando en el archivo lab_05_01.sql para volver a crear la tabla messages.

2) En la sección de declaraciones, declare dos variables: v_ename del tipo employees.last_name y v_emp_sal del tipo employees.salary. Inicialice la última en 6000.

DECLARE
  v_ename employees.last_name%TYPE;
  v_emp_sal employees.salary%TYPE := 6000;

3) En la sección ejecutable, recupere los apellidos de los empleados cuyos salarios son iguales al valor de v_emp_sal. Si el salario introducido devuelve sólo una fila, inserte en la tabla messages el nombre y el importe del salario del empleado.

Nota: no utilice cursores explícitos.

BEGIN
  SELECT last_name INTO v_ename
  FROM employees
  WHERE salary = v_emp_sal;
  INSERT INTO messages (results)
  VALUES (v_ename || ' - ' || v_emp_sal);

4) Si el salario introducido no devuelve ninguna fila, maneje la excepción con un manejador de excepciones adecuado e inserte en la tabla messages el mensaje “No employee with a salary of <salary>”.

EXCEPTION
  WHEN no_data_found THEN
    INSERT INTO messages (results)
    VALUES ('No employee with a salary of '|| TO_CHAR(v_emp_sal));

5) Si el salario introducido devuelve varias filas, maneje la excepción con un manejador de excepciones adecuado e inserte en la tabla messages el mensaje “More than one employee with a salary of <salary>”.

WHEN too_many_rows THEN
  INSERT INTO messages (results)
  VALUES ('More than one employee with a salary of '|| TO_CHAR(v_emp_sal));

6) Maneje cualquier otra excepción con un manejador de excepciones adecuado e inserte en la tabla messages el mensaje “Some other error occurred”.

WHEN others THEN
  INSERT INTO messages (results)
  VALUES ('Some other error occurred.');
END;

7) Muestre las filas de la tabla messages para comprobar si el bloque PL/SQL se ha ejecutado correctamente.

/
SELECT * FROM messages;

La salida es la siguiente:

8) Cambie el valor inicializado de v_emp_sal a 2000 y vuelva a ejecutar. La salida es la siguiente:

DECLARE
  v_ename employees.last_name%TYPE;
  v_emp_sal employees.salary%TYPE := 6000;
BEGIN
  SELECT last_name INTO v_ename
  FROM employees
  WHERE salary = v_emp_sal;
  INSERT INTO messages (results)
  VALUES (v_ename || ' - ' || v_emp_sal);
EXCEPTION
  WHEN no_data_found THEN
    INSERT INTO messages (results)
    VALUES ('No employee with a salary of '|| TO_CHAR(v_emp_sal));
  WHEN too_many_rows THEN
    INSERT INTO messages (results)
    VALUES ('More than one employee with a salary of '|| TO_CHAR(v_emp_sal));
  WHEN others THEN
    INSERT INTO messages (results)
    VALUES ('Some other error occurred.');
END;
/
SELECT * FROM messages;

Solución 8-2: Manejo de Excepciones de Oracle Server Estándar

En esta práctica, escribirá un bloque PL/SQL que declare una excepción para el error de Oracle Server ORA-02292 (integrity constraint violated – child record found). El bloque comprobará la excepción y mostrará el mensaje de error.

1) En la sección de declaraciones, declare una excepción e_childrecord_exists. Asocie la excepción declarada al error de Oracle Server estándar –02292.

SET SERVEROUTPUT ON
DECLARE
  e_childrecord_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_childrecord_exists, -02292);

2) En la sección ejecutable, muestre “Deleting department 40....” Incluya una sentencia DELETE para suprimir el departamento con department_id 40.

BEGIN
  DBMS_OUTPUT.PUT_LINE(' Deleting department 40........');
  delete from departments where department_id=40;

3) Incluya una sección de excepciones para manejar la excepción e_childrecord_exists y muestre el mensaje adecuado.

EXCEPTION
  WHEN e_childrecord_exists THEN
  DBMS_OUTPUT.PUT_LINE(' Cannot delete this department. There are employees in this department (child records exist.) ');
END;

La salida de ejemplo es la siguiente:

SET SERVEROUTPUT ON
DECLARE
  e_childrecord_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_childrecord_exists, -02292);
BEGIN
  DBMS_OUTPUT.PUT_LINE(' Deleting department 40........');
  delete from departments where department_id=40;
EXCEPTION
  WHEN e_childrecord_exists THEN
  DBMS_OUTPUT.PUT_LINE(' Cannot delete this department. There are employees in this department (child records exist.) ');
END;