오라클 SQL, 변환 함수(Conversion Function), CHARTOROWID, TO_CHAR, TO_DATE, TO_NUMBER, 형변환

오라클 SQL, 변환 함수(Conversion Function), CHARTOROWID, TO_CHAR, TO_DATE, TO_NUMBER, 형변환

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=612

 

오라클 SQL, 변환 함수(Conversion Function), CHARTOROWID, TO_CHAR, TO_DATE, TO_NUMBER, 형변환

오라클 SQL, 변환 함수(Conversion Function), CHARTOROWID, TO_CHAR, TO_DATE, TO_NUMBER, 형변환4.3 변환 함수(Conversion Function) 변환함수는 숫자, 문자, 날짜 형 사이의 변환을 수행하는 함수이다. 여러 종류가 있지만 자주 사용되는 변환 함수 몇 가지에 대해 알아보자. CHARTOROW…

ojc.asia

4.3 변환 함수(Conversion Function)

변환함수는 숫자, 문자, 날짜 형 사이의 변환을 수행하는 함수이다. 여러 종류가 있지만 자주 사용되는 변환 함수 몇 가지에 대해 알아보자.

CHARTOROWID(char) : VARCHAR2 또는 CHAR Type을 ROWID Type으로 변환 한다.

TO_CHAR(datetime|number [,fmt]) : 숫자나 날짜형 자료를 fmt 형식에 맞춰 문자로 변환 한다.

TO_DATE(char, [,fmt]) : fmt 형식의 문자를 Date값으로 변환 한다.

TO_NUMBER(char [,fmt]) : 숫자형태의 문자 char를 숫자로 변환 한다.

[TO_CHAR, TO_DATE에서 사용할 수 있는 Format 형식]

YYYY or SYYYY : 년도를 지칭하며 S를 앞에 붙이면 기원전 년도인 경우 ‘-‘가 붙음

YYY or YY or Y : 년도의 마지막 3자리, 2자리, 한자리

IYYY : 4자리의 ISO 표준 년도 형식

SYEAR or YEAR : 영문으로 표기된 년도를 표시, 앞에 S가 붙으면 기원전 년도인 경우 ‘-‘가 앞에 붙음)

Q : 분기

MM : 월

MONTH or MON : 월의 영문 표기(9자리) 또는 3자리의 단축 표기

RM : 로마숫자 월

DDD or DD or D : 년,월,주의 몇번째 날 인가를 보여줌

WW or W : 년,월에서 오늘이 몇번째 주 인가를 보여줌

DAY or DY : 요일 명칭 또는 3자리의 단축형

AM or FM : 오전과 오후 표시

HH or HH12 : 시각(1~12)

HH24 : 시각(0~23)

MI : 분

SS : 초

[접미사]

TH : 서수로 표시(DDTH라고 하면 4TH와 같은 형식으로 나타남)

SP : 숫자를 철자로 보여준다.(DDSP à FOUR)

SPTH or THSP : 서수를 철자로 보여준다.(DDSPTH à FOURTH)

[숫자를 문자로 변환 할 때 사용하는 Format 형식]

9 : 숫자의 출력

0 : 숫자가 없는 경우 0을 표시, 숫자가 있으면 숫자를 표시

. : 소수점 자리 표시

, : 콤마 자리 표시

우선 EMP 테이블에서 이름이 ‘SMITH’인 사원의 ROWID를 알아낸 후 ROWID를 이용하여 검색하자. SQL> select ename, sal, rowid from emp 2 where ename = 'SMITH'; ENAME SAL ROWID ---------- ---------- ------------------ SMITH 800 AAAHW7AABAAAMUiAAA SQL> select ename, sal from emp 2 where rowid = CHARTOROWID('AAAHW7AABAAAMUiAAA'); ENAME SAL ---------- ---------- SMITH 800 SQL> select to_char(sysdate, 'YYYY/MM/DD') from dual; TO_CHAR(SY ---------- 2014/09/09 SQL> select to_char(8000000, '999,999,999') from dual; TO_CHAR(8000 ------------ 8,000,000 SQL> select to_number('8000000'), to_number('8,000,000','9,999,999') from dual; TO_NUMBER('8000000') TO_NUMBER('8,000,000','9,999,999') -------------------- ---------------------------------- 8000000 8000000 --오늘을 2014년 9월8일 이라고 한다면 2003년1월1일부터 오늘까지 몇 일 차이가 나는지 확인하기 위해서는 아래와 같이 하면 된다. SQL> select sysdate, 2 round(sysdate - to_date('2003.01.01','yyyy.mm.dd')) 3 from dual; SYSDATE ROUND(SYSDATE-TO_DATE('2003.01.01','YYYY.MM.DD')) ---------- ------------------------------------------------- 14/09/08 4269 -- 년중, 월중, 주중 오늘이 몇번째 일자인지를 확인 하는 예문 SQL> select to_char(sysdate, 'ddd') "1년중 몇일?", 2 to_char(sysdate, 'dd') "월의 몇번째 일?", 3 to_char(sysdate, 'd') "주중 몇번째 일?" 4 from dual; 1년 월 --- -- - 250 08 2 --아래 예문은 Format 문자를 사용하는 예이다. SQL> select to_char(sysdate, 'YEAR MONTH DD DAY HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'YEARMONTHDDDAYHH24:MI:SS') ----------------------------------------------------------------------- TWENTY FOURTEEN 9월 07 일요일 16:30:29 -- 문자열 그대로 출력을 원한다면 “”로 싸 주면 된다. SQL> select ename, sal, to_char(hiredate, 'mon "the" ddth "of" yyyy') hiredate 2 from emp; ENAME SAL HIREDATE ---------- ---------- ------------------------- SMITH 800 12월 the 17th of 1980 ALLEN 1600 2월 the 20th of 1981 WARD 1250 2월 the 22nd of 1981 JONES 2975 4월 the 02nd of 1981 MARTIN 1250 9월 the 28th of 1981 …… SQL> select ename, hiredate from emp 2 where hiredate = to_date('12월 17 80','month dd rr'); ENAME HIREDATE ---------- -------- SMITH 80/12/17 SQL> select ename, hiredate from emp 2 where hiredate = to_date('12월 17 80','month dd yy'); 선택된 레코드가 없습니다. -- 아래에서 YY와 RR 포맷의 차이에 대해 이해하자. -- RR포맷은 오라클에서 Y2K 해결을 위해 도입했는데, --현재년도가 2000~2049년 경우 처리연도가 00~49인 경우의 예를 들면… -- YY포맷은 2000~2049로 인식하고 RR포맷 역시 2000~2049로 인식한다. --현재년도가 2000~2049년인경우 처리연도가 50~99인 경우의 예를 들면… -- YY포맷은 2050~2099로 인식하고 RR포맷은 1950~1999로 인식한다. SQL> select to_date('12월 17 80','month dd yy') from dual; TO_DATE( -------- 80/12/17 SQL> select to_date('12월 17 80','month dd rr') from dual; TO_DATE( -------- 80/12/17 SQL> SELECT TO_CHAR(TO_DATE('99/01/01','RR/MM/DD'),'YYYY.MM.DD') FROM DUAL; TO_CHAR(TO ---------- 1999.01.01 SQL> SELECT TO_CHAR(TO_DATE('99/01/01','YY/MM/DD'),'YYYY.MM.DD') FROM DUAL; TO_CHAR(TO ---------- 2099.01.01

묵시적 형 변환

WHERE절 등에서 비교 타입이 다른 경우 내부적으로 타입 변환을 한다.

WHERE절에 숫자 = 문자 와 같은 비교연산을 수행 시 내부적으로 숫자를 기준으로 묵시적 변환을 시도한다.

SQL> desc myemp1 이름 널? 유형 ----------------------------------------- -------- --------------- EMPNO NOT NULL NUMBER ENAME VARCHAR2(100) DEPTNO VARCHAR2(1) ADDR VARCHAR2(100) SAL NUMBER SUNGBYUL VARCHAR2(1) --empno는 primary key 인덱스, deptno에도 인덱스가 생성되어 있다. --WHERE절의 비교대상 칼럼의 데이터 타입이 “숫자=문자” 와 같이 다르면 숫자로 자동 형 변환된다. Deptno 칼럼의 데이터타입은 VARCHAR2 이다. “where deptno = 숫자”의 경우 deptno 칼럼을 숫자로 변환을 하게 되므로 인덱스가 있더라도 사용이 안 된다.(인덱스 칼럼에 변형이 생김으로 인덱스 사용불가) SQL> desc myemp1 이름 널? 유형 ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER ENAME VARCHAR2(100) DEPTNO VARCHAR2(1) ADDR VARCHAR2(100) SAL NUMBER SUNGBYUL VARCHAR2(1) HIREDATE DATE OUTDATE VARCHAR2(8) -- SQL문의 실행 시간을 표시 SQL> set timing on SQL> select count(*) from myemp1; COUNT(*) ---------- 20000004 경 과: 00:00:06.93 -- deptno 칼럼에 인덱스를 생성하자. SQL> create index idx_myemp1_deptno on myemp1(deptno); 인덱스가 생성되었습니다. -- deptno 칼럼이 VARCHAR2이므로 인덱스를 이용하여 결과가 빨리 나온다. SQL> select count(*) from myemp1 2 where deptno = '3'; COUNT(*) ---------- 5000001 경 과: 00:00:00.35 -- 묵시적으로 deptno칼럼을 숫자로 변환하므로 인덱스 이용불가, 느리다. SQL> select count(*) from myemp1 2 where deptno = 3; COUNT(*) ---------- 5000001 경 과: 00:00:22.42 -- 결국 위 쿼리는 아래와 같은 변환을 하는 것이다. SQL> select count(*) from myemp1 2 where to_number(deptno) = 3; COUNT(*) ---------- 5000001 경 과: 00:00:23.10