What is PL/SQL?

Oracle PL/SQL is an extension of SQL language that combines the

data manipulation power of SQL with the processing power of
procedural language to create super powerful SQL queries. PL/SQL
ensures seamless processing of SQL statements by enhancing the
security, portability, and robustness of the Database.
PL/SQL means instructing the compiler ‘what to do’ through SQL and
‘how to do’ through its procedural way. Similar to other database
languages, it gives more control to the programmers by the use of
loops, conditions and object-oriented concepts. The PL/SQL Full form
is “Procedural Language extensions to SQL”.

Features & Advantages of PL/SQL

1. Better performance, as SQL is executed in bulk rather than a
single statement

2. High Productivity

3. Tight integration with SQL

4. Full Portability

5. Tight Security

6. Supports Object Oriented Programming concepts.

7. Scalability and Manageability

8. Supports Web Application Development

9. Supports Server Page Development

Disadvantages of PL/SQL
 1. Stored Procedures in PL/SQL uses high memory

2. Lacks functionality debugging in stored procedures

3. Any change in underlying database requires change in the

presentation layer also

4. Does not completely separate roles of back-end developer and

front-end developer

5. Difficult to separate HTML development with PL/SQL

development

Difference between SQL and PL/SQL

Here are some important differences between SQL and PL/SQL:

Difference between SQL and PL/SQL

SQL PL/SQL
01 SQL is a single query that PL/SQL is a block of codes that
is used to perform DML used to write the entire program
and DDL operations. blocks/ procedure/ function
02 It is declarative, that PL/SQL is procedural that defines
defines what need to be how the things needs to be done.
done, rather than how
things need to be done
03 Execute as a single Execute as a whole block.
statement.
04 Mainly used to manipulate Mainly used to create an
data. application.
05 Interaction with a No interaction with the database
Database server. server.
06 Cannot contain PL/SQL It is an extension of SQL, so that
code in it. it can contain SQL inside it.
OR

SQL PL/SQL

01 SQL can execute a single operation PL/SQL can perform multiple operation at
at a time. a time.

02 SQL is a declarative language PL/SQL is a procedural language

03 SQL can be embedded in a PL/SQL can also be embedded in

PL/SQL block. SQL code.

04 SQL directly interacts with PL/SQL does not directly interacts with
database server. database server.

05 SQL is data oriented language. PL/SQL is application oriented language.

06 SQL is used to write queries, PL/SQL is used to write program blocks,

create and execute DDL and functions, procedures, triggers and
DML statements packages.

PL/SQL block structure

DECLARE
Declaration statements;
BEGIN
Execution statements;
EXCEPTION—Optional
Exception handling statements; Optional
END;
/

DECLRE

Declarations
This section starts with the keyword DECLARE. It is an optional section and defines all
variables, cursors, subprograms, and other elements to be used in the program.

BEGIN

Executable Commands
This section is enclosed between the keywords BEGIN and END and it is a
mandatory section. It consists of the executable PL/SQL statements of the
program. It should have at least one executable line of code, which may be just a
NULL command to indicate that nothing should be executed

Exception Handling
This section starts with the keyword EXCEPTION. This optional section contains
exception(s) that handle errors in the program.

END
 Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within
other PL/SQL blocks using BEGIN and END.
Example
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/

PL/SQL If
PL/SQL supports the programming language features like conditional statements and
iterative statements. Its programming constructs are similar to how you use in
programming languages like Java and C++.
Syntax for IF Statement:
There are different syntaxes for the IF-THEN-ELSE statement.

Syntax: (IF-THEN statement):

1. IF condition
2. THEN
3. Statement: {It is executed when condition is true}
4. END IF;

This syntax is used when you want to execute statements only when condition is TRUE.
Syntax: (IF-THEN-ELSE statement):
1. IF condition
2. THEN
3. {...statements to execute when condition is TRUE...}
4. ELSE
5. {...statements to execute when condition is FALSE...}
6. END IF;

