RDBMS - Exception Handling 2

RDBMS - Exception Handling 2

EXCEPTION HANDLING IN PL/SQL

Q. create a PL/SQL code block that takes rollno from the user & displays the name, dateofbirth, stream of that student


SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declarations*/
rl number(4);
nm varchar2(25);
dob date;
str varchar2(3);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Student Roll No.: ');
rl:=&rl;
/*SELECT query*/
SELECT name, dateofbirth, stream INTO nm, dob, str
FROM stutab
WHERE rollno=rl;
/*display*/
DBMS_OUTPUT.PUT_LINE('Details of Student Roll No. ' || rl);
DBMS_OUTPUT.PUT_LINE('Name: ' || nm);
DBMS_OUTPUT.PUT_LINE('Date of Birth: ' || dob);
DBMS_OUTPUT.PUT_LINE('Stream: ' || str);
END;
/

-----------------------------------------------
->The above mentioned PL/SQL code block produces an exception:  "no data found" when we feed a rollno that does not exist.
---------------------------------------------------------------
Exceptions:
Run Time Errors
When Exceptions are detected at Run Time, They crash the PL/SQL code Block
Crash - untimely code block exit
No statement below the error prone statement is executed.
Exception Handling:
the basic idea is to avoid a crash.
PL/SQL code block continues its execution, even if an exception is detected.
just skips the error prone statement / statements.

---------------------------------------------------------------
PL/SQL SYNTAX:

DECLARE
--
BEGIN
--
EXCEPTION
--
END;
------------------------------------------------
syntax:
------

EXCEPTION
WHEN exceptionname THEN
action to be carried out

---------------------------------------------------
Built in Exceptions:

1. NO_DATA_FOUND
when the select command does not return any row.
2. TOO_MANY_ROWS
when we fetch less no. of records than what the select statement throws.
3. DUP_VAL_ON_INDEX
when trying to  insert duplicate data on a PK col
4. Generic Exception: can catch all remaining exceptions
WHEN OTHERS THEN
action statements
-----------------------------------------------------------
1. NO_DATA_FOUND example

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declarations*/
rl number(4);
nm varchar2(25);
dob date;
str varchar2(3);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Student Roll No.: ');
rl:=&rl;
/*SELECT query*/
SELECT name, dateofbirth, stream INTO nm, dob, str
FROM stutab
WHERE rollno=rl;
/*display*/
DBMS_OUTPUT.PUT_LINE('Details of Student Roll No. ' || rl);
DBMS_OUTPUT.PUT_LINE('Name: ' || nm);
DBMS_OUTPUT.PUT_LINE('Date of Birth: ' || dob);
DBMS_OUTPUT.PUT_LINE('Stream: ' || str);
EXCEPTION
WHEN NO_DATA_FOUND THEN
/*display an error message*/
DBMS_OUTPUT.PUT_LINE('Roll no. ' || rl || ' does not exist!');
END;
/

======================================================

2. TOO_MANY_ROWS example (plus handling multiple exceptions)

/*create a PL/SQL code block that takes a name & display some the information of that name from the stutab table*/
/*we can catch multiple exceptions in a PL/SQL code block*/

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
nm varchar2(25);
rl number(4);
str varchar2(3);
p number(4,1);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter the Student s Name: ');
nm:=&nm;
/*SELECT query*/
SELECT rollno, stream, per INTO rl, str, p
FROM stutab
WHERE name = nm;
/*display the record*/
DBMS_OUTPUT.PUT_LINE('Details of Student ' || nm);
DBMS_OUTPUT.PUT_LINE('Roll no.: ' || rl);
DBMS_OUTPUT.PUT_LINE('Stream: ' || str);
DBMS_OUTPUT.PUT_LINE('Per: ' || p);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
/*display an error message*/
DBMS_OUTPUT.PUT_LINE('Name ' || nm || ' exists multiple times in the table!');
WHEN NO_DATA_FOUND THEN
/*display an error message*/
DBMS_OUTPUT.PUT_LINE('Name ' || nm || ' does not exist!');
WHEN OTHERS THEN
/*display an error message*/
DBMS_OUTPUT.PUT_LINE('Unknown Problem detected!');
END;
/

===============================================================

3. DUP_VAL_ON_INDEX example:

/*create a PL/SQL code block that inserts a new product by taking input from the user*/

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
pid number(3);
pnm varchar2(20);
ppr number(5,2);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Product ID: ');
pid:=&pid;
DBMS_OUTPUT.PUT_LINE('Enter Product Name: ');
pnm:=&pnm;
DBMS_OUTPUT.PUT_LINE('Enter Product Price: ');
ppr:=&ppr;
/*INSERT Query*/
INSERT INTO product
(prodid, prodname, prodprice)
VALUES(pid, pnm, ppr);
/*Message*/
DBMS_OUTPUT.PUT_LINE('Record Inserted!');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Product ID ' || pid || ' already exists in the table!');
END;
/

===================================================================