[SQL 08] 단일 행 함수 - 변환 함수

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
데이터 변환 유형

오라클은 상식적인 수준의 형 변환을 자동으로 구현하고 있다. (암시적 데이터 변환)

varchar2, char → number (문자가 유효한 숫자로만 나타내는 경우)

varchar2, char →date

number  → varchar2

date  → varchar2

예를 들면

select '1' + '2'

from dual;

  연산자를 사용할 경우 문자 '1'과 '2'를 숫자 1,2로 자동으로 변환되어 계산된다.

데이터 변환 유형은 2가지이다.

오라클 자체적으로 자동으로 데이터를 변환하는 것을 암시적 데이터 변환이라 하고,

변환 함수를 사용하여 데이터의 형 변환을 하는 것을 명시적 데이터 변환이라 한다.

SQL문의 좀 더 빠른 응답을 위해서는 명시적인 함수의 사용을 권장한다.

No. 02
단일 행 함수에서 사용하는 출력 형식

▣ 날짜 출력 형식

	표현 문자	결  과
년	YYYY	네 자리로 표현된 년도 (2000, 2007, 2017)
	YY	두 자리로 표현된 년도 (99, 07, 17)  ◆ 앞에 생략된 두 자리는 현재 년도를 사용한다.  ◆ 99년은 2099년을 의미한다.
	RR	두 자리로 표현된 년도 (99, 07)  ◆ 앞에 두 자리른 현재 년도와 가까운 년도를 사용한다.  ◆ 99년은 1999년을 의미한다.
월	MM	두 자리 숫자로 표현된 월 (06, 07)
	MONTH	영문이나 한글로 표현된 월 (JUNE, JULY, 6월, 7월)
	MON	약자로 표현된 영문 및 한글 월 (JUN, JUL, 6월, 7월)  ◆ 한글인 경우 MONTH와 동일하다.
일	DD	두 자리 숫자로 표현된 일자 (07, 10)
	DAY	영문이나 한글 요일 (SUNDAY, MONDAY, 일요일, 월요일)
	DY	약자로 표현된 요일 (SUN, MON, 일, 월)

▣ 시간 출력 형식

	표현 문자	결  과
시	HH24	1시에서 24시까지 표현
	HH	1시에서 12시까지 표현   ◆ AM/PM은 표현되지 않는다.
분	MI	두 자리 분 표시
초	SS	두 자리 초 표시
	SSSSS	하루를 초로 환산한 다음 표현(0-86399)
오전/오후	AM/PM	오전 오후 표기 (둘 중 하나만 쓰면 된다.)

▣ 숫자 출력 형식

표현 문자	결  과
9	숫자의 출력 폭지정. (자리수가 부족하면 생략)
0	선행 0 표기 (자리수를 반드시 맞춘다.)
$	화폐 표기
L	지역 화폐 표기 (각 국가 코드에 따라 다르다.)
,	쉼표 위치 지정
.	마침표 위치 지정
MI	음수의 - 기호를 오른쪽에 표기
EEEE	실수 표현법을 이용

No. 03
변환 함수

숫자 → 문자, 날짜 → 문자 : TO_CHAR 함수

문자 → 숫자 : TO_NUMBER 함수

문자 → 날짜 : TO_DATE 함수

▣ 변환 함수

함수명	기 능	사 용
TO_CHAR	날짜나 숫자를 문자로 변환한다.	TO_CHAR(날짜, 출력형식)   TO_CHAR(숫자, 출력형식)
TO_DATE	문자를 날짜형으로 변환한다.	TO_DATE(문자, 출력형식)
TO_NUMBER	문자를 숫자로 변환한다.	TO_NUMBER(문자, 출력형식)

◈ 예제

▶ 현재 날짜를 다양한 형식으로 출력

select to_char(sysdate, 'yyyy/mm/dd') 날짜,

   to_char(sysdate, 'yyyy/mm/dd:hh24:mi:ss') "날짜+시간",

   to_char(sysdate, 'yyyy/mm/dd/hh:mi:ss am') "오전/오후 표시"

from dual;

select to_char(sysdate, 'dd month yyyy') 오늘날짜1,

   to_char(sysdate, 'day mon yy') 오늘날짜2,

   to_char(sysdate, 'dy mon yy') 오늘날짜3

from dual;

select to_char(sysdate, '"오늘 날짜는 "yyyy"년 "mm"월 "dd"일 입니다."')

from dual;

형식 안에 문자열을 추가할 경우 이중 인용부호(")을 사용해야 한다.

▶ 다양한 형식으로 숫자를 출력

select to_char(1234.56, '999,999.9999') 숫자

from dual;

  소수점 이하에 지정한 자리는 0을 채우지만 앞쪽 빈자리는 무시한다.

select to_char(1234.56, '009,999.99') 숫자

from dual;

  숫자 출력형식 0을 사용하면 자리수를 반드시 맞춘다.

select to_char(1234.56, '9999.9') 숫자

from dual;

  소수점 이하 자리수를 부족하게 지정하면 반올림 된다.

select to_char(1234.56, '999.99') 숫자

from dual;

  소수점 앞쪽 형식을 원래 값보다 작게 하면 제대로 표시되지 않는다.

select to_char(1234.56, '$99,999.99') 화폐표기, to_char(1234.56, 'L99,999.99') 지역화폐표기

from dual;

지역화폐표기는 시스템에 설정되어 있는 지역마다 다르다. (￥, ￦, ＄ …)

▶ TO_DATE 사용

select '2002/01/01' + 1

from dual;

ORA-01722: invalid number

01722. 00000 -  "invalid number"

select to_date('2002/01/01') + 1

from dual;

얼핏 보면 첫 번째 SQL문도 실행이 될 것 같지만 에러가 난다. '2002/01/01'는 모양은 날짜같지만 실제로는 문자이기 때문이다.

이럴 때 TO_DATE 함수를 사용하여 날짜를 바꾸어 계산할 수 있다.