Block structure and Advantages of PL/SQL

Index - PL/SQL Tutorial

Introduction to PL/SQL
Chap 1 : Block structure and Advantages of PL/SQL
Chap 2 : PL/SQL Variables and Constants
Chap 3 : PL/SQL Records
Chap 4 : PL/SQL IF ELSE statement / Conditional statements
Chap 5 : PL/SQL FOR and WHILE LOOP / Iterative Statements
Chap 6 : Cursors in PL/SQL
Chap 7 : PL/SQL Procedures
Chap 8 : PL/SQL Functions
Chap 9 : Parameters-Procedure and Function in PL/SQL
Chap 10 : What are PL/SQL Triggers
Chap 11 : Exception Handling in PL/SQL

PL/SQL is a language focused on blocks, with procedural and processing characteristics of error handling. These blocks are composed of procedures, functions, and anonymous blocks which are grouped together in logical point of view.

In block there are three basic parts which are declaration, execution, and exception handling. Only execution part is required and the others are optional.


• Declaration - all block objects must be declared [optional]


• Execution – the objects are defined for the data processing


• Exceptions – here are located the error handling routines [optional]


DECLARE
Variable declaration;
BEGIN
Program Execution;
EXCEPTION
Exception handling;
END;


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.

Advantages of PL/SQL

• Integration with Oracle Server


• Supports the basic SQL commands


• Defining and managing blocks of instructions


• Management of variables, constants and cursors


• Allow implementation and use of triggers


• PL SQL consists of blocks of code, which can be nested within each other.


• PL SQL engine processes multiple SQL statements simultaneously as a single block


• It also reduces the network traffic.


• Detection and management execution errors, exceptions