PL/SQL Block Structure

Every PL/SQL program — anonymous or named — is built from blocks. Understanding block structure is the foundation of everything that follows.

The Four Sections of a Block

DECLARE
    -- Optional: variable declarations, type definitions, cursor declarations
BEGIN
    -- Required: executable statements
EXCEPTION
    -- Optional: error handlers
END;
/

Only BEGIN ... END; is mandatory. The DECLARE and EXCEPTION sections are optional.

Minimal Block

BEGIN
    NULL;  -- the no-op statement; useful as a placeholder
END;
/

Full Block

DECLARE
    v_dept_name   VARCHAR2(100);
    v_emp_count   NUMBER := 0;
BEGIN
    SELECT d.department_name, COUNT(e.employee_id)
    INTO   v_dept_name, v_emp_count
    FROM   departments d
    LEFT JOIN employees e ON e.department_id = d.department_id
    WHERE  d.department_id = 90
    GROUP BY d.department_name;

    DBMS_OUTPUT.PUT_LINE(v_dept_name || ': ' || v_emp_count || ' employees');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Department 90 not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

Anonymous Blocks

Anonymous blocks are not stored in the database. They compile and execute immediately, then are discarded. Use them for:

One-time data migrations
Testing logic before creating stored procedures
SQL*Plus scripts

-- Anonymous block: runs once, gone forever
DECLARE
    v_today DATE := SYSDATE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Today is: ' || TO_CHAR(v_today, 'DD-MON-YYYY'));
END;
/

Named Blocks

Named blocks are stored in the data dictionary and can be called repeatedly:

-- Stored procedure (named block)
CREATE OR REPLACE PROCEDURE greet_employee(p_emp_id IN NUMBER) IS
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO   v_name
    FROM   employees
    WHERE  employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('Hello, ' || v_name || '!');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || p_emp_id || ' not found.');
END greet_employee;
/

-- Call it
BEGIN
    greet_employee(101);
    greet_employee(999);
END;
/

Output:

Hello, Neena Kochhar!
Employee 999 not found.

ℹ

In named blocks the DECLARE keyword is replaced by the header (IS or AS). Both are equivalent: PROCEDURE foo IS and PROCEDURE foo AS work identically.

Nested Blocks

Blocks can be nested inside other blocks. Inner blocks can see variables declared in outer blocks, but not the reverse.

DECLARE
    v_outer VARCHAR2(50) := 'outer value';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Outer block: ' || v_outer);

    DECLARE
        v_inner VARCHAR2(50) := 'inner value';
    BEGIN
        -- Inner block can see v_outer
        DBMS_OUTPUT.PUT_LINE('Inner sees outer: ' || v_outer);
        DBMS_OUTPUT.PUT_LINE('Inner block: ' || v_inner);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Inner error: ' || SQLERRM);
    END;

    -- v_inner is not visible here
    -- DBMS_OUTPUT.PUT_LINE(v_inner);  -- would cause PLS-00201

    DBMS_OUTPUT.PUT_LINE('Back in outer block');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Outer error: ' || SQLERRM);
END;
/

Scope Rules

Item	Visible in
Outer `DECLARE` variables	Outer block + all inner blocks
Inner `DECLARE` variables	Inner block only
Cursor declared in outer	Outer + inner blocks
Exception handler	Only in the block where declared

Block Labels

Labels (<<label_name>>) identify blocks — useful for resolving name conflicts in nested blocks:

<<outer>>
DECLARE
    v_salary NUMBER := 5000;
BEGIN
    <<inner>>
    DECLARE
        v_salary NUMBER := 9000;  -- hides outer v_salary
    BEGIN
        -- Reference outer block's variable explicitly
        DBMS_OUTPUT.PUT_LINE('Inner salary:  ' || v_salary);
        DBMS_OUTPUT.PUT_LINE('Outer salary:  ' || outer.v_salary);
    END inner;
END outer;
/

Output:

Inner salary:  9000
Outer salary:  5000

Comments

PL/SQL supports two comment styles:

DECLARE
    -- Single-line comment: starts with two dashes
    v_count NUMBER := 0;

    /*
     * Multi-line comment block.
     * Good for header documentation.
     */
    v_name  VARCHAR2(100);
BEGIN
    v_count := v_count + 1;  -- inline comment
    NULL;
END;
/

✓

Name the END of procedures and functions with the subprogram name (END greet_employee;). Oracle validates that the name matches, catching accidental mismatches in large programs.

The NULL Statement

NULL is a valid executable statement that does nothing. It's essential wherever Oracle requires at least one statement:

-- Exception handler that intentionally ignores an error
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;  -- swallow the exception silently
END;

⚠

Silently swallowing exceptions with NULL hides bugs. Only use it when you genuinely intend to discard an error — and add a comment explaining why.

Summary

Every PL/SQL program is a block: DECLARE / BEGIN / EXCEPTION / END.
Only BEGIN ... END is required; DECLARE and EXCEPTION are optional.
Anonymous blocks run once and are not stored. Named blocks (procedures, functions, packages, triggers) are stored and reusable.
Blocks can nest; inner blocks see outer variables but not the reverse.
Block labels (<<name>>) resolve variable name conflicts and identify loop exit points.
Use NULL as a no-op placeholder or intentional exception swallower — always with a comment.