CASE Expressions

CASE Expressions:

• It is similar to a switch statement in C.
• When a particular search condition evaluates to TRUE, the group of statements associated with this condition are executed.
• We can evaluate conditions using the normal conditional operators or SQL*Plus operators.

CASE expression Syntax:
CASE test_var
    WHEN value_1 THEN
        Sequence_of_Statements1;
    WHEN value_2 THEN
        Sequence_of_Statements_2;
……..
    WHEN value_n THEN
        Sequence_of_Statements_n;
[ELSE
        else_sequence;]
END CASE;

• The reserved word “CASE” marks the beginning of the CASE statement.
• The selector is a value that determines, which “WHEN” clause should be executed.
• Each when clause contains as expression and or more executable statements associated with it.
• The “ELSE” clause is optional.
• Each “CASE” statement is marked with “END CASE”.
• Where test_var is the variable or expression to be tested, value_1 through value_n are the comparison values.
• If none of the values are equal, then else_sequence will be executed.

SQL>DECLARE
    v_Dname varchar2(20);
        v_Deptno number;
    BEGIN
        SELECT deptno into v_Deptno
        FROM emp
        WHERE empno=&eno;
        CASE v_Deptno
        WHERE 10 THEN
        v_Dname:=‘ACCOUNTING’;
        WHERE 20 THEN
        v_Dname:=‘RESEARCH’;
        WHERE 30 THEN
        v_Dname:=‘SALES’;
WHERE 40 THEN
        v_Dname:=‘OPERATIONS’;
        ELSE
        v_Dname:=‘UNKNOW’;
        END CASE;
        DBMS_OUTPUT.PUT_LINE(‘Emp dept name is:’||v_Dname);
    END;