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

T10- Gestió d'excepcions

• Exercici 1

Escriu un bloc PLSQL per seleccionar el nom de l'empleat amb un valor concret de sou. Cal que demanis el sou a l'usuari mitjançant un paràmetre SQL*PLUS.

a. si el sou introduït torna més d'una fila, gestiona l'excepció amb un manegador d'excepcions apropiat i afegeix a la taula MESSAGES el texte: "Més d'un empleat amb un sou de <sou>".

b. si el sou introduït no torna cap fila, gestiona l'excepció amb un manegador d'excepcions apropiat i afegeix a la taula MESSAGES el texte: "Cap empleat amb un sou <sou>".

c. si el sou introduït només torna una fila, afegeix a la taula MESSAGES el nom del empleat i l'import del sou.

d. Si es produeix qualsevol altra excepció, gestiona-la amb un manegador d'excepcions apropiat i afegeix a la taula MESSAGES el texte: "S'ha produït algun altre error".

e. Prova el bloc amb diferents cassos i assegurat de que funciona correctament.

SET VERIFY OFF;

ACCEPT salari PROMPT 'Introdueixi el salari a buscar: '

DECLARE

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE :=TO_NUMBER('&salari');

BEGIN

SELECT ename INTO v_ename

FROM emp

WHERE emp.sal=v_sal;

INSERT INTO messages (results)

VALUES(v_ename||' - '||v_sal);

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO missatges

VALUES('No hi ha cap empleat amb aquest salari introduït' || TO_CHAR(v_sal));

WHEN TOO_MANY_ROWS THEN

INSERT INTO missatges

VALUES('Hi ha més d'un empleat amb aquest sou de ' || TO_CHAR(v_sal));

WHEN OTHERS THEN

INSERT INTO missatges

VALUES('algún altre error sha produit')

END;

/

SQL>select *

2 from missatges;

• Exercici 2

Modifica l'exercici 3 del tema 5 t5p3.sql per afegir manegadors d'excepcions per una excepció definida per l'usuari.

a. escriu un manegador per a que comuniqui missatge a l'usuari dient que el departament especificat no existeix, si és el cas.

b. fes la comprovació amb un departament que no existeixi.

SET SERVEROUTPUT ON;

ACCEPT p_deptno PROMPT 'Introdueixi el número del departament: '

ACCEPT p_loc PROMPT 'Introdueixi la localitat del departament: '

DECLARE

e_invalid_dept EXCEPTION;

v_deptno dept.deptno%TYPE :=p_deptno;

v_loc dept.loc%TYPE :='p_loc';

BEGIN

UPDATE dept

SET loc=v_loc

WHERE deptno=v_deptno;

IF SQL%NOTFOUND THEN

raise e_invalid_dept;

END IF;

EXCEPTION

WHEN e_invalid_dept THEN

DBMS_OUTPUT.PUT_LINE('Department '||TO_CHAR(v_deptno) || ' NO EXISTEIX');

END;

/

SET VERIFY ON

• Exercici 3

Escriu un bloc PLSQL que imprimeixi el nombre d'empleats que guanyen 100$ mes o menys que un valor introduït per teclat.

a. si no hi ha cap empleat dins aquest rang mostra un missatge al usuari indicant l'error que s'ha produït.

b. si hi ha un o més empleats dins aquests rang el missatge hauria d'indicar quants empleats hi ha en aquest rang de sous.

c. gestiona qualsevol altra excepció indicant que s'ha produït un altre tipus d'error.

SET VERIFY OFF

ACCEPT p_sal PROMPT 'Introdueixi el salari: '

DECLARE

v_sal emp.sal%TYPE := &p_sal;

v_low_sal emp.sal%TYPE := v_sal - 100;

v_high_sal emp.sal%TYPE := v_sal + 100;

v_no_emp NUMBER(7);

e_no_emp_returned EXCEPTION;

e_more_than_one_emp EXCEPTION;

BEGIN

SELECT COUNT(ename)

INTO v_no_emp

FROM emp

WHERE sal BETWEEN v_low_sal and v_high_sal;

IF v_no_emp = 0 THEN

RAISE e_no_emp_returned;

ELSE IF v_no_emp > 0 THEN

RAISE e_more_than_one_emp;

END IF;

EXCEPTION

WHEN e_no_emp_returned THEN

DBMS_OUTPUT.PUT_LINE('No hi ha cap empleat que tingui un salari entre '|| TO_CHAR(v_low_sal) || ' y ' || TO_CHAR(v_high_sal));

WHEN e_more_than_one_emp THEN

DBMS_OUTPUT.PUT_LINE('Hi ha ' || TO_CHAR(v_no_emp) || 'empleat(s) amb un salari entre '|| TO_CHAR(v_low_sal) || ' y ' || TO_CHAR(v_high_sal));

WHEN others THEN

DBMS_OUTPUT.PUT_LINE('S'ha produït algun altre error'));

END;

/

SET VERIFY OFF