Introduction to Oracle: SQL and PL/SQL, Instructor Guide, Volumen 1 Introduction to Oracle: SQL and PL/SQL, Instructor Guide, Volumen 1
Práctica 23: Gestionando Excepciones Práctica 23: Gestionando Excepciones
---Ejercicio 1: Ejercicio 1:
Escribe un Bloque PL/SQL para seleccionar el nombre del empleado con un Escribe un Bloque PL/SQL para seleccionar el nombre del empleado con un valor de salario dado.
valor de salario dado.
a) Si el salario ingresado retorna más de un registro, trate la ex a) Si el salario ingresado retorna más de un registro, trate la ex cepción con un gestor de excepción apropiado
cepción con un gestor de excepción apropiado
e inserte dentro de la tabla MESSAGES el mensaje "Más de un Emp e inserte dentro de la tabla MESSAGES el mensaje "Más de un Emp leado con un salario de <salario>."
leado con un salario de <salario>."
b) Si el salario ingresado no retorna ningún registro-fila, trate b) Si el salario ingresado no retorna ningún registro-fila, trate la excepción con un gestor de excepción apropiado
la excepción con un gestor de excepción apropiado
e inserte dentro de la tabla MESSAGES el mensaje "Ningún emplea e inserte dentro de la tabla MESSAGES el mensaje "Ningún emplea do con un salario de <salario>".
do con un salario de <salario>".
c) Si el salario ingresado retorna un registro-fila solamente, i c) Si el salario ingresado retorna un registro-fila solamente, i nserte dentro de la tabla MESSAGES el nombre del empleado
nserte dentro de la tabla MESSAGES el nombre del empleado y el monto del salario.
y el monto del salario.
d) Trate cualquier otra excepción con un gestor de excepción apropia d) Trate cualquier otra excepción con un gestor de excepción apropia do e inserte dentro de la tabla MESSAGES el mensaje
do e inserte dentro de la tabla MESSAGES el mensaje "Ocurrió algún otro error"
"Ocurrió algún otro error"
e) Testee el Bloque para una variedad de casos de prueba e) Testee el Bloque para una variedad de casos de prueba
" " RESULTS RESULTS ---SMITH - 800 SMITH - 800
Mas de un empleado con un salario de 3000 Mas de un empleado con un salario de 3000 Ningún empleado con un salario de 6000 Ningún empleado con un salario de 6000 " " Respuesta 1) Respuesta 1) ---S SQQLL>> TTRRUUNNCCAATTE E TTAABBLLEE MMEESSSSAAGGEESS;; S SQQLL>> eeddiitt pp2233qq11..ssqqll
Contenido del archivo de comandos con el Bloque PL/SQL: p23q1.sql Contenido del archivo de comandos con el Bloque PL/SQL: p23q1.sql ---" " / /** SSEETT SSEERRVVEERROOUUTTPPUUTT OONN **// AC
ACCECEPTPT p_p_sasall PRPROMOMPT PT 'P'Por or fafavovor ir ingngrerese se un un vavalolor r de de sasalalaririo: o: ';'; DECLARE DECLARE v v__eemmpp__ssaall EEMMPP..SSAALL%%TTYYPPEE ::== &&pp__ssaall;; v v__eemmpp__eennaammee EEMMPP..EENNAAMMEE%%TTYYPPEE;;
-- 1.a) Declaración de la Excepción definida por usuario para caso q -- 1.a) Declaración de la Excepción definida por usuario para caso q ue retorna mas de un empleado con ese salario
ue retorna mas de un empleado con ese salario ex
-- 1.b) Declaración de la Excepción definida por usuario para caso q -- 1.b) Declaración de la Excepción definida por usuario para caso q ue no retorna ningún empleado con ese salario
ue no retorna ningún empleado con ese salario e exxcc__nniinngguunnoo EEXXCCEEPPTTIIOONN;; BEGIN BEGIN S SEELLEECCTT EENNAAMMEE I INNTTOO vv__eemmpp__eennaammee F FRROOMM EEMMPP W WHHEERREE SSAAL = L = vv__eemmpp__ssaall;; I IFF SSQQLL%%RROOWWCCOOUUNNT T > > 11 TTHHEENN R RAAIISSEE eexxcc__mmaass__ddee__uunnoo;; E ELLSSIIFF SSQQLL%%RROOWWCCOOUUNNT T = = 00 TTHHEENN R RAAIISSEE eexxcc__nniinngguunnoo;; E ELLSSIIFF SSQQLL%%RROOWWCCOOUUNNT T = = 11 TTHHEENN
--1.c) Caso Normal que retorna un empleado con ese salar --1.c) Caso Normal que retorna un empleado con ese salar io ingresado
io ingresado
I
INNSSEERRTT ININTTOO MMEESSSSAAGGEESS VA
VALULUESES ( v_( v_ememp_p_enaename me ' - ' ' - ' v_ v_ememp_p_sasal );l ); COMMIT; COMMIT; END IF; END IF; EXCEPTION EXCEPTION W WHHEENN eexxcc__mmaass__ddee__uunnoo TTHHEENN I INNSSEERRT T IINNTTOO MMEESSSSAAGGEESS VAL
VALUESUES ( 'M( 'Más dás de un Ee un Emplmpleadeado coo con un n un salsalariario de o de ' ' v_ev_empmp _sal ); _sal ); COMMIT; COMMIT; W WHHEENN eexxcc__nniinngguunnoo TTHHEENN I INNSSEERRT T IINNTTOO MMEESSSSAAGGEESS VAL
VALUESUES ( 'Ni( 'Ningúngún empn emplealeado codo con un sn un salaalario drio de ' ve ' v_em_emp_sp_saa l ); l ); COMMIT; COMMIT; W WHHEENN OOTTHHEERRSS TTHHEENN I INNSSEERRT T IINNTTOO MMEESSSSAAGGEESS VA
VALULUESES ( 'O( 'Ocucurrrrió aió alglgún oún otrtro ero erroror' )r' );; COMMIT; COMMIT; END; END; / / / /** SSEETT SSEERRVVEERROOUUTTPPUUTT OOFFFF **// " " S SQQLL>> ssttaarrtt pp2233qq11..ssqqll
Con el valor de salario: 800 Con el valor de salario: 800
Procedimiento PL/SQL terminado correctamente. Procedimiento PL/SQL terminado correctamente. S SQQLL>> SSEELLEECCTT RREESSUULLTTSS F FRROOMM MMEESSSSAAGGEESS;; RESULTS RESULTS ---SMITH - 800 SMITH - 800 S SQQLL>> ssttaarrtt pp2233qq11..ssqqll
Con el valor de salario: 3000 Con el valor de salario: 3000
Procedimiento PL/SQL terminado correctamente. Procedimiento PL/SQL terminado correctamente. S SQQLL>> SSEELLEECCTT RREESSUULLTTSS F FRROOMM MMEESSSSAAGGEESS;; RESULTS RESULTS ---SMITH - 800 SMITH - 800
Ocurrió algún otro error Ocurrió algún otro error
[>>>MAL FUNCIONAMIENTO DE: p23q1.sql <<<] [>>>MAL FUNCIONAMIENTO DE: p23q1.sql <<<]
S
SQQLL>> ssttaarrtt pp2233qq11..ssqqll
Con el valor de salario: 6000 Con el valor de salario: 6000
Procedimiento PL/SQL terminado correctamente. Procedimiento PL/SQL terminado correctamente. S SQQLL>> SSEELLEECCTT RREESSUULLTTSS F FRROOMM MMEESSSSAAGGEESS;; RESULTS RESULTS ---SMITH - 800 SMITH - 800
Ocurrió algún otro error Ocurrió algún otro error Ocurrió algún otro error Ocurrió algún otro error [>>>MAL FUNCIONAMIENTO DE: p23q1.sql <<<] [>>>MAL FUNCIONAMIENTO DE: p23q1.sql <<<]
Respuesta del APÉNDICE A-79 Respuesta del APÉNDICE A-79 ---S SQQLL>> TTRRUUNNCCAATTE E TTAABBLLEE MMEESSSSAAGGEESS;; S SQQLL>> eeddiitt pp2233qq11__vv22..ssqqll
Contenido del archivo de comandos con el Bloque PL/SQL: p23q1_v2.sql Contenido del archivo de comandos con el Bloque PL/SQL: p23q1_v2.sql ---" " S SEETT VVEERRIIFFYY OOFFFF AC
ACCECEPTPT p_p_sasall PRPROMOMPT PT 'P'Por or fafavovor ir ingngrerese se un un vavalolor r de de sasalalaririo: o: ';'; DECLARE DECLARE v v__eennaammee EMEMPP..EENNAAMMEE%%TTYYPPEE;; v v__ssaall EEMMPP..SSAALL%%TTYYPPEE ::== &&pp__ssaall;; BEGIN BEGIN S SEELLEECCTT EENNAAMMEE I INNTTOO vv__eennaammee F FRROOMM EEMMPP W WHHEERREE SSAAL L = = vv__ssaall;; I INNSSEERRTT IINNTTOO MMEESSSSAAGGEES S ( ( RREESSUULLTTS S )) VA
VALULUESES ( v( v_e_enaname me ' - ' - ' ' v v_s_sal al );); EXCEPTION EXCEPTION W WHHEENN nnoo__ddaattaa__ffoouunndd TTHHEENN I INNSSEERRT T IINNTTOO MMEESSSSAAGGEES S ( ( RREESSUULLTTS S )) VA
VALULUESES ( ( 'N'Nininguguno no cocon n sasalalaririo o ' ' v_v_sasal l );); W
WHHEENN ttoooo__mmaannyy__rroowwss TTHHEENN I
INNSSEERRT T IINNTTOO MMEESSSSAAGGEES S ( ( RREESSUULLTTS S )) VA
VALULUESES ( 'M( 'Más dás de une uno coo con san salalaririo ' o ' v_v_sasal );l ); W
WHHEENN OOTTHHEERRSS TTHHEENN I
INNSSEERRT T IINNTTOO MMEESSSSAAGGEES S ( ( RREESSUULLTTS S )) VA
VALULUESES ( 'O( 'Ocucurrrrió aió alglgún oún otrtro ero erroror' )r' );; END; END; / / S SEETT VVEERRIIFFYY OONN " " S SQQLL>> ssttaarrtt p2p233qq11__vv22..ssqqll S SQQLL>> ssttaarrtt p2p233qq11__vv22..ssqqll S SQQLL>> ssttaarrtt p2p233qq11__vv22..ssqqll Ej
Ejerercicicicio 2:o 2: MoModidififiquque el ae el arcrchihivo dvo de coe comamandndos pos p1818q3q3.s.sql pql parara aña añadadirirle ule un gen geststoror de excepciones.
a) Escriba un gestor de excepción para el error que pase un mensaj 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.
e al usuario que diga que el departamento especificado no existe.
b) Ejecute el Bloque PL/SQL ingresando un número de departamento q b) Ejecute el Bloque PL/SQL ingresando un número de departamento q ue no existe.
ue no existe.
" "
Por favor ingrese el número de departamento: 50 Por favor ingrese el número de departamento: 50
Por favor ingrese la localización del departamento: HOUSTO Por favor ingrese la localización del departamento: HOUSTO N
N
Procedimiento PL/SQL terminado correctamente. Procedimiento PL/SQL terminado correctamente. G_MESSAGE
G_MESSAGE
---Departamento
Departamento 50 50 es es un un departamento departamento inválidado.inválidado. " " Respuesta 2) Respuesta 2)
---Contenido del archivo de comandos con el Bloque PL/SQL: p18q3.sql Contenido del archivo de comandos con el Bloque PL/SQL: p18q3.sql ---"
" AC
ACCECEPTPT p_p_dedeptpt_d_depeptntnoo PRPROMOMPTPT 'P'Por or fafavovor r iningrgresese e el el núnúmemero ro dedel l dedepapartrtamamee nto: '; nto: '; A ACCCCEEPPTT pp__ddeepptt__lloocc PPRROOMMPPTT ''PPoor r ffaavvoor r iinnggrreesse e lla a llooccaalliiddaad d ddeel l ddeeppaa rtamento: '; rtamento: '; DECLARE DECLARE v_
v_dedeptpt_d_depeptntnoo DEPTDEPT.D.DEPEPTNTNO%O%TYTYPEPE;; v v__ddeepptt__lloocc DDEEPPTT..LLOOCC%%TTYYPPEE;; BEGIN BEGIN v v__ddeepptt__ddeeppttnnoo ::== &&pp__ddeepptt__ddeeppttnnoo;; v v__ddeepptt__lloocc ::== '&'&pp__ddeepptt__lloocc'';; U UPPDDAATTEE DDEEPPTT S SEETT lloocc = v= v__ddeepptt__lloocc WH
WHEREREE dedeptptno no = = v_v_dedeptpt_d_depeptntno;o; COMMIT; COMMIT; END; END; / / " " S SQQLL>> eeddiitt pp2233qq22..ssqqll
Contenido del archivo de comandos con el Bloque PL/SQL: p23q2.sql Contenido del archivo de comandos con el Bloque PL/SQL: p23q2.sql ---" " S SEETT SSEERRVVEERROOUUTTPPUUTT OONN AC
ACCECEPTPT p_p_dedeptpt_d_depeptntnoo PRPROMOMPTPT 'P'Por or fafavovor r iningrgresese e el el núnúmemero ro dedel l dedepapartrtamamee nto: '; nto: '; A ACCCCEEPPTT pp__ddeepptt__lloocc PPRROOMMPPTT ''PPoor r ffaavvoor r iinnggrreesse e lla a llooccaalliiddaad d ddeel l ddeeppaa rtamento: '; rtamento: '; DECLARE DECLARE v_
v_dedeptpt_d_depeptntnoo DEPTDEPT.D.DEPEPTNTNO%O%TYTYPEPE;; v v__ddeepptt__lloocc DDEEPPTT..LLOOCC%%TTYYPPEE;; e exxcc__ddeepptt__ddeeppttnnoo__nnoo__eexxiissttee EXEXCCEEPPTTIIOONN; -; ---DDeeccllaarraacciióón dn de le la Ea Exxcc epción epción BEGIN BEGIN v v__ddeepptt__ddeeppttnnoo ::== &&pp__ddeepptt__ddeeppttnnoo;; v v__ddeepptt__lloocc ::== '&'&pp__ddeepptt__lloocc'';; U UPPDDAATTEE DDEEPPTT S SEETT lloocc = v= v__ddeepptt__lloocc
WH
WHEREREE dedeptptno no = = v_v_dedeptpt_d_depeptntno;o;
-- Si (función del CURSOR SQL IMPLÍCITO) la sentencia SQL Mas recien -- 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
te no afectó ningún registro-fila entonces provoca la gestión de la excepción I
IFF SSQQLL%%NNOOTTFFOOUUNNDD TTHHEENN RA
RAISISEE exexc_c_dedeptpt_d_depeptntno_o_nono_e_exixistste;e; END IF; END IF; COMMIT; COMMIT; EXCEPTION EXCEPTION --Gestion de la Excepción --Gestion de la Excepción W WHHEENN eexxcc__ddeepptt__ddeeppttnnoo__nnoo__eexxiissttee TTHHEENN
DBMS_OUTPUT.PUT_LINE( 'El Departamento ' v_dept_deptn DBMS_OUTPUT.PUT_LINE( 'El Departamento ' v_dept_deptn o ' no es válido.' ); o ' no es válido.' ); END; END; / / S SEETT SSEERRVVEERROOUUTTPPUUTT OOFFFF " " S SQQLL>> ssttaarrtt pp2233qq22..ssqqll Co
Con en el nl númúmerero do de de depeparartatamementnto:o: 2525
Procedimiento PL/SQL terminado correctamente Procedimiento PL/SQL terminado correctamente El Departamento 25 no es válido.
El Departamento 25 no es válido. Ej
Ejerercicicicio 3:o 3: EsEscrcribibe un Be un Bloloquque PLe PL/S/SQL qQL que iue impmpririma ema el núl númemero dro de eme emplpleaeadodos qus que hae ha cen mas/menos $100 del valor de salario ingresado.
cen mas/menos $100 del valor de salario ingresado.
a) Si no hay empleados dentro de ese rango de salarios, imprima a) Si no hay empleados dentro de ese rango de salarios, imprima un mensaje indicando al usuario cuál es el caso.
un mensaje indicando al usuario cuál es el caso. Use una excepción para este caso. Use una excepción para este caso.
b) Si hay uno o mas empleados dentro de ese rango de salarios, e 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 l mensaje debería indicar cuántos empleados están comprendidos en ese rango de salario s.
s.
c) Trate cualquier otra excepción con un gestor de excepciones apr c) Trate cualquier otra excepción con un gestor de excepciones apr opiado. El mensaje debería indicar que ocurrió algún otro error.
opiado. El mensaje debería indicar que ocurrió algún otro error. Respuesta 3) Respuesta 3) ---S SQQLL>> eeddiitt pp2233qq33..ssqqll
Contenido del archivo de comandos con el Bloque PL/SQL: p23q3.sql Contenido del archivo de comandos con el Bloque PL/SQL: p23q3.sql ---" " S SEETT SSEERRVVEERROOUUTTPPUUTT OONN A ACCCCEEPPTT pp__eemmpp__ssaall PPRROOMMPPT T ''PPoor r ffaavvoor r iinnggrreesse e eel l ssaallaarriioo: : '';; DECLARE DECLARE v v__eemmpp__ssaall EEMMPP..SSAALL%%TTYYPPEE ::== &&pp__eemmpp__ssaall;; v v__ccuueennttaa NNUUMMBBEERR;; v v__mmiinn EEMMPP..SSAALL%%TTYYPPEE ::== vv__eemmpp__ssaal l - - 110000;; v v__mmaaxx EEMMPP..SSAALL%%TTYYPPEE ::== vv__eemmpp__ssaal l + + 110000;; e exxcc__nniinngguunnoo EEXXCCEEPPTTIIOONN;; ----DDeeccllaarraacciióón n dde e lla a EExxcceeppcciióón n ppaarra a ee l caso en que no hay empleados en ese rango de valores de salario
l caso en que no hay empleados en ese rango de valores de salario BEGIN BEGIN S SEELLEECCTT CCOOUUNNTT((**)) I INNTTOO vv__ccuueennttaa F FRROOMM EEMMPP WH
WHEREREE SASAL BL BETETWEWEEN EN v_v_mimin An AND ND v_v_mamax;x; I
IFF vv__ccuueenntta a = = 00 TTHHEENN R
e no hay Empleados en ese rango de valores de salario e no hay Empleados en ese rango de valores de salario
END IF; END IF;
DBMS_OUTPUT.PUT_LINE('Hay ' v_cuenta ' Empleados con salar DBMS_OUTPUT.PUT_LINE('Hay ' v_cuenta ' Empleados con salar io entre ' v_min ' y ' v_max);
io entre ' v_min ' y ' v_max); EXCEPTION
EXCEPTION W
WHHEENN eexxcc__nniinngguunnoo TTHHEENN ----GGeessttiioonna a lla a EExxcceeppcciióónn. . CCaasso o een n qq ue no hay Empleados en ese rango de valores de salario
ue no hay Empleados en ese rango de valores de salario
DBMS_OUTPUT.PUT_LINE('No hay Empleados con salario entre DBMS_OUTPUT.PUT_LINE('No hay Empleados con salario entre ' v_min ' y ' v_max);
' v_min ' y ' v_max); W
WHHEENN OOTTHHEERRSS TTHHEENN
DBMS_OUTPUT.PUT_LINE('Ocurrió algún otro error'); DBMS_OUTPUT.PUT_LINE('Ocurrió algún otro error'); END; END; / / S SEETT SSEERRVVEERROOUUTTPPUUTT OOFFFF " " S SQQLL>> ssttaarrtt pp2233qq33..ssqqll Con el salario: 0 Con el salario: 0
No hay Empleados con salario entre -100 y 100 No hay Empleados con salario entre -100 y 100 Procedimiento PL/SQL terminado correctamente. Procedimiento PL/SQL terminado correctamente. S
SQQLL>> ssttaarrtt pp2233qq33..ssqqll C
Coon n eel l vvaalloorr:: 11110000
Hay 1 Empleados con salario entre 1000 y 1200 Hay 1 Empleados con salario entre 1000 y 1200 Procedimiento PL/SQL terminado correctamente. Procedimiento PL/SQL terminado correctamente. S
SQQLL>> ssttaarrtt pp2233qq33..ssqqll C
Coon n eel l vvaalloorr:: 99000000
No Hay Empleados con salario entre 8900 y 9100 No Hay Empleados con salario entre 8900 y 9100 Procedimiento PL/SQL terminado correctamente. Procedimiento PL/SQL terminado correctamente. S
SQQLL>> ssttaarrtt pp2233qq33..ssqqll C
Coon n eel l vvaalloorr:: llaallaallaa
Muestra una gran cantidad de mensajes de error. Muestra una gran cantidad de mensajes de error. El Procedimiento PL/SQL no termina.