• Author / Uploaded
• Ezequiel Hernán Villanueva

Citation preview

Introduction to Oracle: SQL and PL/SQL, Instructor Guide, Volumen 1

Práctica 23: Gestionando Excepciones --------------------------------------------------------------------

Ejercicio 1: Escribe un Bloque PL/SQL para seleccionar el nombre del empleado con un valor de salario dado. a) Si el salario ingresado retorna más de un registro, trate la ex cepción con un gestor de excepción apropiado e inserte dentro de la tabla MESSAGES el mensaje "Más de un Emp leado con un salario de ." b) Si el salario ingresado no retorna ningún registro-fila, trate la excepción con un gestor de excepción apropiado e inserte dentro de la tabla MESSAGES el mensaje "Ningún emplea do con un salario de ". c) Si el salario ingresado retorna un registro-fila solamente, i nserte dentro de la tabla MESSAGES el nombre del empleado y el monto del salario. d) Trate cualquier otra excepción con un gestor de excepción apropia do e inserte dentro de la tabla MESSAGES el mensaje "Ocurrió algún otro error" e) Testee el Bloque para una variedad de casos de prueba " RESULTS ------SMITH - 800 Mas de un empleado con un salario de 3000 Ningún empleado con un salario de 6000 " Respuesta 1) -----------SQL>

TRUNCATE TABLE MESSAGES;

SQL>

edit

p23q1.sql

Contenido del archivo de comandos con el Bloque PL/SQL: p23q1.sql ----------------------------------------------------------------" /* SET SERVEROUTPUT ON */ ACCEPT p_sal PROMPT 'Por favor ingrese un valor de salario: '; DECLARE v_emp_sal EMP.SAL%TYPE := &p_sal; v_emp_ename EMP.ENAME%TYPE; -- 1.a) Declaración de la Excepción definida por usuario para caso q ue retorna mas de un empleado con ese salario exc_mas_de_uno EXCEPTION;

-- 1.b) Declaración de la Excepción definida por usuario para caso q ue no retorna ningún empleado con ese salario exc_ninguno EXCEPTION; BEGIN SELECT ENAME INTO v_emp_ename FROM EMP WHERE SAL = v_emp_sal; IF SQL%ROWCOUNT > 1 THEN RAISE exc_mas_de_uno; ELSIF SQL%ROWCOUNT = 0 THEN RAISE exc_ninguno; ELSIF SQL%ROWCOUNT = 1 THEN --1.c) Caso Normal que retorna un empleado con ese salar io ingresado INSERT INTO MESSAGES VALUES ( v_emp_ename || ' - ' || v_emp_sal ); COMMIT; END IF; EXCEPTION WHEN exc_mas_de_uno THEN INSERT INTO MESSAGES VALUES ( 'Más de un Empleado con un salario de ' || v_emp _sal ); COMMIT; WHEN exc_ninguno THEN INSERT INTO MESSAGES VALUES ( 'Ningún empleado con un salario de ' || v_emp_sa l ); COMMIT; WHEN OTHERS THEN INSERT INTO MESSAGES VALUES ( 'Ocurrió algún otro error' ); COMMIT; END; / /* SET SERVEROUTPUT OFF */ " SQL>

start

p23q1.sql

Con el valor de salario: 800 Procedimiento PL/SQL terminado correctamente. SQL> SELECT RESULTS FROM MESSAGES; RESULTS ------SMITH - 800 SQL>

start

p23q1.sql

Con el valor de salario: 3000 Procedimiento PL/SQL terminado correctamente. SQL> SELECT RESULTS FROM MESSAGES; RESULTS ------SMITH - 800 Ocurrió algún otro error

[>>>MAL FUNCIONAMIENTO DE: p23q1.sql >>MAL FUNCIONAMIENTO DE: p23q1.sql start p23q1_v2.sql SQL> start p23q1_v2.sql SQL> start p23q1_v2.sql Ejercicio 2: Modifique el archivo de comandos p18q3.sql para añadirle un gestor de excepciones.

a) Escriba un gestor de excepción para el error que pase un mensaj e al usuario que diga que el departamento especificado no existe. b) Ejecute el Bloque PL/SQL ingresando un número de departamento q ue no existe. " Por favor ingrese el número de departamento: 50 Por favor ingrese la localización del departamento: HOUSTO N Procedimiento PL/SQL terminado correctamente. G_MESSAGE -----------------------------------------Departamento 50 es un departamento inválidado. " Respuesta 2) -----------Contenido del archivo de comandos con el Bloque PL/SQL: p18q3.sql ----------------------------------------------------------------" ACCEPT p_dept_deptno PROMPT 'Por favor ingrese el número del departame nto: '; ACCEPT p_dept_loc rtamento: '; DECLARE v_dept_deptno v_dept_loc BEGIN v_dept_deptno v_dept_loc UPDATE DEPT SET loc WHERE deptno COMMIT; END; / " SQL>

