데이터베이스

decode 와 case

늘근이 2014. 10. 16. 15:35

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;

태그

문서에 대하여


저작자표시 비영리 변경금지

'데이터베이스' 카테고리의 다른 글

rownum에 대한거 paging  (0) 2014.10.17
sql joins 관련 이미지  (0) 2014.10.16
인덱스에 대한 설명  (0) 2014.10.16
oracle distinct  (0) 2014.09.11
oracle 조인  (0) 2014.09.11

'데이터베이스'의 다른글

  • 현재글decode 와 case

관련글

티스토리툴바