PL/SQL stands for Procedural Language extension of SQL.
PL/SQL is a combination of SQL along with the procedural features of programming languages.
It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
The PL/SQL Engine:
Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored
in the client system (client-side) or in the database (server-side).
A Simple PL/SQL Block:
Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.
PL/SQL Block consists of three sections:
• The Declaration section (optional).
• The Execution section (mandatory).
• The Exception (or Error) Handling section (optional).
Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This
section is optional and is used to declare any placeholders like variables, constants, records and
cursors, which are used to manipulate data in the execution section. Placeholders may be any of
Variables, Constants and Records, which stores data temporarily. Cursors are also declared in
this section.
Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends
with END. This is a mandatory section and is the section where the program logic is written to
perform any task. The programmatic constructs like loops, conditional statement and SQL
statements form the part of execution section.
Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This
section is optional. Any errors in the program can be handled in this section, so that the PL/SQL
Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled,
the Block terminates abruptly with errors.
Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be
nested within other PL/SQL blocks. Comments can be used to document code.
Advantages of PL/SQL
These are the Advantages of PL/SQL
• Block Structures:
PL SQL consists of blocks of code, which can be nested within each
other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in
the database and reused.
• Procedural Language Capability:
PL SQL consists of procedural language constructs such
as conditional statements (if else statements) and loops like (FOR loops).
• Better Performance:
PL SQL engine processes multiple SQL statements simultaneously as
a single block, thereby reducing network traffic.
• Error Handling:
PL/SQL handles errors or exceptions effectively during the execution of a
PL/SQL program. Once an exception is caught, specific actions can be taken depending
upon the type of the exception or it can be displayed to the user with a message.