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;