23.10.10. Oracle SQL 숫자 함수, 날짜 함수

■ 숫자 함수

ex) (PL/SQL) ERP(재무, 회계) 
 round (): 지정된 소수점 자릿수 값을 반올림 하는 함수 

SELECT 
    round(45.926, 2) "소수점 두번째 반올림", 
    round(45.926, 1) "소수점 첫번째 반올림", 
    round(45.926, 0) "일의 자리 반올림" , 
    round(45.926), --생략 가능  
    round(45.926, -1) "십의 자리 반올림",
    round(55.926, -2) "백의 자리 반올림"
FROM dual;

SELECT round(((salary*12) + (salary*12*nvl(commission_pct, 0))) + 0.123, 0)
FROM hr.employees ;

- trunc() : 지정된 소수점 자릿수 값을 버리는 함수 

SELECT 
    trunc(45.926, 2), trunc(45.926, 1), trunc(45.926, 0), trunc(45.926), --생략 가능  
    trunc(45.926, -1), trunc(55.926, -2)
FROM dual;

- ceil : 숫자값을 가장 큰 정수로 반환하는 함수. 즉, 소수점이 있는 경우 무조건 '올려서' 다음 정수값을 반환한다.

SELECT round(10.1, 0), ceil(10.1), ceil(10.0), ceil(10.000001)
FROM dual;

- floor : 숫자값을 가장 작은 정수로 반환하는 함수. 즉 소수점이 있는 경우 무조건 '내려서' 값을 내려서 정수값을 반환한다.

SELECT trunc(10.1, 0), floor(10.1), floor(10.000001), floor(-10.00001)
FROM dual;

- mod : 어떤 값을 나눈 나머지를 반환하는 함수 ex) 짝수, 홀수, 윤년계산, hash 알고리즘 등

SELECT 12/5, mod(12, 5) 
FROM dual;

- power : 거듭제곱

SELECT 2*2*2, power(2,3)
FROM dual;

- sqrt : 루트 

SELECT sqrt(9)
FROM dual;

■ 날짜 함수 
- sysdate : 현재 '서버' 날짜를 리턴하는 함수
- systimestamp : 현재 '서버' 날짜, 시간, 타임존을 리턴하는 함수
- current_date : 현재 '클라이언트'의 날짜를 리턴하는 함수
- current_timestamp : 현재 '클라이언트'의 날짜, 시간, 타임존을 리턴하는 함수
- localtimestamp : 현재 '클라이언트'의 날짜, 시간을 리턴하는 함수

SELECT
    sysdate, 
    systimestamp, 
    current_date, 
    current_timestamp,
    localtimestamp
FROM dual;

 

client ----------------- server(db server 한국지역) 

ALTER SESSION SET TIME_ZONE = '+09:00';

- 날짜계산 
1) 날짜 + 숫자(일수) = 날짜
2) 날짜 - 숫자(일수) = 날짜
3) 날짜 - 날짜 = 숫자(일수)
4) 날짜 + 날짜 = 오류
5) 날짜 + 시간 = 날짜 시간
6) 날짜 - 시간 = 날짜 시간

SELECT employee_id, hire_date, hire_date + 100
FROM hr.employees;

SELECT sysdate, sysdate + 100, sysdate -100 
FROM dual;

SELECT employee_id, hire_date, trunc(sysdate - hire_date)
FROM hr.employees;

SELECT 
    systimestamp, 
    systimestamp + 10/24 "10시간 더하기" , -- systimestamp + 10/24 : 10시간을 더하기 / 10/24: 시간의 구분자, 시/24
    to_char(systimestamp + 10/24, 'yyyy-mm-dd hh24:mi:ss') "문자 타입으로 변형" ,-- date형을 문자타입(to_char)으로 변경하기
    to_char(systimestamp + 10/(24*60), 'yyyy-mm-dd hh24:mi:ss') "분 단위" , -- 분/(24*60), 분/1400
    to_char(systimestamp + 10/(24*60*60), 'yyyy-mm-dd hh24:mi:ss') "초 단위" -- 초/(24*60*60) , 초/86400
FROM dual;

- months_between : 두 날짜간의 달수(개월수)를 리턴하는 함수

SELECT 
    employee_id, 
    hire_date "입사일", 
    trunc(sysdate - hire_date) "근무일수" ,
    trunc(months_between(sysdate, hire_date))"근무 개월수" ,
    trunc(months_between(hire_date, sysdate)) "근무 달수" ,
    trunc(months_between(sysdate, hire_date)/12) "근무 연수"
FROM hr.employees;

- add_months : 달수를 더하거나 빼는 함수 

SELECT 
    sysdate , 
    add_months(sysdate, 6) ,
    add_months(sysdate, -1)
FROM dual;

- next_day : 입력한 날짜를 기준으로 찾고자 하는 '요일'의 첫번째 날짜를 반환하는 함수 

SELECT
    sysdate,
    next_day(sysdate, '토요일')
FROM dual;

- last_day : 기준날짜달의 마지막 날짜를 리턴하는 함수 

SELECT 
    sysdate, 
    last_day(sysdate) ,
    last_day(add_months(sysdate, 2))
FROM dual;

[문제1] 20년 이상 근무한 사원들의 사원번호(employee_id), 입사년도(hire_date), 근무개월수를 출력하세요.

-- 년도수
SELECT 
    employee_id "입사년도", 
    hire_date "입사년도", 
    trunc(months_between(sysdate, hire_date))"근무 개월수"
FROM hr.employees 
WHERE trunc(months_between(sysdate, hire_date)/12) >= 20 ;

-- 개월수
SELECT 
    employee_id "입사년도", 
    hire_date "입사년도", 
    trunc(months_between(sysdate, hire_date))"근무 개월수"
FROM hr.employees 
WHERE trunc(months_between(sysdate, hire_date)) >= 240 ;

[문제2] 사원의 last_name, hire_date 및 근무 6개월 후 월요일에 해당하는 날짜를 출력하세요

SELECT 
    last_name, 
    hire_date, 
    add_months(hire_date, 6) "6개월 후",
    next_day(add_months(hire_date, 6), '월요일') "6개월 후 월요일"
FROM hr.employees;