Overview of PL/SQL

The PL/SQL stands for Procedural Language extensions to Structured Query Language. Fundamentally, SQL is utilized to perform essential operations in the database, such as:

1. Making a database
2. putting away information within the database 
3. Adding information within the database 
4. Recovering the stored data of the database

Features of PL/SQL

Following are some useful features of PL/SQL:

1. Efficient: Calculations are performed efficiently without using oracle engine.
2. Self-check: PL/SQL allows error checking and displays error messages which are easier to comprehend.
3. Development: We can execute SQL statements along with functionality of variable declaration, conditional statements, procedures and triggers.
4. Handling Exceptions: It is capable of handling exceptions that affect the flow of programs.

Thus, PL/SQL is capable of performing various logical operations that cannot be executed with the help of SQL queries.

For example, let’s take an example of a Business Management System. We want to run a query that gives us following data:

1. List of customers who subscribe to the services in last 5 years
2. Subscription amount deposited by those customers
3. Also, total amount of salary given to the staff at the same time.

Such data cannot be fetched through SQL queries without running multiple SQL queries. However, PL/SQL can write procedures to execute complex logic on a database. Moreover, we can detect errors easily.

PL/SQL Architecture

PL/SQL has following 3 components:

1. PL/SQL Block: It creates a logical and structured block of codes that describes the process that has to be executed. SQL statements along with the PL/SQL instructions are passed to the oracle.
2. PL/SQL Engine: It consists of PL/SQL statement executor which executes the procedural statements contained withing the PL/SQL block.
3. Database Server: This is the Oracle DB or MySQL DB which stores the data in tables.

Here is the example of the three components working together:

Related posts:

PL/SQL Block RSA Archer Framework Architecture Datatypes in PL/SQL How to Manage and Create Logical Volume Using vgcreate, lvcreate and lvextend Commands