Conditional Statements in PL / SQL

In many case we would be in a state whether we have to choose Yes or No. These types of questions are also called Decision Statements. Here, the user is compelled to choose one answer. In this case we choose the answer based on the condition in which we are present. In the same way, Every Program has its moments like choosing if this condition is positive then we choose Yes statement else if condition is false then we choose No statement while coding.

The Every Programming Language has a few Conditional statements. These Conditional statements are also known as Decision Statements. There are a few Conditional Statements or Decision Statements in PL / SQL (Procedure Language / Structured Query Language).

The Conditional Statements can only be used in BEGIN State.

Types of Conditional Statements

if condition then statement
if condition then else statement
nested if condition then statements
if condition then elseif condition then else statements

1.) if Condition then Statement

This is one of the first important PL / SQL Conditional Statement. In this statement only if the condition is true, then the output is going to be shown. Else in any other cases there is no opportunity for outputs.

Syntax

IF <condition> then
Statement 1 
Statement 2
.
.
.
.
Statement N
END IF;

Example Queries

SQL> DECLARE
N1 INTEGER;
BEGIN
N1:=10;
IF N1 > 10 THEN
dbms_output.put_line('The Value is' || N1);
END IF;
END;
/

OUTPUT:

PL/SQL procedure successfully completed.

SQL> DECLARE
N1 INTEGER;
BEGIN
N1:=10;
IF N1 >= 10 THEN
dbms_output.put_line('The Value is ' || N1);
END IF;
END;
/

OUTPUT:

The Value is 10

PL/SQL procedure successfully completed.

SQL> DECLARE
N1 INTEGER;
BEGIN
N1:=&N1;
IF N1 > 10 THEN
dbms_output.put_line('The Value is' || N1);
END IF;
END;
/

OUTPUT:

Enter value for n1: 5
old   4: N1:=&N1;
new   4: N1:=5;

PL/SQL procedure successfully completed.

SQL> DECLARE
N1 INTEGER;
BEGIN
N1:=&N1;
IF N1 > 10 THEN
dbms_output.put_line('The Value is ' || N1);
END IF;
END;
/

OUTPUT:

Enter value for n1: 15
old   4: N1:=&N1;
new   4: N1:=15;
The Value is 15

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    BEGIN
    N1:=&N1;
    IF N1 >= 10 THEN
    dbms_output.put_line('The Value is ' || N1);
    END IF;
    END;
    /

OUTPUT:

Enter value for n1: 9
old   4:     N1:=&N1;
new   4:     N1:=9;

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    BEGIN
    N1:=&N1;
    IF N1 >= 10 THEN
    dbms_output.put_line('The Value is ' || N1);
    END IF;
    END;
    /

OUTPUT:

Enter value for n1: 10
old   4:     N1:=&N1;
new   4:     N1:=10;
The Value is 10

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    BEGIN
    N1:=&N1;
    IF N1 >= 10 THEN
    dbms_output.put_line('The Value is ' || N1);
    END IF;
    END;
    /

OUTPUT:

Enter value for n1: 23
old   4:     N1:=&N1;
new   4:     N1:=23;
The Value is 23

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 >= N2 THEN
    dbms_output.put_line('The Value is ' || N1*N2);
    END IF;
 11
 12
    END;
    /

OUTPUT:

Enter value for n1: 5
old   6:     N1:=&N1;
new   6:     N1:=5;
Enter value for n2: 4
old   7:     N2:=&N2;
new   7:     N2:=4;
The Value is 20

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 >= N2 THEN
    dbms_output.put_line('The Value is ' || N1*N2);
    END IF;
 11
 12
    END;
    /

OUTPUT:

Enter value for n1: 9
old   6:     N1:=&N1;
new   6:     N1:=9;
Enter value for n2: 11
old   7:     N2:=&N2;
new   7:     N2:=11;

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 >= N2 THEN
    dbms_output.put_line('The Value is ' || N1*N2);
    END IF;
 11
 12
    END;
    /

OUTPUT:

Enter value for n1: 10
old   6:     N1:=&N1;
new   6:     N1:=10;
Enter value for n2: 10
old   7:     N2:=&N2;
new   7:     N2:=10;
The Value is 100

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 >= N2 THEN
    dbms_output.put_line('The Value is ' || N1*N2);
    END IF;
 11
 12
    END;
    /

OUTPUT:

