Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks - Nested blocks with local variable declarations

(Page 5 of 5 )

This is just an extension to the previous section. I will show you the sample example in the previous section, with a few variations in nested blocks. The only issue I wanted to address here is that the nested blocks can have their own local declarations without having any relation to their parent or other friendly (parallel) blocks.

Declare
  v_empno1 emp.empno%Type := &empno1;
  v_empno2 emp.empno%Type := &empno2;
Begin

 

  Declare
    v_ename1 emp.ename%Type;
  Begin
    Select ename into v_ename1
    From emp Where empno=v_empno1;
    dbms_output.put_line('Name: '||v_ename1);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno1);
  End;

 

 

  Declare
    v_ename2 emp.ename%Type;
  Begin
    Select ename into v_ename2
    From emp Where empno=v_empno2;
    dbms_output.put_line('Name:'||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno2);
  End;

End;

From the above program, you can clearly observe that every nested block has its own DECLARE section. The variable 'v_ename1' in the first nested block cannot be accessed beyond the first block (in this case, cannot be accessed by the parent block or even the second block) and the case of 'v_ename2' is similar. You can also localize all variables (based on the situation) and remove the declarations section from the parent block, as shown in the following example:

Begin

 

  Declare
    v_ename1 emp.ename%Type;
    v_empno1 emp.empno%Type := &empno1;
  Begin
    Select ename into v_ename1
    From emp Where empno=v_empno1;
    dbms_output.put_line('Name: '||v_ename1);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno1);
  End;

 

 

  Declare
    v_ename2 emp.ename%Type;
    v_empno2 emp.empno%Type := &empno2;
  Begin
    Select ename into v_ename2
    From emp Where empno=v_empno2;
    dbms_output.put_line('Name:'||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno2);
  End;

End;

That is how we can separate every aspect of the program within a PL/SQL block into several blocks. And each of these blocks can be further separated or nested any number of times.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

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!