[SQL] 데이터 유형 변환 함수 (단일 행 함수 4)

데이터 유형 변환

1. 암시적 데이터 유형 변환
   1. 오라클 서버에 의해 자동으로 수행되는 데이터 유형 변환
2. 명시적 데이터 유형 변환
   1. 변환 함수를 사용하여 사용자에 의해 수행되는 데이터 유형 변환
   2. SQL 문의 신뢰성과 가독성을 높이기 위해서 권장되는 방법

변환 함수

1. SQL은 값의 데이터 유형을 변환하는 세 가지 함수 제공
   1. TO_CHAR
   2. TO_DATE
   3. TO_NUMBER

 

• 암시적 형변환, 숫자 데이터 유형인 salary열의 값은 문자 함수 LPAD 사용으로 오라클 서버에 의해 자동으로 문자 데이터 유형으로 변환

SELECT emp_name, position, LPAD(salary, 10, '*')
FROM y_emp
WHERE email LIKE '%k%';

 

• 암시적 형변환, WHERE 절의 문자 100을 숫자 100으로 데이터 유형의 자동 변환을 수행한다.
• 문자 유형에서 숫자 유형으로의 변환은 그 문자열이 숫자로만 이루어져 있을 때 가능

SELECT *
FROM y_dept
WHERE dept_id = '100';

 

TO_CHAR 함수

1. 숫자 또는 날짜 데이터를 문자 데이터로 변환할 때 사용
2. 구문 

TO_CHAR([숫자 | 날짜], 'format_model')

 

숫자 데이터에 TO_CHAR 함수 사용
(예) 숫자 데이터에 통화 기호나 자릿수 구분기호를 추가하여 출력
형식 요소	설명
9	실제 자릿수를 나타낸다.
0	전체 자릿수를 나타낸다. 빈 자리는 강제로 0을 표시한다.
$	$기호를 강제로 추가한다.
L	해당 지역의 통화 기호를 추가한다.
,	천 단위 구분 기호를 표시한다.
.	소수점을 표시한다.

---

날짜 데이터에 TO_CHAR 함수 사용
(예) 기본 형식의 날짜 데이터를 사용자가 지정하는 날짜 형식으로 변환
형식 요소	설명
YYYY / YY	두 자리 또는 네 자리 숫자로 표현된 연도
YEAR	문자(영어)로 표현된 연도
MONTH	문자로 표시된 달의 이름
MON	세 문자의 약어로 표시된 달의 이름
MM	두 자리의 숫자로 표시된 달
DAY	요일 전체 이름
DY	약어로 표시된 요일 (SUN, MON, 화, 수...)
DDD / DD / D	숫자로 표현된 연도, 월, 주 기준의 일
WW / W	연도 또는 월 기준의 주수
Q	분기
HH / HH24	시간
MI	분
SS	초
AM / PM	오전 / 오후

 

• 100번 부서 사원들의 급여를 국가통화기호 및 자릿수 구분기호를 추가하여 표시
• 현재 나라의 국가통화기호와 숫자는 만 단위까지, 천 단위마다 , 로 구분하고 소수점 둘째 자리까지

SELECT emp_id, emp_name,
       TO_CHAR(salary*12, 'L99,999.00') ANNUAL_SALARY
FROM y_emp
WHERE dept_id = 100;

 

• 200번 부서 사원의 입사일을 'yyyy-mm' 형식으로 표시

SELECT emp_id, emp_name,
       TO_CHAR(hiredate, 'yyyy-mm') hiredate
FROM y_emp
WHERE dept_id = 200;

 

• SYSDATE와 TO_CHAR 함수를 사용하여 오늘 날짜를 'YYYY/MM/DD HH:MI:SS AM'의 형식으로 표시

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

 

• 형식에 fm요소를 추가하여 선행 0 또는 공백을 제거해 표시

SELECT TO_CHAR(hiredate, 'fmyy/month/dd') "fm 사용",
       TO_CHAR(hiredate, 'yy/month/dd') "fm 미사용"
FROM y_emp;

 

TO_NUMBER 함수

1. 사용자가 인수로 지정한 데이터 형식 요소를 제거하면 숫자가 되는 값에 인수의 형식을 명시
2. 문자인 첫 번째 인수도 작은 따옴표 필요
3. 형식 요소를 제거했을 때 숫자가 되는 데이터만 유효
4. 구문

TO_NUMBER('char' [, 'format_model'])

 

• 연봉이 1억 이상인 사원을 검색할 때 WHERE 절에서 비교조건인 10,000을 숫자 10000으로 변환하여 비교

SELECT emp_id, emp_name, position, dept_id
FROM y_emp
WHERE salary * 12 > TO_NUMBER('10,000', '99,999');

 

 

 

TO_DATE 함수

1. 지정되는 데이터를 날짜로 변환
   1. 입력되는 인수와 형식요소에 날짜와 관련된 값들만 사용
2. 기본 날짜 형식과 일치하지 않는 날짜를 오라클이 날짜가 아닌 문자로 처리해서 검색에 실패하는 경우 사용
3. 구문

TO_DATE('char'[,'format_model'])

 

• 1992년 1월 1일 이후에 태어난 사원을 검색하고자 할 때 다음과 같이 사용자가 지정한 날짜가 현재의 날짜 형식과 맞지않아서 오류가 발생한다.

SELECT emp_id, emp_name, birthdate
FROM y_emp
WHERE birthdate >= '01-01-1992';

 

• 위 문장을 수정하여 1992년 1월 1일 이후에 태어난 사원 표시

SELECT emp_id, emp_name, birthdate
FROM y_emp
WHERE birthdate >= TO_DATE('01-01-1992', 'DD-MM-YYYY');

 

RR과 YY 형식 요소

1. 사용자가 연도를 두 자리만 입력하면 오라클은 자동으로 연도 앞에 세기를 추가하여 검색하는 방식
   1. YY 날짜 형식 요소는 현재 날짜와 같은 세기정보를 사용하는 형식
   2. RR 날짜 형식 요소는 사용자가 지정한 날짜의 두 자리 연도와 현재 연도의 마지막 두 자리에 따라 오라클에 인식되는 세기값이 달라지는 형식
      1. RR 형식 요소는 현재 연도가 0~49년 사이일 때 인수로 사용되는 데이터가 50~99년 사이의 값을 가지면 이전세기를 사용하는 날짜 형식
      2. 연도 두 자리만 지정해서 처리하는 많은 응용프로그램에서 21세기에 20세기 데이터를 처리하는 동안 유용하게 사용

 

• 이전 세기에 해당되는 1992년을 연도 두자리만으로 검색하기 위해서는 RR형식을 사용

SELECT emp_id, emp_name, birthdate
FROM y_emp
WHERE birthdate >= TO_DATE('01-01-92', 'DD-MM-RR');

 

• YY 형식을 사용하면 1992년이 아닌 2092년을 검색하기 때문에 결과가 없다.

SELECT emp_id, emp_name, birthdate
FROM y_emp
WHERE birthdate >= TO_DATE('01-01-92', 'DD-MM-YY');