SQL함수(날짜형 함수, 형변환 함수)

2023.07.10수업

▷ 날짜형 함수

- 데이터형 date or timestamp를 대상으로 연산을 수행하는 함수
- '특정일을 기준으로 과거 3일전 1달 후 날짜는 언제인가?' 등 식의 날짜를 구하는데 쓰는 함수

1) sysdate : 현재 날짜와 시간을 반환

- 인터넷시간이 아닌 컴퓨터의 클럭을 이용한 시간을 가져옴

SELECT SYSDATE FROM DUAL; -- 결과 : 2023-07-10 09:32:26.000

2) ADD_MONTHS(date, n) : 입력날짜(date)에 입력 값(n개월)을 더한 날짜를 구한다
- n은 음수 가능(뺀 날짜)

SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; -- 결과 : 2023-08-10 09:38:29.000
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL; -- 2023-06-10 16:57:42.000
SELECT ADD_MONTHS(SYSDATE, -11) FROM DUAL; -- 2022-08-10 16:57:50.000

3) MONTHS_BETWEEN(date1, date2) : date1과 date2사이의 개월 수 차이를 리턴
- date1 > date2 : 반환 값은 양수 아니면 음수

SELECT MONTHS_BETWEEN(SYSDATE + 31, SYSDATE) FROM DUAL; -- 결과 1 (7월이라 31일이 한달임)

4) LAST_DAY(date) : 주어진 date가 속한 월의 마지막 일자를 반환

- 마지막 날짜 구하기

SELECT LAST_DAY(SYSDATE) FROM DUAL; -- 2023-07-31 10:03:55.000
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, 2)) FROM DUAL; -- 2023-09-30 10:03:55.000

5)  NEXT DAY(date, expr요일표현식) : date를 기준으로 expr에서 명시한 날짜 반환
- 가까운 다음 날짜 가져오기
- expr은 요일을 의미 '일요일' OR 1 (숫자로 표현하면 1 = 일요일 ~ 7 = 토요일)

SELECT NEXT_DAY(SYSDATE, '월요일') FROM DUAL; -- 2023-07-17 10:08:07.000 
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL; -- 2023-07-16 10:09:25.000

6) ROUND(date, format) : 날짜를 반올림
- 주어진 날짜를 format형식에서 지정한 형식을 기준으로 반올림
- format은 YEAR, MONTH, DD, HH, HH24, Mi 등 사용 가능

-- 7월이라 반올림하면 24년도
SELECT ROUND(SYSDATE, 'YEAR') FROM DUAL; -- 2024-01-01 00:00:00.000
-- 4월이라 반올림이 안되어 23년도
SELECT ROUND(ADD_MONTHS(SYSDATE, -3), 'YEAR') FROM DUAL; -- 2023-01-01 00:00:00.000
-- 7월 10일이라 반올림이 안되어 7월
SELECT ROUND(SYSDATE, 'MONTH') FROM DUAL; -- 2023-07-01 00:00:00.000
-- 7월 17일이라 반올림하면 8월
SELECT ROUND(SYSDATE + 7, 'MONTH') FROM DUAL; -- 2023-08-01 00:00:00.000
-- 10일 오전이라 반올림하면 10일
SELECT ROUND(SYSDATE, 'DD') FROM DUAL; -- 2023-07-10 00:00:00.000

SELECT ROUND(SYSDATE, 'YEAR') FROM DUAL; -- 2024-01-01 00:00:00.000
-> YEAR 형식 기준으로 반올림

7) TRUNC(date, format) : date를 format에 맞추어 잘라낸 날짜 반환(버림)

SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL; -- 2023-01-01 00:00:00.000
-- 4월(7월 -3)에 TRUNC : 년도만 남기고 나머지는 시작날짜로 초기화
SELECT TRUNC(ADD_MONTHS(SYSDATE, -3), 'YEAR') FROM DUAL; -- 2023-01-01 00:00:00.000
-- 12월(7월 +5)에 TRUNC : 년도만 남기고 나머지는 시작날짜로 초기화
SELECT TRUNC(ADD_MONTHS(SYSDATE, 5), 'YEAR') FROM DUAL; -- 2023-01-01 00:00:00.000
-- 1년 2개월(7월 +7): 1년 2개월이면 1년은 포함되고 2개월만 버림
SELECT TRUNC(ADD_MONTHS(SYSDATE, 7), 'YEAR') FROM DUAL; -- 2024-01-01 00:00:00.000
-- 12월(7월 -7) : 기준 년도가 변경되어 22년이 됨
SELECT TRUNC(ADD_MONTHS(SYSDATE, -7), 'YEAR') FROM DUAL;-- 2022-01-01 00:00:00.000

---

 

▷ 형변환 함수

- 특정 데이터형을 다른 데이터형으로 바꾸기
- 숫자 -> 문자, 문자 -> 숫자 / 문자 -> 날짜, 날짜 -> 문자 등

 

1) TO_NUMBER(char) : 문자형을 숫자로 변환

SELECT TO_NUMBER('123456') FROM DUAL; --123,456
SELECT TO_NUMBER('123456.890') FROM dual; -- 123,456.89

2) TO_CHAR(number, number_format) : 숫자를 number_format형식에 맞는 문자로 변환
- 기본형이면 number_format생략 가능

-- number_format 생략
SELECT TO_CHAR(123456) FROM dual; --123456
SELECT TO_CHAR(123456.7) FROM dual; -- 123456.7
SELECT TO_CHAR(123456, '999,999') FROM dual; --123,456
SELECT TO_CHAR(123456, '99,999') FROM dual; --#######(표기에러)
SELECT TO_CHAR(123456.7895, '999,999.9999') FROM dual; -- 123,456.7895
SELECT TO_CHAR(123456.7895, '999,999.99') FROM dual; -- 123,456.79

3) TO_CHAR(date, date_format) : 날짜를 date_format형식에 맞는 문자로 변환
- 일반적인(미터법) 형식이라면 date_format 생략 가능

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- '2023-07-10 10:50:30'

4) TO_DATE(char, date_fromat) : 문자열을 date_format형식에서 지정한 날짜형식으로 변환

SELECT TO_DATE('2023-07-10 10:50:50', 'YYYY-MM-DD') FROM DUAL; -- 2023-07-10 11:02:50

5) 숫자변환형식(number_format)
ㄱ. ,(콤마) : 콤마 표시할 때 사용  

SELECT TO_CHAR(123456, '999,999') -- 123,456

ㄴ. .(소수점) : 소수점 표시 

SELECT TO_CHAR(123456.7, '999,999.9'_ -- 123,456.7

ㄷ. 9 : 10진수로 한 자리 숫자를 나타냄.
- 사용할때는 실제 값의 자리 수와 같거나 커야함. (크면 공백으로 표시 됨)
- 정수부 자리가 모자라면 표기에러 출력
- ',', '.'은 자리수에 안들어감
- 소수점 이하 자리수가 모자라면 잘라내고 반올림

SELECT TO_CHAR(123456, '9,999,999') -- 123,456 : 자리수를 의미하는 9는 실제 숫자의 크기가 6자리이므로 6개

-> '999,999'로 나타나야하며 작으면 표기 에러가 된다(큰 것은 상관없음)

6) 날짜형_변환형식 : 날짜형은 년/월/일/시/분/초로 구성되므로 변환형식이 복잡함
ㄱ. 년형식 : 연도 표시 
형식표기 : 'YYYY ', 'YYY', 'YY', 'Y'

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

ㄴ. 월형식 : 월 표시
형식표기 : 'MONTH', 'MON', 'MM'(숫자만 표시), 'mm' (숫자만 표시)

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, 'MM') FROM DUAL; -- 07

ㄷ. D : 주중 요일은 1~7 숫자로 표시
형식표기 : 1 = 일요일, 2 = 월요일 ... 7 = 토요일

SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL; -- 2

-> 월요일 

ㄹ. DAY : 주중 요일을 월요일~일요일로 표시 

SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL; -- 월요일

ㅁ. DD : 각 일들을 1~31숫자로 표시 (01, 02, ... 31 형태)

SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL; -- 10

ㅂ. DDD : 각 일들을 1~365숫자로 표시 (001, 002 ... 365 형태)

SELECT TO_CHAR(SYSDATE, 'DDD') FROM DUAL; -- 191

ㅅ. DL : 날짜를 완전한 형식으로 표현 - 0000년 00월 00일 0요일 형식

SELECT TO_CHAR(SYSDATE, 'DL') FROM DUAL; -- 2023년 7월 10일 월요일

 

(참고) INSERT구문 작성시 날짜를 지정할때 오라클이 문자열을 날짜형식으로 암시적 형변환을 해줌

- 정석인 명시적 형변환으로 하는 것 권고

-- 오라클이 문자열을 날짜형식으로 암시적 형변환(자동)
INSERT INTO EMPLOYEE (emp_id, EMP_NAME, GENDER, AGE, HIRE_DATE)
	values(10, '황진이', '여성', 33, '2023-01-05');
-- 동일(명시적 형변환)	
INSERT INTO EMPLOYEE (emp_id, EMP_NAME, GENDER, AGE, HIRE_DATE)
	values(10, '황진이', '여성', 33, to_date('2023-01-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

7) 시간변환형식 : 시간으로 표현하는 표현식
ㄱ. HH || HH12 : 시간을 01 ~ 12 형식으로 표현

- 오후 1시

SELECT TO_CHAR(SYSDATE, 'HH') FROM DUAL; -- 01

ㄴ. HH24: 시간을 01 ~ 24형식으로 표현

- 오후 1시

SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL; -- 13

ㄷ. MI : 분을 01 ~ 59형식으로 표현

SELECT TO_CHAR(SYSDATE, 'MI') FROM DUAL; -- 47

ㄹ. SS : 초를 01 ~ 59형식으로 표현

SELECT TO_CHAR(SYSDATE, 'SS') FROM DUAL; -- 36

ㅁ. WW : 1년을 주 단위 형태로 표시 01 ~ 53

- 7월 10일은 28째주

SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL; -- 28

8) NULL 처리함수(NVL, NVL2, COALESCE, NULLIF)
ㄱ. NVL(표현식1, 표현식2) : 표현식1이 NULL이면 표현식2(대체값)로 변환
- NULL허용 컬럼을 조회할 때 해당 컬럼에 데이터가 있으면 표현식1의 값을 출력
- 데이터가 없다면(NULL)인 경우는 표현식2의 값을 출력

ㄴ. NVL2(표현식1, 표현식2(표현식1 <> NULL), 표현식3(표현식1 = NULL))
- 표현식1의 값이 NULL이면 표현식3의 값 출력 NULL이 아니면 표현식2의 값 출력

ㄷ. COALESCE(표현식1, 표현식2, 표현식3, ...)
- 매개변수로 전달된 표현식의 값들 중에서 처음으로 NULL이 아닌 값이 나오는 경우의 표현식 값을 리턴한다

ㄹ. NULLIF(표현식1, 표현식2)
- 표현식1과 표현식2를 비교해서 두 값이 같으면 NULL이 출력
- 다르면 표현식1을 출력

ex)

SELECT 
    NVL(NULL, '값 없음'), -- 값 없음
    NVL2(1, 2, 3), -- 2
    COALESCE(NULL, NULL, 5, 4, NULL), -- 5
    NULLIF('NULL', 'null') -- NULL
  FROM DUAL;

9) 데이터형 크기비교(대소비교) 함수(GREATEST, LEAST, DECODE)
ㄱ. GREATEST(expr1, expr2, expr3, ...)
expr1, expr2, ... 표현식들 중에 비교하여 가장 큰 값을 찾아 반환
expr 매개변수 값들에 대하여 내부적인 비교연산을 통해 큰 값을 반환
문자, 숫자, 날짜형이 동시에 올 수 있다
오라클이 내부적으로 비교가 가능하도록하여 대소관계를 만들며 그 반환형은 expr1의 데이터형식을 따른다
ex) GREATEST('a', 2, 3, 4, 5)를 비교하는 경우
첫 번째 자료형이 문자형이므로 2, 3, 4, 5는 내부에서 암시적으로 문자형으로 취급
ASCII(아스키코드) 문자비교가 가능, 아스키코드에서는 숫자가 문자보다 코드 값이 작으므로 'a' > '4'보다 큰 값으로 간주된다

-- 첫 값이 문자이므로 나머지를 문자형으로 변환   
SELECT GREATEST('abc', 1, 2, 5, 'd') FROM DUAL; -- d  
-- 첫 값이 숫자이므로 나머지를 숫자로 변환
SELECT GREATEST(1, '100', 3, 2, 5) FROM DUAL; -- 100

ㄴ. LEAST(expr1, expr2, expr3, ...)
expr1, expr2, ... 표현식들 중에 비교하여 가장 작은 값을 찾아 반환

SELECT LEAST(1, '100', 3, 2, 5) FROM DUAL; -- 1

ㄷ. DECODE : if ~ else와 유사
* 단순 DECODE - DECODE(컬럼, 조건1, 참인 경우 값, 거짓인 경우 값)
* 복합 DECODE - DECODE(컬럼명, 조건1, 결과1, 조건2, 결과2, ... , default값)
-> 표현식(컬럼)과 조건1을 비교하여 같으면 결과 1 반환, 다르면 조건2와 비교하 같으면 결과2 반환 ..(반복)
더 이상 같은 값이 없으면 (else) default 값 반환

-- 임시테이블 만들기
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 : 임시테이블을 만들때 사용
임시로 사용하는 테이블 조회 시 다시 재사용해야하는 경우
WITH 임시테이블명 AS(조회SQL구문)형식으로 임시 질의 SQL에 별명을 붙여 다른 SQL에서 재사용
view에 비해 속도가 빠르고 쿼리플랜을 이용해서 재사용성을 높여 작은 데이터를 사용하는 경우 유용
UNION ALL 중복허용, 다음 구문과 합쳐라 

 

10) CASE
- DECODE함수를 구체화하여 SELECT절의 구문형식으로 확장할 수 있다
- DECODE함수와 같이 컬럼이나 표현식을 비교하여 조건에 맞는 결과 값을 리턴한다
* 구문 :
ㄱ. 단순형 구문(switch와 유사)

  CASE 표현식 WHEN 비교조건1 THEN 결과1 
             WHEN 비교조건2 THEN 결과2
             WHEN 비교조건3 THEN 결과3
              else 기본값
  END

[해석]

표현식 == 비교조건1 이면 결과 1 리턴
표현식 == 비교조건2 이면 결과 2 리턴
표현식 == 비교조건3 이면 결과 3 리턴
표현식이 어떤 조건과도 같지 않으면 기본값 리턴

ㄴ. 검색형 구문(다중 if와 유사)

    CASE WHEN 조건식1 THEN 결과1 
         WHEN 조건식2 THEN 결과2
         ...
         else 기본값
    END

[해석]
조건식1이 참이면 결과1 리턴
조건식2이 참이면 결과2 리턴
...
위의 조건식이 모두 거짓이면 기본값 리턴

-- 검색형 CASE 구문
SELECT 
	e.EMP_NAME,
	e.AGE,
	CASE WHEN e.AGE BETWEEN 0 AND 19 THEN '10대'
		 WHEN e.AGE BETWEEN 20 AND 29 THEN '20대'
		 WHEN e.AGE BETWEEN 30 AND 39 THEN '30대'
		 WHEN e.AGE BETWEEN 40 AND 49 THEN '40대'
		 WHEN e.AGE BETWEEN 50 AND 59 THEN '50대'
		 ELSE '60대 이상'
	END ages
FROM EMPLOYEE e;