날짜형 함수
- 데이터형 date or timestamp를 대상으로 연산을 수행하는 함수
- 특정일을 기준으로 ‘과거 3일전’, ‘한달 후’ 등 이와 같이 날짜 계산 가능 또한, 해당 월의 마지막 날이 언제인지 등 날짜를 구할 수 있음
- 중요한 기본 날짜 함수
- SYSDATE
• 현재 날짜와 시간 반환 (인터넷 시간이 아닌 컴퓨터의 시계를 이용)
SELECT SYSDATE FROM dual;
→ 결과 : 2023-07-10 00:32:08.000
- ADD_MONTHS( date, n )
• 입력날짜 date에 입력달 n개월을 더한 날짜를 반환
• n < 0인 경우, 입력날짜에서 n개월을 뺀 날짜를 반환
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual;
→ 결과 : 2023-08-10 00:37:51.000
- MONTHS_BETWEEN(date1, date2)
• date1과 date2 사이의 개월 수를 반환
• date1 > date2 인 경우, 반환값 : 양수
• date1 < date2 인 경우, 반환값 : 음수
SELECT MONTHS_BETWEEN(SYSDATE+31, SYSDATE) FROM dual;
→ 결과 : 1
SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE+31) FROM dual;
→ 결과 : -1
- LAST_DAY(date)
• 주어진 date가 속한 월의 마지막 일자를 반환
SELECT LAST_DAY(SYSDATE) FROM dual;
→ 결과 : 2023-07-31 00:51:43.000
- NEXT_DAY(date, expr요일표현식)
• date를 기준으로 expr에서 명시한 날짜 반환
• expo : 요일을 의미
일요일을 기준으로 1 : 일요일 | 2 : 월요일
SELECT NEXT_DAY(SYSDATE, ‘일요일’) FROM dual;
- ROUND(date, format)
• 주어진 날짜를 format형식에서 지정한 형식을 기준으로 반올림함
• format : YEAR, MONTH, DD, HH, HH24, MI 등 사용 가능
YEAR : 해달 연, 월, 일의 7월 1일을 기준
MONTH : 각 달의 16일 기준
DD : 해당 일의 정오(12:00:00)를 기준
HH, HH24 : 해당 일의 시간을 기준
MI : 해당 일 시간의 분을 기준
SELECT ROUNG(SYSDATE, ‘YEAR’) FROM dual;
→ 결과 : 2024-01-01 00:00:00.000
- TRUNC(date, format)
• 주어진 날짜를 format형식에서 지정한 형식을 기준으로 버림
SELECT TRUNC(SYSDATE, ‘YEAR’) FROM dual;
→ 결과 : 2023-01-01 00:00:00.000 (월, 일 초기화)
연습
SELECT sysdate FROM dual; -- 결과 : 2023-07-10 00:41:33.000
SELECT ADD_MONTHS(SYSDATE, 1) FROM dual; -- 결과 : 2023-08-10 00:41:21.000
SELECT ADD_MONTHS(SYSDATE, -1) FROM dual; -- 결과 : 2023-06-10 00:41:10.000
SELECT MONTHS_BETWEEN(SYSDATE+31, SYSDATE) FROM dual; -- 결과 : 1
SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE+31) FROM dual; -- 결과 : -1
-- 7월의 마지막 날짜 반환
SELECT LAST_DAY(SYSDATE) FROM dual; -- 결과 : 2023-07-31 00:51:43.000
-- 8월의 마지막 날짜 반환
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, 1)) FROM dual; -- 결과 : 2023-08-31 00:54:06.000
-- 오늘을 기준으로 일요일의 날짜 반환
SELECT NEXT_DAY(SYSDATE, '일요일') FROM dual; -- 결과 : 2023-07-16 01:07:36.000
SELECT NEXT_DAY(SYSDATE, 1) FROM dual; -- 결과 : 위와 동일
-- 오늘을 기준으로 월요일의 날짜 반환
SELECT NEXT_DAY(SYSDATE, 2) FROM dual; -- 결과 : 2023-07-17 01:08:34.000
-- 년도를 기준으로 날짜 반올림
SELECT ROUND(SYSDATE, 'YEAR') FROM dual; -- 결과 : 2024-01-01 00:00:00.000
-- 날짜 반올림 기준 : 'MONTH' - 각 월의 16일 기준
-- 현재 날짜가 10일이므로 반올림되지 않음
SELECT SYSDATE FROM dual; -- 결과 : 2023-07-10 01:19:08.000
SELECT ROUND(SYSDATE, 'MONTH') FROM dual; -- 결과 : 2023-07-01 00:00:00.000
-- 17일인 경우, 월 기준 반올림된 날짜 반환
SELECT SYSDATE + 7 FROM dual; -- 결과 : 2023-07-17 01:27:22.000
SELECT ROUND(SYSDATE + 7, 'MONTH') FROM dual; -- 결과 : 2023-08-01 00:00:00.000
-- 반올림 기준 : 'DD' - 정오(12:00:00)를 기준
SELECT ROUND(SYSDATE, 'DD') FROM dual; -- 결과 : 2023-07-10 00:00:00.000
-- 날짜 버림 기준 : 'YEAR' - 월, 일 초기화
SELECT TRUNC(SYSDATE, 'YEAR') FROM dual; -- 결과 : 2023-01-01 00:00:00.000
-- 4월에 버림 적용해보기
SELECT TRUNC(ADD_MONTHS(SYSDATE, -3), 'YEAR') FROM dual; -- 결과 : 2023-01-01 00:00:00.000
-- 내년 2월에 버림 적용
SELECT TRUNC(ADD_MONTHS(SYSDATE, 7), 'YEAR') FROM dual; -- 결과 : 2024-01-01 00:00:00.000
-- 작년 12월 기준 버림 적용
SELECT TRUNC(ADD_MONTHS(SYSDATE, -7), 'YEAR') FROM dual; -- 결과 : 2022-01-01 00:00:00.000
형변환 함수
- 특정 데이터형을 다른 데이터형으로 바꾸기
- 숫자 → 문자, 문자 → 숫자, 문자 → 날짜, 날짜 → 문자
- TO_NUMBER(char)
• 문자형을 숫자로 변환
SELECT TO_NUMBER(‘12345’) FROM dual;
→ 결과 : 12,345
- TO_CHAR(number, number_format)
• 숫자를 number_format 형식에 맞는 문자로 변환
• 일반적으로 number_format의 경우, 생략 가능
SELECT TO_CHAR(12345, ’99,999’) FROM dual;
→ 결과 : 12,345
- TO_CHAR(date, date_format)
• 날짜형 date를 date_format을 사용하여 문자로 변환
• 일반적으로 미터법 형식의 경우, 생략 가능
SELECT TO_CHAR( SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) FROM dual;
→ 결과 : 2023-07-10 10:51:50
- TO_DATE(char, date_format)
• 문자열을 date_format에서 지정한 날짜형식으로 반환
SELECT TO_DATE (‘2023-07-10 10:50:50’, ‘YYYY-MM-DD’) FROM dual;
- 숫자변환형식 (number_format)
• , (콤마)
• . (소수점)
TO_CHAR(123456.7, ‘999,999.9’) → 123,456.7
• 9 : 10진수로 한자리 숫자를 나타냄
사용할 때는 실제 값의 자리 수와 같거나 커야 함
TO_CHAR(123456, ‘9999,999’) → 123,456
TO_CHAR(123456, ‘99,999’) → 실제 값보다 자리수가 적기 때문에 오류 발생
-TO_NUMBER( ) 사용해서 문자열을 숫자로 바꾸어 계산하는 경우는 많음
TO_CHAR( ) 사용해서 숫자를 문자형으로 바꾸는 경우는 많지 않음
- 날짜형_변환형식
• 년 / 월 / 일 / 시 / 분 / 초로 구성되므로 변환형식이 복잡함
• 년도 형식 : 연도 표시
YYYY
YYY
YY
Y
TO_CHAR(SYSDATE, ‘YYYY’) → 2023
• 월 형식
➊ MONTH | MON
TO_CHAR(SYSDATE, ‘MONTH’) → 7월
❷ MM : 01 ~ 12 형태로 반환
TO_CHAR(SYSDATE, ‘MM’) → 07
• 주중 요일을 숫자로 반환
➊ D : 일요일(1) ~ 토요일(7)로 숫자로 반환
TO_CHAR(SYSDATE, 'D') → 2
❷ DAY : 월요일 ~ 일요일 형태로 반환
TO_CHAR(SYSDATE, ‘DAY’) → 월요일
• 일 형식
➊ DD : 01 ~ 31 형태로 반환
TO_CHAR(SYSDATE, ‘DD’) → 10
❷ DDD : 001 ~ 365 형태로 반환
TO_CHAR(SYSDATE, ‘DDD’) → 191
❸ DL : 날짜를 완전한 형식( 0000년 00월 00일 0요일 )으로 반환
TO_CHAR(SYSDATE, ‘DL’) → 2023년 7월 10일 월요일
- 시간_변환형식
• 시간
➊ HH | HH12 : 시간을 01 ~ 12 형식으로 반환
TO_CHAR(SYSDATE, ‘HH’) → 11
❷ HH24 : 24시간 형식으로 반환
TO_CHAR(SYSDATE, ‘HH24’) → 23
• 분
MI : 01 ~ 59 형식으로 반환
TO_CHAR(SYSDATE, ‘MI’) → 49
• 초
SS : 01 ~ 59 형식으로 반환
TO_CHAR(SYSDATE, ‘SS’) → 27
• 년도를 주 단위로 반환
WW : 01 ~ 53로 반환
TO_CHAR(SYSDATE, ‘WW’) → 28
연습
SELECT TO_NUMBER('12345') FROM dual; -- 결과 : 12,345
SELECT TO_CHAR(12345, '99,999') FROM dual; -- 결과 : 12,345
SELECT TO_CHAR( SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM dual; -- 결과 : 2023-07-10 01:51:27
SELECT TO_DATE ('20230710', 'YYYY-MM-DD') FROM dual; -- 결과 : 2023-07-10 00:00:00.000
-- 문자를 숫자로 변환
SELECT TO_CHAR(123456, '999,999') FROM dual; -- 결과 : 123,456
SELECT TO_CHAR(123456, '99,999') FROM dual; -- 실제 값보다 자리수가 적으므로 표기 에러 발생 : #######
-- 숫자를 문자로 변환
-- number_format 생략한 경우
SELECT TO_CHAR(123456) FROM dual; -- 결과 : 123456
SELECT TO_CHAR(123456.7) FROM dual; -- 결과 : 123456.7
-- number_format 사용한 경우
SELECT TO_CHAR(123456, '999,999') FROM dual; -- 결과 : 123,456
-- 소수점 이하 자리수가 모자란 경우, 반올림하여 반환
SELECT TO_CHAR(123456.789, '999,999.99') FROM dual; -- 결과 : 123,456.79
-- 날짜형 변환 - 년도
-- : 'YYYY' 형식에 따라서 4자리 연도 ~ 1자리 연도까지 반환
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM dual; -- 결과 : 2023
SELECT TO_CHAR(SYSDATE, 'YYY') FROM dual; -- 결과 : 023
SELECT TO_CHAR(SYSDATE, 'YY') FROM dual; -- 결과 : 23
SELECT TO_CHAR(SYSDATE, 'Y') FROM dual; -- 결과 : 3
-- 날짜형 변환 - 월
SELECT TO_CHAR(SYSDATE, 'MONTH') FROM dual; -- 결과 : 7월
SELECT TO_CHAR(SYSDATE, 'MON') FROM dual; -- 결과 : 7월
SELECT TO_CHAR(SYSDATE, 'MM') FROM dual; -- 결과 : 07
-- 날짜형 반환 - 요일
SELECT TO_CHAR(SYSDATE, 'D') FROM dual; -- 결과 : 2 (현재 월요일)
SELECT TO_CHAR(SYSDATE, 'DAY') FROM dual; -- 결과 : 월요일
-- 날짜형 반환 - 일
SELECT TO_CHAR(SYSDATE, 'DD') FROM dual; -- 결과 : 10
SELECT TO_CHAR(SYSDATE, 'DDD') FROM dual; -- 결과 : 191
-- 날짜형 반환 - 완전한 형식
SELECT TO_CHAR(SYSDATE, 'DL') FROM dual; -- 결과 : 2023년 7월 10일 월요일
-- 시간 형식 변환
SELECT TO_CHAR(SYSDATE, 'HH') FROM dual;
-- 1년을 주 단위로 반환
SELECT TO_CHAR(SYSDATE, 'WW') FROM dual; -- 결과 : 28
-- 날짜 + 시간 형식으로 출력
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 결과 : 2023-07-10 03:02:42
-- 문자열 형식의 날짜 문자열을 날짜 데이트로 고쳐보자
SELECT TO_DATE('2023-07-10 23:10:10', 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 결과 : 2023-07-10 23:10:10.000
-- 오라클의 자동 암시적 형변환 : 문자열 -> 날짜 형식
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, GENDER, AGE, HIRE_DATE)
VALUES(10, '이하나', '여성', 20, '2023-01-05');
-- 명시적 형변환 쓰는 것을 권장
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, GENDER, AGE, HIRE_DATE)
VALUES(10, '이하나', '여성', 20, TO_DATE('2023-01-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
NULL 처리 함수
- NVL, NVL2, COALESCE, NULLIF 중 ORACLE에서 NVL, NULLIF 함수가 가장 많이 사용
- NVL( 표현식1, 표현식2 )
• 표현식1이 NULL이면, 표현식2로 변환하는 함수
• NULL 허용 컬럼을 조회할 때 해당 컬럼에 데이터가 있으면 표현식1의 값을 출력
데이터가 없다면(=NULL인 경우) 표현식2(대체값)을 출력
SELECT NVL(NULL, ‘값없음’),
NVL2(1, 2, 3),
COALESCE(NULL, NULL, 5, 4, NULL),
NULLIF(‘abc’, ‘ABC’)
FROM dual;
- NVL2( 표현식1, 표현식2, 표현식3 )
• 해당 컬럼에 데이터가 있으면 표현식1의 값 출력
표현식1 <> NULL인 경우, 표현식2의 값 출력
표현식1 = NULL인 경우, 표현식3의 값 출력
- COALESCE (표현식1, 표현식2, 표현식3, … )
• 매개변수로 전달된 표현식의 값들 중에서 처음으로 NULL이 아닌 값이 나오는 경우의 표현식 값을 반환
- NULLIF(표현식1, 표현식2)
• 표현식1과 표현식2를 비교해서 두 값이 같으면 NULL 반환
두 값이 다르면 표현식1의 값 반환
연습
-- null 처리 함수 : NVL, NVL2, COALESCE, NULLIF 함수 예제
SELECT NVL(NULL, '값없음'),
NVL2(1, 2, 3),
COALESCE(NULL, NULL, 5, 4, NULL),
NULLIF('abc', 'ABC')
FROM dual; -- 결과 : 값없음 2 5 abc
GREATEST, LEAST, DECODE
< 데이터형 크기 비교 함수 : GREATEST, LEAST, DECODE >
- GREATEST( expr1, expr2, expr3, … ) ↔︎ LEAST
• expr1, expr2, expr3, … 표현식들 중에서 비교하여 가장 큰 값을 찾아 반환
• expr 매개변수 값들에 대하여 내부적인 비교연산을 통해 큰 값을 반환
• expr 매개변수로 문자, 숫자, 날짜형이 동시에 가능함
→ 이와 같은 경우, 오라클이 내부적으로 비교가 가능하도록 하여 대소관계를 만들어내며 단 그 반환형은 expr1의 데이터형식을 따름
GREATEST(‘a’, 2, 3, 4, 5)
→ 첫번째 자료형이 문자형이므로, 나머지 숫자의 경우 내부적으로 문자형으로 암시 형변환이 일어남
∴ ASCII 코드에 따라 비교됨 ( 문자 > 숫자 )
‘a’ : 97이므로 ‘a’ > 2 > 3 > 4 > 5 이므로 가장 큰 값인 ‘a’가 반환됨
- DECODE ( 조건컬럼명, ‘조건’, ‘조건이 true면 출력’, ‘조건이 false면 출력’ )
• if (조건) { }
else { } 의 함축적인 형태
WITH절
- WITH절 : 오라클 9 이후 버전부터 사용 가능
- 이름이 부여된 서브쿼리로, 임시테이블을 생성함 → 한번 실행할 쿼리문 내에 정의되어 있을 경우, 그 쿼리문 안에서만 실행됨
- WITH절과 아래 SELECT문을 함께 블록 처리한 후, 실행시키기
- 서브쿼리로 추출된 데이터를 별칭을 지정해서 임시테이블이나 VIEW와 같이 관리 가능
→ 이를 통해 특정 서브쿼리의 결과 관리 가능, 같은 서브쿼리가 필요할 때 별칭을 주었던 임시테이블에 대해 쿼리 작성 가능
- 다중 WITH절
• 집합 연산자 이용
① UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력함 → 합집합
중복된 행은 한 번만 출력
② UNOIN ALL : 두 SELECT문의 조회 결과를 통합하여 모두 출력함 → 합집합
중복된 행도 그대로 출력
• 형식
WITH 임시테이블명 AS (
SELECT ‘EX1’ A FROM dual
UNION ALL
SELECT ‘EX2’ B FROM dual
UNION ALL
SELECT ‘EX3’ C FROM dual
)
SELECT * FROM dual ;
연습
-- GREATEST 예시
SELECT GREATEST('a', 2, 3, 4, 5) FROM dual; -- 결과 : a
SELECT GREATEST('abc', 1, 2, 5, 'd') FROM dual; -- 결과 : d
SELECT GREATEST(1, '100', 3, 2, 5) FROM dual; -- 결과 : 100
SELECT LEAST(1, '100', 3, 2, 5 ) FROM dual; -- 결과 : 1
-- DECODE 예시
-- 1. 단순 DECODE : DECODE(조건컬럼명, 조건, 조건이 참인 경우의 값, 조건이 거짓인 경우의 값)
-- 2. 복합 DECODE : DECODE(조건컬럼명, 조건1, 조건1이 참인 경우의 값, 조건2, 조건2가 참인 경우의 값, ...)
-- 임시테이블을 만들때 많이 사용됨
-- 임시로 사용하는 테이블 조회 시 다시 재사용해야하는 경우가 있음
-- 이 때, WITH 임시테이블명 AS (조회SQL구문) 형식으로 임시 질의 SQL에 별칭을 붙여 다른 SQL에서 재사용 가능
-- view에 비해 속도가 빠르고 쿼리 플랜을 이용해서 재사용성을 높여 작은 데이터를 사용하는 경우 유용함
-- * UNION ALL : 합집합 - 중복되는 값도 포함
-- UNION : 합집합 - 중복되는 값 포함 안됨
WITH tmp AS(
SELECT 'M' gender FROM dual UNION ALL
SELECT 'F' gender FROM dual UNION ALL
SELECT 'X' gender FROM dual
)
SELECT gender,
DECODE(gender, 'M', '남자', 'F', '여자', '기타') gender2
FROM tmp;
- WITH절
• 'temp'라는 임시테이블 생성
• UNION ALL 구문을 사용하여 같은 컬럼구조를 가지는 합성테이블 생성
• gender라는 컬럼명을 가지는 문자형 레코드가 3개인 테이블 생성
• 생성된 < temp > 테이블
| gender |
| M |
| F |
| X |
'백엔드 > Oracle' 카테고리의 다른 글
| SQL ( GROUP BY ~ HAVING / 집합연산자 ) (0) | 2023.07.12 |
|---|---|
| Oracle : SQL ( CASE WHEN THEN / GROUP BY ) (0) | 2023.07.11 |
| Oracle : SQL - 내장 함수 (0) | 2023.07.07 |
| Oracle : SQL ( SELECT -2 ) (0) | 2023.07.06 |
| Oracle : SQL ( SELECT - 1 ) (0) | 2023.07.05 |