Case statement in SQL and PL/SQL

The following as examples using SIMPLE and SEARCHED CASE statement in pl/sql.
Simple CASE:

text := case n
when 1 then one
when 2 then two

when 3 then three
else other
end case;


Searched CASE:


text := case
when n = 1 then one

when n = 2 then two
when n = 3 then three
when ( n > 3 and n <>
else other
end;


Exception handling:

...
case
when p = 1 then Action1;
when r = 2 then Action2;
when q > 1 then Action3;
end case;
exception
when case_not_found
...



In SQL, you can also have SIMPLE and SEARCHED case.

SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM employees ORDER BY last_name;



SELECT last_name, job_id, salary,
(CASE
WHEN job_id LIKE 'SA_MAN' AND salary <>
WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
WHEN job_id LIKE 'IT_PROG' AND salary <>
WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
ELSE 'NOT APPLICABLE'
END ) Raise
FROM employees;