PL/SQL Variable Scope & Inner Outer Block: Nested Structure

Details

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

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.

Nested Blocks in Pl/Sql

Below is the syntax for the nested block.

Nested Blocks in Pl/Sql

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;

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 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.

Nested Blocks in Pl/SQL 

<<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:

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.

Nested Blocks in Pl/SQL 

<<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:

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.

 

YOU MIGHT LIKE: