Oracle/PLSQL: IF-THEN-ELSE Statement

There are three different syntaxes for these types of statements.

Syntax #1: IF-THEN

IF condition THEN
   {...statements...}
END IF;

Syntax #2: IF-THEN-ELSE

IF condition THEN
   {...statements...}

ELSE
   {...statements...}

END IF;

Syntax #3: IF-THEN-ELSIF

IF condition THEN
   {...statements...}

ELSIF condition THEN
   {...statements...}

ELSE
   {...statements...}

END IF;

Here is an example of a function that uses the IF-THEN-ELSE statement:

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 example, we've created a function called IncomeLevel. It has one parameter called name_in and it returns a varchar2. The function will return the income level based on the employee's name.