What is PL/SQL?

What is pl/sql?
 

PL/SQL stands for “Procedural Language extensions to the Structured Query Language or SQL”.

SQL is the powerful tool for both querying and update data in relational databases. Oracle introduced PL/SQL to extend some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on Oracle database.

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

Below is the example of pl/sql:-
 

EMP_NO	EMP_NAME	PHONE	SALARY	DEPT_NO	ADDRESS
X	XXXX	XXXX	XXXX	XXXX	XXXX
X	XXXX	XXXX	XXXX	XXXX	XXXX
X	XXXX	XXXX	XXXX	XXXX	XXXX
X	XXXX	XXXX	XXXX	XXXX	XXXX

EMPLOYEE TABLE

Suppose if, you want to perform the below tasks:-
 

1)    Increase the salary of employees by 10 percent whose designation is Manager.

2)    Increase the salary of employees by 20 percent whose salary is below 10,000.

3)    Increase the salary of employees by 10 percent who is under department 10.

4)    Increase the salary of employees by 5 percent whose salary is more than 10,000.

5)    Increase the salary of employees by 30 percent whose salary is less than 5,000.

You can perform this update by using sql queries but you would need to fire 5 queries. But if you will use pl/sql then you can perform this in one query by using various construct like loop, if else statements. That’s the beauty of the pl/sql.

When you use pl/sql anonymous block or other named program, you write part of the code in pl/sql and part of the code in sql. Pl/sql part will go to the pl/sql compiler and sql part will go to the sql compiler. Let’s have an example:-

How pl/sql works?
 

Pl/sql program consists of sql and pl/sql statements which form’s pl/sql block.

Pl/sql has 4 blocks:-

·       
            Declare

·         Begin

·         Exception

·         End

When you write a code using block it’s called “Anonymous block”.

Pl/sql has three sections:-
 

1)    Declare (optional)

2)    Begin (Mandatory)

3)    Exception (optional)

From the above three sections, there is only one section which is mandatory i.e. Begin. Begin is just not a begin block. In pl/sql each begin has its end at the end of block or section, hence when I am saying begin is mandatory that means I have started a block by using begin (reserve keyword) and that begin will always have end (reserve keyword) at the end of anonymous block or pl/sql section. Like below example:-

Begin

Sql statements;

End;

Understanding different sections in detail:-

Declare Section:-

These sections of pl/sql block start with reserve keyword DECLARE which can be used for declaring placeholders like variables, records, cursors and constants.

Begin Section:-

This section of Pl/sql block start with reserve keyword BEGIN and ends with reserve keyword END. This is a mandatory section of pl/sql block which is use to write program logic for performing tasks. In this section we can use conditional operator and cursors also.

Exception Section:-

This section in pl/sql block starts with reserve keyword EXCEPTION. This is an optional section in pl/sql block. However we can handle any errors in this section.

 

Below is the Sample pl/sql block:-

 

Declare

(Declarative Section)

Begin

(Execution Section)

Exception

(Handling any error)

End;

(End of the Block)

Pl/sql blocks are also called ANONYMOUS BLOCKS.

ANONYMOUS BLOCK is pl/sql program unit which consist of declare, begin exception and end sections.

Read Also:- Oracle Architecture