SQL 주요함수: 날짜, 형변환, NVL, DECODE, CASE

SQL 주요함수

4. 날짜 함수

SYSDATE : 시스템의 현재 날짜를 표시

ROUND(date, format) : 인자로 받은 날짜를 특정 포맷으로 반올림

TRUNC(date, format) : 인자로 받은 날짜를 특정 기준으로 버림

MONTHS_BETWEEN(date1, date2) : 두 날짜 사이가 몇 개월인지를 반환

ADD_MONTHS(date, number) : 특정 날짜에 개월수를 더한다.

NEXT_DAY(date, 요일) : 특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환

LAST_DAY : 해당 달의 마지막 날짜를 반환

1) SYSDATE : 시스템의 현재 날짜를 표시

ex) SELECT SYSDATE FROM DUAL;

결과는 16/01/13 형식으로 시스템의 현재 날짜가 출력된다.

날짜 연산

ex) SELECT SYSDATE-1 어제,SYSDATE, SYSDATE+1 오늘 FROM DUAL;

SYSDATE에 날짜수를 더하거나 빼서 어제 날짜, 오늘 날짜, 내일 날짜를 출력.

ex) SELECT SYSDATE-HIREDATE 근무일수 FROM EMP;

결과 : 오늘날짜에서 입사날짜를 빼서 근무일수를 출력할 수 있다.

2) ROUND(date, format) : 인자로 받은 날짜를 특정 포맷으로 반올림

포맷모델

CC, SCC : 4자리 연도 끝의 두 글자를 기준으로 반올림

SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y : 년도(7월 1일부터 반올림)

DDD, D, J : 일을 기준

HH, HH12, HH24 : 시를 기준

Q : 한 분기의 두번째 달의 16일을 기준으로 반올림

MONTH, MON, MM, RM : 월(16일을 기준으로 반올림)

DAY, DY, D : 한 주가 시작되는 날짜

MI : 분을 기준

ex) SELECT HIREDATE, ROUND (HIREDATE, 'MONTH') FROM EMP;

입사일을 달을 기준으로 반올림하여 16일 이전이면 해달 달의 1일, 16일 이후면 다음 달의 1일로 표시된다.

3) TRUNC(date, format) : 인자로 받은 날짜를 특정 기준으로 버림

ex) SELECT HIREDATE, TRUNC (HIREDATE, 'MONTH') FROM EMP;

입사일을 달을 기준으로 잘라내서 해당 달의 날짜가 전부 1일로 표시된다.

4) MONTHS_BETWEEN(date1, date2) : 두 날짜 사이가 몇 개월인지를 반환

ex) SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;

MONTHS_BETWEEN의 결과값인 날짜 사이의 개월수가 소수점 자리까지 표시된다. 때문에 TRUNC 함수로 절삭이 필요하다.

ex) SELECT ENAME, SYSDATE, HIREDATE, 

TRUNC( MONTHS_BETWEEN(SYSDATE, HIREDATE)) FROM EMP;

TRUNC 함수를 써서 소수점을 절삭하면 개월수만 표시된다.

5) ADD_MONTHS(date, number) : 특정 날짜에 개월수를 더한다.

ex) SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 6) FROM EMP;

입사 날짜에 6개월이 더해진다.

6) NEXT_DAY(date, 요일) : 특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환

- 두번째 인자값은 한글인 경우 일, 월, 화...영문인 경우 SUN, MON, TUE 등으로 나타날 수 있다.

- 요일을 숫자로 표시할 경우 월요일은 1, 화는 2, ...일은 7로 표시한다.

ex) SELECT SYSDATE, NEXT_DAY(SYSDATE, '수요일') FROM DUAL;

현재 날짜와 오늘을 기준으로 가장 가까운 다음 수요일이 언제인지 출력된다.

7) LAST_DAY : 해당 달의 마지막 날짜를 반환

대부분 달은 마지막 날이 정해져 있지만 2월인 경우 마지막 날이 28일일수도, 29일일수도 있으므로 이 때 효과적이다.

ex) SELECT SYSDATE, LAST_DAY(HIREDATE) FROM EMP;

입사한 달의 마지막 날이 출력된다.

5. 형 변환 함수

TO_CHAR(날짜 데이터, '날짜 출력 형식') : 날짜를 문자형(VARCHAR2)으로 변환

TO_CHAR(숫자, '숫자 출력 형식') : 숫자형을 문자형으로 변환

TO_DATE('문자', '날짜 출력 형식') : 문자형을 날짜형으로 변환