edit

PROMPT 'Por favor ingrese la localidad del depa DEPT.DEPTNO%TYPE; DEPT.LOC%TYPE; := :=

&p_dept_deptno; '&p_dept_loc';

= v_dept_loc = v_dept_deptno;

p23q2.sql

Contenido del archivo de comandos con el Bloque PL/SQL: p23q2.sql ----------------------------------------------------------------" SET SERVEROUTPUT ON ACCEPT p_dept_deptno PROMPT 'Por favor ingrese el número del departame nto: '; ACCEPT p_dept_loc PROMPT 'Por favor ingrese la localidad del depa rtamento: '; DECLARE v_dept_deptno DEPT.DEPTNO%TYPE; v_dept_loc DEPT.LOC%TYPE; exc_dept_deptno_no_existe EXCEPTION; --Declaración de la Exc epción BEGIN v_dept_deptno := &p_dept_deptno; v_dept_loc := '&p_dept_loc'; UPDATE DEPT SET loc = v_dept_loc

WHERE deptno = v_dept_deptno; -- Si (función del CURSOR SQL IMPLÍCITO) la sentencia SQL Mas recien te no afectó ningún registro-fila entonces provoca la gestión de la excepción IF SQL%NOTFOUND THEN RAISE exc_dept_deptno_no_existe; END IF; COMMIT; EXCEPTION --Gestion de la Excepción WHEN exc_dept_deptno_no_existe THEN DBMS_OUTPUT.PUT_LINE( 'El Departamento ' || v_dept_deptn o || ' no es válido.' ); END; / SET SERVEROUTPUT OFF " SQL> start p23q2.sql Con el número de departamento: 25 Procedimiento PL/SQL terminado correctamente El Departamento 25 no es válido. Ejercicio 3: Escribe un Bloque PL/SQL que imprima el número de empleados que ha cen mas/menos $100 del valor de salario ingresado. a) Si no hay empleados dentro de ese rango de salarios, imprima un mensaje indicando al usuario cuál es el caso. Use una excepción para este caso. b) Si hay uno o mas empleados dentro de ese rango de salarios, e l mensaje debería indicar cuántos empleados están comprendidos en ese rango de salario s. c) Trate cualquier otra excepción con un gestor de excepciones apr opiado. El mensaje debería indicar que ocurrió algún otro error. Respuesta 3) -----------SQL>

edit

p23q3.sql

Contenido del archivo de comandos con el Bloque PL/SQL: p23q3.sql ----------------------------------------------------------------" SET SERVEROUTPUT ON ACCEPT p_emp_sal PROMPT 'Por favor ingrese el salario: '; DECLARE v_emp_sal EMP.SAL%TYPE := &p_emp_sal; v_cuenta NUMBER; v_min EMP.SAL%TYPE := v_emp_sal - 100; v_max EMP.SAL%TYPE := v_emp_sal + 100; exc_ninguno EXCEPTION; --Declaración de la Excepción para e l caso en que no hay empleados en ese rango de valores de salario BEGIN SELECT COUNT(*) INTO v_cuenta FROM EMP WHERE SAL BETWEEN v_min AND v_max; IF v_cuenta = 0 THEN RAISE exc_ninguno; --Provoca la Excepción. Caso en qu

e no hay Empleados en ese rango de valores de salario END IF; DBMS_OUTPUT.PUT_LINE('Hay ' || v_cuenta || ' Empleados con salar io entre ' || v_min || ' y ' || v_max); EXCEPTION WHEN exc_ninguno THEN --Gestiona la Excepción. Caso en q ue no hay Empleados en ese rango de valores de salario DBMS_OUTPUT.PUT_LINE('No hay Empleados con salario entre ' || v_min || ' y ' || v_max); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Ocurrió algún otro error'); END; / SET SERVEROUTPUT OFF " SQL> start p23q3.sql Con el salario: 0 No hay Empleados con salario entre -100 y 100 Procedimiento PL/SQL terminado correctamente. SQL> start p23q3.sql Con el valor: 1100 Hay 1 Empleados con salario entre 1000 y 1200 Procedimiento PL/SQL terminado correctamente. SQL> start p23q3.sql Con el valor: 9000 No Hay Empleados con salario entre 8900 y 9100 Procedimiento PL/SQL terminado correctamente. SQL> start p23q3.sql Con el valor: lalala Muestra una gran cantidad de mensajes de error. El Procedimiento PL/SQL no termina.