SYSDATE 의 연산 : SYSDATE는 기본적인 숫자연산이 가능하다. 단, 이때 입력되는 정수값은 일 수로 처리된다.
SELECT SYSDATE,SYSDATE +1, SYSDATE +2/24
FROM DUAL;
1일 2시간 3분 4초 후를 나타낸다면,
SELECT SYSDATE, sysdate+1+2/24+3/(24*60)+4/(24*60*60)
FROM DUAL;
현재일로 부터 1년 2개월 3일 4시간 5분 6초 후를 나타낼 때, 다음과 같은 함수도 있다.
SELECT SYSDATE,
sysdate +
TO_YMINTERVAL('01-02')+
TO_DSINTERVAL('003 04:05:06')
FROM dual;
TO_YMINTERVAL : '년 - 월' 단위로 입력된 숫자를 Date타입으로 바꾼다.
TO_DSINTERVAL : '일 시:분:초' 단위로 입력된 숫자를 Date타입으로 바꾼다.
이 두 함수를 이용하면 date 값의 변화를 쉽게 할 수 있다.
변환함수 : 날짜에 대한 값은 상당히 다변형으로 자주 요구된다. 이를 편리하게 처리하기 위해 다음과 같은 함수가 지원된다.
TO_CHAR : 날짜, 숫자를 문자로 변경
TO_DATE : 문자를 날짜로 변경
TO_NUMBER : 문자를 숫자로 변경
EXTRACT : DATE 타입 중 원하는 값을 NUMBER형으로 반환
연도 가져오기
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY'), TO_CHAR(SYSDATE, 'Year'), EXTRACT(year FROM sysdate)FROM DUAL;
문자와 날짜형은 왼쪽으로 정렬 되고 숫자형은 우측으로 정렬이 된다.
달 가져오기
SELECT SYSDATE, TO_CHAR(SYSDATE, 'MM'), TO_CHAR(SYSDATE, 'Month'), EXTRACT(month FROM sysdate)
FROM DUAL;
일 가져오기
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD'), TO_CHAR(SYSDATE, 'Day'), TO_CHAR(SYSDATE, 'Dy'), EXTRACT(Day FROM sysdate)
FROM DUAL;
시간 가져오기
SELECT SYSDATE, TO_CHAR(SYSDATE, 'HH'),TO_CHAR(SYSDATE, 'HHAM'),TO_CHAR(SYSDATE, 'HH24') FROM DUAL;
분 가져오기
SELECT SYSDATE, TO_CHAR(SYSDATE, 'MI'),TO_CHAR(SYSDATE, 'SS'),TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL;
초 가져오기
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS DAY') FROM DUAL;
이를 응용해서 양식을 원하는데로 바꿀 수도 있다.
SELECT TO_CHAR(SYSDATE,'YYYY')||'년'||TO_CHAR(SYSDATE,'MM')||'월'|| TO_CHAR(SYSDATE,'DD')||'일'|| TO_CHAR(SYSDATE,'HH24')||'시'||TO_CHAR(SYSDATE,'MI')||'분'||TO_CHAR(SYSDATE,'SS')||'초'||TO_CHAR(SYSDATE,'DAY')
FROM DUAL;
날짜를 변환하는데 많이쓰이는 몇몇 수학함수를 알아보자
CEIL : 입력값 보다 높은 최소의 정수를 반환한다.
FLOOR : 입력값보다 낮은 최대의 정수를 반환한다.
MOD : 앞의 입력값을 뒤의 입력값으로 나눈 나머지를 반환한다.
SELECT 1.9,CEIL(1.9),FLOOR(1.9)
FROM DUAL;
SELECT 70/60 ,MOD(70, 60),FLOOR(70/60)
FROM DUAL;
이를 이용하여 수정이 용의하도록 바꿀 수 있다. 일자와 시간을 초로 변환해 보자 (1일 2시간 3분 4초)
SELECT 1 * (24*60*60) + 2 * (60*60) + 3*60 + 4
FROM DUAL;
이를 다시 날자형으로 변환하자.
SELECT 93784,MOD(93784, 60) "초",MOD(FLOOR(93784/60),60) "분"
,MOD(FLOOR((93784/60)/60),24) "시",FLOOR(((93784/60)/60)/24) "일"
FROM DUAL;
예제) Scott계정의 emp에서 현재날짜까지 근무일수를 구해라
SELECT ENAME, HIREDATE, SYSDATE,
FLOOR((SYSDATE - HIREDATE) * (24*60*60) / (24*60*60)) "일",
FLOOR( MOD((SYSDATE - HIREDATE) * (24*60*60) , (60*60*24)) / (60*60) ) "시간",
MOD( FLOOR((SYSDATE - HIREDATE) * (24*60*60) / 60) , 60) "분",
MOD((SYSDATE - HIREDATE) * (24*60*60) , 60) "초"
FROM EMP;
이를 년,월,일로 구해보자.
SELECT ename, hiredate,SYSDATE,(SYSDATE-hiredate),
FLOOR((SYSDATE-hiredate)/365)||'년 '||
FLOOR(MOD((SYSDATE-hiredate),365)/30)||'월 '||
FLOOR(MOD(MOD((SYSDATE-hiredate),365),30)/1)||'일 ' ||
FLOOR(MOD(MOD(MOD((SYSDATE-hiredate),365),30),1)/(1/24))||'시 ' ||
FLOOR(MOD(MOD(MOD(MOD((SYSDATE-hiredate),365),30),1),(1/24))/(1/(24*60)))||'분 ' ||
FLOOR(MOD(MOD(MOD(MOD(MOD((SYSDATE-hiredate),365),30),1),(1/24)),(1/(24*60))) / (1/(24*60*60))) ||'초 '
"근무기간 (년=365일 월=30일) "
FROM EMP;
MONTHS_BETWEEN : 두 DATE타입 값 간의 차이를 반환한다. 이때 반환 값은 달 이다.
SELECT ename,SYSDATE,hiredate, MONTHS_BETWEEN(SYSDATE,hiredate)
FROM EMP;
ADD_MONTHS : 입력받은 DATE 타입 값에 입력받은 값을 달로 더한다. 기본 연산인 일자로 연산할 시, 달의 변동에 따라 신경을 써줘야 하는 반면에 직접 달의 날자만을 바꿀 수 있어 편리하다. 입력받은 DATE 값의 달의 마지막날로 인한 변환 시에는 그달의 마지막 일로 처리된다.
SELECT '2009-01-31', ADD_MONTHS('2009-01-31',1)
FROM DUAL;
SELECT ename, hiredate,ADD_MONTHS(hiredate,3), ADD_MONTHS(hiredate,-3)
FROM EMP;
NEXT_DAY : 요일을 입력받아 다음에 오는 요일의 날자를 반환한다.
LAST_DAY : 입력받은 날의 달 값의 마지막 날을 반환한다.
SELECT SYSDATE,NEXT_DAY(SYSDATE,'수'),NEXT_DAY(SYSDATE,'금'),LAST_DAY(sysdate)FROM dual;
이주홍이 오늘 군대에 입대한다. 복무기간이 2년일 때 제대일자는 언제인가? 복무 기간동안 먹어야하는 짬밥 수는?
SELECT SYSDATE "입대",ADD_MONTHS(SYSDATE,24) "전역", (ADD_MONTHS(SYSDATE,24)-sysdate )*3 "짬밥"
FROM dual;
CASE : CASE WHEN THEN ELSE END 양식으로 쓰인다. CASE 문뒤에 조건이 올 때에는 WHEN절의 값과 같아야 하며, CASE WHEN 뒤로 조건이 올 때에는 값을 기준으로 비교가 가능하다.
[기본형]
CASE 대상값 WHEN 비교값1 THEN 처리1
WHEN 비교값2 THEN 처리2
.........
ELSE 디폴트 처리
END
[검색창]
CASE WHEN 비교값1 THEN 처리1
WHEN 비교값2 THEN 처리2
.........
ELSE 디폴트 처리
END
SELECT saname,jubun,
case SUBSTR(jubun,7,1)
WHEN '1' THEN '남'
WHEN '3' then '남'
else '여'
END "성별",
case WHEN SUBSTR(jubun,7,1) IN ('1','3')
THEN '남'
else '여'
end "성별2",
DECODE (SUBSTR(jubun,7,1) ,'1', '남','3','남' ,'여')
"성별3"
FROM tbl_sawon;
이제 위의 함수들을 이용하여 기존의 TBL_SAWON 테이블에서 사원번호 사원명 주민번호 성별 현재나이 입사일을 검색해 보자.
SELECT sano "사원번호",saname "사원명",SUBSTR(jubun,1,6)||'-'||SUBSTR(jubun,7) "주민번호", case when SUBSTR(jubun,7,1) IN ('1','3') THEN '남' ELSE '여' end "성별",
EXTRACT (year FROM sysdate) -
(TO_NUMBER(
case SUBSTR(jubun,1,1) WHEN '0'
THEN case WHEN SUBSTR(jubun,7,1) IN ('3','4')
then TO_NUMBER('20'||SUBSTR(jubun,1,2))
ELSE TO_NUMBER('19'||SUBSTR(jubun,1,2))
end
ELSE TO_NUMBER('19'||SUBSTR(jubun,1,2))
end) ) +1
"현재나이",
hiredate "입사일"
FROM tbl_sawon;
이에 정년퇴직일, 근무일수, 남은일수을 추가해 보자. 우선 SYS 에서 GRANT CREATE VIEW TO scott; 명령으로 Create View 권한을 할당하여 뷰를 생성해보자.
CREATE OR REPLACE VIEW view_sawon
AS
(SELECT V.* , FLOOR(SYSDATE-입사일) "근무일수", FLOOR(정년퇴직일 - SYSDATE) "남은일수"
FROM (SELECT T.*,
TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE,12*(60-현재나이)),'YYYY')||'-'||TO_CHAR(입사일,'MM-DD')) "정년퇴직일"
FROM (SELECT sano "사원번호",saname "사원명",SUBSTR(jubun,1,6)||'-'||SUBSTR(jubun,7) "주민번호",
case when SUBSTR(jubun,7,1) IN ('1','3') THEN '남' ELSE '여' end "성별",
EXTRACT (year FROM SYSDATE) -
(TO_NUMBER(
case SUBSTR(jubun,1,1) WHEN '0'
THEN case WHEN SUBSTR(jubun,7,1) IN ('3','4')
then TO_NUMBER('20'||SUBSTR(jubun,1,2))
ELSE TO_NUMBER('19'||SUBSTR(jubun,1,2))
end
ELSE TO_NUMBER('19'||SUBSTR(jubun,1,2))
end) ) +1
"현재나이", hiredate "입사일"
FROM tbl_sawon)T)V);
SELECT * FROM view_sawon;
댓글 없음:
댓글 쓰기