Enter value for n1: 12
old   6:     N1:=&N1;
new   6:     N1:=12;
Enter value for n2: 2
old   7:     N2:=&N2;
new   7:     N2:=2;
The Value is 24

PL/SQL procedure successfully completed.

2.) if Condition then else Statement

This is also one of the first important PL / SQL Conditional Statement. In this statement only if the condition is true, then the output is going to be shown. Else in any other cases there is opportunity for outputs to seen. If the if condition fails then the else statements are executed.

This means if condition is true If statements are executed. If not possible else statements are executed.

This kind of conditional statements are required for finding few real life problems like finding odd or even number, Checking the number is divisible by two, three, etc., Finding Largest Number, Finding Smallest Number, etc. many programs like this.

Syntax

IF >CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N


ELSE 
Statement 1 
Statement 2
.
.
.
.
Statement N

END IF;

Example Queries

SQL> DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=10;
    N2:=5;
    IF N1 > N2 THEN
    dbms_output.put_line('The Values of N1 is greater than N2');
    ELSE
    dbms_output.put_line('The Values of N2 is greater than N1');
 12
    END IF;
 14
 15
    END;
    /

OUTPUT:

The Values of N1 is greater than N2

PL/SQL procedure successfully completed.

SQL> DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=10;
    N2:=5;
    IF N1 > N2 THEN
    dbms_output.put_line('The Values of N2 is smaller than N1');
    ELSE
    dbms_output.put_line('The Values of N1 is smaller than N2');
 12
    END IF;
 14
 15
    END;
    /

OUTPUT:

The Values of N2 is smaller than N1
PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 > N2 THEN
    dbms_output.put_line('The Values of N2 is smaller than N1');
    ELSE
    dbms_output.put_line('The Values of N1 is smaller than N2');
 12
    END IF;
 14
 15
    END;
    /

OUTPUT:

Enter value for n1: 15
old   6:     N1:=&N1;
new   6:     N1:=15;
Enter value for n2: 16
old   7:     N2:=&N2;
new   7:     N2:=16;

OUTPUT:

The Values of N1 is smaller than N2

PL/SQL procedure successfully completed.

SQL>  DECLARE
    N1 INTEGER;
    N2 INTEGER;
  4
    BEGIN
    N1:=&N1;
    N2:=&N2;
    IF N1 > N2 THEN
    dbms_output.put_line('The Values of N2 is smaller than N1');
    ELSE
    dbms_output.put_line('The Values of N1 is smaller than N2');
 12
    END IF;
 14
 15
    END;
    /

OUTPUT:

Enter value for n1: 10
old   6:     N1:=&N1;
new   6:     N1:=10;
Enter value for n2: 6
old   7:     N2:=&N2;
new   7:     N2:=6;
The Values of N2 is smaller than N1

PL/SQL procedure successfully completed.

SQL>  DECLARE
  2      N1 INTEGER;
  3      N2 INTEGER;
  4
  5      BEGIN
  6      N1:=&N1;
  7      N2:=&N2;
  8      IF N1 > N2 THEN
  9      dbms_output.put_line('The Values of N1 is greater than N2');
 10      ELSE
 11      dbms_output.put_line('The Values of N2 is greater than N1');
 12
 13
 14      END IF;
 15
 16
 17      END;
 18      /

OUTPUT:

Enter value for n1: 15
old   6:     N1:=&N1;
new   6:     N1:=15;
Enter value for n2: 26
old   7:     N2:=&N2;
new   7:     N2:=26;
The Values of N2 is greater than N1

PL/SQL procedure successfully completed.

SQL>  DECLARE
  2      N1 INTEGER;
  3      N2 INTEGER;
  4
  5      BEGIN
  6      N1:=&N1;
  7      N2:=&N2;
  8      IF N1 > N2 THEN
  9      dbms_output.put_line('The Values of N1 is greater than N2');
 10      ELSE
 11      dbms_output.put_line('The Values of N2 is greater than N1');
 12
 13
 14      END IF;
 15
 16
 17      END;
 18      /

OUTPUT:

Enter value for n1: 12
old   6:     N1:=&N1;
new   6:     N1:=12;
Enter value for n2: 11
old   7:     N2:=&N2;
new   7:     N2:=11;
The Values of N1 is greater than N2
PL/SQL procedure successfully completed.

