oracle中case 我很,oracle中CASE 的用法（摘录）

In ANSI SQL 1999, there are four types of CASE statements:

Simple

Searched

NULLIF

COALESCE

Previous to Oracle9i, simple case statements were already supported. In Oracle9i, support for the remaining types of case statements is provided.

Simple CASE Statements

Simple case statements are much like the decode statement. They can be used to search and then replace a given value within a given SQL Statement. Here is an example:

SELECT ename,

(CASE deptno

WHEN 10 THEN 'ACCOUNTING'

WHEN 20 THEN 'RESEARCH'

WHEN 30 THEN 'SALES'

WHEN 40 THEN 'OPERATIONS'

ELSE 'Unassigned'

END ) as Department

FROM emp;

ENAME      DEPARTMENT

---------- ----------

SMITH      RESEARCH

ALLEN      Unassigned

WARD       SALES

JONES      RESEARCH

MARTIN     SALES

BLAKE      SALES

CLARK      ACCOUNTING

SCOTT      RESEARCH

KING       ACCOUNTING

TURNER     SALES

ADAMS      RESEARCH

JAMES      SALES

FORD       RESEARCH

MILLER     ACCOUNTING

In this example, if the deptno column has a 10 in it, the SQL query will return the value accounting rather than the number 10. If the deptno is not 10, 20, 30, or 40, then the CASE statement will fall through to the ELSE clause, which will return unassigned. Note that with a simple CASE statement, no comparison operators can be used.

Searched CASE Statements

The searched case statement is the much more powerful cousin of the simple case statement. The searched case statement is like an if…then…else structure, and can be used to conditionally search and replace values using logical operators and multiple conditions. Let's look at an example:

SELECT ename, sal, deptno,

CASE

WHEN sal <= 500 then 0

WHEN sal > 500 and sal<1500  then 100

WHEN sal >= 1500 and sal < 2500  and deptno=10 then 200

WHEN sal > 1500  and sal < 2500 and deptno=20 then 500

WHEN sal >= 2500 then 300

ELSE 0

END "bonus"

FROM emp;

ENAME             SAL     DEPTNO      bonus

---------- ---------- ---------- ----------

SMITH             800         20        100

ALLEN            1600         90          0

WARD             1250         30        100

JONES            2975         20        300

MARTIN           1250         30        100

BLAKE            2850         30        300

CLARK            2450         10        200

In this example, you are trying to determine how much of a bonus each employee is eligible for. The bonus amount is based on the salary of the employee, but notice that some conditions have been added based on what department number the employee is in. You can see that a searched case statement can have many different when clauses, and that you can apply many criteria in those clauses to get the answers you need.

NULLIF and COALESCE

To further comply with SQL 1999, the NULLIF and COALESCE statements have been added to Oracle9i. The NULLIF statement is very simple. It takes two arguments. If they are equivalent, then the result is a NULL. If they are not equivalent, then the first argument is returned by the function. Here is an example of a NULLIF statement:

SELECT ename, NULLIF (comm, 0) COMM FROM emp;

ENAME            COMM

----------       ----------

SMITH

ALLEN             300

WARD              500

JONES

MARTIN           1400

BLAKE

CLARK

SCOTT

In this example, if the comm column (which is the commision for an employee) has a 0 value, it will be returned as a NULL as shown in the sample output.

The coalesce statement is a bit like the Oracle NVL function. Given an unlimited number of arguments, it will return the first non-null value in those arguments. Here is an example:

SELECT ename, COALESCE(comm, 0) COMM FROM emp;

ENAME            COMM

----------       ----------

SMITH               0

ALLEN             300

WARD              500

JONES               0

MARTIN           1400

BLAKE               0

CLARK               0

SCOTT               0

In this case, if the comm column is NULL, a 0 value will be returned. Note that with coalesce, there is no implicit type conversion of the arguments passed to it, so the following code would not work:

SELECT ename, COALESCE(comm, 'None') FROM emp;

The following code, however, would work:

SELECT ename, COALESCE(to_char(comm), 'None') COMM FROM emp;

ENAME            COMM

----------       ----------

SMITH            None

ALLEN            300

WARD             500

JONES            None

MARTIN           1400

BLAKE            None

CLARK            None

SCOTT            None

(3)SCALAR SUBQUERIES

A scalar subquery expression is a subquery that returns exactly one column value from one row. The returned value of the scalar subquery expression is the return value of the selected list item of the subquery. If zero rows are returned by the subquery, then the value of the scalar subquery expression is NULL, and if the subquery returns more than one row, then Oracle returns an error.

Limited scalar subqueries were allowed in Oracle8i. Oracle9i allows more. Be careful when using scalar subqueries though. They tend to be resource intensive. There are often more efficient ways of getting at the data you are interested in than using a scalar subquery.

Let's look at some of the scalar subqueries possible in Oracle9i. First, here is an example of a scalar subquery used in the select clause of a SQL statement:

SELECT empno,

(SELECT ename FROM emp b WHERE b.empno=a.mgr) manager

FROM emp a

ORDER BY mgr;

EMPNO MANAGER

---------- ----------

7788 JONES

7902 JONES

7499 BLAKE

7521 BLAKE

7839

In this example, a join between a table called EMP and itself is being created to display the name of the employees' managers. A regular join in this case would probably be more efficient. Here is another example, a scalar subquery in the where clause:

SELECT ename, sal, comm

FROM emp a

WHERE ( (SELECT comm FROM bonus z where

z.empno=a.empno) >

(SELECT AVG(bonus) from historical_bonus WHERE year = 1999 ) );

ENAME             SAL       COMM

---------- ---------- ----------

FORD             3000        600

MILLER           1300        600

This example prints the employee name and salary for all employees who are getting bonuses that are larger than the average of all 1999 bonuses. Again, a join here would probably be much more efficient. A scalar subquery can also be used in an order by clause, as shown in this example:

SELECT empno, ename, deptno

FROM emp a

ORDER BY (SELECT dname FROM DEPT b where a.deptno=b.deptno);

EMPNO ENAME          DEPTNO

---------- ---------- ----------

7782 CLARK              10

7839 KING               10

7934 MILLER             10

7369 SMITH              20

7876 ADAMS              20

7902 FORD               20

7788 SCOTT              20

7566 JONES              20

7521 WARD               30

7698 BLAKE              30

7654 MARTIN             30

In this case, the output was ordered by department name, a column that is not readily available in the EMP table, and not even one displayed in the query.

Note that scalar subqueries are still not valid in Oracle9i in the following cases:

As default values for columns

As hash expressions for clusters

In the returning clause of DML statements

In function-based indexes

In check constraints

In when conditions of case expressions

In group by and having clauses

In start with and connect by clauses

In statements that are unrelated to queries, such as create profile

原文URL：http://www.cnblogs.com/ballpenxp/archive/2007/07/20/824824.html