When an exception is raised in a PL/SQL block, normal execution is halted and control is transferred to the exception section. You can never return to the execution section once an exception is raised in that block. In some cases, however, the ability to continue past exceptions is exactly the desired behavior.
Consider the following scenario: I need to write a procedure that performs a series of DML statements against a variety of tables (delete from one table, update another, insert into a final table). My first pass at writing this procedure might produce code like the following:
PROCEDURE change_data ISBEGIN DELETE FROM employee WHERE...; UPDATE company SET...; INSERT INTO company_history SELECT * FROM company WHERE...;END;This procedure certainly contains all the appropriate DML statements. But one of the requirements for this program is that, although these statements are executed in sequence, they are logically independent of each other. In other words, even if the DELETE fails, I want to go on and perform the UPDATE and INSERT.
With the current version of change_data, I cannot make sure that all three DML statements will at least be attempted. If an exception is raised from the DELETE, for example, then the entire program's execution will halt and control will be passed to the exception section, if there is one. The remaining SQL statements will not be executed.
How can I get the exception to be raised and handled without terminating the program as a whole? The solution is to place the DELETE within its own PL/SQL block. Consider this next version of the change_data program:
PROCEDURE change_data ISBEGIN BEGIN DELETE FROM employee WHERE...; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN UPDATE company SET...; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN INSERT INTO company_history SELECT * FROM company WHERE...; EXCEPTION WHEN OTHERS THEN NULL; END;END;With this new format, if the DELETE raises an exception, control is immediately passed to the exception section. But what a difference! Because the DELETE statement is now in its own block, it can have its own exception section. The WHEN OTHERS clause in that section smoothly handles the error by doing nothing. Control is then passed out of the DELETE's block and back to the enclosing change_data procedure.
Execution in this enclosing block then continues to the next statement in the procedure. A new anonymous block is then entered for the UPDATE statement. If the UPDATE statement fails, the WHEN OTHERS in the UPDATE's own exception section traps the problem and returns control to change_data, which blithely moves on to the INSERT statement (contained in its very own block).
Figure 6-2 shows this process for two sequential DELETE statements.
