ORACLE中CASE的用法

CASE運算式可以在SQL中實現if-then-else型的邏輯，而不必使用PL/SQL。CASE的工作方式與DECODE()類似，但應該使用CASE,因為它與ANSI相容。
CASE有兩種運算式:

1. 簡單CASE運算式,使用運算式確定傳回值.

文法:

CASE search_expression WHEN expression1 THEN result1 WHEN expression2 THEN result2 ... WHEN expressionN THEN resultN ELSE default_result END

例:

select product_id,product_type_id, case product_type_id when 1 then 'Book' when 2 then 'Video' when 3 then 'DVD' when 4 then 'CD' else 'Magazine' end from products

結果:

PRODUCT_ID PRODUCT_TYPE_ID CASEPROD
---------- --------------- --------
         1               1                             Book
         2               1                             Book
         3               2                              Video
         4               2                             Video
         5               2                             Video
         6               2                            Video
         7               3                             DVD
         8               3                             DVD
         9               4                              CD
        10               4                               CD
        11               4                             CD
        12                                            Magazine

12 rows selected.

2. 搜尋CASE運算式,使用條件確定傳回值.

文法:

CASE WHEN condition1 THEN result1 WHEN condistion2 THEN result2 ... WHEN condistionN THEN resultN ELSE default_result END

例:

select product_id,product_type_id,
case
when product_type_id=1 then 'Book'
when product_type_id=2 then 'Video'
when product_type_id=3 then 'DVD'
when product_type_id=4 then 'CD'
else 'Magazine'
end
from products

結果與上相同.

＋——————————————————————————————————————————————————

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 # 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