sql 숫자 함수와 날짜 연산 ,날짜함수,변환함수

숫자 함수

함수명	기능	함수명	기능
ROUND	반올림해서 소수 이하n 자리까지 출력한다. ROUND(m, n)	CEIL	m 보다 큰 가장 작은 정수를 검색한다. CEIL(m)
	ROUND(123.4567, 3) → 123.457		CEIL(2.34)→ 3
TRUNC	소수 이하 n 자리 미만을 절삭한다. TRUNC(m, n)	FLOOR	m 보다 작은 가장 큰 정수를 검색한다. FLOOR(m)
	TRUNC(123.4567, 3) → 123.456		FLOOR(2.34)→ 2
MOD	m을 n으로 나눈 나머지를 계산한다. MOD(m, n)	SQRT	제곱근을 검색한다. SQRT(m)
	MOD(10, 4) → 2		SQRT(9) → 3
POWER	m의 n승을 계산한다. POWER(m,n)	SIGN	음수: -1, 양수: 1, 0 이면0을검색한다. SIGN(m)
	POWER(2, 4) → 16		SIGN(-3) → -1

 

CEIL →   보통 많이 사용

floor → trunk랑 비슷하기 때문에 사용 잘 안 함.

숫자 함수의 이용

 

• 다양한 숫자함수의 이용한 검색결과를 확인하기

 

SQL> SELECT ROUND(98.765), TRUNC(98.765),

2 ROUND(98.765,2), TRUNC(98.765,2)

3 FROM dual;

 

SQL> SELECT MOD(19,3), MOD(-19,3)

2 FROM dual;

 

SQL> SELECT CEIL(3.5), FLOOR(3.5)

2 FROM dual;

 

SQL> SELECT POWER(13,4), SQRT(16)

2 FROM dual;

 

 

날짜연산

연산	결과	해석
날짜+ m	날짜	m 일 이후날짜
날짜-m	날짜	m 일 이전날짜
날짜-날짜	숫자	두날짜간에기간(일수)

- 시간: m/24

 

• 날짜데이터

‐ 문자처럼 표기하지만 숫자처럼 계산된다.

‐ 6 byte 크기로 저장된다.

‐ 년월일시분초(YYYY MM DD HH24 MI SS)를저장한다.

‐ 출력은 터미널의 출력 식에 따른다.

‐ 날짜 관련검색에서는 보이지 않는 부분에 주의한다.

‐ 예약어처럼 쓰이는 sysdate함수를 이용해서 시스템의 현재날짜를 검색한다.

 

 

날짜검색

 

• 현재날짜를 다양한 포맷으로 검색하기

 

SQL> SELECT sysdate FROM dual;

 

SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD';

 

SQL> SELECT sysdate FROM dual;

 

SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD:HH24:MI:SS';

 

SQL> SELECT sysdate FROM dual;

 

‐ Windows 기본 날짜출력형식: 'YY/MM/DD'

‐ UNIX 기본 날짜출력형식: 'DD-MON-YY'

‐ nls_date_format은 세션 레벨에서 설정한다.

‐ glogin.sql 파일을 이용한다.

 

 

날짜연산

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

 

SQL> SELECT eno, ename, sysdate 오늘, hdate 입사일,

2 TRUNC(sysdate)-TRUNC(hdate)+1 근무일, hdate+99 "100일"

3 FROM emp; 

 

‐ TRUNC는 시분초를 버린다.

‐ 날짜연산은 ROUND 하는 경우가 거의 없고 TRUNC 하는 것이 대부분이다.

 

 

 

날짜함수

 

함수명	기능		함수명	기능
ROUND	형식에 따라 반올림한다. ROUND(날짜, 형식)	YYYY 년 MM 월 DD 일 HH24 시 MI 분 SS 초	LAST_DAY	날짜를 포함한 달의 마지막 날짜를 검색한다. LAST_DAY(날짜)
	ROUND(sysdate, 'DD') → '2021/09/23'			LAST_DAY('2013/09/24') → 2013/09/30
TRUNC	형식에 따라 절삭한다. TRUNC(날짜, 형식)		ADD_MONTHS	날짜에 n 달을 더한 날짜를 검색한다. ADD_MONTHS(날짜, n)
	TRUNC(sysdate, 'YYYY') → '2021/01/01'			ADD_MONTHS('2011/07/01', 23) → 2013/06/01
MONTHS_BETWEEN	두 날짜 간의 기간을 월 수로 계산한다. 날짜1 > 날짜2 :양수. MONTHS_BETWEEN(날짜1, 날짜2)		NEXT_DAY	날짜 이후 지정된 요일에 해당하는 날짜를 검색한다. -일요일, SUNDAY, 1 모두동일하다. NEXT_DAY(날짜, 요일)
	MONTHS_BETWEEN('2013/09/01', '2013/01/01') → 8			NEXT_DAY('2013/07/14', '일요일') → 2013/07/21

 

 

 

날짜함수의 이용

 

SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD:HH24:MI:SS';

SQL> SELECT sysdate,

2 ROUND(sysdate, 'YYYY') 년, 

3 ROUND(sysdate,'MM') 월,

4 ROUND(sysdate,'DD') 일

5 FROM dual;

 

SQL> SELECT sysdate,

2 TRUNC(sysdate,'YY') 년,

3 TRUNC(sysdate,'MM') 월,

4 TRUNC(sysdate,'DD') 일

5 FROM dual;

 

• 문시현이오늘까지일한일수를검색한다.

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

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

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

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

 

SQL> SELECT ename, TRUNC(sysdate)-TRUNC(hdate)+1 DAY

2 FROM emp

3 WHERE ename='문시현';

 

