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 →

Latest Courses

We provides tutorials and interview questions of all technology like java tutorial, android, java frameworks

Contact info

G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India

[email protected].

Python

AI, ML and Data Science

Java

B.Tech and MCA

Web Technology

Software Testing

Technical Interview

Java Interview

Web Interview

Database Interview

Company Interviews

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

Latest Courses

Contact info

Follow us

Tutorials

Interview Questions

Online Compiler