using IF THEN ELSE Statement in oracle database query

An IF-THEN-ELSE Statement in Oracle PLSQL is basically a conditional statement that evaluates an expression, if the expression evaluates to true, then the ‘THEN’ portion of the code is executed, if the expression evaluates to false, then ‘ELSE’ part of the code gets executed.

There are 3 syntaxes of IF-THEN-ELSE Statement in Oracle PLSQL:

Syntax 1: IF-THEN Statement in Oracle PLSQL
IF condition THEN
Business_Logic
END IF;

Example: IF-THEN Statement in Oracle PLSQL

IF department = 'IT' THEN
  commission := 10;
END IF;

Syntax 2: IF-THEN-ELSE Statement in Oracle PLSQL
IF condition THEN
Business_Logic_1
ELSE
Business_Logic_2
END IF;

Example: IF-THEN-ELSE Statement in Oracle PLSQL

IF department = 'IT' THEN
  commission := 10;
ELSE
  commission := 20;
END IF;

Syntax 3: IF-THEN-ELSEIF Statement in Oracle PLSQL
IF condition_1 THEN
Business_Logic_1
ELSEIF condition_2 THEN
Business_Logic_2
ELSEIF condition_3 THEN
Business_Logic_3
.
.
ELSEIF condition_N THEN
Business_Logic_N
ELSE
Default_Business_Logic
END IF;

Example: IF-THEN-ELSEIF Statement in Oracle PLSQL

IF department = 'IT' THEN
  commission :=10;
ELSEIF department = 'Sales' THEN
  commission := 20;
ELSEIF department = 'Support' THEN
  commission := 30;
ELSE
  commission := 5;
END IF;

Let’s see an example to understand how to use IF-THEN-ELSE Statement:

CREATE OR REPLACE FUNCTION SalaryLevel
   ( emp_id_in IN NUMBER)
   RETURN VARCHAR2
IS
   salary_value NUMBER(6);
   SalLevel VARCHAR2(20);
   CURSOR cur_sal IS
     SELECT salary
     FROM employee
     WHERE employee_id = emp_id_in;
BEGIN
   OPEN cur_sal;
   FETCH cur_sal INTO salary_value;
   CLOSE cur_sal;

   IF salary_value <= 15000 THEN
      SalLevel := 'Low Income';
   ELSIF salary_value > 15000 AND salary_value <= 30000 THEN
      SalLevel := 'Average Income';
   ELSIF salary_value > 30000 AND salary_value <= 45000 THEN
      SalLevel := 'Moderate Income';
   ELSE
      SalLevel := 'High Income';
   END IF;

   RETURN SalLevel;
END;