PL/SQL If Else Statement Example | PL/SQL If Else Tutorial
PL/SQL IF-THEN-ELSIF statement allows you to choose between several alternatives. An IF-THEN statement can be followed by an optional ELSIF…ELSE statement. The ELSIF clause lets you add additional conditions. When using IF-THEN-ELSIF statements there are a few tips to keep in mind.
- It’s ELSIF, not ELSEIF.
- An IF-THEN statement can have zero or one ELSE’s and it must appear after any ELSIF’s.
- An IF-THEN statement can have zero to many ELSIF’s and they must appear before the ELSE.
- Once an ELSIF succeeds, none of the left ELSIF’s or ELSE’s will be tested.
PL/SQL If Else Statement
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.
Types of If-Else statements available in PL/SQL is:
- If then statement
- If then else statement
- Nested if-then statements
- 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 suggests. 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.
If condition then Statements End if;
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
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.
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.
See the following syntax.
If (condition) then Statements of if block Else Statements of else block
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
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 statements mean an if statement inside the other if statement which means we can place an if statement inside other if statement.
If (condition1) then Statements for condition1 If (condition2) then Statements for conditon2 End if; End if;
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;
Num1 is smaller than Num2 Num1 is also smaller than Num3 Nested if finished
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.
If (condition) then Statement; Elseif (condition) then Statement; . . . Else Statement; End if
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;
Num1 is small After If-then-elseif-then-else ladder
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.