What is PLSQL ?

What is SQL?

SQL is a short-form of the structured query language, and it is pronounced as S-Q-L or sometimes as See-Quell.

What is PLSQL?

PL/SQL stands for “Procedural Language extensions to the Structured Query Language”. It is program or block of SQL statements to perform various operation with table or data.

PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to oracle engine all at once which increases processing speed and decreases the traffic.

Disadvantages of SQL:

1	SQL doesn’t provide the programmers with a technique of condition checking, looping and branching.
2	SQL statements are passed to Oracle engine one at a time which increases traffic and decreases speed.
3	SQL has no facility of error checking during manipulation of data.

 

To overcome the demerits of SQL, PLSQL concept came, let’s discuss about PL/SQL.

Features of PL/SQL:

1	PL/SQL is basically a procedural language, which provides the functionality of decision making, iteration and many more features of procedural programming languages
2	PL/SQL can execute a number of queries in one block using single command
3	One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored in the database for reuse by applications
4	PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as exception handling block
5	Applications written in PL/SQL are portable to computer hardware or operating system where Oracle is operational
6	PL/SQL Offers extensive error checking

 

Differences between SQL and PL/SQL:

SQL	PL/SQL
SQL is a single query that is used to perform DML and DDL operations	PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc
It is declarative, that defines what needs to be done, rather than how things need to be done	PL/SQL is procedural that defines how the things needs to be done
Execute as a single statement	Execute as a whole block
Mainly used to manipulate data	Mainly used to create an application
Cannot contain PL/SQL code in it	It is an extension of SQL, so it can contain SQL inside it

 

Structure of PL/SQL Block:

 

PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other.

Typically, each block performs a logical action in the program.

Example of a PLSQL block:

DECLARE

    Variable/constant declaration statements;

BEGIN

    Business logic/executable statements;

EXCEPTIONS

    Exception handling statements;

END;

 

Declare section starts with DECLARE keyword in which variables, constants, records as cursors can be declared which stores data temporarily. It basically consists definition of PL/SQL identifiers. This part of the code is optional.

Execution section starts with BEGIN and ends with END keyword. This is a mandatory section and here the program logic is written to perform any task like loops and conditional statements. It supports all DML commands, DDL commands and SQL*PLUS built-in functions as well.

Exception section starts with EXCEPTION keyword. This section is optional which contains statements that are executed when a run-time error occurs. Any exceptions can be handled in this section.

PL/SQL identifiers:

There are several PL/SQL identifiers such as variables, constants, procedures, cursors, triggers etc.

Variables:

Like several other programming languages, variables in PL/SQL must be declared prior to its use. They should have a valid name and data type as well.

Syntax for variable declaration :

Variable_Name [CONSTANT ] Datatype [NOT NULL := value ];

Example of variables declaration in PL/SQL :

AGE INTEGER;

NAME VARCHAR2 (20);

IS_DELETED CHAR (1):=’N’;