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;