009. DECODE & CASE

IRISH·2024년 3월 4일

OracleStudy oracle

Oracle

목록 보기

11/17

학습일자 : 2024.02.28

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

DECODE

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

⇒ 예제 1

-- 부서번호가 10이면 행정, 20이면 마케팅, 30이면 구매
-- 나머지는 기타를 출력하는 예제
SELECT department_id, DECODE(department_id, 10 , '행정' ,
                              20 , '마케팅' ,
                              30 , '구매', 
                              '기타') DEPARTMENT_NAME
  FROM departments;

⇒ 예제 2

DECODE 함수에서 집계 함수를 사용한 예제

-- 10부서는 급여합계를, 20부서는 최대값을, 30부서는 최소값을 출력하는 예제
SELECT department_id, DECODE(department_id, 10 , SUM(salary),
                              20 , MAX(salary),
                              30 , MIN(salary)) "연봉"
  FROM employees
 WHERE department_id IN (10, 20, 30)
 GROUP BY department_id
 ORDER BY department_id ASC;

⇒ 예제 3

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

-- 부서별로 급여 합계를 출력한다. 
SELECT department_id, NVL(SUM(DECODE(department_id, 10, salary)),0) deptno10, 
                      NVL(SUM(DECODE(department_id, 20, salary)),0) deptno20,
					  NVL(SUM(DECODE(department_id, 30, salary)),0) deptno30,
					  NVL(SUM(DECODE(department_id, 40, salary)),0) deptno40
  FROM employees
 WHERE department_id IN (10, 20, 30)
 GROUP BY department_id
 ORDER BY department_id ASC;

⇒ 예제 4

일반적인 집계함수를 사용할 때는 결과가 행으로 조회

-- 부서별로 급여 합계를 행으로 출력한다. 
SELECT d.department_id, NVL(SUM(e.salary),0) salary
  FROM employees e, departments d
 WHERE e.department_id(+) = d.department_id
   AND e.department_id IN (10, 20, 30)
 GROUP BY d.department_id
 ORDER BY department_id ASC;

⇒ 예제 5 [예제 4의 결과를 열로 변경해보기]

DECODE와 MAX함수를 사용하면 열로 값을 표시할 수 있다.

 -- 부서별로 급여 합계를  열로 출력한다. 
SELECT MAX(NVL(SUM(DECODE(department_id, 10, salary)),0)) department_id10, 
       MAX(NVL(SUM(DECODE(department_id, 20, salary)),0)) department_id20,
       MAX(NVL(SUM(DECODE(department_id, 30, salary)),0)) department_id30,
       MAX(NVL(SUM(DECODE(department_id, 40, salary)),0)) department_id40
  FROM employees
 GROUP BY department_id;

CASE

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

⇒ 예제 1

-- 위의  DECODE예제를 CASE함수로 변환한 예이다. 
SELECT department_id, 
       CASE department_id
         WHEN 10 THEN 'ACCOUNTING'
         WHEN 20 THEN 'RESEARCH'
         WHEN 30 THEN 'SALES'
         ELSE 'OPERATIONS'
       END as "Dept Name"
  FROM departments
 WHERE department_id IN (10, 20 , 30);

⇒ 예제 2

--급여별로 인상율을 다르게 계산하였다. 
SELECT last_name
       ,CASE
          WHEN salary < 1000  THEN salary+(salary*0.8)
          WHEN salary BETWEEN 1000 AND 2000 THEN salary+(salary*0*0.5)
          WHEN salary BETWEEN 2001 AND 3000 THEN salary+(salary*0.3)
          ELSE salary+(salary*0.1)
       END salary
  FROM employees;