This syntax is used when you want to execute one set of statements when condition is
TRUE or a different set of statements when condition is FALSE.
Example 1 : In this example, we are going to print message whether the given number
is odd or even.
DECLARE
a NUMBER:=11;
BEGIN
dbms_output.put_line (‘Program started');
IF( mod(a,2)=0) THEN
dbms_output.put_line('a is even number' );
ELSE
dbms_output.put_line('a is odd number1);
END IF;
dbms_output.put_line (‘Program completed.’);
END;
/

Example 2
Example of PL/SQL If Statement
1. DECLARE
2. a number(3) := 500;
3. BEGIN
4. -- check the boolean condition using if statement
5. IF( a < 20 ) THEN
6. -- if condition is true then print the following
7. dbms_output.put_line('a is less than 20 ' );
8. ELSE
9. dbms_output.put_line('a is not less than 20 ' );
10. END IF;
11. dbms_output.put_line('value of a is : ' || a);
12. END;

After the execution of the above code in SQL prompt, you will get the following result:
a is not less than 20
PL/SQL Loop
The PL/SQL loops are used to repeat the execution of one or more statements
for specified number of times. These are also known as iterative control
statements.
Syntax for a basic loop:
1. LOOP
2. Sequence of statements;
3. END LOOP;

Types of PL/SQL Loops

There are 4 types of PL/SQL Loops.
1. Basic Loop / Exit Loop

2. While Loop

3. For Loop

4. Cursor For Loop

PL/SQL Exit Loop (Basic Loop)

PL/SQL exit loop is used when a set of statements is to be executed at least once
before the termination of the loop. There must be an EXIT condition specified in the
loop, otherwise the loop will get into an infinite number of iterations. After the occurrence
of EXIT condition, the process exits the loop.

Syntax of basic loop

1. LOOP
2. Sequence of statements;
3. END LOOP;

example simple loop:

1. DECLARE
2. i NUMBER := 1;
3. BEGIN
4. LOOP
5. EXIT WHEN i>10;
6. DBMS_OUTPUT.PUT_LINE(i);
7. i := i+1;
8. END LOOP;
9. END;
OUTPUT
1
2
3
4
5
6
7
8
9
10

PL/SQL While Loop

PL/SQL while loop is used when a set of statements has to be executed as long
as a condition is true, the While loop is used. The condition is decided at the
beginning of each iteration and continues until the condition becomes false.
Syntax of while loop:
1. WHILE <condition>
2. LOOP statements;
3. END LOOP;

example of PL/SQL WHILE loop.

DECLARE
2. i INTEGER := 1;
3. BEGIN
4. WHILE i <= 10 LOOP
5. DBMS_OUTPUT.PUT_LINE(i);
6. i := i+1;
7. END LOOP;
8. END;

OUTPUT
1
2
3
4
5
6
7
8
9
10

PL/SQL FOR Loop

PL/SQL for loop is used when when you want to execute a set of statements for
a predetermined number of times. The loop is iterated between the start and
end integer
values. The counter is always incremented by 1 and once the counter reaches
the value of end integer, the loop ends.
Syntax of for loop:
1.

FOR counter IN initial_value .. final_value LOOP

2. LOOP statements;
3. END LOOP;
o initial_value : Start integer value
o final_value : End integer value
example of PL/SQL FOR loop.
1. BEGIN
2. FOR k IN 1..10 LOOP
3. -- note that k was not declared
4. DBMS_OUTPUT.PUT_LINE(k);
5. END LOOP;
6. END;

After the execution of the above code, you will get the following result:
OUTPUT-

1
2
3
4
5
6
7
8
9
10

PL/SQL For Loop REVERSE Example

Let's see an example of PL/SQL for loop where we are using REVERSE keyword.
1. DECLARE
2. VAR1 NUMBER;
3. BEGIN
4. VAR1:=10;
5. FOR VAR2 IN REVERSE 1..10
6. LOOP
7. DBMS_OUTPUT.PUT_LINE (VAR1);
8. END LOOP;
9. END;

OUTPUT-
10
9
8
7
6
5
4
3
2
1