TO_NUMBER('문자', '숫자 출력 형식') : 문자형을 숫자형으로 변환

TO_CHAR : 날짜형 혹은 숫자형을 문자형을 변환

1) TO_CHAR(날짜 데이터, '날짜 출력 형식') : 날짜를 문자형(VARCHAR2)으로 변환

※ 날짜 출력 형식

YYYY : 4자리 년도 표시

YY : 2자리 년도 표시

MM : 월을 숫자로 표시

MON : 월을 문자로 표시 ex) 'JANUARY'나 '1월'

DD : 일자를 문자로 표시

DAY : 요일 표시

DY : 요일을 약어로 표시 ex) 'SAT'나 '토'

ex) SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

년도를 4자리(YYYY)로 표시하며 날짜를 /가 아니라 -로 구분된 형태로 출력

ex) SELECT HIREDATE, TO_CHAR(HIREDATE, 'YY-MON-DY') FROM EMP;

년도는 2자리(YY), 월은 문자(MON), 요일은 약어(DY)로 표시한 예

ex) SELECT HIREDATE, TO_CHAR(HIREDATE, 'YYYY "년" MM "월" DD "일"') FROM EMP;

※ 시간 출력 형식

AM 또는 PM : AM, P.M으로 시각 표시

A.M 또는 P.M : A.M이나 P.M으로 시각 표시

HH 또는 HH12 : 12시간으로 표시(1~12)

HH24 : 24시간으로 표시(1~24)

MI : 분 표현

SS : 초 표현

ex) SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24 : MI : SS') FROM DUAL;

현재 날짜에 시간, 분, 초 표시.

2) TO_CHAR(숫자, '숫자 출력 형식') : 숫자형을 문자형으로 변환

ex) SELECT TO_CHAR(1230000) FROM DUAL;

결과는 1230000이라는 숫자를 그대로 출력한다.

※ 숫자 출력 형식

0 : 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다.

9 : 자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다.

L : 각 지역별 통화 기호 앞에 표시한다.

. : 소수점

, : 천 단위 자리 구분

ex) SELECT ENAME, SAL, TO_CHAR(SAL, 'L999,999') FROM EMP;

SAL을 6자리로 출력하며 1000단위마다 ,로 구분, 앞에 L을 붙여 통화 기호(\)를 앞에 붙이도록 하여 출력했다.

ex) SELECT TO_CHAR(123456, '000000000'), TO_CHAR(123456, '999,999,999') FROM DUAL;

- 1번째 것은 0으로 자릿수를 나타내며(9자리), 자릿수가 맞지 않을 경우 0으로 채운다.

- 2번째 것은 9로 자릿수를 나타내며(9자리), 자릿수가 맞지 않으면 채우지 않는다. 

3) TO_DATE('문자', '날짜 출력 형식') : 문자형을 날짜형으로 변환

ex) SELECT HIREDATE FROM EMP 

WHERE HIREDATE = TO_DATE('1981-12-03', 'YYYY-MM-DD');

HIREDATE에서 문자형인 1981년 12월 03일을 찾아 'YYYY-MM-DD' 형식으로 출력한다.

ex) SELECT TRUNC(SYSDATE - TO_DATE('2008-01-01', 'YYYY-MM-DD')) FROM DUAL;

오늘 날짜(SYSDATE)에서 2008년 1월 1일을 뺀 결과를 출력한다. 

데이터형이 일치하지 않기 때문에 TO_DATE를 써서 데이터형을 날짜형으로 일치시키고,

TRUNC 함수로 소수점을 절삭했다.

4) TO_NUMBER('문자', '숫자 출력 형식') : 문자형을 숫자형으로 변환

SELECT 20000 - 10000 FROM DUAL;를 해보면 10000라는 결과가 나온다.

이번엔 숫자에 콤마(,)를 넣은 문자열의 계산을 해보자.

SELECT '2,0000' - '10,000' FROM DUAL;을 해보면 ' ' 안의 숫자는 문자열이기 때문에 계산이 불가능하다.

하지만 TO_NUMBER를 씌워주면 계산을 할 수 있다.

SELECT TO_NUMBER('2,0000', '99,999') - TO_NUMBER('10,000', '99,999') FROM DUAL;

-- '99,9999' 이렇게 숫자 출력 형식을 반드시 표시해야 하는 것이 중요.

6. NULL을 변환하는 NVL 함수

NVL(COMM, 0)

NULL 값을 0 또는 다른 값으로 변환하여 사용하는 함수.

ex) SELECT ENAME, SAL, COMM, JOB FROM EMP

ORDER BY JOB;