SQL> DECLARE
  2      N1 INTEGER;
  3
  4
  5      BEGIN
  6      N1:=&N1;
  7
  8      IF MOD(N1,2)=0 THEN
  9      dbms_output.put_line('EVEN NUMBER');
 10      ELSE
 11      dbms_output.put_line('ODD NUMBER');
 12
 13
 14      END IF;
 15
 16
 17      END;
 18      /

Enter value for n1: 5
old   6:     N1:=&N1;
new   6:     N1:=5;
ODD NUMBER

PL/SQL procedure successfully completed.

SQL> DECLARE
  2      N1 INTEGER;
  3
  4
  5      BEGIN
  6      N1:=&N1;
  7
  8      IF MOD(N1,2)=0 THEN
  9      dbms_output.put_line('EVEN NUMBER');
 10      ELSE
 11      dbms_output.put_line('ODD NUMBER');
 12
 13
 14      END IF;
 15
 16
 17      END;
 18      /

Enter value for n1: 6
old   6:     N1:=&N1;
new   6:     N1:=6;
EVEN NUMBER

PL/SQL procedure successfully completed.

3.) if condition elsif condition else statements

Syntax

IF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N
ELSEIF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N

ELSE
Statement 1 
Statement 2
.
.
.
.
Statement N

END IF;

Example Queries

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2 num1 INTEGER:= 7;
  3 num2 INTEGER:= 8;
  4
  5 BEGIN
  6
  7 IF num1 > num2 THEN
  8 dbms_output.put_line('NUM1 SMALLER THAN NUM2');
  9
 10 ELSIF num1 = num2 THEN
 11 dbms_output.put_line('EQUAL');
 12
 13 ELSE
 14 dbms_output.put_line('NUM2 SMALLER THAN NUM1');
 15 END IF;
 16
 17 END;
 18 /

Output:

NUM2 SMALLER THAN NUM1

PL/SQL procedure successfully completed.

SQL> DECLARE
  2 num1 INTEGER:= 7;
  3 num2 INTEGER:= 7;
  4
  5 BEGIN
  6
  7 IF num1 > num2 THEN
  8 dbms_output.put_line('NUM1 SMALLER THAN NUM2');
  9
 10 ELSIF num1 = num2 THEN
 11 dbms_output.put_line('EQUAL');
 12
 13 ELSE
 14 dbms_output.put_line('NUM2 SMALLER THAN NUM1');
 15 end if;
 16
 17 END;
 18 /

Output:

EQUAL

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER:= 16;
  3  num2 INTEGER:= 7;
  4
  5  BEGIN
  6
  7  IF num1 > num2 THEN
  8  dbms_output.put_line('NUM1 SMALLER THAN NUM2');
  9
 10  ELSIF num1 = num2 THEN
 11  dbms_output.put_line('EQUAL');
 12
 13  ELSE
 14  dbms_output.put_line('NUM2 SMALLER THAN NUM1');
 15  end if;
 16
 17  END;
 18  /

Output:

NUM1 SMALLER THAN NUM2

PL/SQL procedure successfully completed.

SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
  4
BEGIN
num1:=&num1;
num2:=&num2;
IF num1 > num2 THEN
dbms_output.put_line('NUM1 SMALLER THAN NUM2');
 10
ELSIF num1 = num2 THEN
dbms_output.put_line('EQUAL');
 13
ELSE
dbms_output.put_line('NUM2 SMALLER THAN NUM1');
end if;
 17
END;
/

Input:

Enter value for num1: 0
old   6: num1:=&num1;
new   6: num1:=0;
Enter value for num2: 0
old   7: num2:=&num2;
new   7: num2:=0;

Output:

EQUAL

PL/SQL procedure successfully completed.

SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
  4
BEGIN
num1:=&num1;
num2:=&num2;
IF num1 > num2 THEN
dbms_output.put_line('NUM1 SMALLER THAN NUM2');
 10
ELSIF num1 = num2 THEN
dbms_output.put_line('EQUAL');
 13
ELSE
dbms_output.put_line('NUM2 SMALLER THAN NUM1');
end if;
 17
END;
/

Input:

Enter value for num1: 11
old   6: num1:=&num1;
new   6: num1:=11;
Enter value for num2: 6
old   7: num2:=&num2;
new   7: num2:=6;

Output:

NUM1 SMALLER THAN NUM2

PL/SQL procedure successfully completed.

SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
  4
