형 변환 함수 [타입변환 함수]
오라클 DBMS는 데이터 타입 변환을 2가지 방법으로 제공한다.
- 암시적 (implicit) 변환 : 자동 변환
- 묵시적 (explicit) 변환 : 함수를 통한 변환
데이터 타입에 따라 사용할 수 있는 변환 함수가 다르다.
예를 들어서, NUMBER 타입에서 DATE 타입으로 한 번에 형 변환을 할 수 없다.
따라서 무조건 CHARACTER 타입을 거쳐서 가야 한다.
날짜: DATE -> 9999.12.31까지
숫자: number(전체 p, 소수점 이하 s) ex) number(5,2) : 123.54 ---> 최대 38자리수까지 가능
문자: char(고정길이) - 최대 2000 바이트까지 가능
varchar2(가변길이) - 최대 4000 바이트까지 가능
1. TO_CHAR (원래날짜, '원하는 모양') : 날짜를 문자로 형 변환
: NUMBER/DATE 타입을 CHARACTER 타입으로 변환하는 함수
+) WW : 1,2,3...53 (년별 주) - 1월1일부터 1월7일가지가 1주차
IW : 1,2,3...52 (ISO 년별 주) - 1월1일부터 첫 번째 일요일까지가 1주차)
ex) 현재 날짜를 문자형으로 변환하여 출력한다.
SYSDATE TIME
-------- --------------------
20/05/21 2020-05-21SQL> select sysdate, to_char(sysdate, 'yyyy-mm-dd') as time from dual;
ex) 사원들의 입사일을 출력하되, 요일까지 함께 출력하는 쿼리문
HIREDATE TIME
-------- ----------------------------------------------
80/12/17 1980-12-17 수요일
81/02/20 1981-02-20 금요일
81/02/22 1981-02-22 일요일
81/04/02 1981-04-02 목요일
81/09/28 1981-09-28 월요일
81/05/01 1981-05-01 금요일
81/06/09 1981-06-09 화요일
81/11/17 1981-11-17 화요일
81/09/08 1981-09-08 화요일
81/12/03 1981-12-03 목요일
81/12/03 1981-12-03 목요일
HIREDATE TIME
-------- ----------------------------------------------
82/01/23 1982-01-23 토요일SQL> select hiredate, to_char(hiredate, 'yyyy-mm-dd day') as time from emp;
ex) 현재 날짜와 시간을 출력하는 쿼리문
TIME
----------------------------------------
2020/05/21, 09:53:33SQL> select to_char(sysdate,'YYYY/MM/DD, HH24:MI:SS') as TIME from dual;
1. 다음과 같이 시간을 출력해보자.
DATE [24시간 기준]
------------------------------
오전 10:10:56
DATE [12시간 기준]
------------------------------
오전 10:11:21
DATE
--------------------------------------
5월 목, 2020
DATE
----------------------------------------------
2020-5-21 목요일
DATE
----------------------------------------------
2020-05-21 목요일
DATE
--------------------------------------------------------------------------------
TWENTY TWENTY, 2
SQL> select to_char(sysdate, 'PM HH24:MI:SS') as DATE from dual;
TO_CHAR(SYSDATE,'PMHH24:MI:SS'
------------------------------
오전 10:10:56
SQL> select to_char(sysdate, 'AM HH:MI:SS') as DATE from dual;
TO_CHAR(SYSDATE,'AMHH:MI:SS')
------------------------------
오전 10:11:21
SQL> select to_char(sysdate, 'MON DY, YYYY') as DATE from dual;
TO_CHAR(SYSDATE,'MONDY,YYYY')
--------------------------------------
5월 목, 2020
SQL> select to_char(sysdate, 'YYYY-fmMM-DD DAY') as DATE from dual;
TO_CHAR(SYSDATE,'YYYY-FMMM-DDDAY')
----------------------------------------------
2020-5-21 목요일
SQL> select to_char(sysdate, 'YYYY-MM-fmDD DAY') as DATE from dual;
TO_CHAR(SYSDATE,'YYYY-MM-FMDDDAY')
----------------------------------------------
2020-05-21 목요일
SQL> select to_char(sysdate, 'YEAR, Q') as DATE FROM DUAL;
TO_CHAR(SYSDATE,'YEAR,Q')
--------------------------------------------------------------------------------
TWENTY TWENTY, 2
2. 사원 테이블에서 다음과 같은 포맷으로 출력해보자.
ENAME HIREDATE T_HIREDATE
-------------------- -------- --------------------------------
T_KOR
--------------------------------------
CLARK 81/06/09 9 6월 1981
1981년 06월 09일
KING 81/11/17 17 11월 1981
1981년 11월 17일
MILLER 82/01/23 23 1월 1982
1982년 01월 23일SQL> select ename, hiredate, to_char(hiredate, 'fmDD Month yyyy') as T_HIREDATE, to_char(hiredate, 'yyyy"년" mm"월" dd"일"') as T_KOR from emp
2 where deptno = 10;
3. 괄호 안에 '목요일'을 기입해보자.
TEST
----------------------------------------------------------------------------
20/05/21(목요일) 10:48:19 오전SQL> select to_char(sysdate, 'yy/mm/dd(day) HH:MI:SS AM') as test from dual;
2. TO_DATE (원래날짜, '원하는 모양') : 날짜를 문자로 형 변환
: NUMBER/DATE 타입을 CHARACTER 타입으로 변환하는 함수
1) '20200101' 문자열을 date 타입으로 변환해보자.
SQL> select to_date('20200101','yyyymmdd') from dual;
TO_DATE(
--------
20/01/01
2) '20200101' 문자열을 다음의 문자열 형태로 변환해보자.
TIME
--------------------
2020,1월SQL> select to_char(to_date('20200101','yyyymmdd'),'YYYY,MON') as time from dual;
3) '980630' 문자열을 다음의 date 타입처럼 변환해보자.
TIME
--------
98/06/30SQL> select to_date('980630','yymmdd') as time from dual;
4) '200407'이라는 년월을 표시하는 문자값에서 15개월 후의 년월을 문자로 표시해보자.
TEST // 2005년 10월
------------
200510SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE('200407','YYYYMM'),15),'YYYYMM') AS TEST FROM DUAL;
5) RR 형식 & YY 형식
TEST1 TEST2 TEST3 TEST4
-------- -------- -------- --------
1998 2005 2098 2005SQL> SELECT TO_CHAR(TO_DATE('98','RR'),'YYYY') TEST1,
2 TO_CHAR(TO_DATE('05','RR'),'YYYY') TEST2,
3 TO_CHAR(TO_DATE('98','YY'),'YYYY') TEST3,
4 TO_CHAR(TO_DATE('05','YY'),'YYYY') TEST4 FROM DUAL;* RR형식: 현재 연도와 지정된 연도의 끝의 두 자리를 비교하여 50년을 기준으로 해석한다.
ex) 현재 연도가 2000~2050년 일 경우,
RR에서는 '01'~'49'까지는 2000년대로 해석하고, '50'~'99'년도까지는 1900년대로 해석한다.
* YY형식 : 연도의 세기를 현재 세기로 표시
3. TO_NUMBER
1) 주민등록번호의 앞 6자리를 가져오고 싶을 때.
4. TO_TIMESTAMP_TZ [ TZ: Time Zone ]
TIME
---------------------------------------------------------------------------
20/05/21 00:00:00 +09:00
TIME
---------------------------------------------------------------------------
20/05/21 00:00:00// DB에 들어가는 timestamp 메서드
SQL> select to_timestamp_tz (sysdate, 'yyyy-mm-dd hh:mi:ss tzh:tzm') as time from dual;
SQL> select to_timestamp (sysdate, 'yyyy-mm-dd hh:mi:ss tzh:tzm') as time from dual;
5. TO_DSINTERVAL
1) 현재 날짜를 기준으로 3일 12시간 이후는 몇 일 몇 시간인지 계산해보자 (TO_DISINTERVAL 사용)
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions184.htm
SYSDATE TIME
-------- --------------------------
20/05/21 2020-05-24 23SQL> SELECT SYSDATE, TO_CHAR(SYSDATE + TO_DSINTERVAL('003 12:00:00'),'YYYY-MM-DD HH24') AS TIME FROM DUAL;
6. 일반 함수
1) NVL(칼럼, 치환할 값) : NULL 값을 다른 값으로 치환해서 출력 (단, 두 개가 데이터타입이 같아야 한다)
2) NVL(칼럼1, 칼럼2, 칼럼3) : 칼럼1의 값이 NULL이 아니면 칼럼2, NULL이면 칼럼3 출력
3) DECODE 함수
- DECODE함수는 데이터들을 다른 값으로 바꿔준다.
- DECODE(VALUE, IF1, THEN, IF2, THEN2 ...)
: VALUE 값이 IF1인 경우 THEN1으로 값을 변경하고,
IF2인 경우 THEN2로 값을 변경하고....
Q. DECODE 함수를 이용해서 사원 테이블의 급여가 1000보다 작으면 'A', 1000~2500미만이면 'B', 나머지는 C로 하자.
ENAME SAL RE
-------------------- ---------- --
SMITH 800 A
ALLEN 1600 B
WARD 1250 B
JONES 2975 C
MARTIN 1250 B
BLAKE 2850 C
CLARK 2450 B
KING 5000 C
TURNER 1500 B
JAMES 950 A
FORD 3000 C
ENAME SAL RE
-------------------- ---------- --
MILLER 1300 BTIP: DECODE에서 비교연산값을 사용할 때에는 SIGN 메소드를 이용하여 중첩 사용한다.
SQL> SELECT ENAME, SAL,
2 DECODE(SIGN(SAL-1000),-1,'A', // SAL에서 1000을 뺀 것이 음수(-1)라면...
3 DECODE(SIGN(SAL-2500),-1,'B','C')) AS RESULT
4 FROM EMP;
4) COALESCE(...) : ( ) 안에 인자들의 값 중에 첫 번째 NULL이 아닌 값을 리턴한다.
- 사원테이블에서 이름, 커미션, 봉급을 출력하되 커미션이 NULL이 아닌 경우 커미션을, 커미션이 NULL일 경우 봉급을 출력하되 둘 다 NULL일 경우 50을 지정하자
SQL> SELECT ENAME, COMM, SAL, COALESCE(COMM,SAL,50) AS RESULT FROM EMP;ENAME COMM SAL RESULT
-------------------- ---------- ---------- ----------
SMITH 800 800
ALLEN 300 1600 300
WARD 500 1250 500
JONES 2975 2975
MARTIN 1400 1250 1400
BLAKE 2850 2850
CLARK 2450 2450
KING 5000 5000
TURNER 0 1500 0
JAMES 950 950
FORD 3000 3000
ENAME COMM SAL RESULT
-------------------- ---------- ---------- ----------
MILLER 1300 1300
5) XMLELEMENT
- XML형식으로 데이터를 출력하기
RESULT
--------------------------------------------------------------------------------
<EMP><사원이름>CLERK SMITH</사원이름><입사일>1980-12-17</입사일></EMP>
<EMP><사원이름>SALESMAN ALLEN</사원이름><입사일>1981-02-20</입사일></EMP>
<EMP><사원이름>SALESMAN WARD</사원이름><입사일>1981-02-22</입사일></EMP>
<EMP><사원이름>MANAGER JONES</사원이름><입사일>1981-04-02</입사일></EMP>
<EMP><사원이름>SALESMAN MARTIN</사원이름><입사일>1981-09-28</입사일></EMP>
<EMP><사원이름>MANAGER BLAKE</사원이름><입사일>1981-05-01</입사일></EMP>
<EMP><사원이름>MANAGER CLARK</사원이름><입사일>1981-06-09</입사일></EMP>
<EMP><사원이름>PRESIDENT KING</사원이름><입사일>1981-11-17</입사일></EMP>
<EMP><사원이름>SALESMAN TURNER</사원이름><입사일>1981-09-08</입사일></EMP>
<EMP><사원이름>CLERK JAMES</사원이름><입사일>1981-12-03</입사일></EMP>
<EMP><사원이름>ANALYST FORD</사원이름><입사일>1981-12-03</입사일></EMP>
RESULT
--------------------------------------------------------------------------------
<EMP><사원이름>CLERK MILLER</사원이름><입사일>1982-01-23</입사일></EMP>
SQL> SELECT XMLELEMENT("EMP",XMLELEMENT("사원이름",E.job||' '||E.ENAME),
XMLELEMENT("입사일",E.HIREDATE)) AS RESULT FROM EMP E;
6) CASE
- CASE 대상값 WHEN(비교값1 THEN 처리값1 WHEN 비교값2 THEN 처리값2
ENAME SAL 구분
-------------------- ---------- ------------
SMITH 800 초급
ALLEN 1600 초급
WARD 1250 초급
JONES 2975 초급
MARTIN 1250 초급
BLAKE 2850 초급
CLARK 2450 초급
KING 5000 고급
TURNER 1500 초급
JAMES 950 초급
FORD 3000 초급
ENAME SAL 구분
-------------------- ---------- ------------
MILLER 1300 초급SQL> SELECT ENAME, SAL,
2 CASE WHEN SAL <= 3000 THEN '초급' // 쉼표는 주지 말 것~!
3 WHEN SAL <= 4000 THEN '중급'
4 ELSE '고급' END AS 구분
5 FROM EMP;
Q. SAL의 평균을 구해보자. 단, 2000 이하인 SAL(연봉)은 모두 2000으로 처리한다.
AVG(SAL) Average Salary
---------- --------------
2077.08333 2522.91667select avg(sal), avg(case when e.sal > 2000 then e.sal else 2000 end) as "Average Salary"
from emp as e; // 이게 먼저 실행된다! 코드가 복잡해지면 어려우니 별칭을 주자
[ SQLD ] Oracle SQL (4) - 숫자함수, 날짜 함수
이번에는 Oracle SQL에서 숫자함수에 대해 알아보자. 1. 숫자 함수 1) ROUND (숫자, 소수점을 기준으로 출력을 원하는 자리수) : 반올림 2) TRUNC (숫자, 소수점을 기준으로 버림을 원하는 자리수) : 버림 *
sohsungho.tistory.com
About SQL Functions
Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses. Numeric Functions Numeric functions accept numeric i
docs.oracle.com
'학습 과정 공유 및 후기 > SQLD' 카테고리의 다른 글
| [ SQLD ] Oracle SQL (7) - 다중행(Multiple-Row) 서브쿼리 (0) | 2020.05.22 |
|---|---|
| [ SQLD ] Oracle SQL (6) - 서브쿼리 (0) | 2020.05.21 |
| [ SQLD ] Oracle SQL (4) - 숫자함수, 날짜 함수 (0) | 2020.05.20 |
| [ SQLD ] Oracle SQL (3) - 문자함수 (0) | 2020.05.20 |
| [ SQLD ] Oracle SQL (2) - 집계 함수 [count, max, min, sum, avg] (0) | 2020.05.19 |
