Starting in
Oracle 9i, you can use the case statement within an SQL statement. It has the
functionality of an IF-THEN-ELSE statement.
Syntax:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
expression is optional. It is the
value that you are comparing to the list of conditions. (ie: condition_1,
condition_2, ... condition_n)
condition_1 to condition_n must
all be the same datatype. Conditions are evaluated in the order listed. Once a
condition is found to be true, the case statement will return the result and
not evaluate the conditions any further.
result_1 to result_n must all be
the same datatype. This is the value returned once a condition is found to be
true.
Note:
If no condition is found to be
true, then the case statement will return the value in the ELSE clause.
If the ELSE clause is omitted and
no condition is found to be true, then the case statement will return NULL.
You can have up to 255
comparisons in a case statement. Each WHEN ... THEN clause is considered 2
comparisons.
Applies To:
Oracle 9i, Oracle 10g, Oracle 11g
Example:
You could use
the case statement in an SQL statement as follows: (includes the expression
clause)
select
table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'
END
from
all_tables;
Or you could write the SQL
statement using the case statement like this: (omits the expression clause)
select
table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is
SYSTEM'
ELSE 'The owner is another value'
END
from
all_tables;
The above two case statements are
equivalent to the following IF-THEN-ELSE statement:
IF owner =
'SYS' THEN
result := 'The owner is SYS';
ELSIF owner =
'SYSTEM' THEN
result := 'The owner is SYSTEM'';
ELSE
result := 'The owner is another value';
END IF;
The case statement will compare
each owner value, one by one.
One thing to note is that the ELSE
clause within the case statement is optional. You could have omitted it. Let's
take a look at the SQL statement above with the ELSE clause omitted.
Your SQL statement would look as
follows:
select
table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
from
all_tables;
With the ELSE clause omitted, if
no condition was found to be true, the case statement would return NULL.
Example:
Here is an example that
demonstrates how to use the case statement to compare different conditions:
select
CASE
WHEN a < b THEN 'hello'
WHEN d < e THEN 'goodbye'
END
from suppliers;