[Oracle] 함수 :: 형변환 함수(casting function)

형변환 함수

Java처럼 앞에 자료형을 붙여서 형변환하는 방식이 아니라 함수로 형변환을 지원한다.

 

형변환 함수의 종류

1. to_char(숫자): 숫자 →문자

2. to_char(날짜): 날짜 → 문자(★★★)

3. to_number(문자): 문자 → 숫자

4. to_date(문자): 문자 → 날짜(★★★)

 

1. TO_CHAR(숫자[, 형식문자열]) : 숫자 → 문자 형변환

 암시적 형변환 때문에 사용도가 그리 높지는 않다.

 

형식 문자열 구성 요소

• 9 : 숫자 1개를 문자 1개로 바꾸는 역할. 빈자리를 스페이스로 치환 → %5d와 유사
• 0 : 숫자 1개를 문자 1개로 바꾸는 역할. 빈자리를 0으로 치환. → %05d와 유사
• $ : 통화 기호 표현
• L : 통화 기호 표현(Locale)
• . : 소숫점
• , : 천단위 표기

 

SELECT 
	weight,
	to_char(weight),
	length(to_char(weight)), -- 문자열 함수,
	length(weight), -- weight > (암시적 형변환) > 문자열
	substr(weight, 1, 1), -- > 이것도 암시적 형변환 해줌!
	weight || 'kg',
	to_char(weight) || 'kg' --> 이것 또한 암시적 형변환
FROM tblcomedian;

SELECT 
	weight,
	'@' || to_char(weight) || '@',
	'@' || to_char(weight, '99999') || '@', -- @    64@ > 추가 1개의 공백은 부호 자리(+/-)
	'@' || to_char(-weight, '99999') || '@', -- @   -64@
	'@' || to_char(weight, '00000') || '@', -- @ 00064@
	'@' || to_char(-weight, '00000') || '@' -- @-00064@
FROM tblcomedian;

SELECT 
	100,
	'$' || 100,
	to_char(100, '$999'),
	-- to_char(100, '999달러') > 안됨!
	100 || '달러',
	to_char(100, 'L999')
FROM dual;


SELECT 
	1234567.89,
	to_char(1234567.89, '9,999,999.9'), -- %,d
	ltrim(to_char(567.89, '9,999,999.9')),
	to_char(21234567.89, '9,999,999.9') -- 실제 데이터가 형식 문자보다 많을 경우 Error 발생
FROM dual;

 

2. TO_CHAR(컬럼, 형식문자열) : 날짜 → 문자 형변환(★★★)

 

형식 문자열 구성 요소

• yyyy
• yy
• month
• mon
• mm
• day
• dy
• ddd
• dd
• d
• hh
• hh24
• mi
• ss
• am(pm)

 

SELECT 
    sysdate,
    to_char(sysdate), --X
    to_char(sysdate, 'yyyy'), --년(4자리)
    to_char(sysdate, 'yy'), --년(2자리)
    to_char(sysdate, 'month'), --월(풀네임)
    to_char(sysdate, 'mon'), --월(약어)
    to_char(sysdate, 'mm') --월(2자리)
  FROM dual;

 

SELECT
    to_char(sysdate, 'day'), --요일(풀네임)
    to_char(sysdate, 'dy'), --요일(약어)
    to_char(sysdate, 'ddd'), --일(올해의 며칠)
    to_char(sysdate, 'dd'), --일(이번달의 며칠)
    to_char(sysdate, 'd') --일(이번주의 며칠) == 요일(숫자)
  FROM dual;

 

SELECT
    to_char(sysdate, 'hh'), --시(12시)
    to_char(sysdate, 'hh24'), --시(24시)
    to_char(sysdate, 'mi'), --분
    to_char(sysdate, 'ss'), --초
    to_char(sysdate, 'am'), --오전/오후
    to_char(sysdate, 'pm') --오전/오후
  FROM dual;

 

SELECT
	sysdate,
	to_char(sysdate, 'yyyy-mm-dd'),
	to_char(sysdate, 'hh24:mi:ss'),
	to_char(sysdate, 'yyyy-mm-dd hh24:mi.ss'),
	to_char(sysdate, 'day am hh:mi:ss') -- 화요일 오후 03:00:25
FROM dual;

 

