[DATABASE] 함수

■ 변환 함수

https://webstudynote.tistory.com/60

https://webstudynote.tistory.com/60

함수	내용
TO_CHAR(number) TO_CHAR(number, format) TO_CHAR(date, FORMAT)	DATE형, NUMBER형을 문자 타입으로 변환
TO_DATE(char, format)	CHAR, VARCHAR2형을 DATE 타입으로 변환
TO_ NUMBER(char)	CHAR, VARCHAR2의 데이터 타입을 숫자형식으로 변환

출처 : http://www.gurubee.net/lecture/1027
- 주요 숫자 포맷과 날짜 포맷을 여기서 확인할 수 있다.

■ 문자열 함수

함수	내용
CONCAT('www.' , 'naver.com')	문자열 결합 연산자 || 와 동일한 기능을 하는 함수
INITCAP(문자)	첫글자를 대문자로, 나머지는 소문자로 변경
LOWER(문자)	모든 문자를 소문자로 변경
UPPER(문자)	모든 문자를 대문자로 변경
SUBSTR(문자,시작인덱스,갯수)	'HELLO'의 첫번째문자에서 세개를 뺀다
LENGTH(문자)	문자열의 길이를 반환한다.
REPLACE(전체 문자,바꿀 영역, 대체 문자)	문자의 일부를 다른 문자로 대체
INSTR(전체 문자, 찾을 문자)	전체 문자에 찾을 문자가 없으면 0, 있으면 위치를 반환
TRIM(전체 문자)	공백 제거

 

■ 숫자 함수

함수	내용
ABS(양수 또는 음수)	양수 또는 음수의 절대값 반환
CEIL(n)	올림값을 반환
FLOOR(n)	버림값을 반환
MOD(m, n)	m을 n으로 나눈 나머지를 반환
ROUND(n, [m])	n값의 반올림 반환. m은 소수점 아래 자릿수
TRUNC(n, m)	n값을 절삭하고 반환. m은 소숫점 아래 자릿수

 

■ 날짜 함수

함수	내용
SYSDATE	시스템 현재 일자 *최소단위 = 1초
SYSTIMESTAMP	시스템 현재 일자(시분초) *최소단위 = 10억분의 1초
ADD_MONTHS(a, b)	a의 날짜에 b의 달을 더한 값을 반환
MONTHS_BETWEEN(a1, a2)	a1과 a2 사이의 달의 수를 NUMBER형 타입으로 반환
LAST_DAY(d)	달의 마지막 날의 날짜를 반환
NEXT_DAY(d, c1)	1(일) ~ 7(토)을 기준으로 돌아오는 명시된 요일의 일자를 반환한다.
ROUND(d[,F])	F에 지정된 단위로 반올림 *생략시 '일'을 기준으로
TRUNC(d[,F])	F에 지정된 단위로 절삭

 

[SYSDATE를 통해 현재시간, MONTH, HOUR, MINUTE 전 시간 구하기]

-- SYSDTE 예제
SELECT TO_CHAR(SYSDATE,'RRRR-MM-DD HH24:MI:SS') "지금시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1,'RRRR-MM-DD HH24:MI:SS') "하루전지금시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24,'RRRR-MM-DD HH24:MI:SS') "1시간전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24/60,'RRRR-MM-DD HH24:MI:SS') "1분전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24/60/10,'RRRR-MM-DD HH24:MI:SS') "6초전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'RRRR-MM-DD HH24:MI:SS') "5시간 30분 10초전"
  FROM DUAL ;

 

[SYSTIMESTAMP를 통해 현재시간, MONTH, HOUR, MINUTE 전 시간 구하기]

-- SYSTIMESTAMP
-- SYSTIMESTAMP 함수를 사용하면 현재 일자와 시간(시스템기준)을 얻을 수 있다.
-- SYSTIMESTAMP 예제
SELECT TO_CHAR(SYSTIMESTAMP,'RRRR-MM-DD HH24:MI:SS.FF3')  
  FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP,'RRRR-MM-DD HH24:MI:SS.FF9')  
  FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP -1/24,'RRRR-MM-DD HH24:MI:SS') "1시간전시간"
  FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP -1/24/60,'RRRR-MM-DD HH24:MI:SS') "1분전시간"
  FROM DUAL ;

 

[내가 살아온 개월 수, 일수]

--Q1. 내가 살아온 월 수 or 일 수
SELECT MONTHS_BETWEEN(TO_DATE(SYSDATE, 'RRRR-MM-DD'),
                      TO_DATE('2000-01-01', 'RRRR-MM-DD')) AS "내가 살아온 월 수"
  FROM DUAL;
  
SELECT TRUNC(MONTHS_BETWEEN(TO_DATE(SYSDATE, 'RRRR-MM-DD'),
                      TO_DATE('2000-01-01', 'RRRR-MM-DD')),1) AS "내가 살아온 월 수"
  FROM DUAL;

SELECT TO_DATE(SYSDATE, 'RRRR-MM-DD') -
       TO_DATE('2000-01-01', 'RRRR-MM-DD') as "내가 살아온 날 수"
  FROM DUAL;

 

■ 집계 함수

함수	내용
AVG()           (평균)	평균 값을 반환
COUNT()       (개수)	검색된 행의 수
MAX()          (최대값)	컬럼값 중에서 최대값을 반환
MIN()           (최소값)	컬럼값 중에서 최소값을 반환
SUM()           (합계)	검색된 컬럼의 합을 반환
STDDEV()	표준변차 반환

 

■ NULL과 관련된 함수

함수	내용
NVL(필드, 대체값)	NULL이면 대체값 대입 (NULL이 아니면 그대로 사용)
NVL2(필드, 참값 ,거짓값)	NULL이 아니면 참값, NULL이면 거짓값

CREATE TABLE MEMBER
(
   DEPTNO NUMBER(5),
   NAME CHAR(3 CHAR),
   RECDATE DATE,
   SECTOR VARCHAR2(100)
)

SELECT  
    NAME, RECDATE, 
    NVL(SECTOR, '미지정') AS SECTOR, 
    NVL2(SECTOR, '지정 완료', '미지정') AS SECTOR
FROM MEMBER

 

 

[출처]

- http://www.gurubee.net/

- 뉴렉처 강의

- 국비 수업 과정 참조