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 syntax

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;