Topics: Hands-On 08 - How to use EXECPTION
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.
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.”
Save the file in the iself directory as “test_exception.sql.”
The Core Subjects are:
1- Pre-defined exception
2- User-defined exception
|
More Resources by
Google: |
|
|
|
|
Go to “MS-DOS.”
Change directory to the iself directory. And login to “SQLPLUS” as
“iself/schooling.”
Set the serveroutput option to on.
>> set serveroutput on
Open the notepad editor.
Open the “test_for_loop3” file from the iself directory.
Modify the PL/SQL block to assign “a” to the department number variable.
begin
-- assign deptno 10
v_deptno := ‘a’;
-- loop to read cursor record.
Save the file in the iself directory as
“test_exception.sql”
Go to “SQLPLUS.”
Run the file.
>> @test_exception
It crashed.
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.”
Save the file.
Go to “SQLPLUS.”
Run the file again.
>> @test_exception
Notice that the PL/SQL execution was not crashed.
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 are less than
10 then raise the exception.
In the exception section, check if the exception raised then print “we need
more good employees.”
>> 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
-- 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,
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.
Notice that the first exception terminates the PL/SQL block.
>> @test_exception
Now, go back to the notepad editor and correct the error to eliminate the
first exception.
Save the file.
Run the file.
Now, you should practice this over and over, until you become a master at it.
Good Luck.
|