Day 53

3. 날짜 관련 함수들

함수명	의미	결과(리턴값)
SYSDATE	시스템의 현재 날짜와 시간	날짜
MONTHS_BETWEEN	두 날짜 사이의 개월 수	숫자
ADD_MONTHS	주어진 날짜에 개월을 더함	날짜
NEXT_DAY	주어진 날짜를 기준으로 돌아오는 날짜 출력	날짜
LAST_DAY	주어진 날짜가 속한 달의 마지막 날짜 출력	날짜
ROUND	주어진 날짜를 반올림 (오전오후 구분할때..)	날짜
TRUNC	주어진 날짜를 버림(연초, 월초 ...)	날짜

ex. MONTHS_BETWEEN은 큰 날짜를 먼저 써야 양수값이 나옴

SELECT SYSDATE, MONTHS_BETWEEN('23/04/10', '23/04/01'),
MONTHS_BETWEEN('2023-04-10', '2023-04-01')
FROM dual;

결과

ex. 이번달 첫날, 이번달 마지막날, 저번달 마지막날, 다음달 첫날 구하기

SELECT LAST_DAY(SYSDATE) + 1 "다음달 첫날"
FROM dual;

SELECT TRUNC(SYSDATE, 'MM') "이번달 첫날"
FROM dual;

SELECT LAST_DAY(SYSDATE) "이번달 마지막날"
FROM dual;

SELECT TRUNC(SYSDATE, 'MM') - 1 "저번달 마지막날"
FROM dual;

4. 형 변환 함수

데이터타입	설명
CHAR(n)	고정길이의 문자를 저장, 최대값은 2000 bytes
VARCHAR2(n)	변하는 길이의 문자를 저장, 최대값은 4000 bytes
NUMBER(p,s)	숫자 값을 저장, p는 전체 자리수로 1-38 자리까지, 소수점 이하 자리수로 -84 ~ 127 자리까지 가능
DATE	총 7Byte로 BC 4712년 1월 1일부터 AD 9999년 12월 31일까지의 날짜를 저장할 수 있음

TO_CHAR 함수

형식

SELECT TO_CHAR(SYSDATE , ‘원하는 모양’)
FROM dual

1. 숫자 -> 문자

종류	의미	사용예	결과
9	9의 개수만큼 자리수	TO_CHAR(1234,’99999’)	1234
0	빈자리를 0으로 채움	TO_CHAR(1234,’099999’)	001234
$	$ 표시를 붙여서 표시	TO_CHAR(1234,’$9999’)	$1234
.	소수점 이하를 표시	TO_CHAR(1234,’9999.99’)	1234.00
,	천 단위 구분기호를 표시	TO_CHAR(12345,’99,999’)	12,345

• 수치 값에 천 단위 콤마를 추가하기 위해서는 TO_CHAR 함수를 많이 사용한다.
• TO_CHAR 함수를 사용할 때는 두 번째 인자에 수치 값의 길이에 맞게 패턴 값을 부여해야 한다.
• 패턴 값의 길이가 작을 경우 값을 반환하지 못한다.
• 패턴 값은 되도록이면 미래에 올 수 있는 최대 길이만큼 미리 선언을 해 놓아야 한다. (출처: https://gent.tistory.com/486)

ex.

SELECT empno, ename, TO_CHAR((sal*12)+comm, '999,999') AS "SALARY"
FROM emp
WHERE ename='ALLEN';

2. 날짜 -> 문자
   연도 YYYY(네 자리), YY(두 자리), YEAR (영문이름 전체)
   월 MM(두 자리), MONTH (영문이름전체)
   일 DD(두 자리), DAY(요일), DDTH(몇 번째 날인지)
   시 HH(12시간 단위) / HH24(24시간 단위)
   분 MI
   초 SS

EX.

SELECT SYSDATE, TO_CHAR(SYSDATE), TO_CHAR(SYSDATE, 'YYYY-MM-DD'),  TO_CHAR(SYSDATE, 'YYYYMMDD'),  TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS')
FROM dual;

결과

4. 일반 함수

1. NVL() 함수 : NULL값을 만나면 다른 값으로 치환해서 출력하는 함수

형식

NVL(컬럼, 치환할 값)

2. NVL2(col1, col2, col3)

형식

NVL2(col1, col2, col3)
--col1값이 null이 아니면 col2, null이면 col3

3. DECODE()
   :DECODE 함수는 일반 개발 언어 등에서 사용중인 분기문인 IF 문을 오라클 SQL 안으로 가져온 함수. 오라클에서만 사용되는 함수로 IF 문을 사용해야 하는 조건 문을 처리할 때 사용

:값의 일치를 비교 (=) 인 경우에만 사용!

ex. 마지막 null은 생략가능

--A 가 B 일 경우 ‘1’ 을 출력하는 경우
DECODE(A, B, '1', null)

--A 가 B 일 경우 ‘1’ 을 출력하고 A 가 C 일 경우 ‘2’ 를 출력하고 둘 다 아닐 경우 ‘3’ 을 출력하는 경우
DECODE (A , B , ‘1’ , C , ‘2’ , ’3’)

--A가 B인 경우 중에서 C가 D를 만족하면 1을 출력, 아닐 경우 null을 출력 (중첩)
DECODE (A , B , DECODE( C , D , ‘1’ , null)) 

--A 가 B 일 경우 중에서 C 가 D 를 만족하면 ‘1’을 출력하고 C 가 D가 아닐 경우 ‘2’ 를 출력하기
DECODE (A , B , DECODE( C , D , ‘1’ , ‘2’))

--A 가 B 일 경우 중에서 C 가 D 를 만족하면 ‘1’을 출력하고 C 가 D가 아닐 경우 ‘2’ 를 출력하고 A 가 B가 아닐 경우 ‘3’ 을 출력하는 경우
DECODE (A , B , DECODE( C , D , ‘1’ , ‘2’ ), '3')

4. CASE문
   형식

CASE 조건 WHEN 결과1 THEN 출력1
	[WHEN 결과2 THEN 출력2]
    ELSE 출력3
    END "컬럼명" (별칭)

ex. 비교조건이 '='인 경우 -> 바로 WHEN으로 조건 붙여주고 값 지정

SELECT name, tel
, CASE SUBSTR(tel, 1, INSTR(tel, ')')-1)
  WHEN '02' THEN '서울'
  WHEN '031' THEN '경기'
  WHEN '051' THEN '부산'
  WHEN '052' THEN '울산'
  WHEN '055' THEN '경남'
  ELSE 'ETC'
  END loc
FROM student;

ex. 비교조건이 '='이 아닌 경우

SELECT name, TO_CHAR(birthday, 'MM')
, CASE WHEN TO_CHAR(birthday, 'MM') IN('01', '02', '03') THEN '1/4분기'
  WHEN TO_CHAR(birthday, 'MM') IN('04', '05', '06') THEN '2/4분기'
  WHEN TO_CHAR(birthday, 'MM') IN('07', '08', '09') THEN '3/4분기'
  WHEN TO_CHAR(birthday, 'MM') IN('10', '11', '12') THEN '4/4분기'
  ELSE 'ETC'
  END 분기
FROM student;