CASE statement is used to select one out of many set of statements to execute.

Types of CASE statements:

SIMPLE - you can choose which set of statements to execute based on the value returned by an expression.

Example:

CASE gender
WHEN 'M' THEN
Maternity_Leave_Count = 0;
DBMS_OUTPUT.PUT_LINE('no maternity leave');
WHEN 'F' THEN
Maternity_Leave_Count = 5;
DBMS_OUTPUT.PUT_LINE('5 maternity leaves granted');
ELSE
RAISE INVALID_GENDER;
END CASE;

SEARCHED - you can choose which set of statements to execute based on the result of a boolean expression.

Example:

CASE
WHEN AGE>20 AND AGE<=30
THEN
INSURANCE_PREMIUM = 1000;
WHEN AGE>30 AND AGE<=40
THEN
INSURANCE_PREMIUM = 2000;
WHEN AGE>40 AND AGE<=60
THEN
INSURANCE_PREMIUM = 3000;
ELSE
INSURANCE_PREMIUM = 4000;
END CASE;

Quick Tips -
1. PL/SQL uses the following error when no ELSE condition is specified:
ELSE
RAISE CASE_NOT_FOUND;
To avoid getting such error, be sure all your results meet any one of the conditions.
2. In case more than one expression evaluates to TRUE, only the statements associated with the first TRUE expression are executed.
3. A CASE expression gives a single value, and a CASE statement gives the result of execution of a set of statements.
4. SIMPLE CASE is useful when you need to use a single expression for decision making. SEARCHED CASE is useful when you need to use BOOLEAN expressions for decision making.
5. In case no WHEN condition is met, the CASE expression will give NULL.
6. CASE statements can be nested.

Example:

CASE
WHEN AGE<20 THEN
    CASE
    WHEN AGE<10 THEN
    ...
    WHEN AGE>=10 THEN
    ...
    END CASE;
WHEN AGE>=20 THEN
...
END CASE;

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com