Oracle IF-THEN-ELSE operator.

Preamble

In Oracle, the IF-THEN-ELSE operator is used to execute code when the condition is TRUE (true), or execute another code when the condition is FALSE (false).

Syntax (IF-THEN)

IF THE CONDITION THEN
{...is executed when the TRUE is true...}
END IF;

Use IF-THEN syntax if you want to execute operators only when the condition is true.

Syntax (IF-THEN-ELSE)

IF THE CONDITION THEN
{...is executed when the TRUE is true...}
ELSE
{...is performed when the FALSE is false...}
END IF;

If you want to execute one set of sentences when the condition is true, use IF-THEN-ELSE syntax, or another set of sentences if the condition is false.

Syntax (IF-THEN-ELSIF)

IF CONDITION1 THEN
{...is performed when CONDITION 1 is true (TRUE)...}
ELSIF CONDITION2 THEN
{...is performed when CONDITION2 is true (TRUE)...}

ELSE
{...is executed when both: NOTICE1 and NOTICE2 are false (FALSE)...}
END IF;

• Use IF-THEN-ELSIF-ELSE syntax if you want to execute one set of sentences when NOTE1 is true (TRUE);
• another set of sentences when NOTE2 is true (TRUE); or a third set of sentences when all previous conditions (i.e.: NOTE1 and NOTE2) are false (FALSE).

Note:

1. After the condition is true (TRUE), the IF-Then-Else operator will execute the declared code and will not evaluate the condition further.
2. If the condition is false (FALSE), the IF-Then-Else operator’s ELSE part is executed.
3. It is important to note that the ELSIF and ELSE parts are additional.

Below is an example of using the IF-Then-Else operator in the Oracle function body:

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 of IF-THEN-ELSE operator, we created IncomeLevel function. It has one parameter name_in and returns varchar2. The function will return ILevel (income level) based on the employee’s name.

PL/SQL tutorial: IF THEN ELSE (IF-ELSE) Statement in Oracle

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.