PL/SQL Block: Structure, Syntax & Anonymous Example

By : Fiona Brown Fiona Brown
Hours Updated

⚡ Smart Summary

PL/SQL Block is the basic unit that groups PL/SQL and SQL instructions so they execute together rather than line by line. Every block has a declaration, execution, and exception-handling section, and blocks are either anonymous or named.

  • 🧱 Core Idea: Code is grouped into a block that runs as a whole, mixing procedural logic with SQL.
  • 📝 Declaration Section: Optional; declares variables, cursors, exceptions, and subprograms used in the block.
  • ▶️ Execution Section: Mandatory; begins with BEGIN and holds at least one executable statement.
  • 🛡️ Exception Section: Optional; begins with EXCEPTION and handles run-time errors.
  • 🏷️ Two Types: Anonymous blocks are unnamed and run once; named blocks are stored as database objects.
  • 🔁 Named Blocks: Procedures and functions are named blocks that can be called repeatedly.
  • ⛔ Terminator: END closes the block, and a forward slash sends it to the compiler.
Read More

PL/SQL Block Structure and Syntax

What is a PL/SQL Block?

In PL/SQL, code is not executed in single-line format; it is always executed by grouping the code into a single element called a block. In this tutorial, you will learn about these blocks.

Blocks contain both PL/SQL and SQL instructions. All these instructions are executed as a whole rather than one instruction at a time.

Block Structure

PL/SQL blocks have a pre-defined structure in which the code is grouped. Below are the different sections of a PL/SQL block:

  1. Declaration section
  2. Execution section
  3. Exception-Handling section

The picture below illustrates the different PL/SQL block sections and their order.

PL/SQL block sections and order

Declaration Section

This is the first section of a PL/SQL block, and it is optional. It is the section in which variables, cursors, exceptions, subprograms, pragma instructions, and collections needed in the block are declared. A few more characteristics of this part:

  • This section is optional and can be skipped if no declarations are needed.
  • It should be the first section in a PL/SQL block, if present.
  • It starts with the keyword ‘DECLARE’ for triggers and anonymous blocks. For other subprograms this keyword is not present; instead, the part after the subprogram name marks the declaration section.
  • It should always be followed by the execution section.

Execution Section

The execution part is the main and mandatory part that actually executes the code written inside it. Since PL/SQL expects executable statements, this block cannot be empty and must have at least one valid executable line. A few more characteristics of this part:

  • It can contain both PL/SQL code and SQL code.
  • It can contain one or many blocks inside it as nested blocks.
  • It starts with the keyword ‘BEGIN’.
  • It should be followed either by ‘END’ or by the exception-handling section, if present.

Exception-Handling Section

Exceptions are unavoidable errors that occur at run-time, and to handle them Oracle provides an exception-handling section in blocks. This section can also contain PL/SQL statements, and it is optional.

  • This is the section where an exception raised in the execution block is handled.
  • It is the last part of the PL/SQL block.
  • Control from this section can never return to the execution block.
  • It starts with the keyword ‘EXCEPTION’.
  • It should always be followed by the keyword ‘END’.

The keyword ‘END’ marks the end of the PL/SQL block.

PL/SQL Block Syntax

Below is the syntax of the PL/SQL block structure.

PL/SQL block syntax

DECLARE --optional
<declarations>

BEGIN --mandatory
<executable statements. At least one executable statement is mandatory>

EXCEPTION --optional
<exception handles>

END; --mandatory
/

Note: A block should always be followed by ‘/’, which sends information to the compiler about the end of the block.

RELATED ARTICLES

Types of PL/SQL Block

PL/SQL blocks are mainly of two types:

  1. Anonymous blocks
  2. Named blocks

The two are compared at a glance below before each is described.

Aspect Anonymous Block Named Block
Has a name No Yes
Stored in database No Yes, as an object
Starts with DECLARE or BEGIN CREATE
Reusable Same session only Callable any time

Anonymous Blocks

Anonymous blocks are PL/SQL blocks that do not have any name assigned to them. They need to be created and used in the same session because they are not stored on the server as database objects.

Since they are not stored in the database, they need no separate compilation step. They are written and executed directly, and compilation and execution happen in a single process. A few more characteristics:

  • These blocks have no reference name.
  • They start with the keyword ‘DECLARE’ or ‘BEGIN’.
  • Because they have no reference name, they cannot be stored for later use and must be created and executed in the same session.
  • They can call other named blocks, but a call to an anonymous block is not possible, as it has no reference.
  • They can contain a nested block that is named or anonymous, and they can also be nested inside any block.
  • They can have all three sections, of which the execution section is mandatory and the other two are optional.

Named Blocks

Named blocks have a specific and unique name. They are stored as database objects on the server, so they can be referred to or used as long as they are present. The compilation process for named blocks happens separately when they are created as database objects. A few more characteristics:

  • These blocks can be called from other blocks.
  • The block structure is the same as an anonymous block, except that it never starts with ‘DECLARE’. Instead, it starts with ‘CREATE’, which instructs the compiler to create it as a database object.
  • These blocks can be nested within other blocks and can also contain nested blocks.
  • Named blocks are basically of two types:
  1. Procedure
  2. Function

You can learn more about these named blocks in the guide to procedures and functions.

FAQs

Only the execution section, which begins with BEGIN and ends with END. The declaration and exception-handling sections are optional and can be omitted if they are not needed.

An anonymous block has no name, is not stored, and runs in the same session. A named block, such as a procedure or function, is stored as a database object and can be called repeatedly.

In tools like SQL*Plus, the slash on its own line tells the compiler the block is complete and should be run. Without it, the tool keeps waiting for more input.

Yes. AI can wrap the logic in a CREATE PROCEDURE header, add parameters, and move declarations into place. Test the result, since a stored version may need explicit parameter modes and error handling.

Yes. The declaration and exception sections are independent options. A block can begin at BEGIN, skip declarations, and still include an EXCEPTION section before END to catch run-time errors.

Summarize this post with: