AppDividend
Latest Code Tutorials

PL/SQL If Else Statement Example | PL/SQL If Else Tutorial

0

PL/SQL If Else Statement Example | PL/SQL If Else Tutorial is today’s topic. Decision making is an important part when it comes to programming languages, unlike other programming languages such as C, C++, Java, Python, etc. SQL also has decision making statements. Decision-making statements in programming languages are used to decide the direction of the flow of the statements to be executed. Decision-making statements are those who will decide the flow-control of SQL statements based on the conditions.

PL/SQL If Else Statement

Types of If-Else statements available in PL/SQL is:

  1. If then statement
  2. If then else statement
  3. Nested if-then statements
  4. If-then-else if-then-else ladder

Let’s understand all the types mentioned above with examples.

#If then statement

If then the statement is as simple as its name suggest. If the statements under if block return TRUE then if block will be executed otherwise nothing will be executed. The control will come out of If statement and rest another set of statements will be executed.

#Syntax

If condition then
Statements
End if;

#Sample Example

declare
Values to be declared
begin
if condition then
dbms_output.put_line('TRUE'); 
end if; 
dbms_output.put_line('If not executed'); 
end;

See the actual example.

declare
num1 number:= 10; 
num2 number:= 20; 
begin
if num1 > num2 then
dbms_output.put_line('Num2 is Larger'); 
end if; 
dbms_output.put_line('Outside The if'); 
end;

See the output.

Outside The if

#Explanation

As 10 > 20 is false, so the statement outside if it is executed. If the condition would have been true, then if the block would have been executed.

#If-then-else

An if-then-else statement, if the condition in the if block is True then statements inside the if block is executed otherwise, statements inside the else block will be executed.

#Syntax

See the following syntax.

If (condition) then
Statements of if block
Else
Statements of else block

#Example

See the following example.

Declare
Num1 number:= 10;
Num2 number:= 20;
Begin
If Num1 < Num2 then
dbms_output_put_line(‘Num1 is smaller than Num2);
else
dbms_output_put_line(‘Num2 is smaller than Num1);
end if;
dbms_output_put_line(‘I am outside the if and else block’);
end;

See the following output.

Num1 is smaller than Num2
I am outside the if and else block

#Explanation

As the value of Num1 is smaller than Num2 the statements inside the if block is executed, leaving behind the else block as the condition was not false.

After if and else statements at last outer block gets executed.

#Nested-if-then

Nested if-then statements mean an if statement inside the other if statement which means we can place an if statement inside other if statement.

#Syntax

If (condition1) then
Statements for condition1
	If (condition2) then
	Statements for conditon2
    End if;
End if;

#Example

declare
Num1 number: =10;
Num2 number: =20;
Num3 number: =30;
begin
if Num1<Num2 then
dbms_output_put_line(‘Num1 is smaller than Num2’);
if Num1<Num3 then
dbms_output_put_line(‘Num1 is also smaller than Num3’);
end if;
end if;
dbms_output_put_line(‘Nested if finished’);
end;

#Output

Num1 is smaller than Num2
Num1 is also smaller than Num3
Nested if finished

#Explanation

As the value of Num1 is smaller than Num2 the statements inside the if block is executed then after that when the second condition again Num1 was smaller than Num3 so second condition statement was also executed. After if and else statements at last outer block gets executed.

#If-then-else if-then-else ladder

The if-then statements usually get executed from top to down. As soon as one of the conditions get true another part of if which is present inside the previous if statements get executed, and the rest of the ladder continues with the same format till the condition gets false.

If none of the conditions evaluates to True, then the final else statements will be executed.

#Syntax

If (condition) then
Statement;
Elseif (condition) then
Statement;
.
.
.
Else
Statement;
End if

#Example

declare
Num1 number: =10;
Num2 number: =20;
begin
if Num1<Num2 then
dbms_output_put_line (‘Num1 is small’);
elseif Num1 =Num2 then
dbms_output_put_line (‘Both are equal’);
else
dbms_output_put_line (‘Num2 is greater’);
end if;
dbms_output_put_line (‘After If-then-elseif-then-else ladder’);
end;

#Output

Num1 is small
After If-then-elseif-then-else ladder

#Explanation

As Num1 is smaller than Num2 first condition block gets executed. After that second condition was not checked as the first condition got true and after that last statement got executed which you can see from the above output.

Finally, PL/SQL If Else Statement Example | PL/SQL If Else Tutorial is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.