[SQL] 데이터 처리와 가공을 위한 오라클 함수_2

날짜 데이터를 다루는 날짜 함수

연산	설명
날짜 데이터 + 숫자	날짜 데이터보다 숫자만큼 일수 이후의 날짜를 반환함.
날짜 데이터 - 숫자	날짜 데이터보다 숫자만큼 일수 이전의 날짜를 반환함.
날짜 데이터 - 날짜 데이터	두 날짜 데이터 간의 일수 차이를 반환함.
날짜 데이터 + 날짜 데이터	연산 불가로, 지원하지 않음.

 

오라클에서 제공하는 날짜 함수의 대표 SYSDATE함수

별다른 입력 데이터 없이 오라클 데이터베이스 서버를 실행하는 OS(Operating System: 운영체제)의 현재 날짜와 시간을 보여줌

SELECT SYSDATE AS NOW,
       SYSDATE-1 AS YESTERDAY,
       SYSDATE+1 AS TOMORROW
FROM DUAL;

 

몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수

특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환 

ADD_MONTHS ([날짜 데이터(필수)], [더할 개월 수(정수)(필수)])
-- 특정 날짜 데이터에 입력할 개월 수만큼 이후의 날짜를 출력

SELECT SYSDATE,
	ADD_MONTHS(SYSDATE, 3)
FROM DUAL;


-- 입사 10주년이 되는 날짜 출력하기
SELECT EMPNO, ENAME, HIREDATE,
	ADD_MONTHS(HIREDATE, 120) AS WORK10YEAR
FROM EMP;


-- 입사 40년을 초과한 사원 데이터를 출력
SELECT EMPNO,
       ENAME,
       HIREDATE,
       SYSDATE
  FROM EMP
WHERE ADD_MONTHS(HIREDATE,480) < SYSDATE;

 

두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수

날짜 데이터 2개를 입력하여 두 날짜 간의 개월 수 차이를 구할 때 사용

MONTHS_BETWEEN ([날짜 데이터1(필수)], [날짜 데이터2(필수)]
-- 두 날짜 데이터 간의 날짜 차이를 개월 수로 계산하여 출력

SELECT EMPNO, ENAME, HIREDATE, SYSDATE,
    MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,
    MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTHS2,
    TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) AS MONTH3
FROM EMP;

 

 

돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수

