The Geek Diary

PL/SQL Nested Blocks

by

Being procedural gives PL/SQL the ability to nest statements. You can nest blocks wherever an executable statement is allowed, thus making the nested block a statement. If your executable section has code for many logically related functionalities to support multiple business requirements, you can divide the executable section into smaller blocks. The exception section can also contain nested blocks.

Nested Blocks: Example

DECLARE
  v_outer_variable VARCHAR2(20):='GLOBAL VARIABLE';
BEGIN
    DECLARE
      v_inner_variable VARCHAR2(20):='LOCAL VARIABLE';
    BEGIN
      DBMS_OUTPUT.PUT_LINE(v_inner_variable);
      DBMS_OUTPUT.PUT_LINE(v_outer_variable);
    END;
  DBMS_OUTPUT.PUT_LINE(v_outer_variable);
END;

The example shown above has an outer (parent) block and a nested (child) block. The v_outer_variable variable is declared in the outer block and the v_inner_variable variable is declared in the inner block.

v_outer_variable is local to the outer block but global to the inner block. When you access this variable in the inner block, PL/SQL first looks for a local variable in the inner block with that name. There is no variable with the same name in the inner block, so PL/SQL looks for the variable in the outer block. Therefore, v_outer_variable is considered to be the global variable for all the enclosing blocks. You can access this variable in the inner block as shown in the slide. Variables declared in a PL/SQL block are considered local to that block and global to all its subblocks.

v_inner_variable is local to the inner block and is not global because the inner block does not have any nested blocks. This variable can be accessed only within the inner block. If PL/SQL does not find the variable declared locally, it looks upward in the declarative section of the parent blocks. PL/SQL does not look downward in the child blocks.

Variable Scope and Visibility

Conside the example shown below:

DECLARE
  v_father_name VARCHAR2(20):='Patrick';
  v_date_of_birth DATE:='20-Apr-1972';
BEGIN
    DECLARE
      v_child_name VARCHAR2(20):='Mike';
      v_date_of_birth DATE:='12-Dec-2002';
    BEGIN
      DBMS_OUTPUT.PUT_LINE('Father''s Name: '||v_father_name);
      DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
      DBMS_OUTPUT.PUT_LINE('Child''s Name: '||v_child_name);
    END;
  DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
END;
/

The output of the block shown in the example above is as follows:

anonymous block completed
Father's Name: Patrick
Date of Birth: 12-DEC-02
Child's Name: Mike
Date of Birth: 20-APR-72

Examine the date of birth that is printed for father and child. The output does not provide the correct information, because the scope and visibility of the variables are not applied correctly.

  • The scope of a variable is the portion of the program in which the variable is declared and is accessible.
  • The visibility of a variable is the portion of the program where the variable can be accessed without using a qualifier.

Scope

  • The v_father_name variable and the first occurrence of the v_date_of_birth variable are declared in the outer block. These variables have the scope of the block in which they are declared. Therefore, the scope of these variables is limited to the outer block.
  • The v_child_name and v_date_of_birth variables are declared in the inner block or the nested block. These variables are accessible only within the nested block and are not accessible in the outer block. When a variable is out of scope, PL/SQL frees the memory used to store the variable; therefore, these variables cannot be referenced.

Visibility

The v_date_of_birth variable declared in the outer block has scope even in the inner block. However, this variable is not visible in the inner block because the inner block has a local variable with the same name.

  1. Examine the code in the executable section of the PL/SQL block. You can print the father’s name, the child’s name, and the date of birth. Only the child’s date of birth can be printed here because the father’s date of birth is not visible.
  2. The father’s date of birth is visible in the outer block and, therefore, can be printed.
Note: You cannot have variables with the same name in a block. However, as shown in this example, you can declare variables with the same name in two different blocks (nested blocks). The two items represented by identifiers are distinct; changes in one do not affect the other.

Using a Qualifier with Nested Blocks

A qualifier is a label given to a block. You can use a qualifier to access the variables that have scope but are not visible.

Example

BEGIN <<outer>>
DECLARE
v_father_name VARCHAR2(20):='Patrick';
v_date_of_birth DATE:='20-Apr-1972';
BEGIN
  DECLARE
  v_child_name VARCHAR2(20):='Mike';
  v_date_of_birth DATE:='12-Dec-2002';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Father''s Name: '||v_father_name);
  DBMS_OUTPUT.PUT_LINE('Date of Birth: '
                      ||outer.v_date_of_birth);
 DBMS_OUTPUT.PUT_LINE('Child''s Name: '||v_child_name);
 DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);
END;
END;
END outer;

In the code example:

  • The outer block is labeled outer
  • Within the inner block, the outer qualifier is used to access the v_date_of_birth variable that is declared in the outer block. Therefore, the father’s date of birth and the child’s date of birth can both be printed from within the inner block.
  • The output of the code in the example above shows the correct information:

    • nested block in pl sql

Note: Labeling is not limited to the outer block. You can label any block.

You May Also Like