2022-11-22 데이터 베이스 오라클 : 변환함수
변환함수
- 자료의 형을 일시적으로 변환하기 위한 함수
- TO_CHAR(숫자나 날짜를 문자로),
TO_NUMBER(문자나 날짜를 숫자로),
TO_DATE(문자를 날짜 데이터로 변환),
CAST(바꾸고싶은대로 바꿈) 제공
1. CAST(expr AS 타입명) -- 기본형으로밖에 밖을수 없음
. 'expr'형을 '타입명'으로 변환
. 물리적으로 변환되지는 않음
-- 변환하고자 하는 새로운 데이터 타입으로 출력
사용예) 2020년 6월 일자별 판매집계를 조회하시오.
SELECT CAST(SUBSTR(A.CART_NO,1,8) AS DATE) AS 일자,
-- 날짜형식으로 바꿈(형변환)
SUM(A.CART_QTY) AS 판매수량,
SUM(A.CART_QTY*B.PROD_PRICE) AS 판매금액
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
GROUP BY CAST(SUBSTR(A.CART_NO,1,8) AS DATE)
ORDER BY 1;
2. TO_CHAR(expr [, fmt])
- 문자열(CHAR, CLOB -> VARCHAR2), 숫자, 날짜 타입의 데이터를 'fmt' 형식에 맞는
문자열 자료로 형변환 -- 특정한 경우 문자열을 문자열로 바꿀수 있다.
(예 -고정길이 문자열 -> 가변길이 문자열)
- format 문자열은 날짜와 숫자로 구분
** 날짜 format string
-----------------------------------------------------------------------------------
format 기능 예
-----------------------------------------------------------------------------------
CC 세기 SELECT TO_CHAR(SYSDATE, 'CC') FROM DUAL;
AD, BC 서기 SELECT TO_CHAR(SYSDATE, 'AD YYYY') FROM DUAL;
YYYY,YYY,YY,Y 년도 SELECT TO_CHAR(SYSDATE, 'YYYY'),
TO_CHAR(SYSDATE, 'YYY'),
TO_CHAR(SYSDATE, 'YY'),
TO_CHAR(SYSDATE, 'Y') FROM DUAL;
Q 분기 SELECT TO_CHAR(SYSDATE, 'YYYY/MM Q') FROM DUAL;
-- 2022/11/4분기
MM, RM 월 SELECT TO_CHAR(SYSDATE, 'MM'),
TO_CHAR(SYSDATE, 'RM'),
TO_CHAR(SYSDATE, 'MONTH'),
TO_CHAR(SYSDATE, 'MON') FROM DUAL;
MONTH, MON '월'문자열
추가 출력
WW, 년의 주수,
W 월의 주수 SELECT TO_CHAR(SYSDATE, 'WW'),
TO_CHAR(SYSDATE, 'W') FROM DUAL;
DDD,DD,D 년의 일,월의 일,
주의 일 SELECT TO_CHAR(SYSDATE, 'DDD'),
TO_CHAR(SYSDATE, 'DD'),
TO_CHAR(SYSDATE, 'D'),
TO_CHAR(SYSDATE, 'DAY'),
TO_CHAR(SYSDATE, 'DY') FROM DUAL;
DAY,DY 요일 -- 수요일 / 수(약자)
AM, PM, A.M 오전, 오후 SELECT TO_CHAR(SYSDATE, 'AM'),
TO_CHAR(SYSDATE, 'P.M.') FROM DUAL;
P.M. -- SYSDATE를 받으면 아무리 P.M.을 선언해도 오전이면 오전이라고 표기된다.
HH,HH12,HH24 시간
MI 분
SS,SSSSS 초 SELECT TO_CHAR(SYSDATE, 'HH:MI:SS'),
TO_CHAR(SYSDATE, 'HH12:MI:SSSSS'),
TO_CHAR(SYSDATE, 'HH24:MI:SS')
FROM DUAL;
"사용자정의 문자열"
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"') FROM DUAL;
>> 2022년 11월 23일
-- **
-- AD, BC 원본데이터가 이미 저장되어있어서 둘다 '서기'로 표시됨
-- RM 로마자로 월 표기
-- MONTH(월의 풀네임), MON(월의 약자 - JUN,DEC) /한글은 월을 표현하는 약자가 없어서 같은 형식으로 출력됨
-- 1년 52주/WW 오늘이1년의 몇 주 차인지/ W 이번달에 오늘은 몇주차인지
-- (11월 23일 WW : 오늘은 1년의 47주차/W : 11월의 4주차)
-- DDD : 날짜가 3자리로 표현한다. => 1월 1일부터 오늘까지 경과된 일
-- DD : 2자리로 표현 이번달 1일부터 오늘까지 경과된 일,
-- D : 이번주의 월요일부터 오늘까지 경과된 일
-- HH = HH12 : 12시간 형식,HH24 : 24시간 형식
-- SS : 1분은 60초 / SSSSS : 5자리로 표기함 : 오늘 0시0초부터 시작해서 지금까지 경과된 초를 출력
** 숫자 format string
사용예) -- 결과는 숫자가 아닌 문자열!!
SELECT TO_CHAR(12345,'999,999'), -- 12,345 /나인모드
TO_CHAR(12345,'000,000'), -- 012,345
TO_CHAR(12345,'999,999.99'), -- 12,345.00 /나인모드여도 소숫점0출력
TO_CHAR(12345,'999,999.00'), -- 12,345.00
TO_CHAR(-12345,'999,999MI'), -- 12,345- / 음수부호가 오른쪽에 표기
TO_CHAR(-12345,'999,999PR'), -- <12,345> / 음수인경우 < 숫자 > 표기 / 양수인경우 일반숫자 표기
TO_CHAR(12345,'L999,999'), -- ￦12,345 / Location의 'L'
TO_CHAR(12345,'$000,999') -- $012,345 / 달러표기와 제로모드
FROM DUAL
-----------------------------------------------------------------------------------
format 기능
-----------------------------------------------------------------------------------
9 유효숫자출력, 무효의
0은 공백 처리
0 유효숫자출력, 무효의
0은 '0' 출력
$,L 화폐기호 출력 -- 맨왼쪽에 출력
MI 음수인 경우 우측에 '-'출력--( 1234-)
PR 음수인 경우 우측에 < >안에 출력
,(Comma) 3자리마다 자리점 출력
.(DOT) 소숫점 출력
/* 나인모드 제로모드
** 01234.30 01234.30
99,999.99 00,000.00
-------------------------------- 유효숫자 출력
1,234 01,234.30
0공백처리 0 출력
(소숫점은 0출력)
화폐 기호 : $ 1,234 / L 1,234 L: 다른나라 화폐기호 */
SELECT TO_CHAR('345') + 9999 -- 실행됨 : 10344
FROM DUAL;
SELECT TO_CHAR('1,344') + 9999 -- 실행불가 : 1,344 => 콤마 때문에
FROM DUAL;
사용예)
SELECT TO_DATE('20211230'), -- 숫자가 날짜로 바뀜
TO_DATE(20211230), -- 숫자가 날짜로 바뀜
TO_DATE('20211230','YYYY-MM-DD'), -- 'YYYY-MM-DD' 여도 == 2021/12/30 표기
TO_CHAR(TO_DATE('20211230095023','YYYYMMDDHH24MISS'),'YYYY-MM-DD HH24:MI:SS')
-- 날짜타입으로 변경 => TO_CHAR로 YYYY-MM-DD 형식으로 표기
FROM DUAL;
사용예)'2022.08.25'를 날짜형식으로 변환하고 10일 후의 날짜와 해당월의 마지막 날짜를 구하시오
SELECT TO_DATE('2022.08.25','YYYY/MM/DD'), -- 2022/08/25
TO_DATE('2022.08.25','YYYY/MM/DD') + 10, -- 2022/09/04
LAST_DAY(TO_DATE('2022.08.25','YYYY/MM/DD')) -- 2022/08/31
FROM DUAL;
SELECT TO_DATE('2022년 08월 25일','YYYY/MM/DD'), -- 에러남
TO_DATE('2022 08 25','YYYY/MM/DD') + 10, -- 2022/09/04
LAST_DAY(TO_DATE('2022.08.25','YYYY/MM/DD')) -- 2022/08/31
FROM DUAL;
-- 출력되기 위해서 형식지정문자열을 기술해줘야함
SELECT TO_DATE('2022년 08월 25일','YYYY"년" MM"월" DD"일"') + 5, -- 2022/08/30
TO_DATE('2022 08 25','YYYY/MM/DD') + 10, -- 2022/09/04
LAST_DAY(TO_DATE('2022.08.25','YYYY/MM/DD')) -- 2022/08/31
FROM DUAL;
4. TO_NUMBER(expr [, fmt])
- 문자열 데이터 expr을 숫자 타입으로 형을 변환 시킴
- 'expr'은 반드시 숫자로 변환 가능해야하며 숫자로 변환할 수 없는 문자열이 포함된 경우
'expr'이 출력되기 위해 요구되는 형식지정문자열을 기술해야 함.
- 'fmt'은 TO_CHAR에 사용된 숫자 format string과 같다.
사용예)
SELECT TO_NUMBER('12345'),
TO_NUMBER('1234.08') FROM DUAL;
-- 소숫점은 별도의 형식없이 그냥 숫자로 변환됨
SELECT TO_NUMBER('12,345'), -- 콤마 사용으로 에러남
TO_NUMBER('12,345.08') FROM DUAL;
SELECT TO_NUMBER('12,345','99,999'),
TO_NUMBER('12,345.08','99,999.99') FROM DUAL;
-- 콤마를 사용하려면 '99,999'의 사용자지정형식 사용
-- 기본숫자열 '12345' 로 표기됨
SELECT TO_NUMBER('<12,345>','99,999PR'), -- 음수 '-12,345'
TO_NUMBER('￦12,345.08', 'L99,999.00') FROM DUAL;
-- 원본숫자 '12345.08' 로 표기됨