Oracle Case

종료일과 현재일을 비교하여 완료된 상태인지..지연 상태인지 확인할 수 있는 쿼리가 필요 했다.
찾아보니.. CASE 라는 넘이 있었네...

RPG게임을 진행한다고 치자...
플레이어에게 일정 기간내게 수행해야할 미션을 주고 플레이어 상태를 본다고 가정해보자. 
미션에 긴박감을 주기 위해 종료일이 긴박했을 경우 알림창을 띄우도록 하자.

1. 다음 테이블의 구조를 가지고 있으며
     LEVEL_PROGRESS
          |_  user_id              :  플레이어 ID  
          |_  part                   : 레벨명
          |_  end_date            : 레벨업을 위한 완료일 ( 해당 기간내에 미션을 클리어 해야함 ) 
          |_  complete_date    : 레벨 종료 여부 ( 종료일을 기록 )

2. 다음 데이터를 가지고 있다.
    userId,  'ONE', date, date     - ONE 단계를 완료 했다.
    userId,  'TWO, date,             - TWO 단계를 진행중이다.

3. 쿼리로 현재일과 종료일을 비교하여 현 상태를 나타내고자 한다.

SELECT end_date,complete_date
    DECODE(part,'ONE','LEVEL1','TWO','LEVEL2') levelName,
    DECODE(part,'ONE',DECODE(complete_date,null,
                 (
                 CASE
                    WHEN to_char(sysdate,'YYYY-MM-DD') <= to_char(end_date,'YYYY-MM-DD') THEN '진행중'
                    ELSE '지연'
                 END ),
                 '레벨업완료')) STEP1,
    DECODE(part,'TWO',DECODE(complete_date,null,
                 (
                 CASE
                    WHEN to_char(sysdate,'YYYY-MM-DD') <= to_char(end_date,'YYYY-MM-DD') THEN '진행중'
                    ELSE '지연'
                 END ),
                 '레벨업완료')) STEP2                             
FROM  LEVEL_PROGRESS WHERE user_id= 'userId'

각 레벨별 현황을 보여주기 위한 쿼리가 완성되었다.
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를 계속 적용하는 것이 가능하다.