PL/SQL CASE Statements 🧠

Mentor's Note: Think of CASE as a more organized version of IF-ELSIF. When you have many choices based on a single value, CASE makes your code much easier to read and faster to maintain. 💡

---

🌟 The Scenario: The Gift Voucher 🎁

Imagine a rewards program.

• Goal: Give a discount based on membership level.
• Logic:
  • 'GOLD' gets 20%.
  • 'SILVER' gets 10%.
  • 'BRONZE' gets 5%.
  • Everyone else gets 0%.

---

💻 1. Simple CASE Statement

Best for checking a single variable against a list of specific values.

DECLARE
   v_level VARCHAR2(10) := 'GOLD';
   v_discount NUMBER;
BEGIN
   CASE v_level
      WHEN 'GOLD'   THEN v_discount := 0.20;
      WHEN 'SILVER' THEN v_discount := 0.10;
      WHEN 'BRONZE' THEN v_discount := 0.05;
      ELSE v_discount := 0;
   END CASE;
   
   DBMS_OUTPUT.PUT_LINE('Discount: ' || (v_discount * 100) || '%');
END;

---

💻 2. Searched CASE Statement

More powerful. It allows you to use ranges and complex logic in each branch.

DECLARE
   v_marks NUMBER := 75;
   v_grade CHAR(1);
BEGIN
   CASE
      WHEN v_marks >= 80 THEN v_grade := 'A';
      WHEN v_marks >= 60 THEN v_grade := 'B';
      WHEN v_marks >= 40 THEN v_grade := 'C';
      ELSE v_grade := 'F';
   END CASE;
   
   DBMS_OUTPUT.PUT_LINE('Final Grade: ' || v_grade);
END;

---

🛡️ 3. CASE vs. IF (Architect's Note)

1. Readability: Use CASE when you are checking one variable for many values. Use IF when your logic involves multiple different variables or boolean tests.
2. Expression vs Statement: In PL/SQL, CASE can be used as a Statement (to perform actions) or an Expression (to return a value directly).

   -- Case as an Expression
   v_discount := CASE v_level 
                    WHEN 'GOLD' THEN 0.20 
                    ELSE 0 
                 END;

---

📈 Learning Path

• ← Back: IF Statements
• Next: Basic LOOP →