|
EXCEPTION
Hands-On
introduction
In this
Hands-On, you modify the �test_for_loop3�
file from previous Hands-On. You need to add
exception to the PL/SQL block.
The PL/SQL
EXCEPTION section is a place that handles
your errors that occurs in the execution
time.
In the
exception section, you will add the �others�
pre-defined exception to inform a user if
the department number variable contains an
invalid number. Then you add a user defined
exception, to check the total number of
employees in a department. You check to see
if the total number of employees was less
than 10, then the procedure raises an
exception and prints a message - �We need
more good employees.� Then save the file in
your directory as �test_exception.sql.�
Go to
�MS-DOS.� Change directory to your
directory. And login to �SQLPLUS� as
�oracle/learning�
Set the
serveroutput option to on.
SQL> set
serveroutput on
Error handling
without EXCEPTION
Open the
notepad editor. Open the �test_for_loop3�
file from your directory.
Modify the
PL/SQL block to assign �a� to the department
number variable.
(Notepad)
DECLARE
v_deptno NUMBER(2);
BEGIN
-- assign
deptno 10
v_deptno :=
�a�;
END;
.
Save the file
in your directory as �c:_exception.sql�
Go to �SQLPLUS.�
Run the file.
SQL> @test_exception
It will crash.
Why? It crashes since the program didn�t
know how to handle the errors.
Error handling
with EXCEPTION
Oracle Defined
EXCEPTION
Go back to
notepad. Add exceptions to the PL/SQL block.
In the exception section, check if any
exceptions happened then print �Invalid
Department number.�
(Notepad)
DECLARE
v_deptno NUMBER(2);
BEGIN
-- assign
deptno 10
v_deptno :=
�a�;
EXCEPTION
--
example of PL/SQL define
exception
WHEN
invalid_number THEN
DBMS_OUTPUT.PUT_LINE
(�Invalid
deptno: � || v_deptno);
WHEN others
THEN
DBMS_OUTPUT.PUT_LINE (�Other problem.�);
END;
/
Save the file.
Go to
�SQLPLUS.� Run the file again.
SQL> @test_exception
Notice that
the PL/SQL execution will not crash this
time. Why?
User Defined
EXCEPTION
Go back to
notepad. Now, add the user defined exception
to raise an exception if the number of
employees is less than 10. In the
declaration section, declare a user defined
exception. In the execution section, check
if the total number of employees is less
than 10 then raise the exception. In the
exception section, check if the exception
was raised then print �we need more good
employees.�
(Notepad)
>> DECLARE
-- define
department statistics
CURSOR c_ds (p_deptno
dept.deptno%type) IS
SELECT dname,
count (*) ttemp,
sum(sal) ttsal, avg(sal)
avsal
FROM dept d,
emp e
WHERE d.deptno
= e.deptno
GROUP BY dname;
--
define deptno variable
v_deptno NUMBER(2);
not_enough_emp EXCEPTION;
BEGIN
-- assign
deptno 10
v_deptno :=
�a�;
-- loop to
read cursor record.
FOR v_ds in
c_ds (v_deptno) LOOP
IF v_ds.ttemp
< 10 THEN
raise
not_enough_emp;
END IF;
-- insert into
dept_stat
INSERT INTO
dept_stat
VALUES
(v_ds.dname, v_ds.ttemp,
v_ds.ttsal,
v_ds.avsal);
END LOOP;
-- save the
insert transaction.
COMMIT;
EXCEPTION
--
example of user define exception
WHEN not_enough_emp THEN
dbms_output.put_line(�We need
more employees�);
-- check
deptno
WHEN
invalid_number THEN
dbms_output.put_line(�Invalid deptno: � ||
v_deptno);
WHEN others
THEN
dbsm_output.put_line(�Other problem.�);
END;
/
Save the file.
Go to �sqlplus.�
Run the file
again.
SQL> @test_exception
Notice that
the first exception terminates the PL/SQL
block.
Correct the
error
Now, go back
to the notepad editor and correct the error
to eliminate the first exception.
(Notepad)
>> DECLARE
-- define
department statistics
CURSOR c_ds
(p_deptno dept.deptno%type) IS
SELECT dname,
count (*) ttemp,
sum(sal) ttsal, avg(sal)
avsal
FROM dept d,
emp e
WHERE d.deptno
= e.deptno
GROUP BY dname;
--
define deptno variable
v_deptno NUMBER(2);
not_enough_emp EXCEPTION;
BEGIN
-- assign
deptno 10
v_deptno :=
10;
-- loop to
read cursor record.
FOR v_ds in
c_ds (v_deptno) LOOP
IF v_ds.ttemp
< 10 THEN
raise
not_enough_emp;
END IF;
-- insert into
dept_stat
INSERT INTO
dept_stat
VALUES (v_ds.dname,
v_ds.ttemp,
v_ds.ttsal,
v_ds.avsal);
END LOOP;
-- save the
insert transaction.
COMMIT;
EXCEPTION
--
example of user define exception
WHEN not_enough_emp THEN
dbms_output.put_line(�We need
more employees�);
-- check
deptno
WHEN
invalid_number THEN
dbms_output.put_line(�Invalid deptno: � ||
v_deptno);
WHEN others
THEN
dbsm_output.put_line(�Other problem.�);
END;
/
Save the
file.Run the file.
Now, you
should not have any error.
Questions:
Q: What is the
EXCEPTION section in the PL/SQL language?
Q: What do you
use the EXCEPTION section for?
Q: What would
be happen if you don�t define your exception
in the PL/SQL procedure?
Q: What is an
Oracle Defined EXCEPTION?
Q: What is a
User Defined EXCEPTION?
Q: What are
the differences between a User Defined and
an Oracle defined exceptions?
Q: Modify the
previous PL/SQL block--last assignment in
the previous hands-on practice--to add a
user defined exception, to check the total
number of employees in a department. Check
if the total number of employees less than
10 then the procedure raises an exception
and print a message - �We need more good
employees.� |
