오라클 SQL 문법 공부10 - TO_CHAR, TO_DATE, TO_NUMBER

서론

이번에는 형변환 함수에 대해서 알아보고자 한다. 문자형, 숫자형, 날짜형 간의 연산을 하기 위해서는 같은 형끼리만 가능한데, 예를들어 VARCHAR2 형태의 속성이라면 값이 10이 들어가더라도 문자 10으로 인식한다. 보통 오라클에서는 WHERE절에 조건을 써 넣을때 COLUMN = 10 이라고 넣으면 COLUMN 컬럼이 문자형이라는 전제하에 묵시적형변환이 되어 자동으로 TO_NUMBER(COLUMN)으로 변경되어 인덱스를 타지도 않고 데이터가 많을 경우 성능이 좋아지지 않는다. 잘 알아두어야 하는 개념이다.

본론

TO_CHAR, TO_NUMBER

--TO_CHAR 함수 (숫자형 -> 문자형으로 변환하기)
--9  9의 개수만큼 자리 수 TO_CHAR(9876, '99999') 9876
--0  0 빈자리를 0으로 채우기 TO_CHAR(9876, '099999') 009876
--$  $ 표시를 붙여서 표시 TO_CHAR(9876, '$99999') $9876
--.  소수점 이하표시         TO_CHAR(9876, '9999.99') 9876.00
--,  천 단위 구분기호를 표시 TO_CHAR(9876, '99,999') 9.876

desc emp2;
select to_char(pay, '999,999') from emp2;
--데이터의 자리수가 크면 안나올 수 있으니 넉넉하게 자리수를 설정해주어야 함-
select to_char(pay, '999,999,999') from emp2;
select max(length(pay)) from emp2;
-- 최대 길이 확인하여 자리수를 설정하면 좋음

--TO_CHAR 함수 (날짜형 -> 문자형으로 변환하기)
--[년도]
--YYYY - 연도를 4자리로 표시
--RRRR - 2000년 이후에 Y2K버그로 인해 등장한 날짜 표기법 연도 4자리
--2000년도 이후의 날짜를 표시하고 싶을때 사용
--YY - 연도를 끝의 2자리마 표시
--RR - 연도를 마지막 2자리만 표시
--YEAR - 연도의 영문 이름

--[월]
--MM - 01~12월 표시
--MONTH, MON - 1~12월 표시

--[일]
--DD - 일을 숫자 2자리로 표시
--DAY - 요일에 해당하는 명칭을 표시. NLS_LANGAGE 에 따라 다름
--DDTH - 몇 번째 날인지를 표시
--WSPTH - 서수 (EX: FOURTH)

--[시간]
--HH24 - 하루를 24시간 표시
--HH - 하루를 12시간 표시
--MI - 분표시
--SS - 초표시

--WW - 1년 기준 몇번째 주인지 표시
--AM ,PM - 오전 오후 표시
--포맷을 소문자로 쓰면 소문자로 표시됨
--Q 분기 (1분기, 2분기, 3분기, 4분기)
alter session set nls_date_language=american;

SELECT SYSDATE,
TO_CHAR(SYSDATE, 'YYYY'),
TO_CHAR(SYSDATE, 'YYYY-MM-DD'),
TO_CHAR(SYSDATE, 'Year'),
TO_CHAR(SYSDATE, 'MON'),
TO_CHAR(SYSDATE, 'DAY'),
TO_CHAR(SYSDATE, 'ddth'),
TO_CHAR(sysdate, 'DDSPTH'),
TO_CHAR(SYSDATE, 'Q')
FROM DUAL;

--89년 이러면 1900년도인지 2000년도인지 모르는데 YYYY로 하면 2000년도로
--RRRR로 하면 1900년도

--직접적으로 TO_NUMBER로 사용하는 경우는 별로 없다
--TO_NUMBER('숫자처럼 생긴문자')
--보통 '2' + 2 이런식으로 하면 TO_NUMBER('2')로 바꿔서 계산 해준다.
--이런걸 보고 묵시적 형변환, 오라클에선 숫자형이 먼저여서 숫자형으로 계산해줌.
--성능상 좋지 않음 비교하고자 하는 컬럼에 함수를 안씌우는것이 최고
--튜닝할때 가장 먼저 체크할 것
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE, 6), 2), 'DAY"the" WSPTH "of" MONTH, YYYY') FROM EMP;