SELECT 
	name,
	to_char(ibsadate, 'yyyy-mm-dd') AS ibsadate,
	to_char(ibsadate, 'day') AS DAY,
	CASE 
		WHEN to_char(ibsadate, 'd') IN ('1', '7') THEN '휴일 입사'
		ELSE '평일입사'
	END
FROM tblinsa;

-- 요일별 입사 인원수?
SELECT 
	count(CASE 
		WHEN to_char(ibsadate, 'd') = '1' THEN 1		
	END) AS 일요일,
	count(decode(to_char(ibsadate, 'd'), '2', 1)) AS 월요일,
	count(decode(to_char(ibsadate, 'd'), '3', 1)) AS 화요일,
	count(decode(to_char(ibsadate, 'd'), '4', 1)) AS 수요일,
	count(decode(to_char(ibsadate, 'd'), '5', 1)) AS 목요일,
	count(decode(to_char(ibsadate, 'd'), '6', 1)) AS 금요일,
	count(decode(to_char(ibsadate, 'd'), '7', 1)) AS 토요일
FROM tblinsa;

-- SQL에는 날짜 상수(리터럴)이 없다.

-- 입사날짜 > 2000년 이후
SELECT * FROM tblinsa WHERE ibsadate >= '2000-01-01'; -- 문자열 > 암시적 형변환 > to_date()

-- 입사날짜 > 2000년에 입사
SELECT * FROM tblinsa
	WHERE ibsadate >= '2000-01-01 00:00:00' AND ibsadate <= '2000-12-31'; -- 오답 > 2000-12-31 00:00:00 까지라 실제는 2000-12-31 23:59:59 까지가 되어야 함!!!
-- 날짜에 시분초가 붙으면 암시적 형변환 불가!! to_date 사용해줘야 함!
	
SELECT * FROM tblinsa
	WHERE to_char(ibsadate, 'yyyy') = '2000';

 

3. TO_NUMBER(문자) : 문자 → 숫자 형변환

암시적 형변환 때문에 사용도가 그리 높지는 않다.

SELECT 
	'123' * 2, -- 암시적 형변환
	to_number('123') * 2
FROM dual;

 

4. TO_DATE(문자, 형식문자열) : 문자 → 날짜 형변환(★★★)

• 문자로 입력한 '날짜'에 시분초가 붙으면 암시적 형변환이 불가하다. → to_date를 사용해서 날짜로 직접 형변환을 해줘야 한다.
• 시간을 설정하지 않으면 기본적으로 00:00:00으로 설정된다. 의도치않게 아래와 같은 오류를 범할 수 있으므로 주의가 필요하다.

insert into tblTest values('테스트', to_date('2024-03-17', 'yyyy-mm-dd'), '기획부');
insert into tblTest values('박동동', to_date('2024-03-17 23:59:58', 'yyyy-mm-dd hh24:mi:ss'), '기획부');

select
    name,
    to_char(ibsadate, 'yyyy mm dd hh24:mi:ss') as ibsadate,
    buseo
  from tblTest;
  
select * from tblTest where ibsadate <= '2024-03-17';
select * from tblTest where ibsadate <= to_date('2024-03-17', 'yyyy-mm-dd');

ibsadate <= to_date('2024-03-17', 'yyyy-mm-dd'); 로 조건절에 입력 시, 시간은 자동으로 00:00:00으로 설정되기 때문에 3월 17일 23:59:58에 입사한 박동동이 결과 레코드에 출력되지 않는다. → 결과 값 오류

 

SELECT 
	'2023-08-29', -- 자료형? 문자 vs 날짜
	to_date('2023-08-29'),
	to_date('2023-08-29', 'yyyy-mm-dd'), -- 원래는 이렇게 형식문자를 적어주는게 맞음!
	to_date('20230829'),
	to_date('20230829', 'yyyymmdd'),
	to_date('2023/08/29'),
	to_date('2023/08/29', 'yyyy/mm/dd'),
	--to_date('2023년08월29일', '2023년mm월dd일')
	--to_date('2023-08-29 15:28:39') -- 이건 암시적 형변환이 안되어서 반드시 어떤 format으로 작성되었는지 형식문자열을 적어줘야만 형변환 됨!!!
	to_date('2023-08-29 15:28:39', 'yyyy-mm-dd hh24:mi:ss')
FROM dual;

SELECT * FROM tblinsa
	WHERE ibsadate >= to_date('2000-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
		AND ibsadate <= to_date('2000-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss');

 

*글 작성에 참고한 내용: 학원 쌤의 열정적인 수업