PL/SQL Conditional Statements

Decision making statements are those statements which are in charge of executing a statement out of multiple given statements based on some condition. The condition will return either true or false. Based on what the condition returns, the associated statement is executed.

For example, if someone says, If I get 40 marks, I will pass the exam, else I will fail. In this case condition is getting 40 marks, if its true then the person will pass else he/she will fail.

This can be logically implemented in PL/SQL block using decision making statements.

The decision making statements in PL/SQL are of two types:

1. If Else statements
2. Case statement

Let's see them all one by one with examples.

PL/SQL: if Statement

The if statement, or the if...then statement can be used when there is only a single condition to be tested. If the result of the condition is TRUE then certain specified action will be performed otherwise if it is FALSE then no action is taken and the control of program will just move out of the if code block.

Syntax:

if <test_condition> then
	body of action
end if;

Below we have a diagram or we can say a flowchart showing the use of if condition statement:

Now that we know what is the purpose of the if statement and its syntax, let's see an example.

Time for an Example!

Belwo we have a simple program to find the greatest number among two given numbers.

set serveroutput on;
DECLARE
	x int:=10;
	y int:=80;
BEGIN
	if(y>x) then
		dbms_output.put_line('Result: ' ||y|| ' is greater than ' ||x);
	end if;
END;

Result: 80 is greater than 10 PL/SQL procedure successfully completed.

PL/SQL: if...then...else statement

Using this statement group we can specify two statements or two set of statements, dependent on a condition such that when the condition is true then one set of statements is executed and if the condition is false then the other set of statements is executed.

Syntax:

if <test_condition> then
	statement 1/set of statements 1
else
	statement 2/set of statements 2
end if;

Below we have a diagram or we can say a flowchart showing the use of if...then...else condition statement:

Now that we know what is the purpose of the if...then...else statement and its syntax, let's see an example.

Time for an Example!

Below we have a program to find whether a given number by user is even or odd.

set serveroutput on;

DECLARE
	x int;
BEGIN
	x := &x;
	if mod(x,2) = 0 then
		dbms_output.put_line('Even Number');
	else
		dbms_output.put_line('Odd Number');
	end if;
END;

Enter value for x:6 Even Number PL/SQL procedure successfully completed.

In the above program, mod function is used which will return the remainder after dividing the value of x by 2.

PL/SQL: if...then...elsif...else statement

It is used to check multiple conditions. Sometimes it is required to test more than one condition in that case if...then...else statement cannot be used. For this purpose, if...then...elsif...else statement is suitable in which all the conditions are tested one by one and whichever condition is found to be TRUE, that block of code is executed. And if all the conditions result in FALSE then the else part is executed.

In the following syntax, it can be seen firstly condition1 is checked, if it is true, the statements following it are executed and then control moves out of the complete if block but if the condition is false then the control checks condition2 and repeats the same process. If all the conditions fail then the else part is executed.

Syntax:

if <test_condition1> then
	body of action
elsif <test_condition2>then
	body of action
elsif<test_condition3>then
	body of action
...
...
...
else
	body of action
end if;

As you can se in the syntax above, we can have multiple elsif statements with as many conditions as we want. Below we have a diagram or we can say a flowchart:

Let's see a code example to understand the concept better,

Time for an Example!

Below we have a program to find whether the two given numbers are equal and if they are not equal then which one is greater.

set serveroutput on;

DECLARE
	a int;
	b int;
BEGIN
	a := &a;
	b := &b;
	if(a>b) then
		dbms_output.put_line(‘a is greater than b’);
	elsif(b>a) then
		dbms_output.put_line(‘b is greater than a’);
	else
		dbms_output.put_line(‘Both a and b are equal’);
	end if;
END;

Enter value for a: 8 Enter value for b: 5 a is greater than b PL/SQL procedure successfully completed.

PL/SQL: Case Statement

If we try to describe the case statement in one line then, then we can say means "one out of many". It is a decision making statement that selects only one option out of the multiple available options.

It uses a selector for this purpose. This selector can be a variable, function or procedure that returns some value and on the basis of the result one of the case statements is executed. If all the cases fail then the else case is executed.

Syntax:

CASE selector
	when value1 then Statement1;
	when value2 then Statement2;
	...
	...
	else statement;
end CASE;

Let's take an example to see case statement in action.

Time for an Example!

Below we have a program to demonstrate the use of a simple case statement.

set serveroutput on;
DECLARE
	a int;
	b int;
BEGIN
	a := &a;
	b := mod(a,2);
	CASE b
		when 0 then dbms_output.put_line('Even Number');
		when 1 then dbms_output.put_line('Odd Number');
 		else dbms_output.put_line('User has not given any input value to check');
	END CASE;
END;

Enter the value for a:7 Odd number PL/SQL procedure successfully completed.

In the above program, mod function is used which will return the remainder after dividing the value of a by 2. The remainder will be either 0 or 1, based on that the selected case will be executed.

Searched Case Statement

In this type of case statement, no selector is used but a test condition is checked by using the WHEN clause itself. When the condition is TRUE the statements following it are executed otherwise other test conditions are checked using the WHEN clause sequentially. And if all the test conditions gets failed then the else case is executed.

Syntax:

CASE
	when <test_condition1> then statement1;
	when <test_condition2> then statement2;
    ...
    ...
	else defaultstatement;
end case;

Let's take an example to see searched case statements in action.

Time for an Example!

Below we have a program to demonstrate the use of Searched Case statement.

set serveroutput on;

DECLARE
	dt Date;
	str varchar2(10);
BEGIN
	dt := '&date';
	str := to_char(dt,'DY');
	CASE
		when str in ('SAT','SUN') then dbms_output.put_line('Its the Weekend');
 		else dbms_output.put_line('Not a Weekend');
	END CASE;
END;

Enter the value for dt:28-APR-2019 Weekend date PL/SQL procedure successfully completed.