Skip to content

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