Manuscript

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.

 

 

v_deptno number(2);

            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

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.

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.