IF-THEN-ELSIF Statement in Oracle PLSQL with Examples

Welcome Back to Javainhand Tutorial! In our previous tutorial, we covered the basics of IF-THEN-ELSE statement. Today We are going to explore a more powerful condition control structure: the IF-THEN-ELSIF Statement.

When you need to choose between more than two alternatives based on different conditions, the ELSIF ladder is the most efficient way to write your logic in PLSQL. Let's dive deep into it.

What is the IF-THEN-ELSIF Statement?

The IF-THEN-ELSIF statement allows you to check multiple conditions sequentially. It works like a decision-making chain.

1. It evaluates the first condition
2. If the first condition is FALSE, it moves to the next ELSIF block.
3. As soon as a condition evaluates to TRUE, the corresponding block of code is executed.
4. Once a match is found, all subsequent conditions are skipped, and control moves to the code after END IF.

Syntax of IF-THEN-ELSIF Statement in PLSQL

The syntax is straightforward. NOTE: In PLSQL, it is spelled ELSIF( without the 'E' in the middle).

IF condition1 THEN
   -- Action when condition1 is TRUE
ELSIF condition2 THEN
   -- Action when condition2 is TRUE
ELSIF condition3 THEN
   -- Action when condition3 is TRUE
ELSE
   -- Default action if NO conditions are TRUE
END IF;

Example of IF-THEN-ELSIF Statement in Oracle PLSQL

Practical Example: Employee Bonus Calculation

Let's take a real-world scenario where we calculate a bonus based on an employee's year of service:

1. More than 10 Years: 20% Bonus
2. More than 5 Years: 15% Bonus
3. More than 2 Years: 10% Bonus
4. Otherwise: 5% Bonus

PLSQL Code Example

DECLARE
   service_years NUMBER := 7;
   bonus_percent NUMBER;
BEGIN
   IF service_years > 10 THEN
      bonus_percent := 20;
   ELSIF service_years > 5 THEN
      bonus_percent := 15;
   ELSIF service_years > 2 THEN
      bonus_percent := 10;
   ELSE
      bonus_percent := 5;
   END IF;

   DBMS_OUTPUT.PUT_LINE('The eligible bonus is: ' || bonus_percent || '%');
END;
/

OUTPUT

The eligible bonus is: 15%

Key Points for Better Coding

1. Spelling Matters: Beginners often make the mistake of writing ELSEIF. Remember, Oracle uses ELSIF.
2. Efficiency: The compiler stops checking conditions as soon as it finds TRUE statement, making it faster than multiple independent IF blocks.
3. The ELSE Block: While the ELSE block as optional, it is recommended to include as a "catch-all" for scenarios that don't meet any specific criteria.

Video Tutorial

For a step-by-step visual guide and live coding demonstration of this topic, check out my YouTube Video.

Watch Video Tutorial Here

Conclusion

The IF-THEN-ELSIF Statement is an essential tool for any Oracle developer to handle complex login clearly and concisely.

I hope this tutorial helped you understand how to implement multiple conditions in your PLSQL blocks.

If you found this helpful, please share it with your friends and fellow learners. Have any questions? Drop then in the comment below! Happy Coding !