BEGIN
num1:=&num1;
num2:=&num2;
IF num1 > num2 THEN
dbms_output.put_line('NUM1 SMALLER THAN NUM2');
 10
ELSIF num1 = num2 THEN
dbms_output.put_line('EQUAL');
 13
ELSE
dbms_output.put_line('NUM2 SMALLER THAN NUM1');
end if;
 17
END;
/

Input:

Enter value for num1: 17
old   6: num1:=&num1;
new   6: num1:=17;
Enter value for num2: 19
old   7: num2:=&num2;
new   7: num2:=19;

Output:

NUM2 SMALLER THAN NUM1

PL/SQL procedure successfully completed.

4.) Nested if condition then statements

This is also one of the conditional statements used in PL / SQL. Here, we are going to write the same if else statements. But in place of if statements we are going to write another if condition else statements. The workings of these statements are same and similar to the previous if else statements.

Syntax

Syntax 1
IF <CONDITION> THEN

IF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N
ELSE 
Statement 1 
Statement 2
.
.
.
.
Statement N
END IF;
ELSE
Statement 1 
Statement 2
.
.
.
.
Statement N
END IF;
Syntax 2:
IF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N


ELSE
IF <CONDITION> THEN
Statement 1 
Statement 2
.
.
.
.
Statement N
ELSE 
Statement 1 
Statement 2
.
.
.
.
Statement N
END IF;
END IF;

Example Queries

 SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
num3 INTEGER;
  5
BEGIN
num1:= 17;
num2:= 12;
num3:= 10;
IF num1 > num2 THEN
 11
 12
 13
 14
IF num1 > num3 THEN
  dbms_output.put_line('The greatest number is '|| num1);
  END IF;
ELSIF num2 > num3 THEN
 dbms_output.put_line('The greatest number is '|| num2);
ELSE
dbms_output.put_line('The greatest number is '|| num3);
END IF;
 23
END;
/

Output:

The greatest number is 17

PL/SQL procedure successfully completed.

SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
num3 INTEGER;
  5
BEGIN
num1:= 9;
num2:= 12;
num3:= 10;
IF num1 > num2 THEN
 11
 12
 13
 14
IF num1 > num3 THEN
  dbms_output.put_line('The greatest number is '|| num1);
  END IF;
ELSIF num2 > num3 THEN
 dbms_output.put_line('The greatest number is '|| num2);
ELSE
dbms_output.put_line('The greatest number is '|| num3);
END IF;
 23
END;
/ 

Output:

The greatest number is 12

PL/SQL procedure successfully completed.

 SQL> DECLARE
num1 INTEGER;
num2 INTEGER;
num3 INTEGER;
  5
BEGIN
num1:= 9;
num2:= 1;
num3:= 10;
IF num1 > num2 THEN
 11
 12
 13
 14
IF num1 > num3 THEN
  dbms_output.put_line('The greatest number is '|| num1);
  END IF;
ELSIF num2 > num3 THEN
 dbms_output.put_line('The greatest number is '|| num2);
ELSE
dbms_output.put_line('The greatest number is '|| num3);
END IF;
 23
END;
/

Output:

The greatest number is 10
PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER;
  3  num2 INTEGER;
  4  num3 INTEGER;
  5
  6  BEGIN
  7  num1:=&num1;
  8  num2:=&num2;
  9  num3:=&num3;
 10  IF num1 > num2 THEN
 11
 12
 13
 14
 15  IF num1 > num3 THEN
 16    dbms_output.put_line('The greatest number is '|| num1);
 17    END IF;
 18  ELSIF num2 > num3 THEN
 19   dbms_output.put_line('The greatest number is '|| num2);
 20
 21  ELSE
 22  dbms_output.put_line('The greatest number is '|| num3);
 23  END IF;
 24
 25  END;
 26  /

Input:

Enter value for num1: 10
old   7: num1:=&num1;
new   7: num1:=10;
Enter value for num2: 16
old   8: num2:=&num2;
new   8: num2:=16;
Enter value for num3: 20
old   9: num3:=&num3;
new   9: num3:=20;

Output:

