Database Interaction with PL/SQL: Nested Blocks in Depth - Labeling the PL/SQL blocks
(Page 2 of 4 )
Here, I provide the solution for the problem defined in the previous section, using the concept of labeling the PL/SQL blocks. Consider the following example:
<<parent>>
Declare
v_empno emp.empno%Type := &empno1;
Begin
<<child>>
Declare
v_empno emp.empno%Type := &empno2;
v_ename emp.ename%type;
Begin
Select ename into v_ename
From emp Where empno=parent.v_empno;
dbms_output.put_line('Empno: '|| parent.v_empno);
dbms_output.put_line('Name: '||v_ename);
Exception
When no_data_found then
dbms_output.put_line('Employee not found with '||v_empno);
End;
End;
The '<<parent>>' label performs the whole magic. That is how we label a block. Even the nested block in the above program got labeled with '<<child>>'. Actually, it is not necessary to label a child block in this case. But, I did label it, just to inform you that you can also label child (nested) blocks in that manner.
The SELECT statement in the above program is also bit different (in WHERE condition). We are using 'parent.v_empno' to denote that we are trying to access the variable 'v_empno' of '<<parent>>' block. This notation itself makes it much clearer to PL/SQL runtime that it needs to access the variable of the parent block (but not the local variable of child block).
The most important issue to remember is that this notation (or labeling) works only with parent-child types of blocks but not paralleled blocks within the same parent block. The following example is INVALID as it tries to access the variable of its own adjacent block (but not parent block).
<<parent>>
Declare
v_empno emp.empno%Type := &empno1;
Begin
<<child1>>
Declare
v_empno emp.empno%Type := &empno2;
v_ename emp.ename%type;
Begin
Select ename into v_ename
From emp Where empno=parent.v_empno;
dbms_output.put_line('Empno: '|| parent.v_empno);
dbms_output.put_line('Name: '||v_ename);
Exception
When no_data_found then
dbms_output.put_line('Employee not found with '||v_empno);
End;
<<child2>>
Declare
v_ename emp.ename%type;
Begin
Select ename into v_ename
From emp Where empno=child1.v_empno; --this is invalid
dbms_output.put_line('Empno: '|| parent.v_empno); --this is valid
dbms_output.put_line('Name: '||v_ename);
Exception
When no_data_found then
dbms_output.put_line('Employee not found with '||v_empno);
End;
End;
Next: EXCEPTION handling in both parent and nested PL/SQL blocks >>
More Oracle Articles
More By Jagadish Chatarji