NEXT_DAY([날짜 데이터(필수)], [요일 문자(필수])
-- 특정 날짜를 기준으로 돌아오는 요일의 날짜를 출력함

LAST_DAY([날짜 데이터(필수)]
-- 특정 날짜가 속한 달의 마지막 날짜를 출력함

SELECT SYSDATE,
    NEXT_DAY(SYSDATE, '월요일'),
    LAST_DAY(SYSDATE)
FROM DUAL;

 

날짜를 반올림, 버림 하는 ROUND, TRUNC함수

숫자 데이터의 반올림, 버림 처리에 사용한 ROUND, TRUNC함수는 날짜 데이터에도 사용할 수 있는데, 이때 소수점 위치 정보를 입력하지 않고 반올림, 버림의 기준이 될 포맷(format)을 지정

입력 데이터 종류	사용 방식
숫자 데이터	ROUND([숫자(필수)], [반올림 위치])
	TRUNC([숫자(필수)], [버림 위치])
날짜 데이터	ROUND([날짜 데이터(필수)], [반올림 기준 포맷])
	TRUNC([날짜 데이터(필수)], [버림 기준 포맷])

포멧 모델	기준 단위
CC, SCC	네 자리 연도의 끝 두 자리 기준
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	날짜 데이터의 해당 연, 원, 일의 7월 1일 기준 (2025년 7월 1일일 경우 2026년으로 처리)
IYYY, IYY, IY, I	ISO 8601에서 제정한 날짜 기준 연도 포맷을 기준
Q	각 분기의 두 번째 달의 16일 기준
MONTH, MON, MM, RM	각 달의 16일 기준
WW	해당 연도의 주(1~53번째 주) 기준
IW	ISO 8601에서 제정한 날짜 기준 해당 연도의 주(week) 기준
W	해당 월의 주(1~5번째 주) 기준
DDD, DD, J	해당 일의 정오(12:00:00) 기준
DAY, DY, D	한 주가 시작되는 날짜 기준
HH, HH12, HH24	해당 일의 시간 기준
MI	해당 일 시간의 분 기준

SELECT SYSDATE,
    ROUND(SYSDATE, 'CC') AS FORMAT_CC,
    ROUND(SYSDATE, 'YYYY') AS FORMAT_YYYY,
    ROUND(SYSDATE, 'Q') AS FORMAT_Q,
    ROUND(SYSDATE, 'DDD') AS FORMAT_DDD,
    ROUND(SYSDATE, 'HH') AS FORMAT_HH
FROM DUAL;

SELECT SYSDATE,
    TRUNC(SYSDATE, 'CC') AS FORMAT_CC,
    TRUNC(SYSDATE, 'YYYY') AS FORMAT_YYYY,
    TRUNC(SYSDATE, 'Q') AS FORMAT_Q,
    TRUNC(SYSDATE, 'DDD') AS FORMAT_DDD,
    TRUNC(SYSDATE, 'HH') AS FORMAT_HH
FROM DUAL;

---

자료형을 변환하는 형 변환 함수

종류	설명
TO_CHAR	숫자 또는 날짜 데이터를 문자열 데이터로 변환
TO_NUMBER	문자열 데이터를 숫자 데이터로 변환
TO_DATE	문자열 데이터를 날짜 데이터로 변환

TO_CHAR([날짜 데이터(필수)], '[출력하길 원하는 문자 형태(필수)]')
-- 날짜 데이터를 원하는 형태의 문자열로 출력

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS 현재날짜시간
   FROM DUAL;

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'MM') AS MM,
    TO_CHAR(SYSDATE, 'MON') AS MON,
    TO_CHAR(SYSDATE, 'MONTH') AS MONTH,
    TO_CHAR(SYSDATE, 'DD') AS DD,
    TO_CHAR(SYSDATE, 'DY') AS DY,
    TO_CHAR(SYSDATE, 'DAY') AS ADY
 FROM DUAL;

 

특정 언어에 맞춰서 날짜 출력하기

TO_CHAR([날짜 데이터(필수)], '[출력하길 원하는 문자 형태(필수)]',
'NLS_DATE_LANGUAGE = language'(선택)
-- 날짜 데이터를 출력할 문자 형태를 지정하고 원하는 언어 양식을 지정


SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'MM') AS MM,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = KOREAN') AS MON_KOR,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = JAPANESE') AS MON_JPN,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH') AS MON_ENG,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = KOREAN') AS MONTH_KOR,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = JAPANESE') AS MONTH_JPN,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = ENGLISH') AS MONTH_ENG
FROM DUAL;


SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'MM') AS MM,
    TO_CHAR(SYSDATE, 'DD') AS DD,
    TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = KOREAN') AS DY_KOR,
    TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = JAPANESE') AS DY_JPN,
    TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = ENGLISH') AS DY_ENG,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = KOREAN') AS DAY_KOR,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = JAPANESE') AS DAY_JPN,
    TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE = ENGLISH') AS DAY_ENG
FROM DUAL;

---

시간 형식 지정하여 출력하기

형식	설명
HH24	24시간으로 표현한 시간
HH, HH12	12시간으로 표현한 시간
MI	분
SS	초
AM, PM, A.M, P.M	오전, 오후 표시

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'HH24:MI:SS') AS HH24MISS,
    TO_CHAR(SYSDATE, 'HH12:MI:SS AM') AS HHMISS_AM,
    TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') AS HHMISS_PM
FROM DUAL;

 

형식	설명
9	숫자 한 자리를 의미함(빈자리를 채우지 않음)
0	빈자리를 0으로 채움
$	달러($) 표시를 붙여서 출력함
L	지역 화폐 단위 기호를 붙여서 출력함
.	소수점을 표시함
,	천 단위의 구분 기호를 표시함

SELECT SAL,
    TO_CHAR(SAL, '$999,999') AS SAL_$,
    TO_CHAR(SAL, 'L999,999') AS SAL_L,
    TO_CHAR(SAL, '999,999.00') AS SAL_1,
    TO_CHAR(SAL, '000,999,999.00') AS SAL_2,
    TO_CHAR(SAL, '000999999.99') AS SAL_3,
    TO_CHAR(SAL, '999,999,00') AS SAL_4
FROM EMP;

 

문자열 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수

