Using IF-THEN-ELSE Statement in Oracle

This Oracle tutorial explains how to use the IF-THEN-ELSE declaration in Oracle with syntax and examples.

Description

In Oracle, the IF-THEN-ELSE assertion is used to execute code when a situation is TRUE, or execute different code if the circumstance evaluates to FALSE.

Syntax

There are different syntaxes for the IF-THEN-ELSE statement.

Syntax (IF-THEN)

The syntax for IF-THEN in Oracle/PLSQL is:

IF condition THEN
   {...statements to execute when condition is TRUE...}
END IF;

You use the the IF-THEN syntax, when you want to execute statements solely when situation is TRUE.

Syntax (IF-THEN-ELSE)

The syntax for IF-THEN-ELSE in Oracle/PLSQL is:

IF condition THEN
   {...statements to execute when condition is TRUE...}

ELSE
   {...statements to execute when condition is FALSE...}

END IF;

You use the IF-THEN-ELSE syntax, when you want to execute one set of statements when condition is TRUE or a one of a kind set of statements when circumstance is FALSE.

Syntax (IF-THEN-ELSIF)

The syntax for IF-THEN-ELSIF in Oracle/PLSQL is:

IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}

ELSIF condition2 THEN
   {...statements to execute when condition1 is FALSE and condition2 is TRUE...}

END IF;

You use the IF-THEN-ELSIF syntax, when you choose to execute one set of statements when condition1 is TRUE or a extraordinary set of statements when condition2 is TRUE.

Syntax (IF-THEN-ELSIF-ELSE)

The syntax for IF-THEN-ELSIF-ELSE in Oracle/PLSQL is:

IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}

ELSIF condition2 THEN
   {...statements to execute when condition1 is FALSE and condition2 is TRUE...}

ELSE
   {...statements to execute when both condition1 and condition2 are FALSE...}

END IF;

You use the IF-THEN-ELSIF-ELSE syntax, when you want to execute one set of statements when condition1 is TRUE, a distinct set of statements when condition2 is TRUE, or a one-of-a-kind set of statements when all previous stipulations (ie: condition1 and condition2) are FALSE.

Note

Once a circumstance is located to be TRUE, the IF-THEN-ELSE statement will execute the corresponding code and not evaluate the conditions any further. If no situation is met, the ELSE portion of the IF-THEN-ELSE assertion will be executed. It is vital to be aware that the ELSIF and ELSE portions are optional.

Example

The following is instance the use of the IF-THEN-ELSE announcement in an Oracle function:

CREATE OR REPLACE Function IncomeLevel
   ( name_in IN varchar2 )
   RETURN varchar2
IS
   monthly_value number(6);
   ILevel varchar2(20);

   cursor c1 is
     SELECT monthly_income
     FROM employees
     WHERE name = name_in;

BEGIN

   open c1;
   fetch c1 into monthly_value;
   close c1;

   IF monthly_value <= 4000 THEN
      ILevel := 'Low Income';

   ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
      ILevel := 'Avg Income';

   ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
      ILevel := 'Moderate Income';

   ELSE
      ILevel := 'High Income';

   END IF;

   RETURN ILevel;

END;

In this IF-THEN-ELSE announcement example, we’ve got created a feature referred to as IncomeLevel. It has one parameter known as name_in and it returns a varchar2. The feature will return the profits stage primarily based on the employee’s name.