Oracle SQL 강좌
목록
DECODE와 CASE 19 21 99,999+

by 구루비 CASE DECODE [2002.01.20]

DECODE와 CASE 함수는 SQL 문장에서 조건에 해당하는 값을 추출하고자 할 때 주로 사용한다

DECODE

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 부서번호가 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 함수에서 집계 함수를 사용한 예제이다

?
1
2
3
4
5
6
7
8
9
10
11
12
-- 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

DECODE함수는 집계함수와 함께 통계 데이터를 추출할 때 많이 사용한다. 아래는 부서별로 급여합계를 조회하는 예이다

?
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 부서별로 급여 합계를 출력한다.
SELECT deptno, NVL(SUM(DECODE(deptno, 10, sal)),0) deptno10,
               NVL(SUM(DECODE(deptno, 20, sal)),0) deptno20,
               NVL(SUM(DECODE(deptno, 30, sal)),0) deptno30,
               NVL(SUM(DECODE(deptno, 40, sal)),0) deptno40
  FROM emp
 GROUP BY deptno;
 
DEPTNO   DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
------- --------- --------- ---------- ----------
     30         0         0       9400          0
     20         0     10875          0          0
     10      8750         0          0          0

아래 부서별 급여합계 예를 보면 일반적인 집계함수를 사용할 때는 급여 합계가 행으로 조회가 되지만, DECODE와 MAX함수를 사용하면 열로 값을 표시할 수 있다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 부서별로 급여 합계를 행으로 출력한다.
SELECT d.deptno, NVL(SUM(e.sal),0) sal
  FROM emp e, dept d
 WHERE e.deptno(+) = d.deptno
 GROUP BY d.deptno;
 
DEPTNO        SAL
-------- ----------
      10       8750
      20      10875
      30       9400
      40          0
 
 
 
-- 부서별로 급여 합계를  열로 출력한다.
SELECT MAX(NVL(SUM(DECODE(deptno, 10, sal)),0)) deptno10,
       MAX(NVL(SUM(DECODE(deptno, 20, sal)),0)) deptno20,
       MAX(NVL(SUM(DECODE(deptno, 30, sal)),0)) deptno30,
       MAX(NVL(SUM(DECODE(deptno, 40, sal)),0)) deptno40
  FROM emp
 GROUP BY deptno;
 
DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40
--------- ---------- ---------- ----------
    8750      10875       9400          0

CASE

  • - CASE 함수는 DECODE함수가 제공하지 못하는 비교연산의 단점을 해결할 수 있는 함수이다.
  • - DECODE함수에서 비교연산을 수행하기 위해서는 GREATEST, LEAST등의 함수를 사용해야 하지만, CASE함수에서는 조건 연산자를 모두 사용 할 수 있다.
  • - CASE함수는 IF.. THEN .. ELSE 구문과 비슷 하다. WHEN절 다음에 여러 조건이 올 수 있다.

위의 DECODE예제를 CASE함수로 변환한 예이다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--위의  DECODE예제를 CASE함수로 변환한 예이다.
SELECT deptno,
       CASE deptno
         WHEN 10 THEN 'ACCOUNTING'
         WHEN 20 THEN 'RESEARCH'
         WHEN 30 THEN 'SALES'
         ELSE 'OPERATIONS'
       END as "Dept Name"
  FROM dept;
 
DEPTNO Dept Name
------- ----------
     10 ACCOUNTING
     20 RESEARCH
     30 SALES
     40 OPERATIONS

아래는 WHEN절 다음에 연산자가 오는 예제이다.

?
1
2
3
4
5
6
7
8
9
--급여별로 인상율을 다르게 계산하였다.
SELECT ename ,
       CASE
          WHEN sal < 1000  THEN sal+(sal*0.8)
          WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)
          WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)
          ELSE sal+(sal*0.1)
       END sal
  FROM emp;

참고링크

- 강좌 URL : http://www.gurubee.net/lecture/1028

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
by LinDol [2005.10.26 12:05:12]
잘봤습니다 :)
by smile [2005.11.09 20:15:07]
퍼갑니다. ^^
by 마농 [2006.03.07 10:16:13]
편리한 함수들이 많이 생겼네요. ^^
참고로 8.1.7에서 CASE (1)번 예시는 안되고 (2)번만 되네요.
by 알자 [2006.08.11 09:49:58]
CASE 1번은 emp테이블이 아니구 dept테이블이 아닐까 생각되는데요 아닌가요?
by 왕초보 [2006.11.17 14:28:50]
안녕하세요... Oracle 왕초보입니다.
CASE 2번 예제 결과물이 어떻게 나오나요?
전 자꾸 에러 나오는데...
고수님들 위 예문에서 에러 있으면 점 수정해주세요...
by 황병준 [2006.12.14 15:30:57]
1번 CASE문 SIMPLE는 안되는데~
by 쭈 [2007.03.09 13:12:57]
1번 case 잘돌아 갑니다. 빠트린거 잇으신지 확인해주세요~
by 시작이다 [2007.04.17 16:00:12]
안되시는분들 이렇게 하면 대는데
SELECT deptno
, CASE WHEN deptno = 10 THEN 'ACCOUNTING'
WHEN deptno = 20 THEN 'RESEARCH'
WHEN deptno = 30 THEN 'SALES'
ELSE 'OPERATIONS' END as Dept_Name
FROM emp
by 어린양 [2007.08.17 13:44:19]
오라9i 클라쓰고있습니다..case문 저렇게 하면안되던데요
전 시작이다님 방식으로 해야 되더라구요

select pay_type_cd, case
when pay_type_cd = 1 then '1이다'
when pay_type_cd = 2 then '2이다'
when pay_type_cd = 3 then '3이다'
else '4이상이다'
end as test
from ordermst;
by 샬 [2007.10.09 18:43:11]
오라클9i 쓰고있습니다.
PL/SQL 에서, 시작이다님 방식이든 예문이든 다 잘됩니다.
by 꽝아 [2008.11.26 11:36:51]
잘봤습니다..~ 감사합니다.~
by 갯바위 [2010.01.20 23:47:38]
decode 함수 매우 유용하게 쓰입니다.
감사합니다.
by 지나가는사람 [2010.05.03 10:44:18]
저도 오라클 9i쓰는데요.. case문 저렇게 하면안된다고 하시는데 그건 데이터유형이 서로 다른경우가 아닌가 생각됩니다. deptno를 number로 만드신 분이라면 위에 simple을 그대로 쓰시면 될것이고요 varchar로 만드신분은 WHEN 10 THEN 'ACCOUNTING' 이부분의 숫자를 문자타입으로 변환해주셔야합니다. '10' 이런식으로요.. ^^;;
by mjc [2011.04.26 16:17:03]
아자! 아자! 아자!
by 송죽 [2011.08.09 17:54:35]
꼭 필요한 것만 정리 좋네요. 감사
by 박맨재 [2012.01.25 17:05:54]
ㅇㄴㄹㄴㅇㄹ
by 돌이맹이 [2012.02.28 13:39:34]
잘 봤스비낟.
by 진 [2012.09.25 12:15:58]

잘 봤습니다~

by 박군 [2013.12.27 09:07:28]

1주행

by 헌헌 [2018.01.04 10:34:38]

잘봤습니다.

DECODE가 CASE에 비해 깔끔하게 작성됨으로 보이네요 ㅎ

by 또니 [2018.05.09 15:14:15]

잘 봤습니다!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입