TO_NUMBER(['문자열 데이터(필수)]', '[인식할 숫자 형태(필수)]')
-- 문자열을 지정한 형태의 숫자로 인식하여 숫자 데이터로 변환함

SELECT TO_NUMBER('1,300', '999,999') - TO_NUMBER('1,500', '999,999')
    FROM DUAL;

 

문자열 데이터를 날짜 데이터로 변환하는 TO_DATE 함수

TO_DATE('[문자열 데이터(필수)]', '[인식할 날짜 형태(필수)]')
-- 문자열 데이터를 날짜형 데이터로 변환

SELECT TO_DATE('2024-08-14', 'YYYY-MM-DD') AS TODATE1,
       TO_DATE('2024/08/14', 'YYYY/MM/DD') AS TODATE2
FROM DUAL;


SELECT *
    FROM EMP
  WHERE HIREDATE > TO_DATE('1981/06/01', 'YYYY/MM/DD');

SELECT TO_CHAR(TO_DATE('49/12/10', 'YY/MM/DD'), 'YYYY-MM-DD') AS YY_YEAR_49,
       TO_CHAR(TO_DATE('49/12/10', 'RR/MM/DD'), 'YYYY-MM-DD') AS RR_YEAR_49,
       TO_CHAR(TO_DATE('50/12/10', 'YY/MM/DD'), 'YYYY-MM-DD') AS YY_YEAR_50,
       TO_CHAR(TO_DATE('50/12/10', 'RR/MM/DD'), 'YYYY-MM-DD') AS RR_YEAR_50,
       TO_CHAR(TO_DATE('51/12/10', 'YY/MM/DD'), 'YYYY-MM-DD') AS YY_YEAR_51,
       TO_CHAR(TO_DATE('51/12/10', 'RR/MM/DD'), 'YYYY-MM-DD') AS RR_YEAR_51,

---

NULL 처리 함수

NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞의 데이터가 NULL일 때 반환할 데이터](필수))
-- 열 또는 데이터를 입력하여 해당 데이터가 NULL이 아니면 데이터를 그대로 반환하고, NULL이면 지정한 데이터를 반환

SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM,
    NVL(COMM, 0),
    SAL+NVL(COMM, 0)
FROM EMP;

NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞 데이터가 NULL이 아닐 때 반환할 데이터 또는 계산식(필수)],
[앞 데이터가 NULL일 때 반환할 데이터 또는 계산식(필수)])
-- 열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 때와 NULL일 때 출력 데이터를 각각 지정

SELECT EMPNO, ENAME, COMM,
    NVL2(COMM, '0', 'X'),
    NVL2(COMM, SAL*12+COMM, SAL*12) AS ANNSAL
FROM EMP;

---

상황에 따라 다른 데이터를 반환하는 DECODE함수와 CASE문

 

DECODE 함수는 기준이 되는 데이터를 먼저 지정한 후 해당 데이터에 따라 다른 결괏값을 내보내는 함수

DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과], 
       [조건 1], [데이터가 조건1과 일치할 때 반환할 결과],
       [조건 2], [데이터가 조건2과 일치할 때 반환할 결과],
       ...
       [조건 n], [데이터가 조건n과 일치할 때 반환할 결과],
       [위 조건1~조건n과 일치하지 않을 때 반환할 결과])
       

SELECT EMPNO, ENAME, JOB, SAL,
       DECODE(JOB,
              'MANAGER', SAL*1.1,
              'SALESMAN', SAL*1.05,
              'ANALYST', SAL,
              SAL*1.03) AS UPSAL
  FROM EMP;

 

 

CASE문은 DECODE함수와 마찬가지로 특정 조건에 따라 반환할 데이터를 설정할 때 사용

기준 데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE함수와 달리 CASE문은 각 조건에 사용하는 데이터가 서로 상관없어도 되며, 기준 데이터와 같은(=) 데이터 외에 다양한 조건을 사용할 수 있음.

CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
    WHEN[조건 1] THEN [조건1의 결괏값이 true일 때 반환할 결과]
    WHEN[조건 2] THEN [조건2의 결괏값이 true일 때 반환할 결과]
    ...
    WHEN[조건 n] THEN [조건n의 결괏값이 true일 때 반환할 결과]
    ELSE[위 조건1~조건n과 일치하지 않을 때 반환할 결과]
  END
  
  
SELECT EMPNO, ENAME, JOB, SAL,
   CASE JOB
        WHEN 'MANAGER' THEN SAL*1.1
        WHEN 'SALESMAN' THEN SAL*1.05
        WHEN 'ANALYST' THEN SAL
        ELSE SAL*1.03
  END AS UPSAL
  FROM EMP;