ITPub博客

首页 > Linux操作系统 > Linux操作系统 > The Power of CASE Expressions in SQL Queries[akadia]

The Power of CASE Expressions in SQL Queries[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-05-27 17:09:04 0 删除 编辑

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

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures. The syntax is:

CASE_expression ::=

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.

Note:

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.

Simple Example

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
SELECT u.id_item,
u.id_type,
u.createdate,
u.llogdate,
u.clogdate,
u.account,
u.fname,
u.lname,
c.lockstate,
i.id_item rootid, (
CASE WHEN EXISTS (
SELECT id_user
FROM participant
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    143
  • 访问量
    106822