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 함수
4. 기타함수(miscellaneous single row function)
4-1) BFILENAME 함수
4-2) COALESCE 함수
4-3) DECODE 함수
4-4) DEPTH 함수
4-5) DUMP 함수
4-6) EMPTY_BLOB 함수
4-7) EMPTY_CLOB 함수
4-8) EXISTSNODE 함수
4-9) EXTRACT(XML) 함수
4-10) EXTRACTVALUE 함수
4-11) GREATEST 함수
4-12) LEAST 함수
4-13) NLS_CHARSET_DECL_LEN 함수
4-14) NLS_CHARSET_ID 함수
4-15) NLS_CHARSET_NAME 함수
4-16) NULLIF 함수
4-17) NVL2 함수
4-18) PATH 함수
4-19) SYS_CONNECT_BY_PATH 함수
4-20) SYS_CONTEXT 함수
4-21) SYS_DBURIGEN 함수
4-22) SYS_EXTRACT_UTC 함수
4-23) SYS_GUID 함수
4-24) SYS_XMLAGG 함수
4-25) SYS_XMLGEN 함수
4-26) UID 함수
4-27) USER 함수
4-28) USERENV 함수
4-29) VSIZE 함수
4-30) XMLAGG 함수
4-31) XMLCOLATTVAL 함수
4-32) XMLCONCAT 함수
4-33) XMLFOREST 함수
4-34) XMLELEMENT 함수
5.그룹함수 Aggregate 함수
5-1) AVG* 함수
5-2) CORR* CORR* 함수
5-3) COUNT* 함수
5-4) COVAR_POP 함수
5-5) COVAR_SAMP 함수
5-6) CUME_DIST 함수
5-7) DENSE_RANK 함수
5-8) FIRST 함수
5-9) GROUP_ID 함수
5-10) Grouping 함수
5-11) GROUPING_ID 함수
5-12) LAST 함수
5-13) MAX 함수
5-14) MIN 함수
5-15) PERCENTILE_CONT 함수
5-16) PERCENTILE_DISC 함수
5-17) PERCENT_RANK 함수
5-18) RANK 함수
5-19) REGR_(linear regression) function* 함수
5-20) STDDEV 함수
5-21) STDDEV_POP 함수
5-22) STDDEV_SAMP 함수
5-23) SUM 함수
5-24) VAR_POP 함수
5-25) VAR_SAMP 함수
5-26) VARIANCE 함수
5-27) Grouping sets 함수
6. Analytic 함수
6-1) AVG* 함수
6-2) CORR* CORR* 함수
6-3) COUNT* 함수
6-4) COVAR_SAMP 함수
6-5) CUME_DIST 함수
6-6) DENSE_RANK 함수
6-7) FIRST 함수
6-8) FIRST_VALUE 함수
6-9) LAG 함수
6-10) LAST_VALUE 함수
6-11) LEAD 함수
6-12) NTILE 함수
6-13) RATIO_TO_REPORT 함수
6-14) ROW_NUMBER 함수
7. 객체 참조 함수
7-1) REF 타입
8. PseudoColumn을 의미하는 것
8-1) ROWID 컬럼
8-2) ROWNUM 컬럼
--------------------------------------------------------------------------------
SQL> select chr(75)||chr(79)||chr(82)||chr(69)||chr(65)
2 from dual;
-----
KOREA
--------------------------------------------------------------------------------
입력되는 두 문자열의 타입이 다를 경우 다음과 같이 반환된다.
【예제】
SQL> select concat('Republic of',' KOREA') from dual;
-----------------
Republic of KOREA
1-3) INITCAP 함수
--------------------------------------------------------------------------------
각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환한 스트링을 반환한다
SQL> select initcap('beautiful corea') from dual;
---------------
Beautiful Corea
1-4) LOWER 함수
--------------------------------------------------------------------------------
SQL> select lower('Beautiful COREA') from dual;
---------------
beautiful corea
1-5) LPAD 함수
--------------------------------------------------------------------------------
지정된 길이 n에서 문자 char1으로 채우고
남은 공간은 왼쪽부터 char2로 채워서 출력한다.
lpad (char1, n [, char2] )
SQL> select lpad ('Corea', 12, '*') from dual;
------------
*******Corea
1-6) LTRIM 함수
--------------------------------------------------------------------------------
좌측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.
ltrim(char [,set] )
SQL> select LTRIM('xyxXxyLAST WORD','xy') from dual;
------------
XxyLAST WORD
--------------------------------------------------------------------------------
각 단어의 첫 글자를 대문자로
나머지는 소문자로 변환한 스트링을 반환한다.
단어의 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;
---------------
Beautiful Corea
2 from dual;
---------------
Beautiful Corea
1-8) NLS_LOWER 함수
--------------------------------------------------------------------------------
단어의 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;
------
citta'
1-9) NLSSORT 함수
--------------------------------------------------------------------------------
단어의 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');
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1104 jijoe 220 100
1103 kim 250 100
--------------------------------------------------------------------------------
단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.
nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,
여기서 sort는 linguistic sort sequence나 binary중의 하나이다.
nls_upper ( char [,'nlsparam'] )
SQL> select nls_upper('gro?e') from dual;
-----
gro?e
2 from dual;
------
grosse
--------------------------------------------------------------------------------
치환될 문자를 지정하지 않으면 해당 문자를 삭제한다.
replace (char, search_string [, replacement_string] )
SQL> select replace('aaabb','a','b') from dual;
-----
bbbbb
--
bb
1-12) RPAD 함수
--------------------------------------------------------------------------------
남은 공간은 오른쪽부터 char2로 채워서 출력한다.
rpad (char1, n [, char2] )
SQL> select rpad('Corea',12,'*') from dual;
------------
Corea*******
--------------------------------------------------------------------------------
우측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.
rtrim(char [,set] )
SQL> select RTRIM('BROWINGyxXxy','xy') "RTRIM example" from dual;
----------
BROWINGyxX
1-14) SOUNDEX 함수
--------------------------------------------------------------------------------
SQL> select name from emp;
----------
Cho
Joe
kim
jijoe
2 where soundex(name) = soundex('jo');
----------
Joe
--------------------------------------------------------------------------------
m이 0이나 1이면 문자열의 첫글자를 의미하고,
n이 생략되면 문자열의 끝까지를 의미한다.
m이 음수이면 뒤쪽으로부터의 위치를 의미한다.
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;
--
cd
-----
cdefg
SU
--
ef
--------------------------------------------------------------------------------
char 내에 포함된 문자중 from_string에 지정한 모든 각각의 문자를
to_string문자로 각각 변경한다.
TRANSLATE ('char','from_string','to_string')
SQL> select translate('ababccc','c','d') from dual;
-------
ababddd
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
3 '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;
-------
9XXX999
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')
3 from dual;
----
2229
--------------------------------------------------------------------------------
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;
---------------------- ---------
Bob
Joe 100000
Tim 1000
--------------------------------------------------------------------------------
문자값의 왼쪽 또는 오른쪽 부분에 정의한 문자를 절삭하여 출력한다.
LEADING은 LTRIM처럼 문자열 왼쪽의 문자를 지정하여 절삭하고,
TRAILING은 RTRIM처럼 문자열 오른쪽 문자를 지정하여 절삭한다.
BOTH는 왼쪽과 오른쪽 문자를 지정하여 절삭한다.
TRIM ([{{{LEADING|TRAILING|BOTH} [trim_char] } | trim_char} FROM]
trim_source )
SQL> select trim (0 from 000123400) from dual;
----
1234
---
abc
---
bca
--
bc
1-19) UPPER 함수
--------------------------------------------------------------------------------
SQL> select upper('Beautiful COREA') from dual;
---------------
BEAUTIFUL COREA
--------------------------------------------------------------------------------
ascii(‘char’) 함수는 주어진 char의 첫 글자의 아스키 값을 반환한다.
char의 타입은 char, varchar2, nchar, nvarchar2중의 하나이어야 한다.
SQL> select ascii('Korea') from dual;
--------------
75
----------
75
--------------------------------------------------------------------------------
지정한 문자가 가장 처음 나타나는 위치를 숫자로 출력한다.
{INSTR|INSTRB|INSTRC|INSTR2|INSTR4}
( string, substring [, position [,occurrence] ] )
SQL> select instr('Corea','e') from dual;
------------------
4
--------------------------------
14
---------------------------------
14
1-22) LENGTH 함수
--------------------------------------------------------------------------------
LENGTHB는 character 대신 byte를 사용하고,
LENGTHC는 unicode를 사용하며,
LENGTH2는 UCS2 codepoint를 사용하고,
LENGTH4는 UCS4 codepoint를 사용한다.
{LENGTH| LENGTHB| LENGTHC| LENGTH2| LENGTH4} (char)
SQL> select length('Corea') from dual;
---------------
5
----------------
5
2-1) ADD_MONTHS 함수
--------------------------------------------------------------------------------
ADD_MONTHS(d, n)는 날짜 d에 n 개월을 더한 일자를 반환한다.
SQL> select current_date today, add_months(current_date,1) "next month"
2 from dual;
--------- ---------
29-JUL-04 29-AUG-04
--------------------------------------------------------------------------------
이 함수는 현재 session의 날짜 정보를 반환한다.
【예제】
SQL> select current_date from dual;
---------
31-JUL-04
--------------------------------------------------------------------------
+09:00
--------------------
31-JUL-2004 09:31:57
--------------------------------------------------------------------------
-05:00
--------------------------------------------------------------------------------
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
【예제】
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
CURRENT_D
---------
04-AUG-04 11.17.40.768776 AM +09:00
04-AUG-04 11.17.40.768776 AM
04-AUG-04
--------------------------------------------------------------------------------
데이터베이스 timezone을 반환한다.
【예제】
SQL> select dbtimezone from dual;
------
-07:00
--------------------------------------------------------------------------
+09:00
--------------------------------------------------------------------------------
원하는 날짜 영역을 추출하여 출력한다.
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;
-------------------------------
2004
--------------------------------------------------------------------------------
FROM_TZ ( timestamp_value, time_zone_value)
SQL> select from_tz(timestamp '2004-8-11 08:00:00','3:00') from dual;
--------------------------------------------------------------------------
11-AUG-04 08.00.00.000000000 AM +03:00
--------------------------------------------------------------------------------
LAST_DAY ( date )
SQL> select sysdate, last_day(sysdate) "last day",
2 last_day(sysdate)- sysdate "Days Left"
3 from dual;
--------- --------- ----------
04-AUG-04 31-AUG-04 27
--------------------------------------------------------------------------------
current_timestamp는 time zone까지 출력되지만,
localtimestamp는 time zone은 출력되지 않는다.
localtimestamp [(timestamp_precision)]
SQL> select current_timestamp, localtimestamp,
2 current_date from dual;
--------------------------------------------------------------------------
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> CREATE TABLE local_test(col1 TIMESTAMP WITH LOCAL TIME ZONE);
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
2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
--------------------------------------------------------------------------
04-AUG-04 11.33.58.183398 AM
2-9) MONTHS_BETWEEN 함수
--------------------------------------------------------------------------------
날짜와 날짜 사이의 개월 수를 출력한다.
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;
----------
13.0322581
--------------------------------------------------------------------------------
여기서 사용되는 zone은 다음 중의 하나이다.
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';
2 '11-10-04 01:23:33', 'MM-DD-YY HH24:MI:SS'),
3 'AST', 'PST') FROM DUAL;
--------------------
09-NOV-2004 21:23:33
2-11) NEXT_DAY 함수
--------------------------------------------------------------------------------
SQL> select next_day('02-AUG-2004','MONDAY') from dual;
--------------------
09-AUG-2004 00:00:00
2-12) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
SQL> select numtodsinterval(100,'MINUTE') from dual;
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;
---------- --------- ---------
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
--------------------------------------------------------------------------------
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
SQL> select numtoyminterval(30,'month') from dual;
---------------------------------------------------------------------------
+000000002-06
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;
---------- --------- ---------
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
2-14) ROUND(date) 함수
--------------------------------------------------------------------------------
날짜 형식이 없으면 가장 가까운 날을 출력한다.
ROUND( date [,fmt] )
SQL> select localtimestamp, round(sysdate,'year') from dual;
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.26.24.197977 PM
01-JAN-05
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.29.57.839269 PM
08-AUG-04
--------------------------------------------------------------------------
ROUND(SYS
---------
04-AUG-04 01.30.11.552050 PM
05-AUG-04
--------------------------------------------------------------------------------
SQL> select sessiontimezone, current_timestamp from dual;
--------------------------------------------------------------------------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
+09:00
04-AUG-04 01.37.13.355873 PM +09:00
--------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00
--------------------------------------------------------------------------------
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM
SQL>
--------------------------------------------------------------------------------
SQL> select sysdate, current_timestamp from dual;
---------
CURRENT_TIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.51.39.767156 PM +09:00
2 (sysdate, 'MM-DD-YYYY HH24:MI:SS') from dual;
-------------------
08-04-2004 13:53:18
--------------------------------------------------------------------------------
SQL> select sysdate,systimestamp,localtimestamp from dual;
---------
SYSTIMESTAMP
--------------------------------------------------------------------------
LOCALTIMESTAMP
--------------------------------------------------------------------------
04-AUG-04
04-AUG-04 01.58.06.346528 PM +09:00
04-AUG-04 01.58.06.346552 PM
--------------------------------------------------------------------------------
to_dsinterval ( char [ ‘nlsparam’] )
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;
--------- ---------
04-AUG-04 08-AUG-04
--------------------------------------------------------------------------------
to_timestamp ( char [,fmt ['nlsparam'] ] )
SQL> select to_timestamp('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')
2 from dual;
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM
2-21) TO_TIMESTAMP_TZ 함수
--------------------------------------------------------------------------------
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;
--------------------------------------------------------------------------
20-AUG-04 01.30.00.000000000 AM -03:00
--------------------------------------------------------------------------------
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;
--------- ---------
04-AUG-04 04-NOV-05
--------------------------------------------------------------------------------
TRUNC ( date [.fmt] )
SQL> select trunc(to_date('27-AUG-04','DD-MON-YY'), 'YEAR')
2 from dual;
---------
01-JAN-04
--------------------------------------------------------------------------------
TZ_OFFSET ( {‘time_zone_name’ | SESSIONTIMEZONE | DBTIMEZONE |
‘{+|-} hh:mi’ } )
SQL> select sessiontimezone, tz_offset('ROK') from dual;
---------------------------------------------------------------------------
TZ_OFFS
-------
+09:00
+09:00
3-1) ASCIISTR 함수
--------------------------------------------------------------------------------
Ä
【예제】
SQL> select ascii('ABÄCDE') from dual;
☜ ABÄCDE의 두번째 Ä는 A에 움라우트(Umlaut)가 붙은 글씨이다.
------
ABDCDE
SQL>
3-2) BIN_TO_NUM 함수
--------------------------------------------------------------------------------
-------------------
10
--------------------------------------------------------------------------------
SQL> select current_date from dual;
---------
30-JUL-04
---------------------------------------------------------------------------
30-JUL-04 12.29.15.000000 PM
--------------------------------------------------------------------------------
【예제】
SQL> select name from emp
2 where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');
----------
jijoe
------------------ ----------
AAAHZ+AABAAAMWiAAA Cho
AAAHZ+AABAAAMWiAAB Joe
AAAHZ+AABAAAMWiAAC kim
AAAHZ+AABAAAMWiAAF jijoe
여기서 rowid의 의미는 다음과 같다.
AAAHZ+ AAB AAAMWi AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-5) COMPOSE 함수
--------------------------------------------------------------------------------
SQL> select compose('aa' || unistr('308') ) from dual;
--
aa
--------------------------------------------------------------------------------
공용 문자셋은 살펴보자.
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;
-------
arirang
--------------------------------------------------------------------------------
hexadecimal digit을 raw 값으로 변환한다.
SQL> create table test(raw_col RAW(10));
--------------------
7D
3-8) NUMTODSINTERVAL 함수
--------------------------------------------------------------------------------
char_expr은 다음 중의 하나이다.
‘DAY’
‘HOUR’
‘MINUTE’
‘SECOND’
SQL> select numtodsinterval(100,'MINUTE') from dual;
--------------------------------------------------------------------------
+000000000 01:40:00.000000000
SQL> connect scott/tiger
Connected.
SQL> select ename, hiredate,
2 numtodsinterval(100,'day')+hiredate from emp;
---------- --------- ---------
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
3-9) NUMTOYMINTERVAL 함수
--------------------------------------------------------------------------------
char_expr은 다음 중의 하나이다.
‘YEAR’
‘MONTH’
SQL> select numtoyminterval(30,'month') from dual;
---------------------------------------------------------------------------
+000000002-06
SQL> connect scott/tiger
Connected.
SQL> select ename,hiredate,
2 numtoyminterval(30,'month')+hiredate from emp;
---------- --------- ---------
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
3-10) RAWTOHEX 함수
--------------------------------------------------------------------------------
SQL> create table test(raw_col RAW(10));
--------------------
7D
--------------------
7D
--------------------------------------------------------------------------------
SQL> create table test(raw_col RAW(10));
--------------------
7D
--------------------
7D
--------------------------------------------------------------------------------
SQL> select rowid from test;
------------------
AAAHbHAABAAAMXCAAA
2 where rowidtochar(rowid) like '%AABAA%';
------------------
AAAHbHAABAAAMXCAAA
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-13) ROWIDTONCHAR 함수
--------------------------------------------------------------------------------
SQL> select rowid from test;
------------------
AAAHbHAABAAAMXCAAA
2 where rowidtochar(rowid) like '%AABAA%';
------------------
AAAHbHAABAAAMXCAAA
2 from test;
---------------------------- ------------------
36 AAAHbHAABAAAMXCAAA
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
3-14) TO_CHAR(character) 함수
--------------------------------------------------------------------------------
데이터베이스 character set으로 변환한다. 즉, 문자로 변환한다.
TO_CHAR( nchar| clob | nclob)
SQL> select to_char('01110') from dual;
-----
01110
3-15) TO_CLOB 함수
--------------------------------------------------------------------------------
TO_CLOBR({ lob_column | char})
SQL> select to_clob('corea') from dual;
--------------------------------------------------------------------------
corea
3-16) TO_DSINTERVAL 함수
--------------------------------------------------------------------------------
to_dsinterval ( char [ ‘nlsparam’] )
SQL> select sysdate,
2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"
3 from dual;
--------- ---------
04-AUG-04 08-AUG-04
--------------------------------------------------------------------------------
SQL> create table test2(zz clob);
2 (select to_lob(p.raw_col) from test p);
--------------------------------------------------------------------------------
SQL> select dump(to_multi_byte('Corea')) from dual;
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
3-19) TO_NCHAR(character) 함수
--------------------------------------------------------------------------------
즉 nchar으로 변환한다. 이는 translate ... using 문의 경우와 같다.
TO_NCHAR({char|clob|nclob} [,fmt [,'nlsparam']])
SQL> select to_nchar('Corea') from dual;
-----
Corea
--------------------------------------------------------------------------------
interval month to year, interval day to second 형식의 데이터를
nchar 형식의 데이터로 변환한다.
TO_NCHAR({datetime|interval} [,fmt [,'nlsparam']])
SQL> select to_nchar(sysdate) from dual;
------------------------------
05-AUG-04
3-21) TO_NCHAR(number) 함수
--------------------------------------------------------------------------------
TO_NCHAR(n [,fmt [,'nlsparam']])
SQL> select to_nchar(1234) from dual;
----
1234
----------------------------------------
1
3-22) TO_NCLOB 함수
--------------------------------------------------------------------------------
TO_NCLOB({char|lob_column})
SQL> select to_nclob('Corea') from dual;
--------------------------------------------------------------------------
Corea
3-23) TO_NUMBER 함수
--------------------------------------------------------------------------------
문자 데이터를 number 형식의 숫자 데이터로 변환한다.
TO_NUMBER(char [,fmt [,'nlsparam']])
SQL> select to_number('1234') from dual;
-----------------
1234
--------------------------------------------------------------------------------
SQL> select dump(to_multi_byte('Corea')) from dual;
-----------------------------------------------------
Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225
------------------------------
Typ=1 Len=5: 67,111,114,101,97
-
A
3-25) TO_YMINTERVAL 함수
--------------------------------------------------------------------------------
SQL> select sysdate,
2 sysdate+to_yminterval('01-03') "15Months later"
3 from dual;
--------- ---------
04-AUG-04 04-NOV-05
--------------------------------------------------------------------------------
TRANSLATE ( text USING {CHAR_CS|NCHAR_CS} )
SQL> select translate('Corea' USING char_cs) from dual;
-----
Corea
-----
Corea
--------------------------------------------------------------------------------
SQL> select unistr('abc0e50f10f6') from dual;
------
abc??o
-----
Corea
4-1) BFILENAME 함수
--------------------------------------------------------------------------------
【형식】
bfilename ('디렉토리‘,’파일이름‘)
SQL> connect system/manager
--------------------------------------------------------------------------------
SQL> select coalesce('','','arirang','kunsan') from dual;
-------
arirang
4-3) DECODE 함수
--------------------------------------------------------------------------------
따라서 일반 프로그래밍 언어의 IF문이 수행 할 수 있는 기능을 포함하고 있다.
select시의 decode 사용은 from 절만 빼고 어디에서나 사용할 수 있다.
일반 프로그래밍과 decode 함수를 서로 비교하여 보자.
IF문 Decode 함수 IF A=B THEN RETURN 'T';END IF; DECODE(A,B,'T') IF A=B THENRETURN 'T';ELSIF A=C THENRETURN 'F';ELSERETURN 'X';END IF; DECODE(A,B,'T',C,'F','X')
【형식】
DECODE(검색컬럼,조건1,결과값1,
조건2,결과값2,...,기본값);
SQL> connect jijoe/jijoe_password
SQL> create table aa(
2 pid number(12) primary key,
3 addr varchar2(20),
4 name varchar2(10));
SQL> insert into aa values(3456,'seoul','sunny')
---------- -------------------- ----------
1234 kunsan jijoe
3456 seoul sunny
----------
jijoe
SQL>
【예제】
SQL> desc ddd
Name Null? Type
----------------------------------------- -------- ----------------------------
NO NUMBER(4)
NAME VARCHAR2(10)
HIRDATE DATE
DEPTNO NUMBER(5)
---------- ---------- --------- ----------
1 student1 01-JAN-04 10
2 student2 01-FEB-04 10
3 student3 01-MAR-04 20
4 student4 01-MAY-04 30
2 count(decode(to_char(hirdate,'MM'),'02',1)) "FEB",
3 count(decode(to_char(hirdate,'MM'),'03',1)) "MAR",
4 count(*) "Total"
5 from ddd
6 where to_char(hirdate,'MM') >= '01' AND
7 to_char(hirdate,'MM') <= '06';
---------- ---------- ---------- ----------
1 1 1 4
4-4) DEPTH 함수
--------------------------------------------------------------------------------
이 함수는 UNDER_PATH 조건에 상관관계의 수치를 반환한다.
SQL> select * from resource_view;
SQL> select path(1), depth(2)
2 from resource_view
3 where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1
4 and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;
------------------------------------------ ----------
/xml.xsd 1
--------------------------------------------------------------------------------
【형식】
DUMP(expr [,반환형식[,시작위치[,길이]]] )
SQL> select dump('Corea', 1016) from dual;
-----------------------------------------------------
Typ=96 Len=5 CharacterSet=KO16KSC5601: 43,6f,72,65,61
---------------------
Typ=96 Len=5: 162,145
-------------------
Typ=96 Len=5: 72,65
--------------------------------------------------------------------------------
또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.
UPDATE print_media SET ad_photo = EMPTY_BLOB();
--------------------------------------------------------------------------------
또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.
UPDATE print_media SET ad_photo = EMPTY_CLOB();
--------------------------------------------------------------------------------
0은 노드가 남아 있지 않은 경우이고,
1은 아직 노드가 존재하는 경우이다.
EXISTSNODE(XMLType_instance, XPath_string [,namespace_string] )
SQL> select * from resource_view;
SQL> select res,any_path
2 from resource_view
3 where existsnode(res, 'xdbconfig.xml') =0;
SQL>
4-9) EXTRACT(XML) 함수
--------------------------------------------------------------------------------
EXTRACT(XMLType_instance, XPath_string [,namespace_string] )
SQL> select * from resource_view;
SQL> select extract(res,'xdbconfig.xml')
2 from resource_view;
26 rows selected.
SQL>
4-10) EXTRACTVALUE 함수
--------------------------------------------------------------------------------
EXTRACTVALUE(XMLType_instance, XPath_string [,namespace_string] )
SQL> select * from resource_view;
SQL> select extractvalue(res,'xdbconfig.xml')
2 from resource_view;
26 rows selected.
SQL>
--------------------------------------------------------------------------------
SQL> select greatest(20,10,30) from dual;
------------------
30
--------------------------------------------------------------------------------
SQL> select least(20,10,30) from dual;
------------------
10
--
aa
--------------------------------------------------------------------------------
SQL> select nls_charset_decl_len
2 (200, nls_charset_id('ja16eucfixed')) from dual
--------------------------------------------------------
100
4-14) NLS_CHARSET_ID 함수
--------------------------------------------------------------------------------
여기서 text는 서버에서 지원되는 CHAR_CS나 NCHAR_CS이다.
SQL> select nls_charset_id('ja16euc') from dual;
-------------------------
830
--------------------------------------------------------------------------------
SQL> select nls_charset_name(830) from dual;
-------
JA16EUC
--------
US7ASCII
4-16) NULLIF 함수
--------------------------------------------------------------------------------
expr1과 expr2를 비교하여
같으면 null을 반환하고,
같지 않으면 expr1을 반환한다.
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
SQL> select nullif('aa','AA') from dual;
--
aa
--
SQL>
4-17) NVL2 함수
--------------------------------------------------------------------------------
expr1이 null이 아니면 expr2를 반환하고,
expr1이 null이면 expr3을 반환한다.
SQL> select nvl2('','Corea','Korea') from dual;
-----
Korea
-----
Corea
4-18) PATH 함수
--------------------------------------------------------------------------------
자원의 관계경로를 반환한다.
SQL> select * from resource_view;
SQL> select path(1), depth(2)
2 from resource_view
3 where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1?
4 and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;
------------------------------------------ ----------
/xml.xsd 1
4-19) SYS_CONNECT_BY_PATH 함수
--------------------------------------------------------------------------------
column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.
SQL> select sys_connect_by_path(name, '/') from emp
2 start with name='jijoe'
3 connect by prior id=1101;
-----------------------------
/jijoe
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
--------------------------------------------------------------------------------
SYS_CONTEXT('namespace','parameter' [,length])
SQL> select sys_context('userenv','session_user') from dual;
-------------------------------------
JIJOE
-----------------------------
US
AUDITED_CURSORID AUTHENTICATION_DATA BG_JOB_ID
CLIENT_IDENTIFIER CLIENT_INFO CURRENT_SCHEMA
CURRENT_SCHEMAID CURRENT_SQL CURRENT_USER
CURRENT_USERID DB_DOMAIN DB_NAME
ENTRY_ID EXTERNAL_NAME FG_JOB_ID
GLOBAL_CONTEXT_MEMORY HOST INSTANCE
IP_ADDRESS ISDBA LANG
LANGUAGE NETWORK_PROTOCOL NLS_CALENDAR
NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_LANGUAGE
NLS_SORT NLS_TERRITORY OS_USER
PROXY_USER PROXY_USERID SESSION_USER
SESSION_USERID SESSIONID TERMINAL
4-21) SYS_DBURIGEN 함수
--------------------------------------------------------------------------------
SYS_DBURIGEN({column|attribute} [rowid],... [,'text()'])
SQL> select sys_dburigen(id,name) from emp
2 where name='jijoe';
------------------------------------------------------------------------
DBURITYPE('/PUBLIC/EMP/ROW[ID=''1104'']/NAME', NULL)
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
--------------------------------------------------------------------------------
UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.
SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;
--------------------------------------------------------------------------
SYS_EXTRACT_UTC(SYSTIMESTAMP)
--------------------------------------------------------------------------
06-AUG-04 02.41.39.258976 PM +09:00
06-AUG-04 05.41.39.258976 AM
SQL>
--------------------------------------------------------------------------------
SQL> select sys_guid() from dual;
--------------------------------
E0F6C6D5767C01ADE034080020B588F4
--------------------------------------------------------------------------------
SYS_XMLAGG( expr [fmt] )
SQL> select sys_xmlagg(sys_xmlgen(name)) from emp
2 where name like 'j%';
--------------------------------------------------------------------------
<ROWSET>
<NAME>jijoe</NAME>
</ROWSET>
SQL>
--------------------------------------------------------------------------------
SYS_XMLGEN( expr [fmt] )
SQL> select sys_xmlgen(name) from emp
2 where name like 'j%';
--------------------------------------------------------------------------
<NAME>jijoe</NAME>
--------------------------------------------------------------------------------
SQL> select uid from dual;
----------
93
--------------------------------------------------------------------------------
SQL> select user,uid from dual;
------------------------------ ----------
JIJOE 93
4-28) USERENV 함수
--------------------------------------------------------------------------------
CLIENT_INFO ENTRYID ISDBA LANG
LANGUAGE SESSIONID TERMINAL
SQL> select userenv('language') from dual;
----------------------------------------------------
AMERICAN_AMERICA.KO16KSC5601
--------------------------------------------------------------------------------
SQL> select name, vsize(name) from emp
2 where name like 'jijoe';
---------- -----------
jijoe 5
..........
NLS_LANG=AMERICAN_AMERICA.KO16KSC5601 ☜ 한글 문자셋으로 설정
export NLS_LANG
NLS_LANG=AMERICAN_AMERICA.UTF8 ☜ UNICODE로 설정
export NLS_LANG
$
테이블에서 저장된 데이터가 한글 문자셋인지 유니코드인지 식별하려면 다음과 같이 확인해 볼 수 있다.
9이면 unicode이고,
6이면 한글 문자셋으로 저장된 것임을 알 수 있다.
SQL> select * from test;
ID NAME
---------- ----------------------------------------
1113 아리랑
1112 쓰리랑
SQL> select name, vsize(name) from test;
NAME VSIZE(NAME)
---------------------------------------- -----------
아리랑 6
쓰리랑 6
SQL>
4-30) XMLAGG 함수
--------------------------------------------------------------------------------
XMLAGG( XMLType_instance [order_by_clause])
SQL> select xmlagg(xmlelement("name",e.name)) from emp e;
--------------------------------------------------------------------------
<name>Cho</name>
<name>Joe</name>
<name>kim</name>
<name>jijoe</name>
4-31) XMLCOLATTVAL 함수
--------------------------------------------------------------------------------
XMLCOLATTVAL( value_expr [AS c_alias],...)
SQL> select xmlcolattval(e.name,e.id,e.salary) from emp e;
--------------------------------------------------------------------------
<column name="NAME">Cho</column>
<column name="ID">1101</column>
<column name="S
<column name="ID">1102</column>
<column name="S
<column name="ID">1103</column>
<column name="S
<column name="ID">1104</column>
<column name=
SQL> select * from emp;
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
4-32) XMLCONCAT 함수
--------------------------------------------------------------------------------
SQL> select xmlconcat(
2 xmlelement("name",e.name),xmlelement("bonus",e.bonus))
3 from emp e;
--------------------------------------------------------------------------
<name>Cho</name>
<bonus>125</bonus>
<bonus>100</bonus>
<bonus>100</bonus>
<bonus>100</bonus>
--------------------------------------------------------------------------------
XMLFOREST( value_expr [AS c_alias],...)
SQL> select xmlelement("emp",
2 xmlforest(e.id, e.name, e.bonus)) from emp e;
--------------------------------------------------------------------------
<emp>
<ID>1101</ID>
<NAME>Cho</NAME>
<BONUS>125</BONUS>
</emp>
<ID>1102</ID>
<NAME>Joe</NAME>
<BONUS>100</BONUS>
</emp>
<ID>1103</ID>
<NAME>kim</NAME>
<BONUS>100</BONUS>
</emp>
<ID>1104</ID>
<NAME>jijoe</NAME>
<BONUS>100</BONUS>
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
--------------------------------------------------------------------------------
SQL> select xmlelement("name",e.name) from emp e
2 where name like 'j%';
--------------------------------------------------------------------------
<name>jijoe</name>
5-1) AVG* 함수
--------------------------------------------------------------------------------
aggregate 함수나
analytic 함수로 사용된다.
AVG( [DISTINCT | ALL] 컬럼명)
[ [OVER] (analytic 절)]
SQL> select * from emp;
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
-----------
240
【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
2 from emp;
-----------------------------------------
236.666667
236.666667
236.666667
250
SQL> select avg(salary) over(partition by bonus order by id
2 rows between 1 preceding and 1 following) as avg from emp;
----------
245
236.666667
235
250
5-2) CORR* CORR* 함수
--------------------------------------------------------------------------------
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]
SQL> select corr(avg(bonus),max(bonus))
2 from employees
3 group by dept_no;
---------------------------
1
--------------------------------------------------------------------------------
쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]
SQL> select count(*) from emp;
----------
4
----------------------
2
-----------------
4
2 over (order by salary)
3 from emp;
---------- ---------------------------
220 1
240 2
250 4
250 4
5-4) COVAR_POP 함수
--------------------------------------------------------------------------------
COVAR_POP(expr1, expr2 [ OVER (analytic 절)] )
SQL> select covar_pop(bonus,salary) from emp;
-----------------------
62.5
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
--------------------------------------------------------------------------------
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )
SQL> select covar_samp(bonus,salary) from emp;
------------------------
83.3333333
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
--------------------------------------------------------------------------------
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)
SQL> select cume_dist(230) within group
2 (order by salary ) from emp;
----------------------------------------
.4
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
5-7) DENSE_RANK 함수
--------------------------------------------------------------------------------
그룹 내에서 순위를 반환한다.
SQL> select * from employees;
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
2 (order by salary, bonus) "Dense Rank"
3 from employees;
----------
2
5-8) FIRST 함수
--------------------------------------------------------------------------------
【형식】
집합함수 KEEP (
DENSE_RANK FIRST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
---------- ----------
220 250
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
5-9) GROUP_ID 함수
--------------------------------------------------------------------------------
번호가 0부터 시작되므로 n번 복제되었으면 n-1의 번호가 출력된다.
SQL> select dept_no, group_id() from employees
2 group by dept_no;
---------- ----------
10 0
20 0
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
5-10) Grouping 함수
--------------------------------------------------------------------------------
grouping 함수에서 기술된 컬럼이 그룹핑시 사용되었는지 보여주는 함수이다.
특별히 연산의 기능은 없으며,
rollup이나 cube 연산 후 출력된 결과에 대한 사용자의 이해를 높이기 위해 사용된다.
즉, grouping 함수를 이용할 경우 출력되는 결과값 중 null 값이 있다면,
이 null 값이 rollup이나 cube 연산의 결과로 생성된 값인지,
원래 테이블상에 null 값으로 저장된 것인지 확인할 수 있다.
. grouping 함수에 사용된 인수는 group by 절에 기술된 값 중에 하나와 반드시 일치해야 한다.
. grouping 함수의 결과값으로 0 또는 1을 반환한다.
0은 해당인수로 쓰인 값이 rollup이나 cube 연산에 사용되었음을 나타나고,
1은 사용되지 않았음을 의미한다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명,...
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호
SQL> select grade,deptno,sum(salary),GROUPING(deptno)
2 from aaa
3 group by rollup(grade,deptno);
---------- ---------- ----------- ----------------
1 10 100 0
1 20 500 0
1 30 300 0
1 900 1
2 10 400 0
2 20 200 0
2 30 600 0
2 1200 1
2100 1
--------------------------------------------------------------------------------
SQL> select * from employees;
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
2 from employees
3 group by dept_no;
----------- --------------------
500 0
460 0
--------------------------------------------------------------------------------
【형식】
집합함수 KEEP (
DENSE_RANK LAST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
---------- ----------
220 250
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
5-13) MAX 함수
--------------------------------------------------------------------------------
MAX ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]
SQL> select max(salary) over (partition by dept_no)
2 from employees;
-----------------------------------
250
250
240
240
-----------
250
--------------------------------------------------------------------------------
MIN ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]
SQL> select min(salary) over (partition by dept_no)
2 from employees;
-----------------------------------
250
250
220
220
-----------
220
5-15) PERCENTILE_CONT 함수
--------------------------------------------------------------------------------
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
[OVER (query_partition_cluause)]
SQL> select dept_no,percentile_cont(0.5) within group
2 (order by salary DESC)
3 from employees GROUP BY dept_no;
---------- --------------------------------------------------
10 250
20 230
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
5-16) PERCENTILE_DISC 함수
--------------------------------------------------------------------------------
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])
[OVER (query_partition_cluause)]
SQL> select dept_no,percentile_disc(0.5) within group
2 (order by salary DESC)
3 from employees GROUP BY dept_no;
---------- --------------------------------------------------
10 250
20 240
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
5-17) PERCENT_RANK 함수
--------------------------------------------------------------------------------
PERCENT_RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
[NULLS {FIRST|LAST}],...)
또는
PERCENT_RANK() OVER( [query_partition_clause] order_by_clause)
SQL> select percent_rank(230,0.05) within group
2 (order by salary,bonus) from employees;
------------------------------------------------------
.25
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
5-18) RANK 함수
--------------------------------------------------------------------------------
RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]
[NULLS {FIRST|LAST}],...)
또는
RANK() OVER( [query_partition_clause] order_by_clause)
SQL> select rank(230,0.05) within group
2 (order by salary,bonus) from employees;
----------------------------------------------
2
SQL> select * from employees;
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
5-19) REGR_(linear regression) function* 함수
--------------------------------------------------------------------------------
사용되는 회귀함수는 자음 중 하나이다.
REGR_SLOPE REGR_INTERCEPT REGR_COUNT
REGR_R2 REGR_AVGX REGR_AVGY
REGR_SXX REGR_SYY REGR_SXY
REGR { REGR_SLOPE|REGR_INTERCEPT|REGR_COUNT|REGR_R2|REGR_AVGX|
REGR_AVGY|REGR_SXX|REGR_SYY|REGR_SXY}
(expr1,expr2) [OVER (analytic_clause)]
SQL> select regr_slope(salary,bonus) from employees
------------------------
.533333333
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
5-20) STDDEV 함수
--------------------------------------------------------------------------------
STDDEV [{DISTINCT|ALL}] (expr) [OVER (analytic_clause)]
SQL> select stddev(salary) from emp;
--------------
14.1421356
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
5-21) STDDEV_POP 함수
--------------------------------------------------------------------------------
STDDEV_POP (expr) [OVER (analytic_clause)]
SQL> select stddev_pop(salary) from emp;
------------------
12.2474487
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
5-22) STDDEV_SAMP 함수
--------------------------------------------------------------------------------
STDDEV_SAMP (expr) [OVER (analytic_clause)]
SQL> select stddev_samp (salary) from emp;
-------------------
14.1421356
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-23) SUM 함수
--------------------------------------------------------------------------------
SUM ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]
SQL> select sum(salary) from emp;
-----------
960
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
5-24) VAR_POP 함수
--------------------------------------------------------------------------------
VAR_POP (expr) [OVER (analytic_clause)]
SQL> select var_pop(salary) from emp;
---------------
150
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-25) VAR_SAMP 함수
--------------------------------------------------------------------------------
VAR_SAMP (expr) [OVER (analytic_clause)]
SQL> select var_samp(salary) from emp;
----------------
200
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
SQL>
5-26) VARIANCE 함수
--------------------------------------------------------------------------------
VARIANCE ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]
SQL> select variance(salary) from emp;
----------------
200
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
5-27) Grouping sets 함수
--------------------------------------------------------------------------------
grouping sets 함수 사용이 불가능한 이전 버전에서
복잡한 union all 연산자를 사용하여 기술하던 것을 간단하게
한 문장 안에서 해결할 수 있어 검색시 효율성이 증대 된다.
다시 말해서, grouping sets 함수를 사용하면,
group by ... union all을 사용한 것보다
SQL 문이 간단해지고 또한 실행시 성능이 빨라진다.
【형식】
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...
[GROUPING SETS (컬럼명,컬럼명, ...), ...]
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grouping sets(grade,deptno);
---------- ---------- -----------
1 900
2 1200
10 500
20 700
30 900
2 from aaa
3 group by grouping sets((grade,name),(deptno,name));
---------- ---------- -----------
1 100
1 300
1 500
2 200
2 400
2 600
10 100
20 200
30 300
10 400
20 500
30 600
SQL>
【예제】Union all을 사용한 경우
SQL> select grade,deptno,sum(salary)
2 from aaa
3 group by grade,deptno
4 union all
5 select grade,deptno,sum(salary)
6 from aaa
7 group by grade,deptno;
---------- ---------- -----------
1 10 100
1 20 500
1 30 300
2 10 400
2 20 200
2 30 600
1 10 100
1 20 500
1 30 300
2 10 400
2 20 200
2 30 600
표현되는 각 컬럼이 하나가 아닌 복수 개의 컬럼으로 정의되는 경우이며
다음 표를 보고 이해하자.
composite column 문의 경우 group by 문의 경우 group by grouping sets(a,b,c) group by a union allgroup by b union allgroup by c group by grouping sets(a,b,(b,c)) group by a union allgroup by b union allgroup by b,c group by grouping sets((a,b,c)) group by a,b,c group by grouping sets(a,(b),()) group by a union allgroup by b union allgroup by () group by grouping sets(a,rollup(b,c)) group by a union allgroup by rollup(b,c) group by rollup(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a) union allgroup by () group by cube(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a,c) union allgroup by (b,c) union allgroup by (a) union allgroup by (b) union allgroup by (c) union allgroup by ()
6-1) AVG* 함수
--------------------------------------------------------------------------------
aggregate 함수나
analytic 함수로 사용된다.
AVG( [DISTINCT | ALL] 컬럼명)
[ [OVER] (analytic 절)]
SQL> select * from emp;
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
-----------
240
【예제】analytic 예
SQL> select avg(distinct salary) over(partition by bonus)
2 from emp;
-----------------------------------------
236.666667
236.666667
236.666667
250
SQL> select avg(salary) over(partition by bonus order by id
2 rows between 1 preceding and 1 following) as avg from emp;
----------
245
236.666667
235
250
6-2) CORR* CORR* 함수
--------------------------------------------------------------------------------
CORR( expr1, expr2 ) [ [OVER] (analytic 절)]
SQL> select corr(avg(bonus),max(bonus))
2 from employees
3 group by dept_no;
---------------------------
1
--------------------------------------------------------------------------------
쿼리한 행의 수를 반환한다.
【형식】
COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]
SQL> select count(*) from emp;
----------
4
----------------------
2
-----------------
4
2 over (order by salary)
3 from emp;
---------- ---------------------------
220 1
240 2
250 4
250 4
6-4) COVAR_SAMP 함수
--------------------------------------------------------------------------------
COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )
SQL> select covar_samp(bonus,salary) from emp;
------------------------
83.3333333
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
6-5) CUME_DIST 함수
--------------------------------------------------------------------------------
CUME_DIST(expr,... WITHIN GROUP (ORDER BY
expr [DESC | ASC] [NULLS {FIRST|LAST}],...)
또는
CUME_DIST() over ([query_partition_clause] order_by_clause)
SQL> select cume_dist(230) within group
2 (order by salary ) from emp;
----------------------------------------
.4
---------- ---------- ---------- ----------
1101 Cho 250 125
1102 Joe 240 100
1103 kim 250 100
1104 jijoe 220 100
--------------------------------------------------------------------------------
그룹 내에서 순위를 반환한다.
SQL> select * from employees;
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
2 (order by salary, bonus) "Dense Rank"
3 from employees;
----------
2
--------------------------------------------------------------------------------
【형식】
집합함수 KEEP (
DENSE_RANK FIRST ORDER BY
expr [DESC|ASC][NULL{FIRST|LAST}],...)
【예제】
SQL> select
2 min(salary) keep (dense_rank first order by salary) "Worst",
3 max(salary) keep (dense_rank last order by salary) "Best"
4 from employees
5 order by id;
---------- ----------
220 250
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
--------------------------------------------------------------------------------
FIRST_VALUE ( expr ) OVER ( analytic_절)
SQL> select salary,first_value(name)
2 over (order by salary asc)
3 from (select * from employees
4 where dept_no = 20
5 order by salary);
---------- ----------
220 jijoe
240 jijoe
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
--------------------------------------------------------------------------------
self join하지 않고 하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.
LAG ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )
SQL> select name,salary,LAG(salary,1,0)
2 OVER (ORDER BY salary) FROM employees;
---------- ---------- ----------------------------------
jijoe 220 0
Joe 240 220
Cho 250 240
kim 250 250
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
6-10) LAST_VALUE 함수
--------------------------------------------------------------------------------
LAST_VALUE ( expr ) OVER ( analytic_절)
SQL> select salary,last_value(name)
2 over (order by salary asc)
3 from (select * from employees
4 where dept_no = 20
5 order by salary);
---------- ----------
220 jijoe
240 Joe
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
6-11) LEAD 함수
--------------------------------------------------------------------------------
하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.
LEAD ( value_expr [,offset] [,default] )
OVER ([query_partition_clause] order_by_clause )
SQL> select name,salary,LEAD(salary,1,0)
2 OVER (ORDER BY salary) FROM employees;
---------- ---------- -----------------------------------
jijoe 220 240
Joe 240 250
Cho 250 250
kim 250 0
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
--------------------------------------------------------------------------------
NTILE ( expr ) OVER ([query_partition_clause] order_by_clause )
SQL> select name,salary,NTILE(3) OVER (ORDER BY salary DESC)
2 FROM employees;
---------- ---------- -------------------------------
Cho 250 1
kim 250 1
Joe 240 2
jijoe 220 3
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
6-13) RATIO_TO_REPORT 함수
--------------------------------------------------------------------------------
RATIO_TO_REPORT ( expr ) OVER ([query_partition_clause])
SQL> select name,salary,RATIO_TO_REPORT(salary) OVER ()
2 FROM employees;
---------- ---------- -----------------------------
Cho 250 .260416667
Joe 240 .25
kim 250 .260416667
jijoe 220 .229166667
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
6-14) ROW_NUMBER 함수
--------------------------------------------------------------------------------
ROW_NUMBER () OVER ([query_partition_clause] order_by_clause )
SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary DESC),name
2 FROM employees;
----------------------------------- ----------
1 Cho
2 kim
3 Joe
4 jijoe
---------- ---------- ---------- ---------- ----------
1101 10 Cho 250 125
1102 20 Joe 240 100
1103 10 kim 250 100
1104 20 jijoe 220 100
--------------------------------------------------------------------------------
테이블의 어떤 컬럼이 독립된 다른 객체 테이블을 참조하는 데이터 타입을 의미한다.
일반적으로 테이블을 만들때 사용하는 외부키(foreign-key)를 이용한 참조관계와 유사하다.
REF 타입의 컬럼 데이터를 읽을 때는 반드시 DEREF 함수를 사용한다.
REF 타입으로 정의된 컬럼에는 실제 데이터가 저장되는 것이 아니고 참조되는 객체가 존재하는 포인트정보만 가지고 있기 때문에 객체가 삭제 된다면, 해당 컬러은 정상적으로 검색되지 못한다.
이러한 현상을 REF의 Dangling 현상이고 한다.
이러한 dangling 현상을 방지하기 위해서는 삭제된 객체 정보를 참조하는 행의 컬럼 정보를 analyze 명령어를 이용하여 null 값으로 변경해 주어야 한다.
【예제】
SQL> connect jijoe/jijoe_password
connected
2 first_name varchar2(10),
3 last_name varchar2(10),
4 phone varchar(12),
5 birthday varchar2(12));
6 /
2 empno number,
3 emp person_type);
4 /
2 oidindex emp_oid;
2 emp_type(1000,person_type('junik','joe','123-1234','20-jul-04')));
2 empno number(4),
3 ename varchar2(15),
4 mgr REF emp_type SCOPE IS emp2);
2 select empno, 'SCOTT', REF(e)
3 from emp2 e
4 where empno=1000;
【예제】
SQL> select ename,empno from dept;
--------------- ----------
SCOTT 1000
--------------------------------------------------------------------------------
DEREF(MGR)(EMPNO, EMP(FIRST_NAME, LAST_NAME, PHONE, BIRTHDAY))
--------------------------------------------------------------------------------
0000220208DFA05B27A63701D9E034080020B588F4DFA05B27A63601D9E034080020B588F4
EMP_TYPE(1000, PERSON_TYPE('junik', 'joe', '123-1234', '20-jul-04'))
SQL>
SQL> select empno,ename,mgr
2 from dept
3 where mgr is dangling;
--------------------------------------------------------------------------------
ROWID, ROWNUM등이 있다.
ROWID는 데이터베이스에서 컬럼이 위치한 장소이다.
SQL> select rowid from test;
------------------
AAAHbHAABAAAMXCAAA
2 where rowidtochar(rowid) like '%AABAA%';
------------------
AAAHbHAABAAAMXCAAA
2 from test;
---------------------------- ------------------
36 AAAHbHAABAAAMXCAAA
여기서 rowid의 의미는 다음과 같다.
AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호
8-2) ROWNUM 컬럼
--------------------------------------------------------------------------------
ROWID, ROWNUM등이 있다.
ROWNUM은 테이블에서 select 되어진 일련 번호임
【예제】
SQL> select rownum,ename from emp;
ROWNUM ENAME
---------- ----------
1 CLARK
2 MILLER
3 JONES
4 ALLEN
5 MARTIN
6 CHAN
6 rows selected.
SQL> delete from emp where ename='JONES';
1 row deleted.
SQL> select rownum,ename from emp;
ROWNUM ENAME
---------- ----------
1 CLARK
2 MILLER
3 ALLEN
4 MARTIN
5 CHAN
SQL>
'컴퓨터 > DB' 카테고리의 다른 글
데이터베이스의 DDL, DML, DCL (0) | 2009.01.15 |
---|---|
페이징 방법 중 하나.. (0) | 2009.01.15 |
오라클 유저생성 및 암호나 sid를 모를때 접속하는 법 (0) | 2009.01.15 |
오라클 오류메시지 (0) | 2009.01.15 |
데이터베이스 스키마 (0) | 2009.01.15 |