DB 5일 (23.03.27)

1. 날짜 함수

• SELECT SYSDATE FROM DUAL;
• SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일 FROM DUAL;

• SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) 근무개월수 FROM EMP;
• SELECT ENAME, SYSDATE, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) 근무개월수 FROM EMP;

• SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 4) "+4개월" FROM EMP;
• SELECT SYSDATE "오늘", NEXT_DAY(SYSDATE, '수요일')"돌아오는 수요일" FROM DUAL;
• SELECT HIREDATE, LAST_DAY(HIREDATE) "해당 월의 마지막 날" FROM EMP;

2. 형 변환 함수

2-1. 날짜를 문자로 바꾸기

• SELECT SYSDATE "기본 날짜형", TO_CHAR(SYSDATE, 'YYYY-MM-DD') "문자형으로" FROM DUAL;
• SELECT HIREDATE, TO_CHAR(HIREDATE, 'YYYY/MM/DD DAY') "날짜에 요일" FROM EMP;

• SELECT ENAME, TO_CHAR(HIREDATE, 'YYYY-MM-DD') FROM EMP;
• SELECT ENAME, TO_CHAR(HIREDATE, 'YYYY/MM/DD DAY') FROM EMP;

• SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS') "현재 시각" FROM DUAL;
• SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH:MI:SS') "현재 시각" FROM DUAL;

2-2. 숫자 표시

• SELECT ENAME, SAL, TO_CHAR(SAL, 'L999,999') FROM EMP;
• SELECT TO_CHAR(123456, '000000000') FROM DUAL;
• SELECT TO_CHAR(123456, '999,999,999') FROM DUAL;

2-3. 문자를 날짜로 바꾸기

• SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19810220, 'YYYYMMDD');
  • HIREDATE = 19810220 (X) > 숫자형
  • HIREDATE = '19810220'
  • HIREDATE = '810220'
  • HIREDATE = '81/02/20'
  • HIREDATE = '81.02.20'

2-4. 숫자 바꾸기

• SELECT TO_NUMBER('20,000', '99,999') FROM DUAL;
• SELECT TO_NUMBER('20,000', '99,999')-TO_NUMBER('10,000', '99,999') FROM DUAL;

3. NVL

1. 전체를 본다.

• SELECT * FROM EMP ORDER BY JOB;

2. NULL인 값은 계산할 수가 없다.

• SELECT ENAME, SAL, COMM, SAL*12+COMM FROM EMP ORDER BY JOB;

3. NVL 함수를 이용하여 계산한다. (COMM이 null이면 0으로 계산하도록 한다.)

• SELECT ENAME, SAL, COMM, NVL(COMM, 0), SAL*12+NVL(COMM, 0) FROM EMP ORDER BY JOB;