모방은 창조의 어머니

[Oracle] 날짜 함수 - 두 날짜 간의 개월 차이를 구하는 함수(MONTHS_BETWEEN)

MONTHS_BETWEEN 함수는 두개의 날짜 데이터 간의 개월 수 차이를 구할 때 사용합니다.

MONTHS_BETWEEN 함수를 이용하여 직원(EMP) 테이블에서 사원들이 입사한지 얼마나 되었는지 알아 봅시다.

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

아래는 입사일로 부터 오늘까지의 기간(개월)을 조회한 결과 입니다. 

입사일에서 오늘까지의 개월수 차이를 구하니, 음수에, 소수점 까지 나오네요. 이것을 보기 좋게 정리해 봅시다.

SELECT  ENAME, HIREDATE, SYSDATE, 
        MONTHS_BETWEEN(SYSDATE, HIREDATE),
        TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS TOTAL_MONTH,
        TRUNC( TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))/12) AS YEAR,
        MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)),12) AS MONTH
FROM EMP;

너무 복잡해 보이시나요?

• MONTHS_BETWEEN(SYSDATE, HIREDATE) : 입사일로 부터 오늘 까지의 개월 수를 구합니다.
• TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) : 개월수의 소수점 이하는 버리고 정수부분만 취합니다.
• TRUNC( TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))/12)  : 개월수를 년으로 환산하기 위해서 12로 나누고 소수점 이하는 버립니다. 
• MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)),12) : 개월수를 12로 나눈 나머지를 구합니다.

하나씩 생각해 보면 익숙하지 않아서 그렇지 쿼리에서 모르는 부분은 없을 것 같네요.

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.
※ 예제에서 소수점 이하를 버리는데 사용된 TRUNC 함수가 궁금하신 분은 '[Oracle] 숫자 함수 - 특정 위치에서 버리는 함수(TRUNC) '부분을 참고 하시면 됩니다. 

[Oracle] 날짜 함수 - 몇 개월 이후의 날짜를 구하는 함수(ADD_MONTHS)

ADD_MONTHS 함수는 특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환 합니다. 이때 지정한 개월 수는 정수여야 합니다. 

ADD_MONTHS ( 날짜 데이터, 더할 개월 수 )

ADD_MONTHS 함수의 예로 지금 부터 6개월 뒤의 날짜를 구해 봅시다.

SELECT  ADD_MONTHS(SYSDATE, 6) 
FROM DUAL;

이번에는 직원들이 입사한지 10년 되는 날이 언제인지 구해 봅시다.

SELECT  ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 10*12) 
FROM EMP;

위 쿼리에서는 10년을 개월수로 표현하기 위하여 120이라는 개월 수를 사용하는 대신 10년을 구분하기 쉽게 '10*12'라는 수식을 사용하였습니다. 

ADD_MONTHS 함수를 이용하여 30년 이상 근무한 사람을  출력할 수도 있습니다.

SELECT ENAME, HIREDATE, SYSDATE
FROM EMP
WHERE ADD_MONTHS(HIREDATE, 30*12) < SYSDATE;

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

[Oracle] 날짜 함수 - 오늘이 몇일(SYSDATE) 

오늘은 SYSDATE 함수로 알 수있습니다. 다만, SYSDATE 함수로 알수 있는 날짜와 시간은 오라클 데이터베이스 서버상의 현재 날짜와 시간입니다. 이 함수를 이용하여, 오늘부터 100일 뒤가 몇월 몇일일까? 오늘이 1000일이면 우리가 만난 날은 언제이지? 와 같은 것을 쉽게 알 수 있는 방법이 없을까요? 오라클에서는 날짜 데이터를 이용한 간단한 연산을 제공합니다.

• 날짜 데이터 + 숫자 : 날짜 데이터보다 숫자만큼 일수 이후의 날짜를 반환합니다.
• 날짜 데이터 - 숫자 : 날짜 데이터보다 숫자만큼 일수 이전의 날짜를 반환합니다.
• 날짜 데이터 - 날짜 데이터 : 두 날짜 데이터 간의 일 수 차이를 반환합니다.
• 날짜 데이터 + 날짜 데이터 : 지원하지 않습니다.(연산불가)

