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
2 N1 INTEGER;
3 BEGIN
4 N1:=10;
5 IF N1 > 10 THEN
6 dbms_output.put_line('The Value is' || N1);
7 END IF;
8 END;
9 / OUTPUT:
PL/SQL procedure successfully completed.
SQL> DECLARE
2 N1 INTEGER;
3 BEGIN
4 N1:=10;
5 IF N1 >= 10 THEN
6 dbms_output.put_line('The Value is ' || N1);
7 END IF;
8 END;
9 /OUTPUT:
The Value is 10
PL/SQL procedure successfully completed.
SQL> DECLARE
2 N1 INTEGER;
3 BEGIN
4 N1:=&N1;
5 IF N1 > 10 THEN
6 dbms_output.put_line('The Value is' || N1);
7 END IF;
8 END;
9 /- OUTPUT:
Enter value for n1: 5
old 4: N1:=&N1;
new 4: N1:=5;
PL/SQL procedure successfully completed.
SQL> DECLARE
2 N1 INTEGER;
3 BEGIN
4 N1:=&N1;
5 IF N1 > 10 THEN
6 dbms_output.put_line('The Value is ' || N1);
7 END IF;
8 END;
9 / 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
2 N1 INTEGER;
3 BEGIN
4 N1:=&N1;
5 IF N1 >= 10 THEN
6 dbms_output.put_line('The Value is ' || N1);
7 END IF;
8 END;
9 / OUTPUT:
Enter value for n1: 9
old 4: N1:=&N1;
new 4: N1:=9;
PL/SQL procedure successfully completed.
SQL> DECLARE
2 N1 INTEGER;
3 BEGIN
4 N1:=&N1;
5 IF N1 >= 10 THEN
6 dbms_output.put_line('The Value is ' || N1);
7 END IF;
8 END;
9 /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
2 N1 INTEGER;
3 BEGIN
4 N1:=&N1;
5 IF N1 >= 10 THEN
6 dbms_output.put_line('The Value is ' || N1);
7 END IF;
8 END;
9 /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
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 Value is ' || N1*N2);
10 END IF;
11
12
13 END;
14 / 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
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 Value is ' || N1*N2);
10 END IF;
11
12
13 END;
14 /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
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 Value is ' || N1*N2);
10 END IF;
11
12
13 END;
14 / 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
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 Value is ' || N1*N2);
10 END IF;
11
12
13 END;
14 /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.
IF >CONDITION> THEN
Statement 1
Statement 2
.
.
.
.
Statement N
ELSE
Statement 1
Statement 2
.
.
.
.
Statement N
END IF;Example Queries
SQL> DECLARE
2 N1 INTEGER;
3 N2 INTEGER;
4
5 BEGIN
6 N1:=10;
7 N2:=5;
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 END IF;
14
15
16 END;
17 /OUTPUT:
The Values of N1 is greater than N2
PL/SQL procedure successfully completed.
SQL> DECLARE
2 N1 INTEGER;
3 N2 INTEGER;
4
5 BEGIN
6 N1:=10;
7 N2:=5;
8 IF N1 > N2 THEN
9 dbms_output.put_line('The Values of N2 is smaller than N1');
10 ELSE
11 dbms_output.put_line('The Values of N1 is smaller than N2');
12
13 END IF;
14
15
16 END;
17 /OUTPUT:
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 N2 is smaller than N1');
10 ELSE
11 dbms_output.put_line('The Values of N1 is smaller than N2');
12
13 END IF;
14
15
16 END;
17 /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
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 N2 is smaller than N1');
10 ELSE
11 dbms_output.put_line('The Values of N1 is smaller than N2');
12
13 END IF;
14
15
16 END;
17 / 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
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 if statements with a condition are executed. Even if else if condition is False, then the else statements are executed.
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; 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
2 num1 INTEGER;
3 num2 INTEGER;
4
5 BEGIN
6 num1:=&num1;
7 num2:=&num2;
8 IF num1 > num2 THEN
9 dbms_output.put_line('NUM1 SMALLER THAN NUM2');
10
11 ELSIF num1 = num2 THEN
12 dbms_output.put_line('EQUAL');
13
14 ELSE
15 dbms_output.put_line('NUM2 SMALLER THAN NUM1');
16 end if;
17
18 END;
19 / 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
2 num1 INTEGER;
3 num2 INTEGER;
4
5 BEGIN
6 num1:=&num1;
7 num2:=&num2;
8 IF num1 > num2 THEN
9 dbms_output.put_line('NUM1 SMALLER THAN NUM2');
10
11 ELSIF num1 = num2 THEN
12 dbms_output.put_line('EQUAL');
13
14 ELSE
15 dbms_output.put_line('NUM2 SMALLER THAN NUM1');
16 end if;
17
18 END;
19 / 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
2 num1 INTEGER;
3 num2 INTEGER;
4
5 BEGIN
6 num1:=&num1;
7 num2:=&num2;
8 IF num1 > num2 THEN
9 dbms_output.put_line('NUM1 SMALLER THAN NUM2');
10
11 ELSIF num1 = num2 THEN
12 dbms_output.put_line('EQUAL');
13
14 ELSE
15 dbms_output.put_line('NUM2 SMALLER THAN NUM1');
16 end if;
17
18 END;
19 / 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
2 num1 INTEGER;
3 num2 INTEGER;
4 num3 INTEGER;
5
6 BEGIN
7 num1:= 17;
8 num2:= 12;
9 num3:= 10;
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 ELSE
21 dbms_output.put_line('The greatest number is '|| num3);
22 END IF;
23
24 END;
25 / Output:
The greatest number is 17
PL/SQL procedure successfully completed.
SQL> DECLARE
2 num1 INTEGER;
3 num2 INTEGER;
4 num3 INTEGER;
5
6 BEGIN
7 num1:= 9;
8 num2:= 12;
9 num3:= 10;
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 ELSE
21 dbms_output.put_line('The greatest number is '|| num3);
22 END IF;
23
24 END;
25 / Output:
The greatest number is 12
PL/SQL procedure successfully completed.
SQL> DECLARE
2 num1 INTEGER;
3 num2 INTEGER;
4 num3 INTEGER;
5
6 BEGIN
7 num1:= 9;
8 num2:= 1;
9 num3:= 10;
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 ELSE
21 dbms_output.put_line('The greatest number is '|| num3);
22 END IF;
23
24 END;
25 / 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.
Leave a Reply