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:
- Making a database
- putting away information within the database
- Adding information within the database
- Recovering the stored data of the database
Features of PL/SQL
Following are some useful features of PL/SQL:
- Efficient: Calculations are performed efficiently without using oracle engine.
- Self-check: PL/SQL allows error checking and displays error messages which are easier to comprehend.
- Development: We can execute SQL statements along with functionality of variable declaration, conditional statements, procedures and triggers.
- 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:
- List of customers who subscribe to the services in last 5 years
- Subscription amount deposited by those customers
- 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 has following 3 components:
- 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.
- PL/SQL Engine: It consists of PL/SQL statement executor which executes the procedural statements contained withing the PL/SQL block.
- 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: