[SQL-Oracle]함수(Function) - 변환함수 - TO_CHAR, TO_NUMBER, TO_DATE

⚫변환함수 :매개변수로 전달받은 값을 원하는 자료형의 값으로 변환하여 반환하는 함수

 

🔘TO_NUMBER(문자값) : 문자값을 전달받아 숫자값으로 변환하여 반환하는 함수
--매개변수로 전달받은 문자값에 숫자가 아닌 형태의 문자가 존재할 경우 에러 발생

--비교할 컬럼의 자료형이 숫자형인 경우 비교값도 숫자값인 경우에만 비교 가능
SELECT EMPNO,ENAME,SAL FROM EMP WHERE EMPNO=7499;


--비교할 컬럼의 자료형이 숫자형인 경우 비교값이 문자값이면 TO_NUMBER 함수를 사용하여 문자값을 숫자값으로 변환하여
--컬럼값과 비교 처리 - 강제 형변환
SELECT EMPNO,ENAME,SAL FROM EMP WHERE EMPNO=TO_NUMBER('7499');

--*그러나 이 함수를 사용할 일이 거의 없음. 아래와 같이 자동형변환이 되기 때문.
--비교할 컬럼의 자료형이 숫자형인 경우 비교값이 문자값이면 자동으로 비교값을 숫자값으로 변환하여 비교 처리 - 자동 형변환
SELECT EMPNO,ENAME,SAL FROM EMP WHERE EMPNO='7499';
--문자값을 산술 연산 처리할 경우 문자값이 자동으로 숫자값으로 변환되어 연산 처리 - 자동 형변환
SELECT 100+200 FROM DUAL;
SELECT '100'+'200' FROM DUAL;


--EMP 테이블에서 사원번호가 7839인 사원의 사원번호,사원이름,급여,세후급여(급여*0.9) 검색
SELECT EMPNO,ENAME,SAL,SAL*0.9 FROM EMP WHERE EMPNO=7839;
SELECT EMPNO,ENAME,SAL,SAL*TO_NUMBER('0.9') FROM EMP WHERE EMPNO=7839;
SELECT EMPNO,ENAME,SAL,SAL*'0.9' FROM EMP WHERE EMPNO=7839;

 

🔘 TO_DATE(문자값[,날짜패턴문자값]) : 문자값을 전달받아 날짜값으로 변환하여 반환하는 함수

--날짜 패턴이 생략된 경우 기본패턴 [RR/MM/DD]으로 문자값을 전달받아 날짜값으로 변환  
--매개변수로 날짜패턴문자값을 전달받은 경우 원하는 패턴의 문자값을 전달받아 날짜값으로 변환 가능
--패턴에 맞지 않는 문자값을 전달받을 경우 에러 발생

--비교할 컬럼의 자료형이 날짜형인 경우 비교값이 문자값이면 TO_DATE 함수를 사용하여 문자값을 날짜값으로 변환하여
--컬럼값과 비교 처리 - 강제 형변환
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE=TO_DATE('82/01/23');
--비교할 컬럼의 자료형이 날짜형인 경우 비교값이 문자값이면 자동으로 비교값을 날짜값으로 변환하여 비교 처리 - 자동 형변환
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE='82/01/23';

--날짜값은 [RR/MM/DD] 패턴의 문자값으로 표현되지만 [YYYY-MM-DD] 패턴의 문자값으로 표현 가능
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE='1982-01-23';

--날짜값을 패턴에 맞지 않는 문자값으로 표현할 경우 에러 발생
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE='01-23-1982';
--TO_DATE 함수를 사용하여 날짜값을 원하는 패턴의 문자값으로 전달받아 사용 가능
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE=TO_DATE('01-23-1982','MM-DD-YYYY');

--2000년 1월 1일에 태어난 사람이 오늘까지 살아온 일수를 계산하여 검색
--검색대상에 산술연산자를 사용하면 문자값을 날짜값이 아닌 숫자값으로 변환하여 연산 처리 - 숫자가 아닌 문자가 포함되어 에러 발생
SELECT SYSDATE-'2000-01-01' FROM DUAL;
--검색대상에 산술연산자를 사용하여 문자값을 날짜값으로 변환하여 연산 처리할 경우 TO_DATE 함수 사용
SELECT SYSDATE-TO_DATE('2000-01-01') FROM DUAL;
SELECT CEIL(SYSDATE-TO_DATE('2000-01-01'))||'일' "오늘까지 살아온 일수" FROM DUAL;

🔘 TO_CHAR({숫자값|날짜값},패턴문자값) : 숫자값 또는 날짜값을 전달받아 원하는 패턴의 문자값으로 변환하여 반환하는 함수

(⭐위 두 함수보다 이 함수를 훨씬 많이 씀⭐)

--날짜패턴문자 : RR(년),YYYY(년),MM(월),DD(일),HH24(시간),HH12(시간),MI(분),SS(초)

SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
	FROM DUAL;

위 코드의 결과

 

--EMP 테이블에서 1981년에 입사한 사원의 사원번호,사원이름,입사일 검색
--현재 접속된 사용자의 환경(세션)에 대한 날짜값의 패턴이 [RR/MM/DD]인 경우에만 검색 가능
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE BETWEEN '81/01/01' AND '81/12/31';
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE HIREDATE LIKE '81%';

--현재 접속된 사용자의 환경(세션)에 대한 날짜값의 패턴과 상관없이 검색 가능
--TO_CHAR 함수를 사용하여 날짜값을 원하는 패턴의 문자값으로 변환하여 반환받아 비교 처리
--아래 세가지 방법 중 마지막이 가장 좋음.
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE,'RR/MM/DD') BETWEEN '81/01/01' AND '81/12/31';
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE,'RR/MM/DD') LIKE '81%';
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1981'; 

--숫자패턴문자 : 9(숫자 또는 공백), 0(숫자), L(화폐단위), $(달러)
SELECT 10000000000,TO_CHAR(1000000000,'9,999,999,990') FROM DUAL;
--매개변수로 전달받은 숫자값의 길이가 패턴문자의 길이보다 큰 경우 숫자값이 [#]으로 변환되어 반환
SELECT 100000000000,TO_CHAR(10000000000,'9,999,999,990') FROM DUAL;

--EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,급여 검색
SELECT EMPNO,ENAME,SAL FROM EMP WHERE EMPNO=7844;
SELECT EMPNO,ENAME,TO_CHAR(SAL,'999,990') SAL FROM EMP WHERE EMPNO=7844;
SELECT EMPNO,ENAME,TO_CHAR(SAL,'L99,990') SAL FROM EMP WHERE EMPNO=7844;
SELECT EMPNO,ENAME,TO_CHAR(SAL,'$99,990.00') SAL FROM EMP WHERE EMPNO=7844;