PL/SQL Blocks

The structure of a PL/SQL block

PL/SQL is a language structured on the block. A PL/SQL block consists of 3 parts: declarative (optional), executable (required) and handling exceptions (optional).

PL/SQL is a language focused on blocks. These blocks are composed of procedures, functions and anonymous blocks.

A PL/SQL block is composed of three parts:

Declaration - Contains all the variables, constants, cursors and exceptions defined by the user

Execution Contains the PL/SQL and SQL statements for data manipulation

Exceptions Specifies the actions performed when errors occur during the execution part

PL/SQL blocks:

DECLARE
   Declare Variables
BEGIN
   Execution Programs
EXCEPTION
   Errors handling
END;

Types of PL/SQL blocks:

Anonymous blocks
Functions
Stored procedures
Packages
Triggers

Anonymous pl/sql blocks:

The anonymous blocks are not stored in the database, shall be declared inline, in the place in a programming environment.

Example:
DECLARE
   variable_name varchar2(10);
BEGIN
   SELECT variable_name INTO column_name FROM table_name;
EXCEPTION
   WHEN exception_name THEN action
END;

Nested anonymous pl/sql blocks

You can nest more blocks.

BEGIN
  DECLARE
    -- variable, cursors, exceptions
  BEGIN
    --pl/sql and sql code
  END;
END;