형 변환과 날짜 포맷

#핵심 요약

데이터 유형은 문자형, 숫자형, 날짜형 세 가지이고, 변환 함수는 TO_CHAR(숫자나 날짜를 문자로), TO_NUMBER(문자를 숫자로), TO_DATE(문자를 날짜로) 세 가지임

TO_DATE('81/11/17', 'RR/MM/DD')처럼 형식을 지정하면 세션 날짜 형식과 상관없이 날짜를 검색할 수 있음

문자와 숫자를 비교하면 Oracle은 문자를 숫자로, 문자와 날짜를 비교하면 문자를 날짜로 바꿈

컬럼 쪽이 변환되면 성능이 나빠질 수 있음
실습의 sal LIKE '30%'는 오류는 없지만 숫자 컬럼을 문자로 바꿔 비교함

#개념과 동작 원리

Oracle이 명시적 변환을 권하는 이유:

1. 변환 함수를 쓰면 SQL을 이해하기 쉬움
2. 암시적 변환은 성능에 나쁠 수 있음
   특히 상수가 아니라 컬럼 값의 타입이 바뀌는 경우임
3. 암시적 변환은 상황에 따라 다르게 동작함
   예를 들어 날짜를 VARCHAR2로 바꿀 때 NLS_DATE_FORMAT에 따라 예상과 다른 연도가 나올 수 있음
4. 암시적 변환 알고리즘은 릴리스와 제품에 따라 바뀔 수 있음
5. 인덱스 식에서 암시적 변환이 일어나면 인덱스는 변환 전 타입으로 정의되어 있어 쓰이지 않을 수 있음

실습의 실행 계획 예(조건식의 원래 SQL은 확인 불가):

|*  1 |  TABLE ACCESS FULL| EMP  |
   1 - filter("SAL"=3000)

설명: 비교값의 문자를 숫자로 암시적 변환한 결과임
컬럼 SAL은 그대로이고 상수 쪽이 바뀌었음

#필수 명령어 또는 SQL

#날짜를 문자로

SELECT TO_CHAR(SYSDATE, 'day') FROM dual;

SELECT ename, hiredate, TO_CHAR(hiredate, 'YYYY')
  FROM emp;

SELECT ename, birth
  FROM emp19
 WHERE TO_CHAR(birth, 'YYYY') = '1991';

SELECT ename, birth
  FROM emp19
 WHERE TO_CHAR(birth, 'MM') = '05';

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 100), 'day') FROM dual;

결과 해석:

설명: 연도 추출, 월 비교 문장은 Oracle과 PostgreSQL에서 같음
PostgreSQL에서 한국어 요일을 보려면 TO_CHAR(current_date, 'TMday')와 한국어 설정이 필요했음

위 세 번째, 네 번째 문장은 컬럼 birth를 함수로 감싸므로 birth에 일반 인덱스가 있어도 쓰이기 어려움
범위 조건(birth >= TO_DATE('1991/01/01', 'YYYY/MM/DD') AND birth < TO_DATE('1992/01/01', 'YYYY/MM/DD'))으로 바꾸는 방법이 있음
함수 기반 인덱스는 인덱스 정의에서 다룸

#숫자를 문자로

SELECT ename, sal, TO_CHAR(sal, 'L999,999')
  FROM emp;

결과 해석:

설명: L은 통화 기호, ,는 천 단위 구분임
실습에서는 연봉(월급*12), 성과급(월급의 115%)에 천 단위와 통화 기호를 붙이는 문제를 냈음

#문자를 날짜로

ALTER SESSION SET nls_date_format = 'DD-MM-RR';

SELECT ename, hiredate
  FROM emp
 WHERE hiredate = TO_DATE('81/11/17', 'RR/MM/DD');

결과 해석: 세션 형식을 DD-MM-RR로 바꿔도 TO_DATE로 형식을 지정했으므로 검색됨

시각까지 들어간 값:

INSERT INTO emp19 (empno, ename, birth)
VALUES (40, '<NAME>', TO_TIMESTAMP('1997/12/31:10:40:00', 'RRRR/MM/DD:HH24:MI:SS'));

결과 해석:

실습에서는 이 행을 넣은 뒤 "1997년에 태어난 학생"을 다시 찾게 했음
birth = TO_DATE('1997/12/31',...)처럼 날짜만으로 비교하면 시각(10:40)이 있는 행은 맞지 않는다는 점을 확인하는 과제로 보이나, 수업 자료에 답과 설명은 없음
바꿀 입력값: <NAME>

#암시적 형 변환과 이식성

SELECT ename, sal
  FROM emp
 WHERE sal LIKE '30%';

결과 해석:

설명: Oracle에서는 오류가 나지 않지만 암시적 형 변환이 일어나 검색 성능이 떨어짐
PostgreSQL에서는 이 문장이 오류가 나고 암시적 변환을 하지 않음
문제: Oracle에서 쓰던 sal LIKE '2%' AND deptno = '10'을 PostgreSQL에서 오류 없이 고치기(답 확인 불가)

#사용 조건과 주의사항

형식 모델 요소(YYYY, MM, day, L, 999,999, HH24:MI:SS)의 전체 목록은 공식 문서 Format Models에 있음