보통 쿼리 출력 양식을 변경하거나 조건 절 등에서 비교를 할때 형을 맞춰주기 위해 사용되는 문자형 변환 함수이다. 다른 DBMS에 비해 오라클이 사용하기 편하게 되어있다. TO_NUMBER는 보통 묵시적 형변환이 되기 때문에 자주 사용되진 않지만 명시적으로 표시하는게 성능상 도움이 된다. 그리고 사실 숫자값만 있는 컬럼들은 보통 NUMBER 컬럼이기 때문에 TO_NUMBER까지 할 필요도 없고 아니면 혹시나 VARCHAR2 형태로 숫자 데이터들이 있다 하더라도 문자형 그대로 '2' 이런식으로 비교를 하면 되기 때문에 자주 사용되진 않는다.

TO_DATE

--TO_DATE() 함수
--TO_DATE('날짜 처럼 생긴 숫자 또는 문자', '앞 문자의 날짜형식')
--TO_CHAR에서 사용된 날짜형을 문자형으로 바꾸기의 포맷들이 동일하게 사용된다.
--따로 포맷 지정 안해주면 YYMMDD 순으로 처리함

SELECT TO_DATE('14/12/25', 'RR/MM/DD'),
TO_DATE('14/12/25', 'YY/MM/DD'),
TO_DATE('141225'),
TO_DATE('14/12/25', 'DD/MM/YY')
FROM DUAL;

SELECT SYSDATE - '14/12/15' FROM DUAL;
--날짜형식이 아니므로 당연히 계산 안됨
SELECT SYSDATE - TO_DATE('14/12/15') FROM DUAL;

TO_DATE를 하는 이유는 보통 날짜 계산을 할때 사용하기 위해 변환을 한다.

2017/09/22 - [공부/Oracle] - 오라클 SQL 문법 공부9 - MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY

위의 글의 함수들을 사용하여 정확하게 날짜들을 계산을 하며 가끔 날짜 정보가 VARCHAR2 형태로 데이터가 입력되는 경우들도 있는데 이럴 경우에도 역시 TO_DATE를 사용하여 포맷 변경 후 날짜 연산을 통해 원하는 값을 추출 할 수가 있을 것이다.

TO_CHAR, TO_DATE 정리

--[TO_CHAR 와 TO_DATE 정리]

--날짜의 포맷 변경은 TO_CHAR로 가능하며 이때 TO_CHAR의 첫번째 인수는 반드시 날짜타입
--=> TO_CHAR(날짜, '원하는 날짜 포맷')

--TO_DATE는 날짜처럼 생긴 문자나 숫자를 DB에 날짜 타입으로 인식시켜 날짜연산이나 날짜포맷변경을 할때 주로 사용된다.
--=>TO_DATE('날짜처럼 생긴 문자', '앞 문자의 날짜형식')
--TO_DATE에서의 YY인식 방법
YY < 50 => 20XX
YY >=50 => 19XX

--EX) TO_DATE('01-12-20', 'DD-YY-MM')
--TO_DATE('97-12-20', 'DD-MM-YY') - 에러발생, 97일이 존재하지 않으므로

SELECT TO_DATE('14/12/25'),
       TO_DATE('14/12/25', 'RR/MM/DD')
  FROM DUAL; --1914로 표현 X
  
  
SELECT TO_DATE('14/12/25'),
       TO_DATE('14/12/25', 'YY/MM/DD')
  FROM DUAL; --2050으로 표현 O
  
--YY는 50보다 크면 19로 하고 작으면 20으로 하는걸로 봐서
--값이 적으면 2000년대 근처라고 생각하고 20 붙이는 것 같음
--RR은 무조건 앞에 20 붙이는 것 같다.

실습문제

--문제1) EMP 테이블에서 10번 부서원의 입사 일자를 '1 MAY 1981'와 '1998년 1월 1일'의 형태로 각각 출력하여라*/
select to_char(hiredate, 'DD MON YYYY'),
       to_char(hiredate, 'YYYY"년" MON"월" DD"일"')
  from emp
 where deptno = 10;

--문제2) EMP 테이블에서 20번 부서원의 급여 앞에 $를 삽입하고 3자리마다 , 를 출력하여라*/
select to_char(SAL, '$9,999')
from emp
where deptno = 20;

--문제3) february 22, 1981에 입사한 사원의 이름, 업무, 입사일자를 출력하여라
--nls_date_language=american일 경우
ALTER SESSION SET nls_date_language=american;
select *
from emp
where HIREDATE = TO_DATE('february 22, 1981', 'MM DD, YYYY');