Oracle9i에서 부터 지원하는 CASE의 간단한 예입니다.

Oracle Magazine에서 참조하였습니다...

EMP 테이블에서 각 사원의 고용일에 대해 근속년수를 구하고 이를 화면에 출력하는

SQL문과 출력 결과는 다음과 같다.

SQL> SELECT ename,

2 (CASE EXTRACT(YEAR FROM hiredate)

3 WHEN 1982 THEN '3 years service'

4 WHEN 1981 THEN '4 years service'

5 WHEN 1980 THEN '5 years service'

6 END) AS "Award for 2000"

7 FROM emp

8 WHERE EXTRACT(YEAR FROM hiredate) IN (1982,1981,1980)

9 ORDER BY hiredate;

위의 간단한 CASE 표현식에 대한 출력 결과는 다음과 같다.

ENAME Award for 2000

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

SMITH 5 years service

ALLEN 4 years service

WARD 4 years service

JONES 4 years service

BLAKE 4 years service

CLARK 4 years service

TURNER 4 years service

MARTIN 4 years service

KING 4 years service

JAMES 4 years service

FORD 4 years service

MILLER 3 years service

12 rows selected.

SQL> SELECT empno, ename,(CASE

2 WHEN sal >= 5000 THEN 'High Sal'

3 WHEN sal >= 3000 AND sal < 5000 THEN 'Middle Sal'

4 WHEN sal >= 1000 AND sal < 3000 THEN 'Average Sal'

5 WHEN sal < 1000 THEN 'Low Sal'

6 END) AS Sal_Category

7 FROM emp;

EMPNO ENAME SAL_CATEGORY

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

7369 SMITH Low Sal

7499 ALLEN Average Sal

7521 WARD Average Sal

7566 JONES Average Sal

7654 MARTIN Average Sal

7698 BLAKE Average Sal

7782 CLARK Average Sal

7788 SCOTT Middle Sal

7839 KING High Sal

7844 TURNER Average Sal

7876 ADAMS Average Sal

7900 JAMES Low Sal

7902 FORD Middle Sal

7934 MILLER Average Sal