ORACLE CONVERTING FUNC
from
1.
ORACLE CONVERTING FUNC
A.
CAST
입력값을 다른 변수 타입으로 변환하는 함수이다. TO_CHAR,
TO_DATE, TO_NUMBER
사용하는 경우와 동일한 결과를 가진다. /*+MULTISET 의
변환에도 사용한다. */
SQL> select cast('2' as number)
from dual;
CAST('2'ASNUMBER)
-----------------
2
B.
CHARTOROWID
CHAR 값을 ROWID로
변환하는 함수이다. CHAR 값 그대로 사용해도 ORACLE이
변환해준다.
SQL> select ename from emp
2 where rowid =
'AAABugAAEAAAACWAAA';
SQL> select ename from emp
2 where rowid =
chartorowid('AAABugAAEAAAACWAAA');
ENAME
----------
SMITH
C.
TO_DSINTERVAL
SQL> select systimestamp from
dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-JUN-09 03.25.34.464440 PM +09:00
SQL> select to_dsinterval('1
00:00:00') from dual;
TO_DSINTERVAL('100:00:00')
---------------------------------------------------------------------------
+000000001 00:00:00.000000000
SQL> select systimestamp +
to_dsinterval('1 00:00:00') from dual;
SYSTIMESTAMP+TO_DSINTERVAL('100:00:00')
---------------------------------------------------------------------------
22-JUN-09 03.27.07.981073000 PM
+09:00
SQL> select systimestamp -
to_dsinterval('1 00:00:00') from dual;
SYSTIMESTAMP-TO_DSINTERVAL('100:00:00')
---------------------------------------------------------------------------
20-JUN-09 03.28.07.236924000 PM
+09:00
D.
TO_TIMESTAMP_TZ
입력값을
TIMESTAMP + TIMEZONE 으로 변환하는 함수이다.
1 select
to_timestamp_tz('2009-Jun-18 13:00:00 +09:00',
2 'YYYY-MON-DD HH24:MI:SS
TZH:TZM') "TS and TZ"
3* from dual
SQL> /
TS and TZ
---------------------------------------------------------------------------
18-JUN-09 01.00.00.000000000 PM
+09:00
E.
TO_YMINTERVAL
입력은 ‘??-??’ 형태로
받고 앞의 입력이 YEAR 뒤의 입력이 MONTH를 가리킨다.
SQL> select sysdate,
2 sysdate +
to_yminterval('03-08')
3 from dual;
위에서 3년하고 8개월 후의 결과를 구했다.
SYSDATE SYSDATE+T
--------- ---------
21-JUN-09 21-FEB-13
F.
TO_CHAR
RM은 로마자 표기를 의미한다.
SQL> select to_char(1998,'RM')
from dual;
TO_CHAR(1998,'R
---------------
MCMXCVIII
CC는 세기표기법이다.
SQL> select
to_char(sysdate,'CC') from dual;
TO
--
21
RR은 YYYY 형태의 연대 표기를 두자리로 표현한다. 주의할
점은 50보다 작을 때는
현재년도에
해당하는 앞의 두자리를 사용하지만 50보다 클때는 현재년도 – 1 에
해당하는
두자리를 사용한다.
예를 들어 1910, 2090 값을
DATE 값으로 변환 후 두자리 표기법으로 변환하였다.
1
select to_char(to_date('1910','YYYY'),'RR'),
2
to_char(to_date('2090','YYYY'),'RR')
3* from dual
SQL> /
TO TO
-- --
10 90
위의 값을 다시 DATE로
변환 후 다시 CHAR 를 이용해 YYYY 표기로 변환하면
10을 50보다
작기에 2010으로 90의 경우 50보다 크기에 1990으로 변환한 것을
볼 수 있다.
1 select
to_char(to_date(to_char(to_char(to_date('1910','YYYY'),'RR')),'RR'),'YYYY'),
2
to_char(to_date(to_char(to_char(to_date('2090','YYYY'),'RR')),'RR'),'YYYY')
3* from dual
SQL> /
TO_C TO_C
---- ----
2010 1990
그 밖에 WW 는
이번년도 시작부터 현재가 몇 번째 주인지 그리고 W는 이번달
몇 번째 주인지 D의
경우 1~7 로 요일을 표기할 때 몇에 해당하는지 ( 1: 일요일)
DDD는 365일
중 몇번째에 해당하는지를 의미한다.
1 select
sysdate,to_char(sysdate,'WW W D DDD ')
2* from dual
SQL> /
SYSDATE TO_CHAR(SYS
--------- -----------
21-JUN-09 25 3 1 172
RN은 입력된 수를 로마 숫자로 표기한다.
SQL> select to_char(123, 'RN')
from dual;
TO_CHAR(123,'RN
---------------
CXXIII
X 는 16진수로
표기하라는 의미이며 자릿수를 늘릴 경우 X를 복수 사용하면 된다.
SQL> select to_char(123,'XX')
from dual;
TO_
---
7B
G.
AVG
SQL> create table samp
2 as select ename,to_char(sal)
sal from emp;
Table created.
SQL> desc samp
Name
Null? Type
-----------------------------------------
-------- ----------------------------
ENAME
VARCHAR2(10)
SAL
VARCHAR2(40)
SQL> select avg(sal) from samp;
AVG(SAL)
----------
2073.21429
SAL TYPE 이 VARCHAR2 임에도
불구하고 오라클은 암시적으로 NUMBER TYPE 으로
변형하여 연산한다.
SQL> insert into samp
values('error','err');
1 row created.
SQL> commit;
Commit complete.
숫자가 아닌값을 넣었다.
SQL> select avg(sal) from samp;
select avg(sal) from samp
*
ERROR at line 1:
ORA-01722: invalid number
숫자가 아닌값이 있기 때문에 에러가 났다.
SQL> select avg(sal) from samp
2 where ename != 'error';
AVG(SAL)
----------
2073.21429
아까 INSERT 한
값을 제외한 값을 가지고 연산을 하자 정상적으로 수행되었다.
H.
MAX, MIN
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
SQL> update samp set sal =
'5000000err'
2 where ename = 'error';
1 row updated.
앞 부분은 숫자인 문자열을 입력했다.
SQL> commit;
Commit complete.
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
MAX 연산을 수행하자 숫자가 아닌 '5000000err' 를 제외하고 수행되었다.
SQL> insert into samp values('error2',NULL);
1 row created.
SQL> commit;
Commit complete.
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
SQL> select min(sal) from emp;
MIN(SAL)
----------
800
I.
SUM, COALESCE
SQL> select sum(sal) from emp;
SUM(SAL)
----------
29025
SQL> select sum(sal) from emp
2 where ename not in
('error','error2');
SUM(SAL)
----------
29025
SUM 의 연산 역시 숫자열로 변환 불가능한 문자열을 제외하고
수행된다.
COALESCE 는 NULL 이
아닌 첫 입력값을 출력하는 함수이다.
SQL> select coalesce(NULL, '1',
'A') from dual;
C
-
1
SQL> select
coalesce(NULL,'A','1') from dual;
C
-
A
SQL> create table samp
2 as select ename,sal from emp
where sal > 2000;
Table created.
SQL> commit;
Commit complete.
J.
DUMP,
VSIZE
DUMP는 입력값에 대한 데이터 타입, 바이트 길이, 내부표현을 출력해 준다.
VSIZE 는 바이트 길이를 리턴한다.
SQL> select ename,dump(ename)
dump,vsize(ename) from emp;
ENAME DUMP
VSIZE(ENAME)
----------
-------------------------------------------------- ------------
SMITH Typ=1 Len=5: 83,77,73,84,72 5
ALLEN Typ=1 Len=5: 65,76,76,69,78 5
WARD Typ=1 Len=4: 87,65,82,68 4
JONES Typ=1 Len=5: 74,79,78,69,83 5
MARTIN Typ=1 Len=6: 77,65,82,84,73,78 6
BLAKE Typ=1 Len=5: 66,76,65,75,69 5
CLARK Typ=1 Len=5: 67,76,65,82,75 5
SCOTT Typ=1 Len=5: 83,67,79,84,84 5
KING Typ=1 Len=4: 75,73,78,71 4
TURNER Typ=1 Len=6: 84,85,82,78,69,82 6
ADAMS Typ=1 Len=5: 65,68,65,77,83 5
ENAME DUMP
VSIZE(ENAME)
----------
-------------------------------------------------- ------------
JAMES Typ=1 Len=5: 74,65,77,69,83 5
FORD Typ=1 Len=4: 70,79,82,68 4
MILLER Typ=1 Len=6: 77,73,76,76,69,82 6
14 rows selected.
SMITH 레코드에 나왔던 내부적인 표현을 CHR 함수로 문자화 해주면 다음과 같다.
SQL> select chr(83)||chr(77)||chr(73)||chr(84)||chr(72)
2 from dual;
CHR(8
-----
SMITH
K.
GREATEST, LEAST
GREATEST, LEAST 함수는 오라클 내부적 표현의 값 크기여부로 판단한다.
SQL> select dump('aA',10) from
dual;
DUMP('AA',10)
-------------------
Typ=96 Len=2: 97,65
a가 97 A가 65에 해당하므로 greatest
경우 a 가 출력된다.
SQL> select greatest('a','A')
from dual;
G
-
a
SQL> select least('a','A') from
dual;
L
-
A
L.
NULLIF, NVL2
nullif(parameter1,
parameter2) 형태로 사용되며
두개의 parameter가 동일한 값이면
null을 리턴하며 다른 경우
parameter1 을 리턴한다.
SQL> select nullif(1,2) from
dual;
NULLIF(1,2)
-----------
1
SQL> select nullif(1,1) from
dual;
NULLIF(1,1)
-----------
nvl2(parameter1,
parameter2, parameter3) 형태로 사용된다.
parameter1 이 null 이면 parameter3를 리턴하며
parameter1이 not null 이면 parameter2를 리턴한다.
SQL> select nvl2(null, 'NOT
NULL', 'NULL') from dual;
NVL2
----
NULL
SQL> select nvl2('1', 'NOT
NULL', 'NULL') from dual;
NVL2('1'
--------
NOT NULL
M.
SYS_CONNECT_BY_PATH
계층을 표현하는
start with / connect by 쿼리에 같이 사용되며
sys_connect_by_path(column_name,
‘구분자’) 로 사용된다.
이 함수는 상위 값에서 자신이 가진 값까지의 경로를 보여준다.
1 select lpad('*',2*level,'*') ||
ename "ENAME",
2
empno,sys_connect_by_path(empno, '.') cbp
3 from emp
4 start with mgr is null
5* connect by prior empno = mgr
ENAME EMPNO CBP
-------------------- --------
------------------------------
**KING 7839 .7839
****JONES 7566 .7839.7566
******SCOTT 7788 .7839.7566.7788
********ADAMS 7876 .7839.7566.7788.7876
******FORD 7902 .7839.7566.7902
********SMITH 7369 .7839.7566.7902.7369
****BLAKE 7698 .7839.7698
******ALLEN 7499 .7839.7698.7499
******WARD 7521 .7839.7698.7521
******MARTIN 7654 .7839.7698.7654
******TURNER 7844 .7839.7698.7844
ENAME EMPNO CBP
-------------------- --------
------------------------------
******JAMES 7900 .7839.7698.7900
****CLARK 7782 .7839.7782
******MILLER 7934 .7839.7782.7934
14 rows selected.
N.
UID, USER
uid는 해당 스키마의 고유번호를 의미하며 user 는 그 스키마 이름을 의미한다.
이는 dba_users 에서
확인할 수 있다.
SQL> select uid,user from dual;
UID USER
----------
------------------------------
41 SCOTT
SQL> conn sys/oracle as sysdba
Connected.
SQL> grant dba to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> select uid,user from dual;
UID USER
----------
------------------------------
41 SCOTT
SQL> select user_id,username
from dba_users;
USER_ID USERNAME
----------
------------------------------
0 SYS
5 SYSTEM
11 OUTLN
24 TEMP
20 DBSNMP
22 MAXGAUGE31
23 MAXGAUGE25
25 HR
26 ORACLE_ADMIN
27 HR_AUDIT
31 DIC
USER_ID USERNAME
----------
------------------------------
32 IDX
41 SCOTT
34 U1
35 U2
38 LOG
40 LOGVIEWER
39 DROP1
18 rows selected.