예제를 통해 간단히 알아 봅시다. 

SELECT  SYSDATE AS NOW,
        SYSDATE+100 AS AFTER_100,
        SYSDATE-1000 AS BEFOR_1000
FROM DUAL;

숫자 함수 - 가장 가까운 정수 찾기(CEIL, FLOOR)

CEIL 함수는 입력한 숫자와 가까운 큰 정수, FLOOR 함수는 입력한 숫자와 가장 작은 정수를 반환하는 함수 입니다.

CEIL( 숫자 )
FLOOR( 숫자 )

CEIL 함수와 FLOOR 함수를 예를 통해 살펴봅시다.

SELECT  CEIL(1234.5678),
        FLOOR(1234.5678),
        CEIL(-1234.5678),
        FLOOR(-1234.5678),
FROM DUAL;

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

숫자 함수 - 특정 위치에서 버리는 함수(TRUNC)

TRUNC 함수는 지정된 자리 이하 숫자를 버리는 함수 입니다. 위치를 지정하지 않으면 소수점 이하 숫자를 버림 처리 합니다.

TRUNC
( 숫자, [버림위치] )

예제를 통헤 RTUNC 함수를 살펴 봅시다.

SELECT  TRUNC(1234.5678), 
        TRUNC(1234.5678, 0),
        TRUNC(1234.5678, 1),
        TRUNC(1234.5678, 2),
        TRUNC(1234.5678, -1),
        TRUNC(1234.5678, -2)
FROM DUAL;

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

숫자 함수 - 특정 위치에서 반올림 함수(ROUND)

특정 숫자를 반올림하면서 반올림 할 위치를 지정할 수 있습니다. 지정하지 않으면 소수 첫째 자리에서 반올림한 결과를 반환합니다.

ROUND( 숫자, [반올림 위치] )

ROUND 함수를 이용하여 다양한 위치에서 반올림 을 해봅시다.

SELECT  ROUND(1234.6578), 
        ROUND( 1234.6578, 0),
        ROUND(1234. 6578, 1),
        ROUND(1234. 6578, 2),
        ROUND(1234. 6578, -1),
        ROUND(1234. 6578, -2)
FROM DUAL;

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

특정 문자 지우기(TRIM, LTRIM, RTRIM)

특정문자를 지우고 싶을때는 TRIM, LTRIM, RTRIM 함수를 사용하시면 됩니다.

1. TRIM 함수

TRIM( [삭제 옵션], [삭제할 문자] FROM 문자열 )

삭제 옵션에는 왼쪽 글자를 지우는 LEADING, 오른쪽 글자를 지우는 TRAILING, 양쪽 글자를 지우는 BOTH 가 있이며,
생략시에는 양쪽 값이 모두 삭제됩니다.

이때, 삭제할 문자는 필수가 아니며, 삭제할 문자를 지정하지 않으면 공백을 지웁니다. 

SELECT  '[' || '   LOVE   YOU   ' || ']' AS ORIGINAL, 
        '[' || TRIM ('   LOVE   YOU   ') || ']' AS TRIM, 
        '[' || TRIM (LEADING FROM '   LOVE   YOU   ') || ']' AS TRIM_L, 
        '[' || TRIM (TRAILING FROM '   LOVE   YOU   ') || ']' AS TRIM_T, 
        '[' || TRIM (BOTH FROM '   LOVE   YOU   ') || ']' AS TRIM_B 
FROM DUAL;

이번에는 삭제할 문자열이 있는 경우를 살펴 봅시다.

SELECT  '[' || '***LOVE***YOU***' || ']' AS ORIGINAL, 
        '[' || TRIM ('*' FROM '***LOVE***YOU***') || ']' AS TRIM, 
        '[' || TRIM (LEADING '*' FROM '***LOVE***YOU***') || ']' AS TRIM_L, 
        '[' || TRIM (TRAILING '*' FROM '***LOVE***YOU***') || ']' AS TRIM_T, 
        '[' || TRIM (BOTH '*' FROM '***LOVE***YOU***') || ']' AS TRIM_B 
