http://blog.naver.com/tinylass/120018762546
1. 문자 함수
1-1) CHR
1-2) CONCAT 함수
1-3) INITCAP 함수
1-4) LOWER 함수
1-5) LPAD 함수
1-6) LTRIM 함수
1-7) NLS_INITCAP 함수
1-8) NLS_LOWER 함수
1-9) NLSSORT 함수
1-10) NLS_UPPER 함수
1-11) REPLACE 함수
1-12) RPAD 함수
1-13) RTRIM 함수
1-14) SOUNDEX 함수
1-15) SUBSTR 함수
1-16) TRANSLATE 함수
1-17) TREAT 함수
1-18) TRIM 함수
1-19) UPPER 함수
1-20) ASCII 함수
1-21) INSTR 함수
1-22) LENGTH 함수
2. 날짜 처리함수(datetime function)
2-1) ADD_MONTHS 함수
2-2) CURRENT_DATE 함수
2-3) URRENT_TIMESTAMP 함수
2-4) DBTIMEZONE 함수
2-5) EXTRACT(datetime) 함수
2-6) FROM_TZ 함수
2-7) LAST_DAY 함수
2-8) LOCALTIMESTAMP 함수
2-9) MONTHS_BETWEEN 함수
2-10) NEW_TIME 함수
2-11) NEXT_DAY 함수
2-12) NUMTODSINTERVAL 함수
2-13) NUMTOYMINTERVAL 함수
2-14) ROUND(date) 함수
2-15) SESSIONTIMEZONE 함수
2-16) SYS_EXTRACT_UTC 함수
2-17) SYSDATE 함수
2-18) SYSTIMESTAMP 함수
2-19) TO_DSINTERVAL 함수
2-20) TO_TIMESTAMP 함수
2-21) TO_TIMESTAMP_TZ 함수
2-22) TO_YMINTERVAL 함수
2-23) TRUNC(date) 함수
2-24) TZ_OFFSET 함수
3.데이터 형 변환 함수(conversion function)
3-1) ASCIISTR 함수
3-2) BIN_TO_NUM 함수
3-3) CAST 함수
3-4) CHARTOROWID 함수
3-5) COMPOSE 함수
3-6) CONVERT 함수
3-7) HEXTORAW 함수
3-8) NUMTODSINTERVAL 함수
3-9) NUMTOYMINTERVAL 함수
3-10) RAWTOHEX 함수
3-11) RAWTONHEX 함수
3-12) ROWIDTOCHAR 함수
3-13) ROWIDTONCHAR 함수
3-14) TO_CHAR(character) 함수
3-15) TO_CLOB 함수
3-16) TO_DSINTERVAL 함수
3-17) TO_LOB 함수
3-18) TO_MULTI_BYTE 함수
3-19) TO_NCHAR(character) 함수
3-20) TO_NCHAR(datetime) 함수
3-21) TO_NCHAR(number) 함수
3-22) TO_NCLOB 함수
3-23) TO_NUMBER 함수
3-24) TO_SINGLE_BYTE 함수
3-25) TO_YMINTERVAL 함수
3-26) TRANSLATE ... USING 함수
3-27) UNISTR 함수
1-1) CHR 함수
--------------------------------------------------------------------------------
입력된 수의 바이너리 코드에 해당하는 문자를 반환한다.
【예제】
SQL> select chr(75)||chr(79)||chr(82)||chr(69)||chr(65)
2 from dual;
CHR(7
-----
KOREA
SQL>
1-2) CONCAT 함수
--------------------------------------------------------------------------------
입력되는 두 문자열을 연결하여 반환한다.
입력되는 두 문자열의 타입이 다를 경우 다음과 같이 반환된다.
첫 번째 문자열 타입 두 번째 문자열 타입 반환되는 문자열 타입 CLOB NCLOB NCLOB NCLOB NCHAR NCLOB NCLOB CHAR NCLOB NCHAR CLOB NCLOB
【예제】
SQL> select concat('Republic of',' KOREA') from dual;
CONCAT('REPUBLICO
-----------------
Republic of KOREA
SQL>
1-3) INITCAP 함수
--------------------------------------------------------------------------------
initcap('string‘) 함수는 입력 문자열 중에서
각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환한 스트링을 반환한다
【예제】
SQL> select initcap('beautiful corea') from dual;
INITCAP('BEAUTI
---------------
Beautiful Corea
SQL>
1-4) LOWER 함수
--------------------------------------------------------------------------------
ower(string) 함수는 입력된 문자열을 소문자로 반환한다.
【예제】
SQL> select lower('Beautiful COREA') from dual;
LOWER('BEAUTIFU
---------------
beautiful corea
SQL>
1-5) LPAD 함수
--------------------------------------------------------------------------------
pad(char1,n,char2) 함수는
지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 왼쪽부터 char2로 채워서 출력한다.
【형식】
pad (char1, n [, char2] )
【예제】
SQL> select lpad ('Corea', 12, '*') from dual;
LPAD('COREA'
------------
*******Corea
SQL>
1-6) LTRIM 함수
--------------------------------------------------------------------------------
LTRIM(문자열, 문자)함수는 문자열중
좌측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.
【형식】
trim(char [,set] )
【예제】
SQL> select LTRIM('xyxXxyLAST WORD','xy') from dual;
LTRIM('XYXXX
------------
XxyLAST WORD
SQL>
1-7) NLS_INITCAP 함수
--------------------------------------------------------------------------------
nls_initcap(‘string’) 함수는 입력 문자열 중에서
각 단어의 첫 글자를 대문자로
나머지는 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
【형식】
nls_initcap ( char [,'nlsparam'] )
【예제】
SQL> select nls_initcap('beautiful corea', 'nls_sort=binary')
2 from dual;
NLS_INITCAP('BE
---------------
Beautiful Corea
SQL> select nls_initcap('beautiful corea','nls_sort=XDutch')
2 from dual;
NLS_INITCAP('BE
---------------
Beautiful Corea
SQL>
1-8) NLS_LOWER 함수
--------------------------------------------------------------------------------
nls_lower(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
【형식】
nls_lower ( char [,'nlsparam'] )
【예제】
SQL> select nls_lower('CITTA''','nls_sort=XGerman') from dual;
NLS_LO
------
citta'
SQL>
1-9) NLSSORT 함수
--------------------------------------------------------------------------------
nlssort(‘string’) 함수는 입력 문자열을 소팅하여 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
【형식】
nlssort ( char [,'nlsparam'] )
【예제】
SQL> select * from emp
2 order by nlssort(name, 'nls_sort=XDanish');
ID NAME SALARY BONUS
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1104 jijoe 220 100
1103 kim 250 100
SQL>
1-10) NLS_UPPER 함수
--------------------------------------------------------------------------------
nls_upper(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
【형식】
nls_upper ( char [,'nlsparam'] )
【예제】
SQL> select nls_upper('gro?e') from dual;
NLS_U
-----
gro?e
SQL> select nls_upper('gro?e','nls_sort=XGerman')
2 from dual;
NLS_UP
------
grosse
SQL>
1-11) REPLACE 함수
--------------------------------------------------------------------------------
이 함수는 문자열에서 지정한 문자를 다른 문자로 치환한다.
치환될 문자를 지정하지 않으면 해당 문자를 삭제한다.
【형식】
replace (char, search_string [, replacement_string] )
【예제】
SQL> select replace('aaabb','a','b') from dual;
REPLA
-----
bbbbb
SQL> select replace('aaabb','a') from dual;
RE
--
bb
SQL>
1-12) RPAD 함수
--------------------------------------------------------------------------------
rpad(char1,n,char2) 함수는 지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 오른쪽부터 char2로 채워서 출력한다.
【형식】
rpad (char1, n [, char2] )
【예제】
SQL> select rpad('Corea',12,'*') from dual;
RPAD('COREA'
------------
Corea*******
SQL>
1-13) RTRIM 함수
--------------------------------------------------------------------------------
RTRIM(문자열, 문자)함수는 문자열중
우측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.
【형식】
rtrim(char [,set] )
【예제】
SQL> select RTRIM('BROWINGyxXxy','xy') "RTRIM example" from dual;
RTRIM exam
----------
BROWINGyxX
SQL>
1-14) SOUNDEX 함수
--------------------------------------------------------------------------------
soundex(‘char’) 함수는 char과 같은 발음의 이름을 표현한다.
【예제】
SQL> select name from emp;
NAME
----------
Cho
Joe
kim
jijoe
SQL> select name from emp
2 where soundex(name) = soundex('jo');
NAME
----------
Joe
SQL>
1-15) SUBSTR 함수
--------------------------------------------------------------------------------
substr(str,m,n) 함수는 문자열 str 중에서 특정 위치 m으로부터 특정 길이n 만큼의 문자를 출력한다.
m이 0이나 1이면 문자열의 첫글자를 의미하고,
n이 생략되면 문자열의 끝까지를 의미한다.
m이 음수이면 뒤쪽으로부터의 위치를 의미한다.
SUBSTRB는 character 대신 byte를 사용하고,
SUBSTDC는 unicode를 사용하며,
SUBSTR2는 UCS2 codepoint를 사용하고,
SUBSTR4는 UCS4 codepoint를 사용한다.
【형식】
{SUBSTR|SUBSTRB|SUBSTRC|SUNBSTD2|SUBSTR4}
( string, position [,substring_length] )
【예제】
SQL> select substr('abcdesfg', 3,2) from dual;
SU
--
cd
SQL> select substr('abcdefg',3) from dual;
SUBST
-----
cdefg
SQL> select substr('abcdefg', -3,2) from dual; ☜ 뒤에서 3번째부터 2글자를 의미한다.
SU
--
ef
SQL>
1-16) TRANSLATE 함수
--------------------------------------------------------------------------------
TRANSLATE (‘char’,‘from_string’,‘to_string’) 함수는
char 내에 포함된 문자중 from_string에 지정한 모든 각각의 문자를
to_string문자로 각각 변경한다.
【형식】
TRANSLATE ('char','from_string','to_string')
【예제】
SQL> select translate('ababccc','c','d') from dual;
TRANSLA
-------
ababddd
SQL> select translate('2KRW229',
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
3 '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;
TRANSLA
-------
9XXX999
SQL> select translate('2KRW229',
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
3 from dual;
TRAN
----
2229
SQL>
1-17) TREAT 함수
--------------------------------------------------------------------------------
TREAT 함수는 선언된 타입을 변경함으로써, 수퍼타입을 서브타입인 것처럼 처리할 수 있도록 한다.
【형식】
TREAT ( expr AS [ REF] [schema . ] type )
【예제】
SQL> select x.p.empno from person_table p;
select x.p.empno from person_table p
*
ERROR at line 1:
ORA-00904: "X"."P"."EMPNO": invalid identifier
SQL> select treat(x.p as employee).empno empno,
2 x.p.last_name last_name
3 from person_table x;
EMPNO LAST_NAME
---------- --------------------
Seoul
1234 Inchon
5678 Arirang
SQL>
【예제】
SQL> select name, TREAT(VALUE(p) AS employee_t).salary salary
2 FROM person p;
NAME SALARY
---------------------- ---------
Bob
Joe 100000
Tim 1000
SQL>
1-18) TRIM 함수
--------------------------------------------------------------------------------
이 함수는 LTRIM과 RTRIM 함수를 결합한 형태로
문자값의 왼쪽 또는 오른쪽 부분에 정의한 문자를 절삭하여 출력한다.
LEADING은 LTRIM처럼 문자열 왼쪽의 문자를 지정하여 절삭하고,
TRAILING은 RTRIM처럼 문자열 오른쪽 문자를 지정하여 절삭한다.
BOTH는 왼쪽과 오른쪽 문자를 지정하여 절삭한다.
【형식】
TRIM ([{{{LEADING|TRAILING|BOTH} [trim_char] } | trim_char} FROM]
trim_source )
【예제】
SQL> select trim (0 from 000123400) from dual;
TRIM
----
1234
SQL> select trim(trailing 'a' from 'abca') from dual;
TRI
---
abc
SQL> select trim(leading 'a' from 'abca') from dual;
TRI
---
bca
SQL> select trim(both 'a' from 'abca') from dual;
TR
--
bc
SQL>
1-19) UPPER 함수
--------------------------------------------------------------------------------
upper(string) 함수는 입력된 문자열을 대문자로 반환한다.
【예제】
SQL> select upper('Beautiful COREA') from dual;
UPPER('BEAUTIFU
---------------
BEAUTIFUL COREA
SQL>
1-20) ASCII 함수
--------------------------------------------------------------------------------
ASCII
ascii(‘char’) 함수는 주어진 char의 첫 글자의 아스키 값을 반환한다.
char의 타입은 char, varchar2, nchar, nvarchar2중의 하나이어야 한다.
【예제】
SQL> select ascii('Korea') from dual;
ASCII('KOREA')
--------------
75
SQL> select ascii('K') from dual;
ASCII('K')
----------
75
SQL>
1-21) INSTR 함수
--------------------------------------------------------------------------------
이 함수는 문자 스트링 중에서
지정한 문자가 가장 처음 나타나는 위치를 숫자로 출력한다.
【형식】
{INSTR|INSTRB|INSTRC|INSTR2|INSTR4}
( string, substring [, position [,occurrence] ] )
【예제】
SQL> select instr('Corea','e') from dual;
INSTR('COREA','E')
------------------
4
SQL> select instr('corporate floor','or',3,2) from dual;
INSTR('CORPORATEFLOOR','OR',3,2)
--------------------------------
14
SQL> select instrb('corporate floor','or',5,2) from dual;
INSTRB('CORPORATEFLOOR','OR',5,2)
---------------------------------
14
SQL>
1-22) LENGTH 함수
--------------------------------------------------------------------------------
LENGTH(char) 함수는 char의 길이를 반환한다.
LENGTHB는 character 대신 byte를 사용하고,
LENGTHC는 unicode를 사용하며,
LENGTH2는 UCS2 codepoint를 사용하고,
LENGTH4는 UCS4 codepoint를 사용한다.
【형식】
{LENGTH| LENGTHB| LENGTHC| LENGTH2| LENGTH4} (char)
【예제】
SQL> select length('Corea') from dual;
LENGTH('COREA')
---------------
5
SQL> select lengthb('Corea') from dual;
LENGTHB('COREA')
----------------
5
SQL>
2-1) ADD_MONTHS 함수
--------------------------------------------------------------------------------
ADD_MONTHS
ADD_MONTHS(d, n)는 날짜 d에 n 개월을 더한 일자를 반환한다.
【예제】
SQL> select current_date today, add_months(current_date,1) "next month"
2 from dual;
TODAY next mont
--------- ---------
29-JUL-04 29-AUG-04
SQL>
2-2) CURRENT_DATE 함수
--------------------------------------------------------------------------------
이 함수는 현재 session의 날짜 정보를 반환한다.
【예제】
SQL> select current_date from dual;
CURRENT_D
---------
31-JUL-04
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
+09:00
SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select current_date from dual;
CURRENT_DATE
--------------------
31-JUL-2004 09:31:57
SQL> alter session set time_zone='-5:0';
Session altered.
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
-05:00
SQL>
2-3) URRENT_TIMESTAMP 함수
--------------------------------------------------------------------------------
이 함수는 현재 session의 날짜와 시간 정보를 반환한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
SQL>
2-4) DBTIMEZONE 함수
--------------------------------------------------------------------------------
데이터베이스 timezone을 반환한다.
【예제】
SQL> select dbtimezone from dual;
DBTIME
------
-07:00
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
+09:00
SQL>
2-5) EXTRACT(datetime) 함수
--------------------------------------------------------------------------------
특정 날짜/시간 값이나 날짜 값을 가진 표현식으로부터
원하는 날짜 영역을 추출하여 출력한다.
【형식】
EXTRACT ({year|month|day|hour|minute|second|
timezone_hour|timezone_minute|
timezone_region|timezone_abbr}
FROM {datetime_value_expr|interval_value_rxpr})
【예제】
SQL> select extract(year from date '2004-8-2') from dual;
EXTRACT(YEARFROMDATE'2004-8-2')
-------------------------------
2004
SQL>
2-6) FROM_TZ 함수
--------------------------------------------------------------------------------
이 함수는 timestamp 값을 timestamp with time zone 값으로 변환한다.
【형식】
FROM_TZ ( timestamp_value, time_zone_value)
【예제】
SQL> select from_tz(timestamp '2004-8-11 08:00:00','3:00') from dual;
FROM_TZ(TIMESTAMP'2004-8-1108:00:00','3:00')
--------------------------------------------------------------------------
11-AUG-04 08.00.00.000000000 AM +03:00
SQL>
2-7) LAST_DAY 함수
--------------------------------------------------------------------------------
이 함수는 지정한 달의 마지막 날을 출력한다.
【형식】
LAST_DAY ( date )
【예제】
SQL> select sysdate, last_day(sysdate) "last day",
2 last_day(sysdate)- sysdate "Days Left"
3 from dual;
SYSDATE last day Days Left
--------- --------- ----------
04-AUG-04 31-AUG-04 27
SQL>
2-8) LOCALTIMESTAMP 함수
--------------------------------------------------------------------------------
이 함수는 timestamp의 현재 날짜와 시각을 출력한다.
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【형식】
ocaltimestamp [(timestamp_precision)]
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
SQL>
【예제】오류가 발생하는 이유를 잘 이해하자.
SQL> CREATE TABLE local_test(col1 TIMESTAMP WITH LOCAL TIME ZONE);
Table created.
SQL> INSERT INTO local_test VALUES
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'))
*
ERROR at line 2:
ORA-01830: date format picture ends before converting entire input string
SQL> INSERT INTO local_test VALUES
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
1 row created.
SQL> select * from local_test;
COL1
--------------------------------------------------------------------------
04-AUG-04 11.33.58.183398 AM
SQL>
2-9) MONTHS_BETWEEN 함수
--------------------------------------------------------------------------------
MONTHS_BETWEEN(date1,date2) 함수는 date1과 date로 나타내는
날짜와 날짜 사이의 개월 수를 출력한다.
【예제】
SQL> select months_between
2 (to_date('02-02-2004','MM-DD-YYYY'),
3 to_date('01-01-2003','MM-DD-YYYY') ) "Months"
4 FROM dual;
Months
----------
13.0322581
SQL>
2-10) NEW_TIME 함수
--------------------------------------------------------------------------------
NEW_TIME(date,zone1,zone2) 함수는 date, zone1 시간대를 zone2 시간대로 출력한다.
여기서 사용되는 zone은 다음 중의 하나이다.
AST,ADT : Atlantic Standard or Daylight Time
BST,BDT : Bering Standard or Daylight Time
CST,CDT : Central Standard or Daylight Time
EST,EDT : Eastern Standard or Daylight Time
GMT : Greenwich Mean Time
HST,HDT : Alaska-Hawaii Standard or Daylight Time
MST,MDT : Mountain Standard or Daylight Time
NST : Newfoundland Standard Time
PST,PDT : Pacific Standard or Daylight Time
YST,YDT : Yukon Standard or Daylight Time
【예제】
SQL> alter session set nls_date_format =
2 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select NEW_TIME(TO_DATE(
2 '11-10-04 01:23:33', 'MM-DD-YY HH24:MI:SS'),
3 'AST', 'PST') FROM DUAL;
NEW_TIME(TO_DATE('11
--------------------
09-NOV-2004 21:23:33
SQL>
2-11) NEXT_DAY 함수
--------------------------------------------------------------------------------
NEXT_DAY(date,char) 함수는 date로부터 char로 명시한 가장 최근의 날짜를 출력한다.
【예제】
SQL> select next_day('02-AUG-2004','MONDAY') from dual;
NEXT_DAY('02-AUG-200
--------------------
09-AUG-2004 00:00:00
SQL>
2-12) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;
NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;
ENAME HIREDATE NUMTODSIN
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82
14 rows selected.
SQL>
2-13) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
【예제】
SQL> select numtoyminterval(30,'month') from dual;
NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;
ENAME HIREDATE NUMTOYMIN
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84
14 rows selected.
SQL>
2-14) ROUND(date) 함수
--------------------------------------------------------------------------------
이 함수는 날짜를 주어진 형식으로 반올림하는 함수이다.
날짜 형식이 없으면 가장 가까운 날을 출력한다.
【형식】
ROUND( date [,fmt] )
【예제】
SQL> select localtimestamp, round(sysdate,'year') from dual;
LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.26.24.197977 PM
01-JAN-05
SQL> select localtimestamp,round(sysdate,'day') from dual;
LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.29.57.839269 PM
08-AUG-04
SQL> select localtimestamp,round(sysdate) from dual;
LOCALTIMESTAMP
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.30.11.552050 PM
05-AUG-04
SQL>
2-15) SESSIONTIMEZONE 함수
--------------------------------------------------------------------------------
이 함수는 현재 세션의 시간대역을 출력한다.
【예제】
SQL> select sessiontimezone, current_timestamp from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
+09:00
04-AUG-04 01.37.13.355873 PM +09:00
SQL> select sessiontimezone, tz_offset(sessiontimezone) from dual;
SESSIONTIMEZONE
--------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00
SQL>
2-16) SYS_EXTRACT_UTC 함수
--------------------------------------------------------------------------------
sys_extract_utc(datetime_with_timezone) 함수는
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.
【예제】
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM
SQL>
2-17) SYSDATE 함수
--------------------------------------------------------------------------------
이 함수는 오늘 현재 날짜와 시각을 출력한다.
【예제】
SQL> select sysdate, current_timestamp from dual;
SYSDATE
---------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.51.39.767156 PM +09:00
SQL> select to_char
2 (sysdate, 'MM-DD-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'MM
-------------------
08-04-2004 13:53:18
SQL>
2-18) SYSTIMESTAMP 함수
--------------------------------------------------------------------------------
이 함수는 시스템의 날짜를 출력한다.
【예제】
SQL> select sysdate,systimestamp,localtimestamp from dual;
SYSDATE
---------
SYSTIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.58.06.346528 PM +09:00
04-AUG-04 01.58.06.346552 PM
SQL>
2-19) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.
【형식】
to_dsinterval ( char [ ‘nlsparam’] )
【예제】
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;
SYSDATE 3days 17h
--------- ---------
04-AUG-04 08-AUG-04
SQL>
2-20) TO_TIMESTAMP 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 timestamp 형식으로 변환하여 출력한다
【형식】
to_timestamp ( char [,fmt ['nlsparam'] ] )
【예제】
SQL> select to_timestamp('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')
2 from dual;
TO_TIMESTAMP('2004-8-201:30:00','YYYY-MM-DDHH:MI:SS')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM
SQL>
2-21) TO_TIMESTAMP_TZ 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 timestamp with time zone 형식으로 변환하여 출력한다.
【형식】
to_timestamp_tz ( char [,fmt ['nlsparam'] ] )
【예제】
SQL> select to_timestamp_tz('2004-8-20 1:30:00 -3:00',
2 'YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;
TO_TIMESTAMP_TZ('2004-8-201:30:00-3:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM -03:00
SQL>
2-22) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------
TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.
【예제】
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;
SYSDATE 15Months
--------- ---------
04-AUG-04 04-NOV-05
SQL>
2-23) TRUNC(date) 함수
--------------------------------------------------------------------------------
이 함수는 날짜를 절삭하여 출력한다.
【형식】
TRUNC ( date [.fmt] )
【예제】
SQL> select trunc(to_date('27-AUG-04','DD-MON-YY'), 'YEAR')
2 from dual;
TRUNC(TO_
---------
01-JAN-04
SQL>
2-24) TZ_OFFSET 함수
--------------------------------------------------------------------------------
이 함수는 time zone의 offset 값을 출력한다.
【형식】
TZ_OFFSET ( {‘time_zone_name’ | SESSIONTIMEZONE | DBTIMEZONE |
‘{+|-} hh:mi’ } )
【예제】
SQL> select sessiontimezone, tz_offset('ROK') from dual;
SESSIONTIMEZONE
---------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00
SQL>
3-1) ASCIISTR 함수
--------------------------------------------------------------------------------
asciistr('string')의 string의 아스키 문자로 반환한다.
Ä
【예제】
SQL> select ascii('ABÄCDE') from dual;
☜ ABÄCDE의 두번째 Ä는 A에 움라우트(Umlaut)가 붙은 글씨이다.
ASCIIS
------
ABDCDE
SQL>
3-2) BIN_TO_NUM 함수
--------------------------------------------------------------------------------
이 함수는 2진수 벡터를 10진수로 변환한다.
SQL> select bin_to_num(1,0,1,0) from dual;
BIN_TO_NUM(1,0,1,0)
-------------------
10
SQL>
3-3) CAST 함수
--------------------------------------------------------------------------------
데이터형식이나 collection 형식을 다른 데이터형식이나 다른 collection 형식으로 변환한다.
【예제】데이터형식인 경우
SQL> select current_date from dual;
CURRENT_D
---------
30-JUL-04
SQL> select cast(current_date as timestamp) from dual;
CAST(CURRENT_DATEASTIMESTAMP)
---------------------------------------------------------------------------
30-JUL-04 12.29.15.000000 PM
SQL>
3-4) CHARTOROWID 함수
--------------------------------------------------------------------------------
이 함수는 char, varchar2, nchar, ncharvar2형 데이터 타입을 rowid 형 데이터 타입으로 변경한다.
【예제】
SQL> select name from emp
2 where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');
NAME
----------
jijoe
SQL> select rowid,name from emp;
ROWID NAME
------------------ ----------
AAAHZ+AABAAAMWiAAA Cho
AAAHZ+AABAAAMWiAAB Joe
AAAHZ+AABAAAMWiAAC kim
AAAHZ+AABAAAMWiAAF jijoe
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHZ+ AAB AAAMWi AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-5) COMPOSE 함수
--------------------------------------------------------------------------------
입력된 스트링을 unicode로 나타낸다.
【예제】
SQL> select compose('aa' || unistr('\0308') ) from dual;
CO
--
aa
SQL>
3-6) CONVERT 함수
--------------------------------------------------------------------------------
입력된 문자열을 지정한 코드로 변환한다.
공용 문자셋은 살펴보자.
US7ASCII US 7-bit ASCII 문자 WE8DEC 서유럽 8비트 문자 WE8HP HP 서유럽 레이져젯 8비트 문자 F7DEC DEC 프랑스 7비트 문자 WE8EBCDIC500 IBM 서유럽 EBCDIC 코드 페이지 500 WE8PC850 IBM PC 코드 페이지 850 WE8ISO8859P1 ISO 8859 서유럽 8비트 문자
【예제】
SQL> select convert('arirang','we8pc850') from dual;
CONVERT
-------
arirang
SQL>
3-7) HEXTORAW 함수
--------------------------------------------------------------------------------
HEXTORAW(char) 함수는 char, varchar2, nchar, nvarchar2 따위의 문자로 주어지는
hexadecimal digit을 raw 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL>
3-8) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
【예제】
SQL> select numtodsinterval(100,'MINUTE') from dual;
NUMTODSINTERVAL(100,'MINUTE')
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;
ENAME HIREDATE NUMTODSIN
---------- --------- ---------
SMITH 17-DEC-80 27-MAR-81
ALLEN 20-FEB-81 31-MAY-81
WARD 22-FEB-81 02-JUN-81
JONES 02-APR-81 11-JUL-81
MARTIN 28-SEP-81 06-JAN-82
BLAKE 01-MAY-81 09-AUG-81
CLARK 09-JUN-81 17-SEP-81
SCOTT 19-APR-87 28-JUL-87
KING 17-NOV-81 25-FEB-82
TURNER 08-SEP-81 17-DEC-81
ADAMS 23-MAY-87 31-AUG-87
JAMES 03-DEC-81 13-MAR-82
FORD 03-DEC-81 13-MAR-82
MILLER 23-JAN-82 03-MAY-82
14 rows selected.
SQL>
3-9) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
【예제】
SQL> select numtoyminterval(30,'month') from dual;
NUMTOYMINTERVAL(30,'MONTH')
---------------------------------------------------------------------------
+000000002-06
SQL>
【예제】
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;
ENAME HIREDATE NUMTOYMIN
---------- --------- ---------
SMITH 17-DEC-80 17-JUN-83
ALLEN 20-FEB-81 20-AUG-83
WARD 22-FEB-81 22-AUG-83
JONES 02-APR-81 02-OCT-83
MARTIN 28-SEP-81 28-MAR-84
BLAKE 01-MAY-81 01-NOV-83
CLARK 09-JUN-81 09-DEC-83
SCOTT 19-APR-87 19-OCT-89
KING 17-NOV-81 17-MAY-84
TURNER 08-SEP-81 08-MAR-84
ADAMS 23-MAY-87 23-NOV-89
JAMES 03-DEC-81 03-JUN-84
FORD 03-DEC-81 03-JUN-84
MILLER 23-JAN-82 23-JUL-84
14 rows selected.
SQL>
3-10) RAWTOHEX 함수
--------------------------------------------------------------------------------
RAWTOHEX(raw) 함수는 raw 값을 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtohex(raw_col) from test;
RAWTOHEX(RAW_COL)
--------------------
7D
SQL>
3-11) RAWTONHEX 함수
--------------------------------------------------------------------------------
RAWTONHEX(raw) 함수는 raw 값을 nvarchar2형 hexadecimal 값으로 변환한다.
【예제】
SQL> create table test(raw_col RAW(10));
Table created.
SQL> insert into test VALUES (HEXTORAW('7D'));
1 row created.
SQL> select * from test;
RAW_COL
--------------------
7D
SQL> select rawtonhex(raw_col) from test;
RAWTONHEX(RAW_COL)
--------------------
7D
SQL>
3-12) ROWIDTOCHAR 함수
--------------------------------------------------------------------------------
RAWIDTOCHAR(rowid) 함수는 rowid 값을 varchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-13) ROWIDTONCHAR 함수
--------------------------------------------------------------------------------
RAWIDTONCHAR(rowid) 함수는 rowid 값을 nvarchar2 형식의 데이터로 변환한다.
【예제】
SQL> select rowid from test;
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select rowid from test
2 where rowidtochar(rowid) like '%AABAA%';
ROWID
------------------
AAAHbHAABAAAMXCAAA
SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)
2 from test;
LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID
---------------------------- ------------------
36 AAAHbHAABAAAMXCAAA
SQL>
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-14) TO_CHAR(character) 함수
--------------------------------------------------------------------------------
이 함수는 nchar, nvarchar2, clob, nclob 형식의 데이터를
데이터베이스 character set으로 변환한다. 즉, 문자로 변환한다.
【형식】
TO_CHAR( nchar| clob | nclob)
【예제】
SQL> select to_char('01110') from dual;
TO_CH
-----
01110
SQL>
3-15) TO_CLOB 함수
--------------------------------------------------------------------------------
이 함수는 LOB 컬럼에 있는 NCLOB나 또는 다른 문자 스트링을 CLOB로 변환한다.
【형식】
TO_CLOBR({ lob_column | char})
【예제】
SQL> select to_clob('corea') from dual;
TO_CLOB('COREA')
--------------------------------------------------------------------------
corea
SQL>
3-16) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------
이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.
【형식】
to_dsinterval ( char [ ‘nlsparam’] )
【예제】
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;
SYSDATE 3days 17h
--------- ---------
04-AUG-04 08-AUG-04
SQL>
3-17) TO_LOB 함수
--------------------------------------------------------------------------------
TO_LOB(long_column) 함수는 LONG, LONG RAW 컬럼의 데이터를 LOB 값으로 변환한다.
【예제】
SQL> create table test2(zz clob);
Table created.
SQL> insert into test2
2 (select to_lob(p.raw_col) from test p);
SQL>
3-18) TO_MULTI_BYTE 함수
--------------------------------------------------------------------------------
TO_MULTI_BYTE(char) 함수는 싱글 바이트 문자를 포함한 모든 문자열을 다중바이트 문자열로 변환한다.
【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;
DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
SQL>
3-19) TO_NCHAR(character) 함수
--------------------------------------------------------------------------------
이 함수는 문자스트링, clob, nclob 형식의 데이터를 national character set,
즉 nchar으로 변환한다. 이는 translate ... using 문의 경우와 같다.
【형식】
TO_NCHAR({char|clob|nclob} [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar('Corea') from dual;
TO_NC
-----
Corea
SQL>
3-20) TO_NCHAR(datetime) 함수
--------------------------------------------------------------------------------
이 함수는 date, timestamp, timestamp with time zone, timestamp with local time zone,
interval month to year, interval day to second 형식의 데이터를
nchar 형식의 데이터로 변환한다.
【형식】
TO_NCHAR({datetime|interval} [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar(sysdate) from dual;
TO_NCHAR(SYSDATE)
------------------------------
05-AUG-04
SQL>
3-21) TO_NCHAR(number) 함수
--------------------------------------------------------------------------------
이 함수는 숫자를 nvarchar2 형식의 데이터로 변환한다.
【형식】
TO_NCHAR(n [,fmt [,'nlsparam']])
【예제】
SQL> select to_nchar(1234) from dual;
TO_N
----
1234
SQL> select to_nchar(rownum) from test;
TO_NCHAR(ROWNUM)
----------------------------------------
1
SQL>
3-22) TO_NCLOB 함수
--------------------------------------------------------------------------------
이 함수는 clob, 문자열 형식의 데이터를 nclob 형식의 데이터로 변환한다.
【형식】
TO_NCLOB({char|lob_column})
【예제】
SQL> select to_nclob('Corea') from dual;
TO_NCLOB('COREA')
--------------------------------------------------------------------------
Corea
SQL>
3-23) TO_NUMBER 함수
--------------------------------------------------------------------------------
이 함수는 숫자를 포함하는 char, varchar2, nchar, nvarchar2 형식의
문자 데이터를 number 형식의 숫자 데이터로 변환한다.
【형식】
TO_NUMBER(char [,fmt [,'nlsparam']])
【예제】
SQL> select to_number('1234') from dual;
TO_NUMBER('1234')
-----------------
1234
SQL>
3-24) TO_SINGLE_BYTE 함수
--------------------------------------------------------------------------------
TO_SINGLE_BYTE(char) 함수는 다중 바이트 문자열을 single byte 문자로 변환한다.
【예제】
SQL> select dump(to_multi_byte('Corea')) from dual;
DUMP(TO_MULTI_BYTE('COREA'))
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
SQL> select dump(to_single_byte('Corea')) from dual;
DUMP(TO_SINGLE_BYTE('COREA'))
------------------------------
Typ=1 Len=5: 67,111,114,101,97
SQL> select to_single_byte(chr(65)) from dual;
T
-
A
SQL>
3-25) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------
TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.
【예제】
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;
SYSDATE 15Months
--------- ---------
04-AUG-04 04-NOV-05
SQL>
3-26) TRANSLATE ... USING 함수
--------------------------------------------------------------------------------
이 함수는 텍스트 형식의 데이터를 지정한 문자 형식의 데이터로 변환한다.
【형식】
TRANSLATE ( text USING {CHAR_CS|NCHAR_CS} )
【예제】
SQL> select translate('Corea' USING char_cs) from dual;
TRANS
-----
Corea
SQL> select to_nchar('Corea') from dual;
TO_NC
-----
Corea
SQL>
3-27) UNISTR 함수
--------------------------------------------------------------------------------
UNISTR('string') 함수는 스트링 형식의 데이터를 nchar 형식의 데이터로 변환한다.
【예제】
SQL> select unistr('abc\00e5\00f1\00f6') from dual;
UNISTR
------
abc??o
SQL> select unistr('Corea') from dual;
UNIST
-----
Corea
SQL>
Posted by 홍반장