연봉 계산을 위해 급여(SAL)와 커미션(COMM) 컬럼을 보면 영업 사원 외의 사원들 커미션은 null로 지정되어 있다.

연봉을 올바르계 계산하려면 커미션이 null인 경우 0으로 바꿔야 한다.

SELECT ENAME, SAL, COMM, SAL*12+COMM,

NVL(COMM, 0), SAL*12+NVL(COMM, 0)

FROM EMP ORDER BY JOB;

NVL을 이용하여 COMM의 null을 0으로 변경, 

SAL*12할 때에도 COMM의 null을 0으로 변경하여 계산한 결과를 출력한다. 

문제) 모든 사원에게는 상관(MGR, 매니저)이 있다. 하지만 CEO인 경우 상관이 없으므로 MGR 값이 NULL이다.

상관이 없는 사원(CEO)만 출력하되 MGR 컬럼값을 NULL 대신 'CEO'로 출력하라.

1) SELECT * FROM EMP;

KING이라는 이름의 사원은 JOB이 PRESIDENT라서 MGR 값이 null인 것을 확인할 수 있다.

2) SELECT ENAME, NVL(MGR, 9999) FROM EMP;

NVL함수를 써서 MGR에 있는 null값을 9999로 바꿨다.

SELECT ENAME, NVL(TO_CHAR(MGR, '9999'), 'CEO') FROM EMP;

- NVL함수로 null 값을 'CEO'라는 문자로 바꾸기 위해, NVL(MGR, 'CEO')로 해야할텐데, MGR은 문자가 아니라 숫자이므로 문자형으로 바꿀 필요가 있다.

- TO_CHAR 함수를 써서 MGR을 문자형으로 바꾼다. NVL에 있으므로 '9999'라는 문자형으로 변경한다.

- NVL(TO_CHAR(MGR, '9999'), 'CEO') 형태로 null 값을 특정 문자형으로 바꿀 수 있다.

7. 선택을 위한 DECODE 함수

하나 이상의 조건에 따른 결과를 반환하는 함수

DECODE (표현식, 조건1, 결과1, 

조건2, 결과2, 

조건3, 결과3, 

기본결과n

 )

DECODE 함수는 프로그램 언어에서 사용되는 SWITCH CASE문과 같은 기능을 한다.

SELECT DEPTNO, DECODE(DEPTNO, 10, 'A', 20, 'B', 'DEFAULT')

FROM EMP ORDER BY DEPTNO;

부서번호 10인 경우에는 'A', 20인 경우에는 'B', 그 외의 번호는 'DEFAULT'로 출력된다.

SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 'ACCOUNTING',                                       

                                                   20, 'RESEARCH',

                                                   30, 'SALES',

                                                   40, 'OPERATIONS') AS DNAME

FROM EMP;

DECODE 결과는 AS DNAME 이란 이름으로, DEPNO에 따라 설정한 각각의 이름으로 출력이 되었다.

문제) 직급에 따라 급여를 인상하는 결과를 출력하라. 직급이 ANAIYST인 사원은 5%, SALESMAN인 사원은 10%, MANAGER인 사원은 15%, CLERK인 사원은 20% 인상한다.

SELECT EMPNO, ENAME, JOB, SAL,

DECODE(JOB, 'ANAIYST', SAL * 1.05,

                'SALESMAN', SAL * 1.10,

            'MANAGER', SAL * 1.15,

            'CLERK', SAL * 1.20,

            SAL -- 기본결과

            ) AS UPSAL

FROM EMP;

8. 조건에 따라 서로 다른 처리가 가능한 CASE 함수

CASE WHEN 조건1 THEN 결과1

WHEN 조건2 THEN 결과2

WHEN 조건3 THEN 결과3

ELSE 결과n

END

CASE 함수는 프로그래밍 언어의 IF ELSE와 유사한 구조를 가진다.

SELECT ENAME, DEPTNO,

CASE WHEN DEPTNO = 10 THEN 'ACCOUNTING'                                       

     WHEN DEPTNO = 20 THEN 'RESEARCH'

     WHEN DEPTNO = 30 THEN 'SALES'

     WHEN DEPTNO = 40 THEN 'OPERATIONS'

     END AS DNAME

FROM EMP;

CASE 함수가 끝나는 END 뒤에 AS DNAME 을 붙여 CASE 함수의 결과를 DNAME이라는 헤딩으로 출력한다.

SELECT DEPTNO,

CASE WHEN DEPTNO >= 20 THEN '회계'

     ELSE '기타'

END AS 부서명

FROM DEPT;