Overview of PL/SQL

The PL/SQL stands for Procedural Language extensions to Structured Query Language. Basically, SQL is used to perform basic operations of creating a database, storing data in the database, updating data in the database, retrieving the stored data of database, etc, whereas PL/SQL is a fully Structured Procedural language which enables the developer to combine the powers of SQL with its procedural statements.

Features of PL/SQL

Following are some useful features of PL/SQL:

1. Portable:

   PL/SQL applications can be executed with all types of operating system where we have oracle installed.

2. Efficient:

   All sorts of calculations can be efficiently performed by PL/SQL without the use of oracle engine. This improves transaction performance.

3. Error-checking:

   PL/SQL allows error-checking and displays user-friendly messages when error occurs.

4. Development tool:

   PL/SQL supports execution of SQL statements along with the functionality of variable declaration, conditional statements, looping and branching, procedures, functions and triggers.

5. Exception Handling:

   PL/SQL code is capable of handling exceptions that can affect the flow of program, hence helps in making the code more reliable.

With the above features present in PL/SQL, the utility of PL/SQL programming can be seen in real life projects using databases. As it is capable of performing various logical operations on the database which cannot be executed using simple SQL queries.

For example, conside we have a School Management System database and we want to see the list of students who took admission in last 5 years in school and the fees deposited by them. And at the same time we want to add the total amount of salary money given to the teaching and non-teaching staff. We cannot fetch these records by just executing SQL queries, we might be able to gather the data but it would require multiple SQL queries.

On the other hand if we use PL/SQL, we can write procedures or methods in PL/SQL program to execute complex logic on a database. Another advantage is that we can also detect errors easily as it displays user-friendly messages and it even supports exception handling.

PL/SQL Architecture

PL/SQL architecture consist of following three components, displayed in the diagram below:

Let's understand what these 3 components are,

1. PL/SQL Block:

   PL/SQL block creates the structured logical blocks of code that describes the process to be executed. Such a block consists of SQL statements and PL/SQL instructions that are then passed to the oracle engine for execution. We will learn about this in details in the next tutorial.

2. PL/SQL Engine:

   The PL/SQL block containing the procedural statements are executed by the PL/SQL statement executor inside the PL/SQL engine. In simpler words, the PL/SQL engine provides the environment in which the PL/SQL block is executed.

3. Database Server:

   Last but not least, comes the Database Server. This can be Oracle DB server or MySQL DB server. The DB server stores data in tables.

Below we have a simple pictorial representation of the 3 components: