Monday, 16 October 2017

Why we need PL/SQL?

To explain the need of PL/SQL, let us consider an example as shown below:

   SELECT first_name, department_id, salary FROM EMPLOYEES;

The above SQL statement is very simple and straight forward. But, if we want to change or alter any data that is retrieved in a conditional manner, we will soon experience the limitations of SQL.

To make it simple to understand, let us consider a scenario, where for every Employee retrieved, we want to check the DEPARTMENT_ID and SALARY. Now based on the DEPARTMENT_ID and SALARY we want to provide varying bonuses to the EMPLOYEES.

Looking at the above problem, we know that we have to execute the above SQL statement, collect the data, and apply logic to the data.
  1. One solution that we might think of is to write a SQL statement for each DEPARTMENTS to give bonuses to the EMPLOYEES in that DEPARTMENT. Please note, in this case we also have to check the SALARY component before deciding the bonus amount. This makes it a little complicated.
  2. The second and the more effective solution that we might think of is to include conditional statements. Thus, PL/SQL is designed to meet such requirements. It provides a programming extension to the already-existing SQL.
If you like the post, please comment, share the post and do like me on Facebook.

Thanks & Regards,
Susanto Paul