Nested Blocks & Variable Scope in Oracle PL/SQL Tutorial [Example]

What is Nested Blocks Oracle?

In PL/SQL, each block can be nested into another block. They are referred as a nested block. Nested blocks are very common when we want to perform the certain process, and at the same time, the code for these process should be kept in a separate container (block).

Nested block concept will help the programmer to improve the readability by separating the complex things into each block and to handle the exception for each block inside the main outer block.

In this tutorial- you will learn.

• Nested Block Structure
• Scopes in Nested Block: Variable Scope

Nested Block Structure

A block can be nested into another block. This can be nested either in the execution part or in the exception handling part. These block can also be labeled. One outer block can contain many inner blocks. Each inner block is once again a PL/SQL block, hence all the properties and characteristics of the inner block will be the same as outer block. The below image gives the pictorial representation of nested block structure. Parent block is the main block and child block is the nested block.

Below is the syntax for the nested block.

Nested Block Syntax<<outer_block>>

DECLARE
 <Declarative section> 
BEGIN
 <Execution part for outer block begins>

 <<inner block>>
 DECLARE
  <Declarative section>
 BEGIN
  <Execution part for inner block>.
 EXCEPTION 
  <Exception handling part>
 END;

 <Execution part for outer block begins> 
EXCEPTION 
<Exception handling part>
END;

• The above syntax shows the nested block that contains a total of two blocks.
• These blocks are labelled as 'outer_block' and 'inner_block'

Scopes in Nested Block: Variable Scope

In nested block, one needs to understand the scope and visibility of each block clearly before using them. Particularly in the inner block, the elements from both outer and the inner block will be visible, Hence proper understanding of this is necessary.

Below points will summarize more regarding the scopes in nested blocks.

• The elements declared in the outer block and value that is defined before the inner block definition is visible inside the inner block.
• The elements declared in the inner block is not visible in the outer block. They are visible only within the inner block.
• Outer block and Inner block can have a variable with the same name.
• In case of variables with the same name, inner block by default, will refer to the variable declared in inner block only.
• If inner block wants to refer the outer block variable that is having the same name as that of the inner block, then outer block should be LABELLED, and the outer block variable can be referred as '<outer_block_label>.<variable_name>'

The below example will help to understand more about these scopes.

Example 1: In this example, we are going to see the scope of variables in the inner and outer block. Also, we are going to see how to refer the variables using block label.

<<OUTER_BLOC>>
DECLARE
varl VARCHAR2(30):='outer_block';
var2 VARCHAR2(30):='value before inner block’;
BEGIN
<<NNER_BLOCK>>
DECLARE
varl VARCHAR2(30):='inner_block';
BEGIN
dbms_output.put_line(varl), ‭ ‬
dbms_output.put_line(OUTER_BLOCKvar1); 
dbms_output.put_line(var2);
END;
var2:='value after inner block';
END;
/

Code Explanation:

• Code line 1: Labelling the outer block as "OUTER_BLOCK".
• Code line 3: Declaring a variable 'var1' as VARCHAR2 (30) with the initial value of "outer block".
• Code line 4: Declaring a variable 'var2' as VARCHAR2 (30) with the initial value of "value before inner block".
• Code line 6: Labeling the inner block as "INNER_BLOCK"
• Code line 8: Declaring a variable 'var1' in the inner block as VARCHAR2 (30) with the initial value of "inner block".
• Code line 10: Printing the value of 'var1'. Since no label is mentioned by default it will take the value from an inner block, hence printing 'inner_block' message.
• Code line 11: Printing the value of outer block variable 'var1'. Since the inner block is having the variable with the same name, we need to refer to outer block label. Thus printing the message 'outer block'.
• Code line 12: Printing the value of outer block variable 'var2'. Since there is no variable with this name present in the inner block, by default it will take the value from an outer block, hence printing 'value before inner block' message.
• The variable 'var2' in the outer block has been assigned with the value 'value after inner block'. But this assignment has happened after the definition of an inner block. Hence this value is not present in the the inner block.

Example 2: In this example, we are going to find the difference between two numbers, one declared at the outer block and another at inner block. Both will have the same name. Let's see how block label is useful in referring these variables.

<<OUTER_BLOC>>
DECLARE 
ln_val NUMBER :=5;
BEGIN
<<INNERBLOC>>
DECLARE 
ln_val NUMBER :=3;
BEGIN
dbms_output.put_line(The difference between outer block and inner block variable is:'||' outer_block. ln_val-inner_block.ln_val);
END;
END;
/

Code Explanation:

• Code line 1: Labelling the outer block as "OUTER_BLOCK".
• Code line 3: Declaring a variable 'ln_val' as NUMBER with the initial value of "5".
• Code line 5: Labelling the inner block as "INNER_BLOCK"
• Code line 7: Declaring a variable 'ln_val' in inner block as NUMBER with the initial value of "3".
• Code line 9: Printing the difference in value of 'ln_val' from outer and inner block. The "<block_name>.<variable_name>" format is used to refer these variables to avoid conflicts due to same variable name.

Summary

In this tutorial, we have learned how to create a nested block and how to handle the scope in inner block and outer blocks. We have also seen an example where the variables from the inner and outer block were referred inside the inner block.