[Oracle] CASE와 DECODE문

 

CASE문

자바의 조건문과도 같은 기능을 하는 SQL의 조건문이다.

CASE문은 WHEN... THEN쌍을 검색한다. 

조회해 올 때의 조건은 WHERE 문으로 조건을 걸어 가져올 수 있다.

하지만 가져온 값에 어떤 조건을 걸어 보여주거나 또는 값을 변환할 필요가 있을 경우는 CASE... WHEN... THEN문을 쓴다. 

 

CASE WHEN [조건문] THEN [조건이 맞으면 반환할 값] ELSE [조건이 맞지않으면 반환할 값] END

 

UPDATE 테이블명 SET 값이 대입될 칼럼명 =
CASE 
    WHEN 비교칼럼1 = 비교값1 
        THEN 대입될 값1
    WHEN 비교칼럼2 = 비교값2
        THEN 대입될 값2
    ELSE 값3
END;

 

ELSE 문 같은 경우 선택사항이지만 혹시 모든 조건이 만족하지 않으면(즉 ELSE가 없다면) UPDATE 가 만족되지 않는다.
따라서 모든 조건이 만족하지 않을 경우를 대비하여 디폴트 값인 ELSE를 추가한다.
또, END 문 뒤에 WHERE 절을 추가해서 조건식이 가능하고,
UPDATE  뿐만 아니라 SELECT, INSERT, DELETE에서도 사용이 가능하다.

 

예제

SELECT cus_id,
    CASE WHEN substring(jumin, 7, 1) = '1' THEN '남자'
        WHEN substring(jumin, 7, 1) = '2' THEN '여자'
    ELSE '미등록'
    END AS '성별'
FROM Customer

 

위의 Customer 테이블에서 cus_id 칼럼과 jumin 칼럼을 가져오고 있다.

그런데 jumin 칼럼의 모든 내용을 가져오는 것이 아니라 substring 함수를 이용해서 7번째에 있는 한 개의 문자만 가져오고 있다.

이 가져온 값이 '1'인 경우에는 '남자'를 '2'인 경우에는 '여자'를 출력한다.

해당사항이 없을 경우에는 '미등록'을 출력한다.

 

CASE WHEN 문에는 두 가지 형태의 사용 방법이 있다.

SELECT * FROM table명 WHERE
column명<검색할 컬럼명> CASE WHEN 'A'='B'<비교조건> THEN '2013' <처리조건>
ELSE 'C'<default 조건>
END<반드시 끝에 EMD를 써야한다>

 

/* 1. 특정 Column으로 여러가지 값을 비교 */
SELECT CASE TO_CHAR(SYSDATE, 'YYYY') WHEN '2013' THEN SYSDATE 
    WHEN '2014' THEN SYSDATE + 1
        ELSE NULL END SYS_DATE
FROM DUAL;
 
/* 2. 여러가지 조건을 비교 */
SELECT CASE WHEN TO_CHAR(SYSDATE, 'YYYY') = '2013' 
        AND TO_CHAR(SYSDATE, 'MM') = '10' THEN SYSDATE
        WHEN TO_CHAR(SYSDATE, 'YYYY') = '2014' 
        OR TO_CHAR(SYSDATE, 'MM') = '11' THEN SYSDATE + 1
    ELSE NULL END SYS_DATE 
FROM DUAL;

 

특정 Column으로 여러 가지 값을 비교할 경우 CASE 와 WHEN 사이에 비교하고자 하는 Column 을 넣고 WHEN 과 THEN 사이에 비교하고자 하는 값을 넣어서 비교하는 방법이다.
여러가지 조건을 비교할 때는 CASE와 WHEN 사이는 비우고(칼럼 없음), WHEN과 THEN 사이에 내가 필요한 조건문을 WHERE 절에 넣는 것처럼 넣으면 된다.
2번의 경우에는 하나의 조건뿐만 아니라 여러 개의 조건을 한꺼번에 넣을 수 있는 장점이 있는데 AND와 OR를 사용하면 된다.

 

---

DECODE문

DECODE함수는 어제 포스팅했었던 NVL함수와 마찬가지로 오라클에서만 존재하는 함수이므로 MY_SQL이나 MS_SQL과의 호환을 염두한다면 사용을 피해야 한다.

DECODE 함수는 조건에 따라 데이터를 다른 값이나 칼럼 값으로 추출할 수 있다.
DECODE(VALUE, IF1, THEN1, IF2, THEN2...) 형태로 사용할 수 있다.
VALUE 값이 IF1일 경우에 THEN1 값을 반환하고, VALUE 값이 IF2일 경우에는 THEN2 값을 반환한다.
DECODE 함수 안에 DECODE함수를 중첩으로 사용 할 수 있다.

 

DECODE(컬럼,조건,TRUE 결과값,FALSE 결과값)

 

아래는 DECODE 함수의 일반적인 예제이다.

-- 부서번호가 10이면 ACCOUNTING, 20이면 RESEARCH, 30이면 SALES
-- 나머지는 OPERATIONS를 출력하는 예제
SELECT deptno, DECODE(deptno, 10 , 'ACCOUNTING' ,
                              20 , 'RESEARCH' ,
                              30 , 'SALES', 'OPERATIONS') name
FROM dept;
 
 
DEPTNO NAME
------ ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS

 

아래는 DECODE 함수에서 집계 함수를 사용한 예제이다.

-- 10부서는 급여합계를, 20부서는 최대값을, 30부서는 최소값을 출력하는 예제
SELECT deptno, DECODE(deptno, 10 , SUM(sal),
                              20 , MAX(sal),
                              30 , MIN(sal)) sal
FROM emp
GROUP BY deptno; 
 
 
 
DEPTNO        SAL
--------- --------
       30      950
       20     3000
       10     8750