SQL> SELECT eno, ename, TRUNC(MONTHS_BETWEEN(sysdate,hdate)) 근무_개월

2 FROM emp

3 WHERE dno = '20';

 

SQL> SELECT eno, ename, hdate, NEXT_DAY(hdate,'일요일') Sunday

2 FROM emp

3 WHERE dno = '20';

 

SQL> SELECT eno, ename, hdate 입사일,

2 hdate+99 "100일", ADD_MONTHS(hdate,120) "10년"

3 FROM emp

4 WHERE dno = '20';

 

SQL> SELECT eno, ename, hdate, LAST_DAY(hdate) 마지막날,

2 LAST_DAY(TRUNC(hdate))-TRUNC(hdate)+1 "마지막달 근무일수"

3 FROM emp 

4 WHERE dno = '20';

 

 

 

변환함수

 

함수명	기능
TO_CHAR	날짜나 숫자를 지정된 형식의 문자로 변환한다. -출력형식을지정한다. TO_CHAR(날짜, 형식), TO_CHAR(숫자, 형식)
TO_DATE	문자를 지정된 형식의 날짜로 해석한다. TO_DATE(문자, 형식)
TO_NUMBER	문자를 숫자로 해석한다. 대부분의 경우 오라클의 자동 형변환에 의해 SQL 구문 작성시에는 사용빈도가 매우낮다.

 

‐TO_CHAR : 데이터를 출력하거나 전달할 때 서식에 맞추어 가공하는 기능을 제공한다. 이를 통해 응용프로그램의 부담을 줄이고 가 독성과 생산성을 높일 수 있다.

ㄴ 문자일 때는 사용할 일 없음. 날짜나 숫자 표현할 때, 지정한 형식대로 출력할 때 사용함. (돈, 날짜는 to_char무조건 사용)

 

‐TO_DATE : 사용자가 입력하거나 전달받은 문자열을 시스템이 정확한 형식에 따라 날 짜로해석하도록 하는 함수로 날짜데이터에 반드시 사용한다.

ㄴ 오라클한테 날짜를 줄때 오라클보고 이렇게 해석해라고 할 때 사용(날짜 값 입력할 때 to_date무조건 사용)

 

 

날짜시간형식

	형식	의미		형식	의미
년	YYYY	네자리년도: 2021, 1999	시	HH24	01 ~ 24 , 24시간표현
	YY	두자리년도: 99-2099		HH	01 ~ 12, 12시간표현
	RR	두자리년도: 99-1999
월	MM	숫자: 01, 02	분	MI	##,분
	MONTH	문자: MARCH, APRIL, 3월, 4월
	MON	약자: MAR,APR,3월, 4월
일	DD	숫자: 01, 02	초	SS	##, 초
	DAY	요일: SUNDAY,일요일		SSSSSS	하루를초로환산(0 ~ 86399)
	DY	약자요일: MON, 월	오전 오후	AM/PM	오전, 오후형식으로의미없음.

 

yy = 99 : 2099

rr = 99 : 1999,2099,2199,1899인지 모름.(어늘날짜의 가장 가까운 연도를 나타냄.)

MONTHS_BETWEEN → 많이 씀.

 

숫자형식

형식	의미
9	숫자의 출력 폭을 지정한다.
0	선생 0을 표기한다
$	화폐 표기(달러)
L	화폐 표기(각 국가의 언어 셋 설정에 따라 다르다.)
,	쉼표 위치 지정(보통 세자리마다 지정함.)
.	소수점 위치를 지정한다.
MI	음수의 기호를 오른쪽에 표기한다.
EEEE	실수 표현기법

 

 

TO_CHAR를 이용한 날짜검색

• 오늘 날짜를 다양한 형식으로 검색한다.

 

SQL> SELECT TO_CHAR(sysdate, 'YYYY/MM/DD') 날짜,

2 TO_CHAR(sysdate, 'YYYY/MM/DD:HH24:MI:SS') 날짜,

3 TO_CHAR(sysdate, 'YY/MM/DD:HH:MI:SS AM') 날짜

4 FROM dual;

 

SQL> SELECT TO_CHAR(sysdate, 'DAY Mon YY') 날짜 FROM DUAL; ← 9월, Sep(September) 검색

 

SQL> SELECT ename||' 사원의 입사일은 '||

2 TO_CHAR(hdate, 'YYYY"년 "MM"월 "DD"일 입니다."') 입사일

3 FROM emp;

 

 

TO_CHAR를 이용한 숫자검색

• 숫자를 다양한 형식으로 검색한다.

 

SQL> SELECT TO_CHAR(12345.678, '999,999.99999') num FROM dual;

 

SQL> SELECT TO_CHAR(12345.678, '099,999.999') num FROM dual;

 

SQL> SELECT TO_CHAR(12345.678, '99,999.9') num FROM dual;

 

SQL> SELECT TO_CHAR(12345.678, '9,999.9') num FROM dual;

 

SQL> SELECT TO_CHAR(1234, '$999,999') num FROM dual;

 

SQL> SELECT TO_CHAR(1234, 'L999,999') num FROM dual;

 

SQL> SELECT TO_CHAR(123456789, '9.999EEEE') num FROM dual;

 

SQL> SELECT TO_CHAR(-1234, '999,999MI') num FROM dual;

 

 

TO_CHAR를 이용한 검색

 

• 1992년 이전에 입사한 사원의 정보를 검색한다.

 

SQL> SELECT eno 사번, ename 이름, hdate 입사일

2 FROM emp

3 WHERE hdate < TO_DATE('19920101','YYYYMMDD');

 

SQL> SELECT eno 사번, ename 이름, hdate 입사일

2 FROM emp

2 WHERE hdate <= TO_DATE('19911231:235959','YYYYMMDD:HH24MISS');