Introduction

What is PL/SQL ? 

The PL/SQL language is an extension of SQL language developed by Oracle Corporation and the full name it is the Procedural Language/Structured Query Language. The PL/SQL language is a procedural programming language which allows data manipulation and sql query to be included in blocks. PL/SQL can be used to group multiple instructions in a single block and sending the entire block to the server in a single call.

The PL/SQL Engine

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL language code can be stored in the client system (client-side) or in the database (server-side).

Structure of PL/SQL Block

PL/SQL block consists of three sections :

1. The Declarartion Section (Optional)
2. The Execution Section (Manadatory)
3. The Exception Handling Section (Optional)

Declaration Section : It start with the reserverd keyword DECLARE .This section is used to declare any placeholders like variable, constants,cursor which are used to mainuplate data in the execution section.

Execution Section : It starts with the reserved keyword BEGIN and with the END. In this section we write logic of the program to perform any task.

Exception Section :It starts with the reserverd keyword EXCEPTION. Error is handled in this section of the program so that the PL/SQL block terminates successfully.

NOTE : Every statement in the above three sections must end with a semicolon(;). PL/SQL blocks can be nested within other PL/SQL blocks.

Basic structure of PL/SQL block:

   DECLARE 

                  <declarations section> 

   BEGIN

                 <executable command(s)> 

   EXCEPTION 

                 <exception handling>

   END;

SQL Command Categories :

According to their functionality SQL commands are divided into four categories -

Data Definition Language (DDL) : These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Data Manipulation Language (DML) : These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.

Transaction Control Language (TCL) :These SQL commands are used for managing changes affecting the data. These commands are COMMIT and ROLLBACK.

Data Control Language (DCL) : These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

Advantages Of PL/SQL :

• Error Handling : PL/SQL handles errors or exceptions in exception section during the execution of the program.Once a exception is caught, specific action can be taken.
• Better performance : Multiple SQL statements simultaneously processed by PL/SQL engine, thereby reducing network traffic.
• Block Structure : PL/SQL consists of blocks of code, which can be nested within each other.  PL/SQL Blocks can be stored in the database and reused.
• Procedural Language Similar :PL/SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
• Static and Dynamic SQL Support : PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. Dynamic SQL is SQL allows embedding DDL statements in PL/SQL blocks.