PL/SQL is a procedural language that Oracle developed as an extension to standard SQL to provide a way to execute procedural logic on the database. It was developed by Oracle Corporation in the early 90's to enhance the capabilities of SQL.PL/SQL program units are compiled by the Oracle Database server and stored inside the database.  And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency. 

               PL/SQL automatically inherits the robustness, security, and portability of the Oracle Database.You can use PL/SQL to encapsulate business rules and other complicated logic. It provides for modularity and abstraction. You can use it in database triggers to code complex constraints, which enforce database integrity; to log changes; and to replicate data. PL/SQL can also be used with stored procedures and functions to provide enhanced database security

PLSQL Block Structure:

The anonymous block has three basic sections that are the declaration, execution, and exception handling. Only the execution section is mandatory and the others are optional.

                                The declaration section allows you to define data types, structures, and variables. You often declare variables in the declaration section by giving them names, data types, and initial values.

                            The execution section is required in a block structure and it must have at least one statement. The execution section is the place where you put the execution code or business logic code. You can use both procedural and SQL statements inside the execution section.

                              The exception handling section is starting with the EXCEPTION keyword. The exception section is the place that you put the code to handle exceptions. You can either catch or handle exceptions in the exception section.

Notice that the single forward slash (/) is a signal to instruct SQL*Plus to execute the PL/SQL block

Syntax:

[DECLARE]

   Declaration statements;

BEGIN

   Execution statements;

  [EXCEPTION]

      Exception handling statements;

END;

/

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;
/
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;

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

block_label.variable_name;

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;
/