공부/데이터베이스

[데이터베이스] 오라클 내장함수 - 숫자함수, 날짜함수, 변환함수, NULL관련 함수

ghhong 2021. 10. 21. 16:16

숫자함수

절대값

SELECT ABS(-35) FROM DUAL;

음수/양수인지 알려주는 함수

SELECT SIGN(35), SIGN(-35), SIGN(0) FROM DUAL; -- 양수 : 1 음수 : -1 0 : 0

반올림

SELECT ROUND(34.456789), ROUND(34.56789) FROM DUAL; --34 , 35

SELECT ROUND(12.3456, 2) ROUND(12.3456, 3) FROM DUAL; -- 12.35, 12.346

나머지 반환

SELECT TRUNC(17/5) , MOD(17,5) 나머지 FROM DUAL; -- 3, 2

제곱근

SELECT POWER(5,2) , SQRT(25) FROM DUAL; --25, 5

날짜함수

현재시간

SELECT SYSDATE, CURRENT_DATE, SYSTIMESTAMP, CURRENT_TIMESTAMP FROM DUAL;

-- 오라클서버의 시간 : SYS , 설정(접속자의)시간 : CURRENT

세션 시간과 포맷 변경

ALTER SESSION SET TIME_ZONE = ‘09:00’; --타임존 변경

ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH24:MI:SS’; --포맷변경

날짜 추출 함수 EXTRACT(YEAR/MONTH/DAY/HOUR/MINUTE/SECOND)

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

SELECT EXTRACT(MONTH FROM SYSDATE) || ‘’ FROM DUAL;

날짜 누적 함수 ADD_MONTHS(날짜, 정수)

SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;

SELECT * FROM MEMBER WHERE ADD_MONTHS(SYSDATE, -6) < REGDATE; --6개월이 안 된 회원

날짜의 차이 추출 함수(개월)

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE(‘2021-01-01’) FROM DUAL;

SELECT * FROM MEMBER WHERE MONTHS_BETWEEN(SYSDATE, REGDATE) < 6; --6개원이 안 된 회원

다음(돌아오는) 요일을 알려주는 함수

SELECT NEXT_DAY(SYSDATE, ‘’) FROM DUAL; -- , 월요일, 2 / 일요일:1 토요일:7

월의 마지막 일자를 알려주는 함수

SELECT LAST_DAY(SYSDATE) FROM DUAL; -- 현재 달의 마지막 날

SELECT LAST_DAY(ADD_MONTHS(SYSDATE, 1)) FROM DUAL; -- 다음 달의 마지막 날

날짜 반올림, 자르기

SELECT ROUND(SYSDATE, ‘CC’), TRUNC(SYSDATE, ‘CC’) FROM DUAL;

CC : 세기, YEAR : , Q : 분기, MONTH :

 

변환함수 (숫자 <-> 문자열 <-> 날짜)

NUMBER -> VARCHAR2 : TO_CHAR()

SELECT TO_CHAR(123456, ‘9,999,999.99’) FROM DUAL;

 -- 포맷 지정. 9 : 숫자, 소수점 모자라면 반올림

DATE -> VARCHAR2 : TO_CHAR()

SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) FROM DUAL; --포맷 지정

VARCHAR2 -> DATE : TO_DATE(), TO_TIMESTAMP()

SELECT TO_DATE(‘2014-11-24’) FROM DUAL;

SELECT TO_DATE(‘2014-11-24 12:15:23’, ‘YYYY-MM-DD HH:MI:SS’) FROM DUAL;

VARCHAR2 -> NUMBER : TO_NUMBER()

SELECT TO_NUMBER(‘1994’) FROM DUAL;

SELET ‘2’ +3 FROM DUAL; -- +연산은 숫자만 가능하므로 묵시적으로 숫자로 계산

 

NULL관련 함수

SELECT NULL + 3 FROM DUAL; -- 결과 null

널값 확인, 대체함수 : NVL, NVL2

SELECT NVL(AGE, 0) FROM MEMBER; --AGENULL인경우 0으로 대체한다.(NVL:널밸류)

SELECT TRUNC(NVL(AGE,0)/10) *10 FROM MEMBER; --널이면 0

SELECT NVL2(AGE, TRUNC(AGE/10)*10 , 0) FROM MEMBER; -- 널이 아니면 두 번째 연산, 널이면 세 번째

널값으로 만들기 : NULLIF

SELECT NULLIF(AGE, 19) FROM MEMBER; -- AGE19NULL 반환, 아니면 그대로 반환

조건에 따른 값 선택하기 DECODE(기준값, 비교값, 출력값, 비교값, 출력값)

SELECT DECODE(GENDER, ‘남성’, 1, 2) FROM MEMBER;

SELECT DECODE(SUBSTR(PHONE, 1, 3), ‘011’, ‘SK’, ‘016’, ‘KR’, ‘기타’) FROM MEMBER;