PL/SQL Nested Block

Summary: in this tutorial, you will learn how to work with PL/SQL nested block that is a PL/SQL block embedded inside another PL/SQL block.

Introducing PL/SQL Nested Block

To nest a block means to embed one or more PL/SQL blocks inside another PL/SQL block that provide you with a better control over program execution and exception handling.

Let’s take a look at the following example:

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
  n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1;
BEGIN
  DECLARE
    n_emp_id employees.employee_id%TYPE := &emp_id2;
    v_name   employees.first_name%TYPE;
  BEGIN
    SELECT first_name
    INTO v_name
    FROM employees
    WHERE employee_id = n_emp_id;

    DBMS_OUTPUT.PUT_LINE('First name of employee ' || n_emp_id || 
                                       ' is ' || v_name);
    EXCEPTION
      WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found');
  END;
END;
/Code language: SQL (Structured Query Language) (sql)

We have a PL/SQL block that is nested inside another PL/SQL block in the above example. The outer PL/SQL block is called parent block or enclosing block and the inner PL/SQL block is known as child block, nested block or enclosed block.

If you take a look at the code carefully, you will see that we have two variables with the same name n_emp_id in the declaration section of both parent and child blocks. This is allowed in this scenario. The question here is which variable does the SELECT statement accepts? If you execute the code in SQL*PLUS you will see that the SELECT statement will accept the variable in the child block. Why? Because PL/SQL gives the first preference to the variable inside its own block. If the variable is not found, PL/SQL will search for the variable in the parent block and resolve it. If no such variable exists, PL/SQL will issue an error. In this case, the  v_emp_id variable in the child block overrides the variable in the parent block.

Notice that it is not good practice to have several variables that have the same name in different blocks. We take this example for the sake of demonstration only.

PL/SQL Block Label

So what if you want to refer to the variable in the parent block inside the child block in the above example? PL/SQL provides you with a feature called block label that you can qualify all references to variables inside the block via a label.

To label a block, you just need to provide a label name before the declaration section as follows:

<<block_label>>
DECLARE
...
BEGIN
...
END;Code language: SQL (Structured Query Language) (sql)

Then, you can refer to a variable inside the block by using a dot notation ( .) as below:

block_label.variable_name;Code language: SQL (Structured Query Language) (sql)

The following is a simple example of using block label:

SET SERVEROUTPUT ON SIZE 1000000;
<<label>>
DECLARE
  v_name varchar2(25) := 'Maria';
BEGIN
  DBMS_OUTPUT.PUT_LINE(label.v_name);
END;
/Code language: SQL (Structured Query Language) (sql)

And we can rewrite the example that has some variables with the same names using block label as follows:

SET SERVEROUTPUT ON SIZE 1000000;
<<parent>>
DECLARE
  n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := &emp_id1;
BEGIN
  <<child>>
  DECLARE
    n_emp_id employees.employee_id%TYPE := &emp_id2;
    v_name   employees.first_name%TYPE;
  BEGIN
    SELECT first_name
    INTO v_name
    FROM employees
    WHERE employee_id = parent.n_emp_id;

    DBMS_OUTPUT.PUT_LINE('First name of employee ' || parent.n_emp_id || 
                         ' is ' || child.v_name);

    EXCEPTION
      WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || parent.n_emp_id || ' not found');
  END;
END;
/Code language: SQL (Structured Query Language) (sql)

There are several advantages of using PL/SQL block label:

• Improve the readability of the code.
• Gain better control of code execution because a block label can be a target for EXITand CONTINUEstatements.
• Allow you to qualify a reference to a variable in the parent block that has the same name with a variable in child block using the dot notation ( .)

In this tutorial, you’ve learned how to work with PL/SQL nested block and how to use block label to qualify references to variables from parent block that has the same name with the variables in the child block.