PL/SQL Nested Blocks – A Study Guide with Examples

What Are PL/SQL Nested Blocks?

PL/SQL nested blocks allow developers to write modular and maintainable code by embedding one block inside another within the BEGIN or EXCEPTION sections of a PL/SQL program. These inner blocks are executed independently and can handle their own exceptions without affecting the outer block. Using nested blocks enhances readability, supports localized error handling, and improves the overall structure of complex logic in Oracle PL/SQL programs.

When to Use Nested Blocks?

When different parts of logic need independent error handling
To isolate a part of code without affecting the main block
To keep code readable and modular
To handle localized exceptions without breaking the entire program

Example: Nested Blocks in PL/SQL

DECLARE: Used to declare variables, constants, cursors. Optional.
BEGIN: The main logic where SQL statements are executed. Mandatory.
EXCEPTION: Used to handle runtime errors gracefully. Optional.
END: Marks the end of the block.

Here’s a full example of a PL/SQL anonymous block with two nested blocks:

SET SERVEROUTPUT ON;

DECLARE
  v_author AUTHORS.FIRST_NAME%TYPE;
BEGIN
  -- 🌐 First Nested Block
  BEGIN
    SELECT first_name INTO v_author
    FROM authors
    WHERE UPPER(last_name) = 'HARTMAN'; -- No such record expected

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDLER for nested block 1');
      DBMS_OUTPUT.PUT_LINE('No author with last name HARTMAN found.');
  END;

  -- 🌐 Second Nested Block
  BEGIN
    SELECT first_name INTO v_author
    FROM authors
    WHERE UPPER(last_name) = 'HARDMAN'; -- Might return multiple rows

  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDLER for nested block 2');
      DBMS_OUTPUT.PUT_LINE('Multiple authors found with last name HARDMAN.');
  END;
END;
/

Output Example (If Exceptions Occur)

EXCEPTION HANDLER for nested block 1
No author with last name HARTMAN found.
EXCEPTION HANDLER for nested block 2
Multiple authors found with last name HARDMAN.

Benefits of Nested Blocks

Benefit	Explanation
Localized Error Handling	Each nested block handles its own errors without affecting outer logic.
More Readable Code	Helps break large code into manageable sections.
Avoids Repetition	Allows reuse of logic with separate exception handling.
Continues Execution	If one block fails, others can still run independently.
Logical Grouping	Blocks related to specific functionality can be grouped together.

Table: Main Block vs Nested Block

Aspect	Main Block	Nested Block
Scope	Global to the PL/SQL block	Limited to its own execution and exception
Error Handling	Handles all block-level errors	Handles specific errors for inner logic
Execution Dependency	Outer block can contain nested blocks	Inner block runs independently
Visibility of Vars	Can access outer variables (if visible)	Cannot access outer variables unless passed

Real-World Use Cases of PL/SQL Nested Blocks

Bank Transactions
You can use nested blocks to process multiple steps like verifying account balance, debiting, crediting, and logging — each with its own error handler.
Order Processing System
Nested blocks can isolate shipping logic, payment confirmation, and stock updates to ensure modular error handling for each part.
Student Grade Calculation
Calculate grades per student inside nested blocks to avoid failing the entire process due to one invalid record.