The greatest number is 20

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER;
  3  num2 INTEGER;
  4  num3 INTEGER;
  5
  6  BEGIN
  7  num1:=&num1;
  8  num2:=&num2;
  9  num3:=&num3;
 10  IF num1 > num2 THEN
 11
 12
 13
 14
 15  IF num1 > num3 THEN
 16    dbms_output.put_line('The greatest number is '|| num1);
 17    END IF;
 18  ELSIF num2 > num3 THEN
 19   dbms_output.put_line('The greatest number is '|| num2);
 20
 21  ELSE
 22  dbms_output.put_line('The greatest number is '|| num3);
 23  END IF;
 24
 25  END;
 26  /

Input:

Enter value for num1: 11
old   7: num1:=&num1;
new   7: num1:=11;
Enter value for num2: 16
old   8: num2:=&num2;
new   8: num2:=16;
Enter value for num3: 12
old   9: num3:=&num3;
new   9: num3:=12;

Output:

The greatest number is 16
PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER;
  3  num2 INTEGER;
  4  num3 INTEGER;
  5
  6  BEGIN
  7  num1:=&num1;
  8  num2:=&num2;
  9  num3:=&num3;
 10  IF num1 > num2 THEN
 11
 12
 13
 14
 15  IF num1 > num3 THEN
 16    dbms_output.put_line('The greatest number is '|| num1);
 17    END IF;
 18  ELSIF num2 > num3 THEN
 19   dbms_output.put_line('The greatest number is '|| num2);
 20
 21  ELSE
 22  dbms_output.put_line('The greatest number is '|| num3);
 23  END IF;
 24
 25  END;
 26  /

Input:

Enter value for num1: 21
old   7: num1:=&num1;
new   7: num1:=21;
Enter value for num2: 20
old   8: num2:=&num2;
new   8: num2:=20;
Enter value for num3: 19
old   9: num3:=&num3;
new   9: num3:=19;

Output:

The greatest number is 21

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  num1 INTEGER;
  3  num2 INTEGER;
  4  num3 INTEGER;
  5
  6  BEGIN
  7  num1:=&num1;
  8  num2:=&num2;
  9  num3:=&num3;
 10  IF num1 > num2 THEN
 11
 12
 13
 14
 15  IF num1 > num3 THEN
 16    dbms_output.put_line('The greatest number is '|| num1);
 17    END IF;
 18  ELSIF num2 > num3 THEN
 19   dbms_output.put_line('The greatest number is '|| num2);
 20
 21  ELSE
 22  dbms_output.put_line('The greatest number is '|| num3);
 23  END IF;
 24
 25  END;
 26  /

Input:

Enter value for num1: 101
old   7: num1:=&num1;
new   7: num1:=101;
Enter value for num2: 129
old   8: num2:=&num2;
new   8: num2:=129;
Enter value for num3: 306
old   9: num3:=&num3;
new   9: num3:=306;

Output:

The greatest number is 306

PL/SQL procedure successfully completed.

This is all about PL / SQL Conditional Expressions.

Next TopicPatterns in PL / SQL

← prev next →

For Videos Join Our Youtube Channel: Join Now

Feedback

Send your Feedback to [email protected]

Help Others, Please Share

Learn Latest Tutorials

Splunk

SPSS

Swagger

Transact-SQL

Tumblr

ReactJS

Regex

Reinforcement Learning

R Programming

RxJS

React Native

Python Design Patterns

Python Pillow

Python Turtle

Keras

Preparation

Aptitude

Reasoning

Verbal Ability

Interview Questions

Company Questions

Trending Technologies

Artificial Intelligence

AWS

Selenium

Cloud Computing

Hadoop

ReactJS

Data Science

Angular 7

Blockchain

Git

Machine Learning

DevOps

B.Tech / MCA

DBMS

Data Structures

DAA

Operating System

Computer Network

Compiler Design

Computer Organization

Discrete Mathematics

Ethical Hacking

Computer Graphics

Software Engineering

Web Technology

Cyber Security

Automata

C Programming

C++

Java

.Net

Python

Programs

Control System

Data Mining

Data Warehouse

^{Like/Subscribe us for latest updates or newsletter}

PL/SQL Tutorial

Control Statements

PL/SQL Procedure

PL/SQL Function

PL/SQL Cursor

PL/SQL Exception

PL/SQL Trigger

Misc

Interview Questions

Quiz

Conditional Statements in PL / SQL

Types of Conditional Statements

1.) if Condition then Statement

2.) if Condition then else Statement

3.) if condition elsif condition else statements

4.) Nested if condition then statements

Feedback

Help Others, Please Share

Learn Latest Tutorials

Preparation

Trending Technologies

B.Tech / MCA