One of the most powerful SQL features is often unknown - CASE Expressions in SQL Queries. An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. An expression generally assumes the datatype of its components.
CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures. The syntax is:
Oracle searches for the first WHEN ... THEN pair for which condition is true.
If Oracle finds such a pair, then the result of the CASE expression is expr1.
If Oracle does not find such a pair,
If an ELSE clause is specified, the result of the CASE expression is expr2.
If no ELSE clause is specified, the result of the CASE expression in null.
At least one occurrence of expr1 or expr2 must be non-null.
The maximum number of arguments in a CASE expression is 255, and each WHEN ... THEN pair counts as two arguments. To avoid exceeding the limit of 128 choices, you can nest CASE expressions. That is expr1 can itself be a CASE expression.
The following statement finds the average salary of all employees in the EMP table. If an employee's salary is less than $2000, the CASE expression uses $2000 instead.
SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal ELSE 2000 END)
from emp e;
More complex Example with a Subquery in the CASE Expression
CREATE OR REPLACE VIEW v_user AS
i.id_item rootid, (
CASE WHEN EXISTS (
WHERE accesstype = 2
AND id_user = u.id_item )
THEN 1 ELSE 0 END
) AS userstate
FROM user u, cug c, item i
WHERE c.id_item = u.id_item
AND i.id_cug (+) = c.id_item
AND i.id_parent (+) = 0
AND u.id_item > 0
ORDER BY u.id_item;
In this example, we use a subquery in the CASE expression block. If the subquery finds an entry the CASE expression evaluates to 1, else to 0 which is stored in userstate.
We think, that such constructs can be very powerful - without to programming PL/SQL.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60588/，如需转载，请注明出处，否则将追究法律责任。