ORACLE CASE 사용법

SQL은 CASE문장의 네가지 유형을 지원합니다.

1. 간단한 CASE 표현식
2. 검색 CASE표현식
3. NULLIF
4. COALESCE

이중에서 간단한 CASE표현식은 Oracle 8.1.7 에서 제공되었으며, 나머지 기능들은  Oracle 9i에 포함됩니다. 또한 CASE표현식은 PL/SQL에서도 지원(9i 이후부터)되며, 표현식과 문장으로 분류됩니다.

간단한 CASE 표현식
------------------------------------------------------------------
이것은 Oracle 9i 이전의 서버에서 제공하던 DECODE 문장과 유사하며 주어진 표현식에서 검색을 하거나 값을 치환하는데 사용될 수 있습니다. 또 각각의 검색 값에 대해서 반환되는 값을 명시할 수 있으나, 비교 연산자는 허용되지 않습니다.

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 표현식
-----------------------------------------------------------------
검색 CASE 표현식은 IF...THEN ELSE 구조와 유사하며, 표현식 내에서 조건에 따른 검색과  값들의 치환을 하는데 사용될 수 있다. 각 WHEN 조건은 논리적 연산자(AND, OR등)와 결합되어 사용될 수 있으며, 조건 표현식에서 비교 연산자를 사용할 수 있다. 따라서, 간단한 CASE 표현식보다는 유연하다고 할 수 있다. 예를 들어 EMP 테이블에서 Sal 컬럼의 범위에 따른 영역을 표현하는 SQL문과 결과는 다음과 같다.

SQL> SELECT empno, ename,
   2     (CASE
   3       WHEN sal >= 5000 THEN 'High Sal'
   4       WHEN sal >= 3000 AND sal <= 5000 THEN 'Middle Sal'
   5       WHEN sal >= 1000 AND sal <  3000 THEN 'Average Sal'
   6       WHEN sal <   1000 THEN 'Low Sal'
   7       END) AS Sal_Category
   8       FROM emp ;

NULLIF 함수
-----------------------------------------------------------------
NULLIF 함수의 경우 첫번째 입력인자와 두번째 입력인자가 같은 경우에는 NULL을 반환하고, 그렇지 않을 경우에는 첫번째 입력인자의 값을 반환한다. 예를 들어 EMP 테이블의 사원중에 Sal 컬럼의 값이 5000인 사람에 대해 NULLIF함수를 적용하는 SQL문과 결과는 다음과 같다.

SQL> SELECT empno, sal
   2         FROM emp
   3      WHERE NULLIF(sal, 5000) IS NULL ;

만약 NULLIF 함수를 CASE표현식으로 나타낸다면 다음과 같다.

CASE
       WHEN expression1 = expression2 THEN NULL
        ELSE expression1
END ;

COALESCE 함수
-----------------------------------------------------------------
COALESCE 함수는 Oracle의 NVL함수를 일반화한 것이다. 즉, 첫번째 입력 인자가 null이 아닌 경우에는 첫번째 입력인자를 반환하고, 아닐 경우에는 두번재 표현식에 대해서 평가를 계속하게 된다. 예를 들어, EMP 테이블에서 COMM 컬럼의 값이 널인지 검사하는 SQL문은 다음과 같다.

SQL> SELECT ename, COALESCE(comm, 'NULL')
   2         FROM emp ;

NULLIF함수와 유사하게 CASE표현식으로 나타내면 다음과 같다.

CASE
        WHEN expression1 IS NOT NULL THEN expression1
         ELSE expression2
END ;

또한, NVL함수에 비해 COALESCE함수의 장점은 여러 대체 값을 취할 수 있다는 것이다. 예를 들어, 첫번째 입력 인자가 null일 경우에는 나머지 표현식에 대해서 COALESCE를 계속 적용하는 것이 가능하다.

[출처] [펌] ORACLE CASE 사용법|작성자 제이피