📌 숫자형 처리함수
- 숫자데이터를 처리하기 위한 함수
- 종류
1) round : 지정한 소숫점 자리로 반올림 처리를 해주는 함수
round(데이터, 자릿수)
자릿수 + 값은 소숫점 이하 자리수를 말한다
ex) round(45.25322,2) ==> 45.25
값은 소숫점을 기준으로 2,3,4이면 100자리, 1000자리, 10000자리로 반올림 처리하는
것을 말한다.
ex) round(2436435,-3) ==> 2436000
2) trunc : 지정한 소숫점 자리까지 남기고 절삭처리
trunc(데이터, 자리수)
자리수는 round와 동일하다. 마이너스일때, 10자리, 100자리 등의 데이터로 절삭 처리한다.
cf) floor() : 자리수 절삭이 아니고, 정수형 소숫점 이하 절삭
SELECT round(24232.2535,3),
round(24232.2535,1),
round(24235.2535,-1),
round(24272.2535,-2),
trunc(2423.23,-1),
trunc(2423.23,1),
trunc(2423.23,-2)
FROM dual; ex) 급여를 부서번호(10==>10%, 20==>20%)기준으로 인상을 하기로 했다.
현재 급여와 인상된 급여를 처리하되 100자리 단위로 절삭하여 사원명과 함께 출력하세요.
SELECT ename, trunc(sal,2), sal*(deptno/100) 인상분, trunc(sal+(sal*(deptno/100)),-2) 인상된급여
FROM emp;3) mod : 데이터베이스에서 산술연산자 %대신 쓰이는 나머지 처리 함수를 말한다.
mod(데이터, m) : 데이터를 m으로 나눈 나머지 값
ex) mod(10,3) ==> 1
SELECT mod(10,3) s1, mod(10,2) s2, mod(10,3) FROM dual;ex) 사번이 짝수인 데이터를 사번, 구분자(0/1), 이름으로 출력하세요
SELECT empno, MOD(empno,2)div, ename
FROM EMP e
WHERE mod(empno,2) = 0;4) ceil : 지정한 값보다 큰 수 중에서 가장 작은 정수
소숫점이하의 내용에 대하여 올림 정수
정수형 올림(소숫점 이하 데이터)
5) floor : 지정한 값보다 작은 수 중에서 가장 큰 정수
소숫점 이하의 내용에 대하여 내림 정수
정수형 내림(소숫점 이하 데이터)
SELECT ceil(253.243) num01, floor(253.243) num02 FROM dual;
SELECT *
FROM emp;ex) 현재 급여를 12로 나눈 월급여를 내림/올림 결과에 따라 출력하세요
사원명, 급여, 월급여(내림), 월급여(올림)
SELECT ename, sal, trunc(sal/12) "월급여(내림)", CEIL(sal/12) "월급여(올림)"
FROM emp;📌 날짜 함수
📖 날짜형 데이터(시간을 포함)
1/1000 => 1초 => (60)1분 => (60)1시간 => (24)1일 => (28,30,31) 1월 => (12)1냔
- 기본 연산
날짜 + 1 : 해당 날짜의 1일 이후 처리(시간을 포함해서 24시간 이후)
날짜 -1 : 해당 날짜의 1일 이전 처리
날짜 +1/24 : 해당 날짜의 1시간 이후 처리
날짜 +200/24/60 : 해당 시간의 분단위로 처리 200분 후 시간 처리.
ex) 현재 시간이 15:18일때, 9시간 이후 날짜는 내일이 된다. 날짜 + 9/24
월단위가 28,30,31 경우로 나누어지고, 해당 월에 따라서 날짜가 다르기 때문에 필요한 함수
add_months(날짜, 추가할월)로 하면 월단위로 처리가 된다. 3. 기본 함수
sysdate : 현재 시간과 날짜를 날짜형으로 나타내는 키워드
SELECT sysdate 현재,
sysdate + 1 내일,
sysdate -1 전날,
sysdate +(10/24) "10시간 이후",
add_months(sysdate,1) "한달 후"
FROM dual; ex) 사원번호, 사원명, 입사일(입사전 8시간, 입사일), 인턴기간(입사후 120일, 근무일수)를 아래와 같이 출력하세요.
SELECT empno,
ename,
(hiredate-8/24) 입사 8시간 전,
hiredate 입사일,
hiredate+120 인턴기간(입사후 120일),
floor(sysdate-hiredate) 근무일수
FROM emp;✍ month_between(날짜1, 날짜2) : 날짜 사이의 개월 수를 표기한다
5/19, 6/19 => 1개월, 15일 => 0.5개월
- 소숫점이하의 데이터를 floor로 절삭을 하는 경우가 많다. ps) 월에 따라서 날짜가 다르기 때문에, 정확하게 월에 따른 개월수를 계산해준다.
2월 ~ 3월 28일을 1로 계산, 3월 ~ 4월 31일을 1로 계산
SELECT ename,
hiredate,
months_between(sysdate,hiredate) "근무개월수",
floor(months_between(sysdate, hiredate)) "근무개월수2",
floor(months_between(sysdate, hiredate)/12) "근무연한"
FROM emp;ex) 오늘로부터 100일 후, 개월수를 출력(dual, 소숫점이하 표기, 절삭하기)
SELECT floor(MONTHS_BETWEEN(sysdate+100, sysdate))
FROM dual;
-------------------------------------------------------------------
SELECT sysdate,
sysdate+100,
MONTHS_BETWEEN(sysdate+100, sysdate) "100일후 개월수1",
floor(MONTHS_BETWEEN(sysdate+100, sysdate)) "100일후 개월수2"
FROM dual;✍ add_months(날짜형데이터, 추가할개월수) : 해당 날짜에 개월을 더한 날짜 계산
SELECT sysdate, add_months(sysdate, 4)
FROM dual; --ex) 사원정보를 통해 부서별로 인턴기간을 다르게 처리하다고 한다. 인턴기간의 만료일
-- 10==> 1개월, 20 ==> 2개월, .. 30 ==> 3개월
SELECT ename, deptno, hiredate, deptno/10 "인턴기간(개월)", add_months(hiredate,
deptno/10)
FROM emp;✍ next_day : 해당 일을 기준으로 명시된 요일의 다음 날짜를 반환.
형식: next_day(지정한 날짜, '요일') : 지정한 날짜로부터 가장 빠른 요일의 날짜
SELECT next_day(sysdate, '일') "가장 빠른 일요일"
FROM dual; ex) 사원들이 입사하고 처음 토요일 여행을 가기로 했다. 사원명, 입사일, 첫번째 토요일을 출력하세요.
SELECT ename "사원명", hiredate "입사일", next_day(hiredate, '토') "첫번째 토요일"
FROM emp; - @@월 @@째 수요일 등을 미국에서 쓰이는 공휴일 처리 방식인, 한국의 선거일의 경우
임기종료 @@주째 전 수요일 같이 날짜를 요일에 대한 날짜를 계산할 때 사용된다. - 앞으로 다가올 두번째 수요일 next_date(next_day(sysdate, '수'),'수')로 중첩함수를 사용하여야 한다.
✍ last_day : 해당 날짜가 속한 마지막 날짜
SELECT last_day(sysdate) "이번달 마지막",
last_day(sysdate)+1 "다음달 일"
FROM dual; ex) 사원의 첫급여일이 다음달 첫날로 지정하였다. 급여일과 근무일수를 출력하세요.
SELECT ename "사원명",
hiredate "입사일",
last_day(hiredate)+1 "첫급여일",
(last_day(hiredate)+1)-hiredate "입사월근무일수",
ceil(last_day(hiredate)-hiredate+1) "입사월근무일수2"
FROM emp;- 사원이 입사한 월의 두번째 수요일
add_months(), last_day(), next_day()의 중첩 사용을 통해서 처리하여야 한다
📖 날짜 데이터의 round, trunc, floor, ceil 함수
함수를 적용해서 날짜 계산은 소숫점 이하 나타날 때가 많다.
1일 ==> 현재시간과 함께 계산되기 때문에 정확하게 안되는 경우가 있다.
ex) 5/22 0시 기준 ==> 현재 시간 sysdate로 처리한 시간도 포함되기 때문에 ceil()올림이나, floor()내림처리하지 않으면 소숫점 이하의 시간 내용도 포함되게 된다.
add_months, months_between : 월이 1이 되기 때문에 15일 0.5 12시간 0.55 등으로 기준되는 단위에서 소숫점 이하로 처리되는 경우가 많다.
📖 MONTHS_BETWEEN를 사용할 때, 소숫점 이하가 나오는 이유??
- 1이라는 월단위 월마다 차이가 있고, 날짜는 1단위 처리되지만 월로 1단위로 처리되어 15인 경우에 0.5로 처리되고 여기서 하위 시간 즉, 12시간인 경우에 0.05 식으로 세분화된 시간적 차이가 있기 때문에 사용된다.
- 이 때, ~부터 시작하여 정확하게 @@개월, @@개월차의 차이가 생긴다.
@@개월 @@일인 경우에 @@일 부분을 절삭할 때는 floor()함수-내림처리를 이용하고
@@개월째 즉, 해당 개월이 이후아직 마지막 날짜가 남아있지만 차수로 계산할 때는 ceil() 올림처리 활용한다.
📌 데이터의 타입 변환
-
oracle에서는 크게, 문자열형, 숫자형, 날짜형으로 나누어진다.
-
각 데이터는 유형에 따라서 변환이 일어난다.
문자열 ==> 숫자, 숫자 ==> 문자열
숫자 ==> 날짜, 날짜 ==> 숫자
날짜 ==> 문자열, 문자열 ==> 날짜 -
형변환의 유형
1) 묵시적 형변환 : oracle 서버에서 자동적으로 형변환을 하여 처리한다.
2) 명시적 형변환 : 변환 함수를 통해서 명시적으로 형변환을 처리한다.📖 묵시적 형변환
-
묵시적인 데이터 타입 변환은 정확한 연산을 위하여 오라클서버 시스템 내부에서 타입을 내부적으로 변환해서 처리 해주는 경우를 말한다.
-- 문자열을 묵시적으로 숫자로 변환해서 조건처리
SELECT *
FROM EMP e
WHERE sal = '800';
-- 명시적 형변환인 to_number()함수를 통해서 처리한다.
SELECT *
FROM EMP e
WHERE sal = to_number('800'); -- 날짜형의 묵시적 형변환
SELECT ename, hiredate
FROM EMP e
WHERE hiredate LIKE '%81%';
-- 묵시적으로 hiredate는 to_char()가 적용이 된 내용으로 비교한다.
-- 주의) 1981형태의 데이터는 기본 묵시형 변환에 포함되어 있지 않으므로 to_char(hiredate,'YYYY')로 설정하여 비교하여야 한다.
SELECT ename, hiredate, to_char(hiredate)
FROM EMP e
WHERE to_char(hiredate) LIKE '%81%';📖 명시적 데이터 변환
- 사용자가 데이터 타입 변수 함수를 이용하여 명시적으로 데이터 타입을 변환처리하는 것을 말한다.
- 명시적 데이터 타입 변환 함수
1) to_char() : 숫자/날짜 데이터를 문자형으로 변환
2) to_number() : 문자형데이터를 숫자형으로 변환
3) to_date() : 문자열데이터를 날짜형으로 변환
✍ to_char 함수
to_char(number|date타입, 'format')
1) 날짜형 데이터의 문자열 format 처리
SELECT sysdate,
to_char(sysdate, 'CC'), -- 세기 표현
to_char(sysdate, 'YYYY'), -- 연도 표현
to_char(sysdate, 'MM'), -- 월 표기
to_char(sysdate, 'DD'), -- 일 표기
to_char(sysdate, 'DAY'), -- 요일 표기
to_Char(Sysdate, 'YYYY/MM/DD') -- 현재날짜를 YYYY/MM/DD 표기
FROM dual; -- ex) to_char 활용하여, 1980에 입사한 사원의 이름, 입사년도, 입사월, 입사일을 출력
SELECT ename 사원이름, hiredate,
to_char(hiredate, 'YYYY') 입사년도, to_char(hiredate, 'MM') 입사월, to_char(hiredate, 'DD') 입사일
FROM emp
WHERE to_char(hiredate, 'YYYY') = '1980';2) 기타 날짜 관련 함수 배개변수 형식
- to_char(날짜형데이터, 형식)
- 'Q' : 분기, 'MM' : 월, 'MONTH' : 월 영문,
- 'WW' : 연단위 주표기 @@@년도 @@@째주,
- 'W' : 월단위 주표기 @@@월의 @@@쨰주,
- 'DD' : 일표기,
- 'DY' | 'DAY' : 요일표기
SELECT ename,
hiredate,
to_char(hiredate, 'Q') "분기", to_char(hiredate, 'MONTH') "월",
to_char(hiredate, 'MM') "월", to_char(hiredate, 'WW') "연단위(주)",
to_char(hiredate, 'W') "월단위(주)", to_char(hiredate, 'DAY') "요일"
FROM emp;
-- ex) 1981년도 1/4 분기에 입사한 사원이 이름, 입사년도, 월단위 주, 월, 날짜를 표기하세요.
SELECT ename, hiredate,
to_char(hiredate, 'YYYY') "입사년도", to_char(hiredate, 'W') "월단위(주)", to_char(hiredate, 'MONTH') "월", to_char(hiredate, 'DAY') "요일", to_char(hiredate, 'Q') "분기"
FROM emp
--WHERE to_char(hiredate, 'YYYY') = '1981' AND to_char(hiredate, 'Q') = 1;
WHERE to_char(hiredate, 'YYYY Q') = '1981 1';3) to_char(숫자형, '출력형식') : 숫자형데이터를 지정한 출력형식으로 문자열을 출력
- 출력 형식
$9999(달러형표기), 9999.99(소숫점 두자리),'9,999,999' 해당 자리수 안에서 첫단위 ,(콤마) 표시
00000 경우 자리수를 채우고 나머지는 0으로 표기
99999 경우 기본 자리수에 맞게 처리
SELECT ename,
sal,
to_char(sal, '$9999') "달러표기",
to_char(sal, '9999.99') "소숫점 표기",
to_char(sal, '9,999,999') "첫단위 콤마"
FROM emp; --ex) 사원명, 급여, 급여+보너스(천단위 콤마표기) 총계로 출력하세요.
SELECT ename "사원명",
sal "급여",
comm "보너스",
to_char(sal+comm, '9,999,999') "급여+보너스"
FROM emp;
SELECT ename "사원명",
sal "급여",
comm "보너스",
to_char(sal + nvl(comm,0),'9,999,999') "급여+보너스"
FROM emp;4) to_number
- 숫자 형태의 문자열로 구성된 데이터를 숫자로 변환 처리
- 형식
to_number('숫자형태문자열')
ps) 프로그래밍에서 입력되는 데이터는 문자열이기 때문에, 숫자형의 데이터는 형변환
함수를 통해서 처리하는 경우가 많다.
-- 문자열로 '2000'인 데이터를 이용하여 2000이상인 급여 데이터를 조회 처리
SELECT *
FROM emp
WHERE sal >= to_number('2000');
-- 입사일이 3분기, 4분기 데이터를 검색하고자 한다.
SELECT ename, hiredate, to_char(hiredate, 'Q') "분기",
to_number(to_char(hiredate, 'Q')) "분기(숫자)"
FROM EMP
WHERE to_number(to_char(hiredate, 'Q')) >= 3;
-- ex) 입사일이 15일 이후인 사원들을 출력하세요.
SELECT *
FROM EMP e
WHERE to_number(to_char(hiredate, 'DD')) >= 15;📌 중첩함수
- 가장 내부에 있는 함수 F1의 결과값을 다음 함수 F2에 인수로 적용하고, 다시 F2의 결과값을 F3의 인수로 사용할 때, F1, F2, F3라는 함수를 중첩 사용한다고 한다.
- 기본형식 : F3(F2(F1(데이터/컬럼, arg1),arg2),arg3)
-- 입사년도 1981 ==> 올해연도로 변환하여 데이터를 출력
SELECT ename,
hiredate,
to_char(sysdate, 'YYYY') || to_char(hiredate, 'MMDD') "올해입사",
to_date(to_char(sysdate, 'YYYY') || to_char(hiredate, 'MMDD')) "날짜형변경"
FROM emp;📌 날짜형 데이터
형식
date : 날짜와 시간을 처리하는 데이터 유형포함 내용
YYYY : 연도
MM : 월
DD : 일
DAY : 요일
AM/PM : 오전/오후 A.M/P.M
HH/HH12 : 시간(1~12) 표시
HH24 : 24시간(0~23) 표시
MI : 분
SS : 초형변환 형식
1) to_char(날짜데이터, '형식')
2) to_date('문자열데이터', '형식지정')
날짜 형식으로 데이터를 등록, 수정할 때, 조건문에서 날짜를 검색할 때, 주로 활용된다.
cf) 회원의 생일을 날짜 타입으로 설정할 때,
생일[YYYY-MM-DD] 이렇게 문자열로 된 데이터를 날짜로 변경해서 입력을 해야 한다.
to_date('1995-12-12','YYYY-MM-DD')
입력하는 문자열이 어떤 형식으로 날짜데이터에 입력하는지를 선언하고, 형변환에 의해
변환처리하여 입력/조회/수정할 수 있게 한다.
SELECT to_char(sysdate, 'AM HH:MI:SS') "현재시간(12)"
FROM dual;
-- ex) 사원정보테이블에서 사원명, 입사일+현재시간 표현 @@@년 @@월 @@일
-- 24시간표 @@시 @@분 @@초 표현하여 출력하세요.
SELECT ename,
hiredate,
to_char(hiredate,'YYYY"년" MM"월" DD"일"') "날짜
to_char(sysdate,'HH24"시" MI"분" SS"초"') "시간",
to_char(hiredate,'YYYY"년" MM"월" DD"일"') || to_char(sysdate,'HH24"시" MI"분" SS"초"') "날짜와 현재시간",
to_date( to_char(hiredate,'YYYYMMDD ') || to_char(sysdate,'HH24MISS'),'YYYYMMDD HH24MISS') "날짜형 변경"
FROM emp;📌 숫자형데이터를 문자열형식 변환처리
- 기본형식: to_char(숫자형 데이터, '형식')
- 주요 형식
해당 자리수 이상의 데이터가 처리될 때 ###으로 표기된다.
9999 : 기본 자리수에 맞게 처리
0000 : 해당 자리수 이하일 때 0으로 채워짐
$ : 앞에 $표기 처리, \
. : 특정한 자리를 표기 소숫점
, : x특정한 위치,를 표기(천단위 표기)
SELECT ename,
sal,
to_char(sal, '99999') "형식1",
to_char(sal, '00000') "형식2",
to_char(sal, '$9999') "형식3",
to_char(sal, '9,999') "형식4",
to_char(sal, '9,999.999') "형식5",
to_char(sal*1000000, '9,999.999') "형식6"
FROM emp;
개발자국