FROM DUAL;

2. LTRIM, RTRIM 함수

TRIM 함수 이외에  LTRIM, RTRIM 함수를 사용에 대한 예제도 살펴 봅시다.

LTRIM( 원본 문자열 데이터, [삭제할 문자 또는 문자열]  )
RTRIM(원본 문자열 데이터, [삭제할 문자 또는 문자열]  )

LTRIM, RTRIM 함수는 각각 왼쪽, 오른쪽의 지정 문자 또는 문자열을 삭제할 때 사용합니다. 삭제할 문자 또는 문자열을 지정하지 않으면 공백 문자를 삭제합니다.

SELECT  '[' || '   LOVE   YOU   ' || ']' AS ORIGINAL, 
        '[' || TRIM ('   LOVE   YOU   ') || ']' AS TRIM, 
        '[' || LTRIM ('   LOVE   YOU   ') || ']' AS LTRIM, 
        '[' || RTRIM ('   LOVE   YOU   ') || ']' AS RTRIM
FROM DUAL;

공백이 아닌 문자열이 있는 경우를 예제로 살펴 봅시다.

SELECT  '[' || '*_*LOVE*_*YOU*_*' || ']' AS ORIGINAL, 
        '[' || TRIM ('*_*LOVE*_*YOU*_*') || ']' AS TRIM, 
        '[' || LTRIM ('*_*LOVE*_*YOU*_*', '*_') || ']' AS LTRIM, 
        '[' || RTRIM ('*_*LOVE*_*YOU*_*', '*_') || ']' AS RTRIM
FROM DUAL;

실무에서는 데이터를 저장할때 저장할 데이터 앞이나, 끝에 의미 없는 공백을 제거하고 저장 한다거나, Login시 사용자의 실수로 입력된 공백이 있을 수 있어 이를 제거하고 비교할때 사용 됩니다.

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

[Oracle] 숫자 함수 - 나머지를 구하는 함수(MOD)

MOD 함수는 나눗셈을 수행하고 남은 나머지를 구하는 함수 입니다.

MOD ( 나머지를 구할 숫자, 나눌 숫자 )

예제를 통해 MOD 함수의 사용을 살펴 봅시다.

SELECT  MOD(100,2),
        MOD(99,2),
        MOD(3.14, 3),
        MOD(-99,2),
        MOD(-3.14,3)
FROM DUAL;

두 문자열 데이터 합치기(CONCAT)

두 문자열 데이터를 하나로 연결하고 표현하고 싶을때 CONCAT 함수를 사용하시면 됩니다. ENAME과 JOB을 합쳐서 출력해 봅시다. 이때, ENAME과 JOB의 구분이 잘 될수 있도록 중간에 ' - ' 를 넣어 봅시다.

SELECT ENAME, JOB, 
    CONCAT(ENAME, JOB),
    CONCAT(ENAME, CONCAT(' - ', JOB))
FROM EMP;

[참고] CONCAT 함수와 비슷한  '||' 연산자

'||' 연산자는 문자열 데이터를 연결하는 연산자로, CONCAT 함수와 비슷하게 사용할 수 있습니다.

위와 같은 결과 값을 갖도록 '||' 연산자를 이용한 쿼리를 만들어 보면 다음과 같습니다.

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

공백을 특정 문자로 채우기(LPAD, RPAD)

문자열에서 문자열 이외에 공간을 특정 문자로 채우고자 하면 LPAD, RPAD 함수를 사용하시면 됩니다.

LPAD( 문자열, 찾는 문자, 데이터의 자리수, [공백에 채울 문자] )
RPAD( 문자열, 찾는 문자, 데이터의 자리수, [공백에 채울 문자] )

예제를 통해 직접 알아 봅시다.

SELECT 'ORACLE', 
    LPAD('ORACLE', 10, '*'), RPAD('ORACLE', 10, '*'),
    LPAD('ORACLE', 10), RPAD('ORACLE', 10)
FROM DUAL;

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.