[Oracle] 14. 단일 행 함수 : 숫자, 날짜 함수 정리

숫자 함수

숫자 함수는 숫자를 인자로 사용하고 결과 값도 숫자를 반환하는 함수이다.

함수명	기능
round	n 자리까지 반올림한다. round(m,n) round(123.4567,3) -> 123.456
trunc	n 자리 미만을 절삭한다. trunc(m,n) trunc(123.4567,3) -> 123.456
mod	m을 n으로 나눈 나머지 계산한다. mod(m, n) mod(10, 4) -> 2
power	m의 n승 계산한다. power(m,n) power(2,4) -> 16
ceil	m보다 큰 가장 작은 정수를 찾는다. cell(m) cell(2.34) -> 3
floor	m보다 작은 가장 큰 수를 찾는다. floor(m) floor(2.34) -> 2
abs	m의 절대 값을 계산한다. abs(m) abs(-4) -> 4
sqrt	m의 제곱근을 계산한다. sqrt(m) sqrt(9) -> 3
sign	m이 음수일 때 -1, 양수일 때 1, 0이면 0을 반환한다. sign(m) sign(-3) -> -1

예제 1. 다양한 숫자 함수를 이용한 결과를 확인한다.

select round(98.765), trunc(98.765), round(98.765,2), trunc(98.765,2)

from dual;

select mod(19,3), mod(-19,3) from dual;

select ceil(3.5), floor(3.5) from dual;

예제 2. 10번 부서의 년봉을 계산한다. 단 100 단위 미만은 절삭한다.

select eno, ename, trunc(sal*12+nvl(comm,0),-2) 년봉

from emp

where dno='10';

날짜 함수와 날짜 연산의 이해

예제 3. 현재 날짜를 검색하고 날짜 출력 양식을 수정해 보자.

alter session set nls_date_format = 'YYYY/MM/DD:HH24:MI:SS';

select sysdate from dual;

alter session set nls_date_format = 'YYYY/MM/DD';

select sysdate from dual;

예제 4. 오늘 날짜, 입사 일자, 입사일로부터 오늘까지 기간, 입사일 이후 100째 되는날 등을 검색하고 날짜 연산의 결과를 살펴보자.

select sysdate 오늘, hdate 입사일, trunc(sysdate)-trunc(hdate)+1 근무일, hdate+99 "100일"

from emp;

날짜 + 숫자	날짜	일 수 이후 날짜
날짜 - 숫자	날짜	일 수 이전 날짜
날짜 + 숫자/24	날짜	시간을 더한 날짜
날짜 - 날짜	숫자	두 날짜 칸에 차(일수)

날짜 함수

함수명	기능
round	형식에 맞추어 반올림한 날짜를 반환한다. round(날짜, 형식) round(sysdate, 'DD') -> 2018/01/02	YYYY : 년도 DD : 날짜 HH : 시간
trunc	형식에 맞추어 절삭한 날짜를 반환한다. trunc(날짜, 형식) trunc(sysdate, 'YYYY') -> 2018/01/01	MI : 분 SS : 초
months_between	두 날짜간의 기간을 월수로 계산한다. months_between(날짜1, 날짜2) 날짜1 > 날짜2 인 경우 결과는 양수 months_between('2013/09/01','2013/01/01') -> 8
add_months	날짜에 n 달을 더한 날짜를 계산한다. add_months(날짜, n) add_months('2011/07/01', 23) -> 2013/06/01
next_day	날짜 이후 지정된 요일에 해당하는 날짜를 계산한다. next_day(날짜, 요일) 요일 표현은 'sun','일요일', 1과 같이 다양한 표현이 가능하다. next_day('2013/07/14', '일요일') -> 2013/07/21
last_day	날짜를 포함한 달의 마지막 날짜를 계산한다. last_day(날짜) last_day('2013/09/24') -> 2013/09/30

예제 5. 숫자와 날짜를 반올림하거나 잘라내고 출력해본다.

select sysdate, round(sysdate,'YY')년, round(sysdate,'MM')월, round(sysdate,'DD')일 from dual;

select sysdate, trunc(sysdate,'YY')년, trunc(sysdate,'MM')월, trunc(sysdate,'DD')일 from dual;

예제 6. 문시현이 오늘까지 일할 일수를 검색한다.

select ename, trunc(sysdate)-trunc(hdate)+1 day

from emp

where ename='문시현';

예제 7. 20번 부서 직원들이 현재까지 근무한 개월 수를 검색한다.

select eno, ename, trunc(months_between(sysdate,hdate)) 근무_개월

from emp

where dno = '20';

예제 8. 20번 부서원들이 입사 100일째 되는 날과 10년째 되는 날을 검색한다.

select eno, ename, hdate 입사일, hdate+99 "입사일", add_months(hdate,120) "10년"

from emp

where dno = '20';

예제 9. 20번 부서원들이 입사한 이후 첫 번째 일요일을 검색한다

select eno, ename, hdate, next_day(hdate, '일요일') Sunday

from emp

where dno = '20';

예제 10. 20번 부서원들의 입사한 달의 마지막 날짜와 입사한 달에 근무 일수를 검색한다.

select eno, ename, hdate, last_day(hdate) 마지막날, last_day(trunc(hdate))-trunc(hdate)+1 "마지막 근무"

from emp

where dno = '20';

실습

 1. 교수들이 부임한 달에 근무한 일수는 몇 일인지 검색한다.

select pname, hiredate, last_day(hiredate)-hiredate

from professor;

#그달의 마지막 날을 구하려면 last_day를 써야한다.

 2. 교수들의 오늘까지 근무한 주가 몇 주 인지 검색한다.

select pname, hiredate, trunc((next_day(sysdate, '토요일') - hiredate)/7,0)

from professor;

# sysdate로 오늘 날짜를 구한다.

# next_day로 이번주의 토요일까지 구한다.

# trunc로 소수점 제거

# 7로 나눠 주 계산

 3. 1991년에서 1995년 사이에 부임한 교수를 검색한다.

select pname, hiredate

from professor

where hiredate between '1991/01/01' and '1995/12/31';

 4. 학생들의 4.5 환산 평점을 검색한다. ( 단 소수 이하 둘째 자리까지)

select sname, trunc((avr/4*4.5),2)

from student;

# 소수점 제한해주는 trunc 함수 사용 2번째자리까지만 사용

 5. 사원들이 일한 날짜에 대해서만 급여를 받는다면 급여가 현재와 동일하다는 조건에서 입사한 달에 급여는 얼마나 지급되었을지 검색한다.

select ename, sal/(last_day(hdate)-trunc(hdate,'MM')+1)*(last_day(hdate)-hdate+1)

from emp;

# (last_day(hdate)-hdate+1) 그달 마지막일수 - 입사일 + 1

# (last_day(hdate)-trunc(hdate,'MM')+1) 그달 마지막일수 - 입사일 달단위로 버림 처리 뒤 +1

 6. 사원들의 오늘까지 근무 기간이 몇 년 몇 개월 몇 일인지 검색한다.

select ename, hdate, trunc((sysdate - hdate)/365,0) 년, trunc(months_between(sysdate,hdate),0) 월, trunc(sysdate-hdate,0) 일

from emp;