PL/SQL Block Structure

PL/SQL blocks have a pre-defined structure in which the code is to be grouped.

Below are different sections of PL/SQL blocks.

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

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

Declaration Section
This is the first section of the PL/SQL blocks. This section is an optional part. This
is the section in which the declaration of variables, cursors, exceptions,
subprograms, pragma instructions and collections that are needed in the block
will be declared. Below are few more characteristics of this part.

 This particular section is optional and can be skipped if no declarations are

needed.
 This should be the first section in a PL/SQL block, if present.
 This section starts with the keyword 'DECLARE' for triggers and
anonymous block. For other subprograms, this keyword will not be
present. Instead, the part after the subprogram name definition marks the
declaration section.
 This section should always be followed by execution section.

Execution Section
Execution part is the main and mandatory part which actually executes the code
that is written inside it. Since the PL/SQL expects the executable statements from
this block this cannot be an empty block, i.e., it should have at least one valid
executable code line in it. Below are few more characteristics of this part.

 This can contain both PL/SQL code and SQL code.

 This can contain one or many blocks inside it as a nested block.
 This section starts with the keyword 'BEGIN'.
 This section should be followed either by 'END' or Exception-Handling
section (if present)

Exception-Handling Section:
The exception is unavoidable in the program which occurs at run-time and to
handle this Oracle has provided an Exception-handling section in blocks. This
section can also contain PL/SQL statements. This is an optional section of the
PL/SQL blocks.

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

The Keyword 'END' marks the end of PL/SQL block.

PL/SQL Block Syntax

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

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 the information to the
compiler about the end of the block.

Types of PL/SQL block

PL/SQL blocks are of mainly two types.

1. Anonymous blocks
2. Named Blocks

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

Since they need not store in the database, they need no compilation steps. They
are written and executed directly, and compilation and execution happen in a
single process.

Below are few more characteristics of Anonymous blocks.

 These blocks don't have any reference name specified for them.
 These blocks start with the keyword 'DECLARE' or 'BEGIN'.
 Since these blocks do not have any reference name, these cannot be
stored for later purpose. They shall be created and executed in the same
session.
 They can call the other named blocks, but call to anonymous block is not
possible as it is not having any reference.
 It can have nested block in it which can be named or anonymous. It can
also be nested in any blocks.
 These blocks can have all three sections of the block, in which execution
section is mandatory, the other two sections are optional.

Named blocks:
Named blocks have a specific and unique name for them. They are stored as the
database objects in the server. Since they are available as database objects,
they can be referred to or used as long as it is present on the server. The
compilation process for named blocks happens separately while creating them as
a database objects.

Below are few more characteristics of Named blocks.

 These blocks can be called from other blocks.

 The block structure is same as an anonymous block, except it will never
start with the keyword 'DECLARE'. Instead, it will start with the keyword
'CREATE' which instruct the compiler to create it as a database object.
 These blocks can be nested within other blocks. It can also contain nested
blocks.
 Named blocks are basically of two types:

1. Procedure
2. Function

PL/SQL anonymous block example

The following example shows a simple PL/SQL anonymous block with one executable section.

1 BEGIN

2 DBMS_OUTPUT.put_line ('Hello World!');

3 END;

The executable section calls the DMBS_OUTPUT.PUT_LINE procedure to display the "Hello
World" message on the screen.
Execute a PL/SQL anonymous block using SQL*Plus
Once you have the code of an anonymous block, you can execute it using SQL*Plus, which is a
command-line interface for executing SQL statement and PL/SQL blocks provided by Oracle
Database.

The following picture illustrates how to execute a PL/SQL block using SQL*Plus:

First, connect to the Oracle Database server using a username and password.

Second, turn on the server output using the SET SERVEROUTPUT ON command so that
the DBMS_OUTPUT.PUT_LINE procedure will display text on the screen.
Third, type the code of the block and enter a forward slash ( /) to instruct SQL*Plus to execute the
block. Once you type the forward-slash (/), SQL*Plus will execute the block and display the Hello
World message on the screen as shown in the illustrations.
Note that you must execute SET SERVEROUTPUT ON command in every session that you connect to
the Oracle Database in order to show the message using the DBMS_OUTPUT.PUT_LINE procedure.
To execute the block that you have entered again, you use / command instead of typing everything
from the scratch: