Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks - The most efficient solution

(Page 4 of 5 )

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.

Next: Nested blocks with local variable declarations >>
 

More Oracle Articles
More By Jagadish Chatarji

Comments On: Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks

· Hello guys, this is my article on working with user-defined excptions (of course ,...   >>> Post your comment now!