Nested Blocks in PL/SQL

A block within a block called as a Nested Block

The variable declared in any block is live as long as the block in which it is declared is getting executed.

In the Nested Block above, the variable X is declared in the outer block and hence it is available in the inner as well as the outer block however the variable Y is declared in the inner block hence it is available only to the inner block.

Now suppose the variable declared in the inner block is also given the same name as the variable in outer block say X then in the inner block when we refer to X it will be always taken as the local variable X and not X of outer block. This effect is called as “Local Screening”. In such situation if one wants to refer to the variable of the outer block then the outer block is given a label and in the inner block when one wants to refer to the outer block variable then it is prefixed with the outer block label.

Example: Labeled Nested Block

-- Outer Block Starts here

<<l_outer>>                                  -- Label given to the outer block

DECLARE

   v_empname   VARCHAR2 (20) := 'Neeta';

   v_salary    NUMBER (4)    := 1000;

BEGIN

   DECLARE                                         -- Inner Block Starts Here

      v_empname   VARCHAR2 (30) := 'Richa';

      v_address   VARCHAR2 (20) := 'Pune';

   BEGIN

      DBMS_OUTPUT.put_line(l_outer.v_empname);

      DBMS_OUTPUT.put_line(v_empname);

   END;                                                    -- Inner block ends

   DBMS_OUTPUT.put_line(v_empname);

   DBMS_OUTPUT.put_line(v_salary);

END;                                                       -- Outer Block Ends

/