HomeOracle Page 4 - Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks
The most efficient solution - Oracle
This is part 7 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I gave an introduction to exception handling and went through some predefined exceptions. In this article, we will focus on user defined exceptions in PL/SQL and some tips for working with exceptions efficiently.
This section not only introduces you the best solution to the problems defined in the previous sections, but also introduces you to nested blocks in the PL/SQL programming structure. Before directly looking into the program, let me define for you the concept of nested blocks.
Everybody knows that a typical PL/SQL block structure starts with DECLARE (the declaration section is actually optional), continues through BEGIN, handles exceptions through the EXCEPTION section(which is also optional), and finally ends at END. This PL/SQL block can be further nested inside with its own nested PL/SQL blocks (again with DECLARE, BEGIN, EXCEPTION and END sections). And they can be nested any number of times.
If properly nested and defined, each nested block will have its own boundary of variable declarations or exceptions, which will not be seen (or accessed) by other parallel nested blocks. The following example introduces you to two parallel nested blocks inside a main PL/SQL block. The example also gives you the best solution to solve the problems explained in the previous two sections.
Declare v_empno1 emp.empno%Type := &empno1; v_ename emp.ename%Type; v_empno2 emp.empno%Type := &empno2; Begin Begin Select ename into v_ename From emp Where empno=v_empno1; dbms_output.put_line('Name: '||v_ename); Exception When no_data_found then dbms_output.put_line('Employee not found with '||v_empno1); End;
Begin Select ename into v_ename From emp Where empno=v_empno2; dbms_output.put_line('Name:'||v_ename); Exception When no_data_found then dbms_output.put_line('Employee not found with '||v_empno2); End; End;
In the above program, every SELECT gets executed within its own block (having no relation to one another) and displays either employee information or an exception message. We also need to observe that the two nested blocks are using variables from the main (parent) block without having their own local declarations. And finally, one should agree that the above style of programming looks clearer and easier to read.