Nested Block in PL/SQL

Nested Block


PL/SQL blocks can be nested.

  • An executable section (BEGIN … END) can contain nested blocks.
  • An exception section can contain nested blocks.

PL/SQL Block Structure


DECLARE (optional)
   –Variables, cursors, user-defined exceptions
BEGIN (mandatory)
    –SQL statements
    –PL/SQL statements
EXCEPTION (optional)
    –Actions to perform when errors occur
END; (mandatory)

Nested Block


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;

Output:

Giving Qualify an Identifier name to Block

BEGIN <<OUTER>>
  DECLARE
    v_fname   VARCHAR2(20):='Bhavesh';
    v_lname  VARCHAR2(20):='Lakhani';
  BEGIN
    DECLARE
      v_fname    VARCHAR2(20):='Jit';
      v_lname    varchar2(20):='Lakhani';
    BEGIN
      DBMS_OUTPUT.PUT_LINE('Accessing  f_name from outer Block : '||outer.v_fname);
      DBMS_OUTPUT.PUT_LINE('Accessing  l_name from outer Block : ' ||outer.v_lname);
      DBMS_OUTPUT.PUT_LINE('Accessing  f_name from inner Block: '||v_fname);
      DBMS_OUTPUT.PUT_LINE('Accessing  l_name from inner Block: '||v_lname);
    END;
  END;
END OUTER;

Output:

Leave a Reply

Your email address will not be published. Required fields are marked *