![totn Oracle / PLSQL](/static/a2cZm/static/picture/oracle_plsql.png?d=6ee31febd&m=a2cZm)
Oracle / PLSQL: IF-THEN-ELSE Statement
This Oracle tutorial explains how to use the IF-THEN-ELSE statement in Oracle with syntax and examples.
Description
In Oracle, the IF-THEN-ELSE statement is used to execute code when a condition is TRUE, or execute different code if the condition 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 only when condition 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 different set of statements when condition 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 want to execute one set of statements when condition1 is TRUE or a different 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 different set of statements when condition2 is TRUE, or a different set of statements when all previous conditions (ie: condition1 and condition2) are FALSE.
Note
- Once a condition is found to be TRUE, the IF-THEN-ELSE statement will execute the corresponding code and not evaluate the conditions any further.
- If no condition is met, the ELSE portion of the IF-THEN-ELSE statement will be executed.
- It is important to note that the ELSIF and ELSE portions are optional.
Example
The following is example using the IF-THEN-ELSE statement in an Oracle function:
CREATE OR REPLACE Function IncomeLevel ( name_in IN varchar2 ) RETURN varchar2IS 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 statement 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.
Advertisement