• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

In PL/SQL, each block can be nested into another block. They are referred as nested block. Nested blocks are very common when we want to perform 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 labelled. 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 nested block.

Nested Blocks in Pl/Sql

Syntax Explanation:

  • 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

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

Nested Blocks in Pl/Sql

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: Labelling 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 with 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 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.

Nested Blocks in Pl/Sql

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 learnt 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:
PL-SQL

PL/SQL Data Types

In oracle, each value or constant is assigned with a data type. Any data type is associated with the...

PL-SQL

Loops in PL/SQL

In this chapter, we are going to see the loop concept in PL/SQL and flow of control in loops. You...