오라클 SQL 문법 공부11 - DECODE, NVL 함수

서론

오라클에서 자주 쓰이는 DECODE 함수이다. 다른 DBMS에는 없을 수도 있는 함수인데 특정값이 참인지 거짓인지 판별하여 다른 문자로 치환할 때 자주 쓰인다. 예를 들면 남자면 MALE, 여자면 FEMALE 등으로 말이다. 이와 비슷한게 다음에 할 CASE 문인데 CASE문에 비해 DECODE 함수는 성능상 좋진 않다고 한다. 필자도 사실 DECODE가 간편해서 자주 사용하는데 현업에서는 보통 성능상의 문제로 DECODE 보단 CASE문을 표준으로 정해 사용하는 일이 대부분이라고 하니 참고하자.

본론

DECODE 함수

--DECODE 함수
--조건문이 축약된 형식의 함수 표헌식이다.
--각 조건이 맞을 경우 각각 문자를 치환할 수 있다.

--A가 B와 같을 경우 1을 출력하는 경우
--DECODE(A, B, '1', null) (단 마지막 null은 생략 가능)

--DECODE(A, B, '1', '2') 참일경우 1 거짓일 경우 2

--DECODE(A, B, '1', C, '2') 참일경우 1 거짓일 경우 2(A가 B이면 1, C이면 2)
--확장이 유리 하지만 성능상 CASE문이 더 좋다.


--부서번호가 10번이면 인사부, 그외 부서는 null 값으로 치환

select deptno, DECODE(deptno, 10, '인사부') from emp;


--부서번호가 10번이면 인사부, 그외 부서는 '기타부서' 값으로 치환
select deptno, DECODE(deptno, 10, '인사부', '기타부서') from emp;

--[참고]
--decode를 통해 비교하려는 값은 비교컬럼과 데이터 타입이 일치되어야 한다.
--decode를 통해 각각 치환된 값은 서로 데이터 타입이 일치되어야 한다.

--부서번호가 10번이면 인사부, 20이면 '재무부', 30이면 'it'부 나머지는 기타부서 값으로 치환
select deptno,
       DECODE(deptno, 10, '인사부',
                      20, '재무부',
                      30, 'it부',
                      '기타부서')
  from emp;

DECODE를 사용할때 치환할 값들의 데이터형이 같아야 된다 정도만 기억하면 사용하는데 크게 지장이 없을 것으로 보인다.

NVL 함수

--NVL(조사할 컬럼, null 일 경우 치환할 값)
--모든 데이터 타입에 적용 가능
--조사할 컬럼과 치환할 값의 데이터 타입이 같아야 한다.
select NVL(comm, ' ') from emp;
select NVL(to_char(comm), ' ') from emp;

--NVL2(조사 대상컬럼, null 아닐 때 치환할 값, null 일 경우 치환할 값)
--조사다생 컬럼과 null 치환 값의 데이터 타입은 일치하지 않아도 된다.
--null일 경우 치환 값과 null 아닐때 치환 값의 데이터 타입은 서로 일치하여야 한다
--반환되는 데이터 타입은 두번째 인자인 null 아닐때 치환할 값의
--데이터 타입이 된다.
--두번째 인자 타입으로 데이터형이 정해져버린다.

select sal, comm, nvl2(comm, comm*1, 1000) as result from emp;
select sal, comm, nvl2(comm, 'comm이 있다', 'comm이 없다') as result from emp;
select sal, comm, nvl2(comm, 0, 'comm이 없다') as result from emp; -- 에러발생
select sal, comm, nvl2(comm, '0', 'comm이 있다') as result from emp; -- 위 해결

보통 NULL 값이 있는 컬럼들에 대해서 처리를 할 때 주로 사용된다. 값이 없을 경우 값없음 이런식으로 출력할때 말이다. 혹시나 컬럼에 값들이 NULL이 아닌 ' ' 빈 문자로 들어가는 경우도 있는데 이때는 당연히 NVL로 처리가 불가능하니 DECODE로 ' ' 일 경우에 처리하는 식으로 해야 한다.

실습문제

인용구

--1) student 테이블의 jumin 컬럼을 이용하여 각 학생의 태어난 날의 요일을 구하라
select jumin, TO_CHAR(TO_DATE(substr(jumin, 1, 6), 'YYMMDD'), 'DAY') from student;

--2) emp 테이블의 사원이름, 매니저번호(mgr)를 출력하고
--매니저번호가 null이면 '상위관리자'로 표시하고
--매니저번호가 있으면 'MGR담당'으로 표시하여라 ex)7809담당
select ename, mgr, NVL2(mgr, TO_CHAR(MGR)||'담당', '상위관리자') from emp;

--3) student 테이블의 jumin 컬럼을 참조하여 학생들의 이름과 태어난 달, 그리고
--분기를 출력하여라. 태어난 달이 01-03월 은 1/4분기, 04-06월은 2/4분기, 07-09월은 3/4 분기
--10-12월은 4/4 분기로 나타내어라
select name,
       jumin,
       TO_CHAR(TO_DATE(substr(jumin, 1, 6), 'YYMMDD'), 'MON'),
       TRUNC(TO_NUMBER((TO_CHAR(TO_DATE(substr(jumin, 1, 6), 'YYMMDD'), 'MM')) - 0.1) / 3),
       DECODE(TRUNC(TO_NUMBER((TO_CHAR(TO_DATE(substr(jumin, 1, 6), 'YYMMDD'), 'MM')) - 0.1) / 3), 0, '1/4분기', 1, '2/4분기',  2, '3/4분기',  3, '4/4분기')
  from student;
--분기 표현하는 포맷이 있었네... 괜히 힘들게 했네 "Q"
select name,
       jumin,
       TO_CHAR(TO_DATE(substr(jumin, 1, 6), 'YYMMDD'), 'MON'),
       TO_CHAR(TO_DATE(substr(jumin, 1, 6), 'YYMMDD'), 'Q') || '/4 분기'
  from student;
--4) emp테이블을 이용하여 새 연봉을 책정하려한다.
--현재까지 근무월수가 420일 이상인 직원은 보너스를 5000,
--그 미만인 직원은 보너스를 3000을 지급하여 새 연봉을 계산하여라.
--단 연봉은 sal * 12 + (위의 차등지급되는 보너스)
--sign 함수 써도 됨. 양수면 1 음수면 -1
SELECT ENAME,
       HIREDATE,
       TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)),
       SAL,
       TRUNC(SIGN(MONTHS_BETWEEN(SYSDATE, HIREDATE) - 420)),
       DECODE(TRUNC(SIGN(MONTHS_BETWEEN(SYSDATE, HIREDATE) - 420)), -1, SAL * 12 + 3000, 1, SAL * 12 + 5000) 
  FROM EMP;

--5) emp테이블에서 sal이 1000미만 이면 c등급, 1000에서 3000미만 B등급
--3000이상이면 A등급으로 등급을 나타내어라
SELECT ENAME,
       SAL,
       DECODE(TRUNC(SAL/1000), 0, 'C등급', 1, 'B등급', 2, 'B등급', 'A등급')    
  FROM EMP;
--이것도 sign 써서 해도 됨
SELECT ENAME,
       SAL,
       DECODE(sign(sal-1000), -1, 'C등급', decode(sign(sal-3000), -1, 'B등급', 'A등급'))    
  FROM EMP;