Oracle PL/SQL: CASE Expression vs. CASE Statement

Conditional Control: CASE Expression vs. CASE Statement

Preserved & Corrected Notes

• CASE Expression: Evaluates conditional logic and returns a single scalar value.
• CASE Statement: Evaluates conditional logic to execute procedural actions/statements.
• Structure & Block Inclusion: A CASE statement can form a complete execution control path within a PL/SQL block.
• Termination Syntax:
  • CASE Statements end with END CASE;
  • CASE Expressions end with END

Production Code Examples

1. CASE Expression (Used inside SQL or PL/SQL Assignment)

DECLARE
   v_job_id  VARCHAR2(10) := 'IT_PROG';
   v_bonus   NUMBER;
BEGIN
   -- CASE Expression returns a value assigned directly to v_bonus
   v_bonus := CASE v_job_id
                 WHEN 'IT_PROG'  THEN 1000
                 WHEN 'SA_REP'   THEN 1500
                 ELSE 500
              END;

   DBMS_OUTPUT.PUT_LINE('Bonus: ' || v_bonus);
END;
/

2. CASE Statement (Procedural Execution Control)

DECLARE
   v_job_id VARCHAR2(10) := 'IT_PROG';
BEGIN
   -- CASE Statement executes executable statements based on conditions
   CASE v_job_id
      WHEN 'IT_PROG' THEN
         DBMS_OUTPUT.PUT_LINE('Department: Information Technology');
         -- Multiple PL/SQL statements allowed here
      WHEN 'SA_REP' THEN
         DBMS_OUTPUT.PUT_LINE('Department: Sales');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Department: General Support');
   END CASE; -- Terminates with END CASE;
END;
/

Interview Notes & Senior Architect Additions

[!IMPORTANT]
Unhandled Cases Trap (CASE_NOT_FOUND):
In a PL/SQL CASE Statement, if no WHEN clause condition matches and no default ELSE clause is specified, Oracle raises a runtime exception: ORA-06592: CASE not found while executing CASE statement.
In a CASE Expression, if no condition matches and no ELSE is specified, it silently evaluates to NULL.