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 syntax
DECLARE Declare Variables BEGIN Execution Programs EXCEPTION Errors handling END;
Types of PL/SQL blocks:
Anonymous pl/sql blocks:
The anonymous blocks are not stored in the database, shall be declared inline, in the place in a programming environment.
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;