2013년 1월 31일 목요일

[펌]오라클 날짜 계산

출처 : http://www.zetswing.com/bbs/board.php?bo_table=ORACLE_TIP&wr_id=20&page=

1. Oracle에서의 날짜 특징

＊oracle은 세기,년,월,일,시간,분,초의 내부숫자 형식으로 날짜를 저장합니다.
＊디폴트 날짜형식은 'DD-MON-YY' 입니다.
＊SYSDATE는 현재의 날짜와 시간을 리턴하는 함수입니다.(date타입)
ex : 2007-01-07 오후 10:34:00
＊DUAL은 SYSDATE를 보기위해 사용된 dummy table입니다.

2.oracle에서의 날짜연산

* 날짜에서 숫자(날수)를 빼거나 더하여 날짜 결과를 리턴합니다. 결과는 날짜형식
* 날짜 사이의 일수를 알기 위하여 2개의 날짜를 뺍니다.
* 시간을 24로 나누어 날짜에 더합니다.
날짜 + 숫자 : 날짜 특정한 날로부터 몇일 후의 날짜 계산
날짜 - 숫자 : 날짜 특정한 날로부터 몇일 전의 날짜 계산
날짜 - 날짜 : 숫자 두 날짜 사이의 차이를 숫자로 계산 

3.oracle에서의 날짜 컬럼데이타형

date 형

4. 월과 일을 문자로 출력시 한글로 나오는거 영문으로 나오게 하기

오라클 환경 설정에 따라 아래 쿼리를 실행시키면 "7월" 이라고 나올수 있다.
SELECT TO_CHAR(SYSDATE,'mon') FROM DUAL;

오라클 환경 설정에 따라 아래 쿼리를 실행시키면 "월요일" 이라고 나올수 있다.
SELECT TO_CHAR(sysdate,'day') FROM DUAL;
영문("Jul")으로 출력시키려면 아래 명령으로 환경설정을 변경한다.
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';

※ 월요일, 화요일 형식이 아닌 월, 화 형식으로 나타내기
SELECT TO_CHAR(sysdate,'day') FROM DUAL;

5.날짜의 순서결과 데이타형

날짜 - 날짜 = 숫자
숫자 + 날짜 = 날짜
(날짜 - 날짜) + 날짜 = 날짜
날짜 + 날짜 = error

※ trunc함수를 날짜데이타에 사용하기

select sysdate from dual;
--2006-02-08 오전 12:11:05

select trunc(sysdate) from dual;
select trunc(sysdate,'dd') from dual;
--단지 시간을 없애고 날짜만 나오게 한다.
--2006-02-08

select trunc(sysdate,'d') from dual;
--시간을 없애고 일을 가장최근에 지난 일요일 일로 초기화합니다.(권장)
--2006-02-05

select trunc(sysdate,'d')+1  from dual;
--시간을 없애고 일을 가장최근에 지난 월요일 일로 초기화합니다.
select trunc(sysdate,'d')-1  from dual;
--시간을 없애고 일을 가장최근에 지난 토요일 일로 초기화합니다.

select trunc(sysdate,'ww') from dual;
--시간을 없애고 일을 가장최근에 지난 일요일 일로 초기화합니다.
--2006-02-05

select trunc(sysdate,'mm') from dual;
--시간을 없애고 일을 1로 초기화합니다.
--2006-02-01

select trunc(sysdate,'Y') from dual;
select trunc(sysdate,'YY') from dual;
select trunc(sysdate,'YYY') from dual;
select trunc(sysdate,'YYYY') from dual;
--시간을 없애고 년도는 올해 년도와 월과 일을 모두 1 로 변경하여 출력합니다.
ex. 2006-01-01

SELECT TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR('20070715') FROM DUAL;
-- 현재 날짜를 YYYYMMDD 형식으로 출력한다.(자주사용)

8자리일자와 6자리시간을 문자열로 출력
select
to_char(sysdate, 'yyyymmdd') ,
to_char(sysdate, 'hh24miss')
from dual

6.날짜 관련 쿼리 예제

해당일이 그달의 몇째주인지 알아내기(w)
SELECT to_char(to_date('20061224', 'yyyymmdd'), 'w') FROM dual;

해당년도의 전체 일수 구하기
SELECT to_date('20001231', 'YYYYMMDD') - to_date('20000101', 'YYYYMMDD') from dual 
SELECT TO_CHAR (TO_DATE (:yyyy || '1231'), 'ddd') ilsu FROM DUAL
-- 위의 쿼리는 년도를 변수로 사용하였다.

UPDATE tab1 SET logdate = sysdate, ismodify = 1 WHERE logdate < sysdate-7
--기록된 날짜(LOGDATE)가 현재날짜(SYSDATE)로부터 일주일이 지났으면
--SYSDATE를LOGDATE에 쓰고 날짜가 바뀌었다는 기록을 남기는(ISMODYFY = 1) 쿼리

UPDATE tab1 SET logdate = sysdate, ismodify = 1 WHERE logdate < TRUNC(sysdate,'d')
기록된 날짜(LOGDATE)가 일요일이 한번이라도 지났다면, 즉 이번주 일요일부터 토요일간의 기록이라면 그대로 두고 그 이상 오래된 경우 현재날짜(SYSDATE)를 LOGDATE에 남기는 쿼리

select ename,job,hiredate from emp where hiredate between '1981-02-20' and '1981-05-01';
--1981년02월20일부터 1985년05월01일까지의 레코드를 검색한다.(꼭옛날날짜에서최근날짜로검색)

select ename,(sysdate - hiredate)/7 week from emp;
--sysdate함수로 현재 날짜시간에서 입사날짜(hiredate)를 빼면 일수가나오고 거기서 7을 나누어
--근무한 주수를 알수있습니다.

select * from emp where hiredate='1980/12/17';
--날짜 비교는 ''을 이용하여 비교합니다.

select months_between(sysdate,hiredate)/12 ,hiredate from emp;
--오늘날짜에서 입사날짜를 빼서 달수를 구한후 12을 나누어 근무한 년수를 구할수있다.

select months_between(to_date(20011129,'yyyymmdd'),to_date(20020228,'yyyymmdd')) from dual;
--첫번째 날짜에서 두번째 날짜를 빼서 달수로 구한다.

select round(months_between(sysdate,hiredate)/12) ,hiredate from emp;
--소수점이 있는 결과에서 반올림합니다.
select trunc(months_between(sysdate,hiredate)/12) ,hiredate from emp;
--소수점이 있는 결과에서 버림합니다.

ADD_MONTHS 함수예제

SELECT ADD_MONTHS(HIREDATE,2) FROM EMP;
-- HIREDATE값에 2달를 더하여 출력

SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-1), 'YYYYMMDD'),
TO_CHAR(SYSDATE-30, 'HH24MIDD') FROM DUAL;
-- DATE형 현재 날짜시간에서 1달을 뺀후 출력

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20060907230000','YYYYMMDDHH24MISS'),
-1),'YYYYMMDDHH24MI') FROM DUAL;
-- CHAR형 현재 날짜시간에서 1달을 뺀후 출력

select add_months(to_date('200706'||'01','yyyymmdd'),-1) from dual
-- 20070601에서 한달을 뺍니다.

select add_months(hiredate,-2) from emp;
--입사날짜에서 2달을 빼서 출력합니다.

select hiredate+100 from emp;
--입사날짜에서 100일을 더합니다.

select hiredate-100 from emp;
--입사날짜에서 100일을 뺍니다.

LAST_DAY() 함수
해당 날짜에 달에 마지막 일의 날짜를 출력한다.
사용예제
SELECT LAST_DAY('2006-05-05') FROM DUAL;
--2006-05-31
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--2006-05-31 오후 10:35:51

※oracle에서는 날짜함수에(sysdate) 산술연산이 가능합니다.
1일->1
1시간->1/24
1분->1/24/60
1초->1/24/60/60

select sysdate-1 from dual;
--지금 시간 기준으로 1일전

select sysdate-(1/24) from dual;
--지금 시간 기준으로 1시간전

select sysdate+1/24/60*1 from dual;
--지금 시간 기주으로 1분전

select sysdate+1/24/60*10 from dual;
--지금 시간 기주으로 10분전

select to_date(200611210800,'yyyymmdd hh24miss')+ 10/24 from duaL;
--10시간을 더한다.

select to_char(to_date('2005-05-05'),'d') from account;
--날짜를 숫자형식의 요일로 출력(1-일요일,7-토요일)
select to_char(to_date('2005-05-05'),'day') from account;
--날짜를 알파벳요일로 출력
select to_char(to_date('2005-05-05'),'year') from account;
--날짜를 알파벳년도로 출력

select to_char(to_date('2005-05-05'),'month') from account;
-- 월을 영문으로 완벽하게 출력

select to_char(to_date('2005-05-05'),'mon') from account;
-- 월을 영문 앞 3글자만 출력

select  decode(to_char(to_date('2005-05-05'),'d'),
              '2','1',
              '3','2',
              '4','3',
              '5','4',
              '6','5',
              '7','6',
              '1','7') "요일"
from   dual;
--날짜의 요일을 숫자로 출력(1-월요일,7-일요일)

DATE형 컬럼 비교시

SELECT * FROM TABLE_NAME WHERE FDATE < to_date('20070711','YYYYMMDD')

6. 프로그래밍 언어에서 날짜 검색시 방법

날짜 관련 컬럼은 DATE, CHAR(8), NCHAR(8)을 주지만 DATE는 7바이트이다.

DATE형은 아래와 같이 검색 조건을 사용한다.

WHERE A_DATE BETWEEN '2005-10-10' AND '2005-10-30';
WHERE A_DATE BETWEEN TO_DATE('2005-10-10') AND TO_DATE('2005-10-30');

CHAR(8), NCHAR(8)형은 아래와 같이 검색조건을 사용한다.

WHERE A_DATE BETWEEN '20051010' AND '20051030';

두가지의 장단점을 알아보자

7. 해당 시간이 현재 24시간이 지났는지 알수 있는 쿼리

SELECT CASE WHEN SYSDATE - TO_DATE('20070727','YYYYMMDD') >= 1
THEN 'Y' ELSE 'N' END RESUAL FROM DUAL;
※ SYSDATE가 날짜형이므로 빼주는 값도 날짜형이어야 합니다.

SELECT round(to_date('95/05/25'),'DAY')
FROM dual
1995/05/28 00:00:00
SELECT TRUNC(TO_DATE('95/05/25'), 'DAY')
FROM dual
1995/05/21 00:00:00

문제는 day 함수에 있습니다.
day함수는 요일을 나타내죠.
따라서 to_date('95/05/25')를 day로 표시하면 수요일이 나옵니다.
위에 쿼리는 그걸 반올림하였으니 그 주에 가장 큰 28일이 나왔구요,
아래 쿼리는 그걸 잘라내버렸으니 그 주에 가장 작은 21일이 나온 겁니다.

SELECT SYSDATE +  2/24 FROM DUAL;
-- 현재시간의 2시간후에 시간을 출력
SELECT SYSDATE -  2/24 FROM DUAL;
-- 현재시간의 2시간전의 시간을 출력

select  to_char(trunc(sysdate), 'rrrr/mm/dd') A from dual;
select  to_char(trunc(sysdate), 'yyyy/mm/dd') A from dual;
YYYY포맺은 현재를 기준으로 합니다.
RRRR기준은 .년도의 뒷자리를 기준으로
2000년도 기준으로 보면
0-49 년은 after year 35/12/12 ->2055/12/12
50-99 년은 before year 51/12/12 ->1951/12/12
가 됨니다.

8. 날짜 관련 함수

SYSDATE 함수
? 현재 시스템 날짜를 출력
SELECT SYSDATE FROM DUAL;
-- ORACLE 10g XE 에서 출력되는 날짜형식
-- 2008-05-17 오후 5:15:17

LAST_DAY 함수
? 해당 날짜의 달에서 마지막 일을 출력
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--2008-05-31 오후 5:16:54
작성자: 시간: 댓글 없음:

[펌] 오라클 데이터 형 변환 함수

출처 : http://pengs.pe.kr/174


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 함수

3-1) ASCIISTR 함수
--------------------------------------------------------------------------------
asciistr('string')의 string의 아스키 문자로 반환한다.
A
【예제】
SQL> select ascii('ABACDE') from dual;
☜ ABACDE의 두번째 A는 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('308') ) 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('abc0e50f10f6') from dual;
UNISTR
------
abc??o
SQL> select unistr('Corea') from dual;
UNIST
-----
Corea
SQL>
작성자: 시간: 댓글 없음:

[펌] 오라클 10g 함수

출처 : http://www.statwith.pe.kr/ORACLE/functions001.htm#F07 

위 사이트 주인 '백승민' 2006년 작성


SQL Functions

1. 수치함수
2. 문자값을 반환하는 문자 함수
3. NLS 문자 함수
4. 수치값을 반환하는 문자함수
5. 일시 함수
6. 일반적인 비교 함수
7. 변환 함수
8. LARGE OBJECT(LOB) 함수
9. 수집 함수
10. 계층 함수
11. XML 함수
12. 인코딩 함수와 디코딩 함수
13. NULL 함수
14. 환경 함수 와 식별자 함수
15. 집계 함수
16. 분석 함수
17. Object 참조 함수
18. 모델 함수
19. 기타 단일행 함수

단일행 함수

단일행 함수는 쿼리 테이블 또는 뷰의 모든 행에 대하여 단일 결과 행을 반환한다. 이 함수는 select lists,where 구문,START WITH, CONNECT BY 구문, HAVING구문을 지정할수 있다.
1. 수치함수

함수명 설명
002.ABS  절대값을 반환한다.
003.ACOS
n의 역코사인(arc cosine)값을 반환한다.
007.ASIN  n의 역사인(arc sine)값을 반환한다.
008.ATAN  n의 역탄젠트(arc tangent)값을 반환한다.
009.ATAN2  ATAN2(n,m)은 atan2(n/m)과 같으며, n/m의 역탄젠트(arc tangent)값을 반환한다.
013.BITAND  인수1과 인수2의 비트에 대한 AND연산을 수행하여 정수를 반환한다.
016.CEIL  인수에서 지정한 수치를 올림하여 정수를 구하는 함수이다.
026.COS  n(라디안으로 표현되는 각도)의 코사인값을 반환한다.
027.COSH
n(라디안으로 표현되는 각도)의 쌍곡 코사인값(hyperbolic cosine)을 반환한다
044.EXP  e의 n 제곱 값을 반환한다.
050.FLOOR  지정한 숫자보다 작거나 같은 정수 중에서 최대값을 반환한다
067.LN  입력값의 자연 로그 값을 반환한다.
070.LOG  LOG(m,n)에서 밑을 m으로 한 n의 로그 값을 반환.
078.MOD  n2을 n1으로 나눈 나머지값을 반환.
080.NANVL  입력 값 n2가 Nan(숫치가 아닌)라면, 대체 값 n1을 반환. n2가 NaN이 아니라면, n2를 반환
102.POWER  n2의 n1승 값을 반환.
118.REMAINDER  n2를 n1으로 나눈 나머지를 반환
120.ROUND (number)  n값을 소수점 이하를 integer를 기준으로 반올림하여 반환한다
130.SIGN  n의 부호를 반환.
131.SIN  n의 사인(sine)값을 반환.
132.SINH  n의 쌍곡선 사인(hyperbolic sine)을 반환.
134.SQRT  n의 제곱근을 반환.
159.TAN  n의 사인(tangent)값을 반환.
160.TANH  n의 쌍곡선 탄젠트(hyperbolic tangent)을 반환.
185.TRUNC (number)  인수 n1을 소수점 자리 파라미터 n2 이하를 절삭.
199.WIDTH_BUCKET  동일한 넓이를 갖는 히스토그램을 생성.


2. 문자값을 반환하는 문자 함수


함수명 설명
018.CHR  10진수 n 에 대응하는 아스키코드를 반환.
022.CONCAT
char1과 char2를 연결하여 반환한다
057.INITCAP  입력 문자열 중에서 각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환하여 반환한다
071.LOWER  입력된 문자열을 소문자로 변환한다
072.LPAD  지정된 자리수 n으로부터 expr1을 채우고,왼편의 남은 공간에 expr1을 채운다.
073.LTRIM  문자열 char 좌측으로부터 set으로 지정된 모든 문자를 제거한다.
081.NCHR  유니코드 문자를 반환.
087.NLS_INITCAP  각 단어의 처음 문자를 대문자로, 나머지 문자를 소문자로 변환하여 char를 반환한다
088.NLS_LOWER  모든 문자를 소문자로 변환하여 반환한다.
089.NLSSORT
입력 문자열을 소팅하여 스트링을 반환한다.
090.NLS_UPPER  입력 문자열을 모두 대문자로 변환한 문자열을 반환한다.
115.REGEXP_REPLACE   지정한 정규 표현을 만족하는 부분을, 지정한 다른 문자열로 치환합니다.
116.REGEXP_SUBSTR   지정한 정규 표현을 만족하는 부분 문자열을 반환.
119.REPLACE   파라미터로 주어지는 첫번째 문자열에서, 두번째 문자열을 모두 세번째 문자열로 바꾼 후 결과를 반환한다.
125.RPAD  인수 expr1 오른편으로 인수 expr2로 지정한 문자를 길이 필요에 따라 반복하여 n만큼 붙여준다
126.RTRIM  인수 char의 오른쪽 끝에서 부터 set으로 지정된 모든 문자를 제거한다.
133.SOUNDEX  char의 음성 표현을 가지는 문자열을 반환.
147.SUBSTR  문자열 Char에서 position 문자 위치로부터 substring_length 문자 길이만큼 문자열을 추출하여 반환.
181.TRANSLATE  from_string에서 각 문자를 to_string안의 대응하는 문자로 치환하여 expr을 반환.
183.TREAT  인수의 선언형을 변경.
184.TRIM  문자열부터 선행 또는 후행(양쪽)문자를 제거.
191.UPPER  모든 문자를 대문자로 변환.

3. NLS 문자 함수

함수명 설명
084.NLS_CHARSET_DECL_LEN  NCHAR열의 선언된 폭을 반환.
085.NLS_CHARSET_ID  문제셋 이름에 상응하는 ID번호를 반환.
086.NLS_CHARSET_NAME  ID번호 number에 상응하는 문자 세트의 이름을 반환.

4. 수치값을 반환하는 문자함수

함수명 설명
005.ASCII  주어진 char의 첫 문자의 아스키 값에 상응하는 10진수값을 반환한다.
058.INSTR  문자열중에서 지정한 문자가 처음 나타나는 위치를 숫자로 반환.
066.LENGTH  인수 char의 길이를 반환한다
114.REGEXP_INSTR   지정한 조건(정규 표현)을 만족하는 부분의 최초의 위치(무슨 문자인지)를 반환.


5. 일시 함수



함수명 설명
004.ADD_MONTHS  일자 date에 특정 개월수 integer를 더한 값을 반환한다
032.CURRENT_DATE
현재 세션의 날짜 정보를 Date 데이터 형으로 반환한다.
033.CURRENT_TIMESTAMP  현재 session의 날짜와 시간 정보를 반환한다
035.DBTIMEZONE  데이터 베이스 time zone의 값을 반환한다
045.EXTRACT (datetime)  특정 날짜,시간 값이나 날짜 값 표현식으로부터 지정된 날짜 영역의 값을 추출하여 반환한다
051.FROM_TZ  timestamp 데이터형과 time zone데이터 형을 TIMESTAMP WITH TIME ZONE 데이터형으로 변환.
062.LAST_DAY  해당 날짜가 속한 달의 마지막 날짜를 반환한다.
069.LOCALTIMESTAMP  timestamp의 현재 날짜와 시각을 출력한다.
079.MONTHS_BETWEEN
일자 date1과 date2 사이의 월을 계산한다
082.NEW_TIME  date,zone1시간대를 zone2 시간대로 출력.
083.NEXT_DAY  해당일을 기준으로 명시된 요일의 다음 날짜를 변환.
093.NUMTODSINTERVAL  n을 INTERVAL DAY TO SECOND 문자로 변경한다.
094.NUMTOYMINTERVAL  n을 INTERVAL YEAR TO MONTH문자로 변경한다
121.ROUND (date)  포맷 모델 fmt에 의해 지정한 단위로 반올림된 날짜를 반환한다
128.SESSIONTIMEZONE  현재 세션의 시간대역(time zone)을 반영한다
152.SYS_EXTRACT_UTC  협정 세계시간 UTC (Coordinated Universal Time—formerly Greenwich Mean Time)을 반환
157.SYSDATE  데이터 베이스가 있는 OS의 일자와 시간을 반환한다
158.SYSTIMESTAMP  시스템의 날짜를 반환한다
165.TO_CHAR (datetime)   사용자가 지정한 폼을 갖는 varchar2 형식의 데이터로 변환한다
169.TO_DSINTERVAL  INTERVAR DAY TO SECOND값으로 변환한다.
178.TO_TIMESTAMP  TIMESTAMP 데이터형의 값으로 변환한다.
179.TO_TIMESTAMP_TZ  TIMESTAMP WITH TIME ZONE 데이터형으로 변환한다.
180.TO_YMINTERVAL  INTERVAL YEAR TO MONTH 형태로 변경한다
186.TRUNC (date)    날짜를 년,월,일을 기준으로 반올림하거나 절삭한다.
187.TZ_OFFSET  문장이 실행된 일자에 근거한 인수에 상응하는 time zone offset을 반환한다


6. 일반적인 비교 함수

함수명 설명
052.GREATEST  하나 이상의 인수중에서 가장 큰 값을 반환.
065.LEAST  인수 EXPR의 리스트 중에서 가장 작은 값을 반환.

7. 변환 함수

함수명 설명
006.ASCIISTR  주어진 문자열의 아스키 문자열을 반환.
012.BIN_TO_NUM
비트(2진수) 벡터를 동등한 수치(10진수)로 변환.
015.CAST    데이터 형식이나 collection 형식을 다른 데이터 형식이나 collection 형식으로변환.
017.CHARTOROWID  CHAR, VARCHAR2, NCHAR, or NVARCHAR2 데이터형태의 값으로부터 ROWID형으로 변환.
021.COMPOSE  완전한 정규화된 형태의 유니코드를 반환.
023.CONVERT  문자세트를 다른 문자세트로 문자열을 변환.
037.DECOMPOSE  입력과 같은 문자 세트로 분해후의 UNICODE 문자열을 반환.
056.HEXTORAW  16진수를 raw값으로 변환.
093.NUMTODSINTERVAL
n을 INTERVAL DAY TO SECOND 문자로 변경.
094.NUMTOYMINTERVAL  n을 INTERVAL YEAR TO MONTH문자로 변경한다.
110.RAWTOHEX  RAW을 16진수의 문자로 변환.
111.RAWTONHEX  RAW을 NVARCHAR2 형태의 16진수로 변환.
123.ROWIDTOCHAR  rowid 값을 VARCHAR2형식으로 변환.
124.ROWIDTONCHAR  rowid값을 NVARCHAR2형식으로 변환.
127.SCN_TO_TIMESTAMP  시스템 변경 번호(SCN)로 평가되는 수치를 인수로 취하여, SCN과 관련된 가까운 timestamp를 반환.
161.TIMESTAMP_TO_SCN  timestamp와 관련된 시스템 변경 번호(system change number,SCN)을 반환.
162.TO_BINARY_DOUBLE  배정밀도 부동소수점을 반환.
163.TO_BINARY_FLOAT  단순정밀도(single-precision) 부동 소수점수(floating-point number)를 반환.
164.TO_CHAR (character)  데이터 베이스 문자 세트로 변환.
165.TO_CHAR (datetime)  지정된 포맷의 VARCHAR2 데이터 타입의 값으로 변환.
166.TO_CHAR (number)  VARCHAR2 데이터형의 값으로 변환.
167.TO_CLOB  NCLOB값을 CLOB값으로 변환.
168.TO_DATE  char을 날짜형 데이터 타입값으로 변환.
169.TO_DSINTERVAL  INTERVAR DAY TO SECOND값으로 변환.
170.TO_LOB  LONG또는 LONG ROW값을 LOB값으로 변환.
171.TO_MULTI_BYTE  multibyte 문자를 상응하는 single-byte 문자로 변환한 문자를 반환.
172.TO_NCHAR (character)  문자열,CLOB,NCLOB 값을 각국 문자 세트로 변환.
173.TO_NCHAR (datetime)  national character set으로 변환.
174.TO_NCHAR (number)  n을 national character set으로 변환.
175.TO_NCLOB  CLOB값을 NCLOB값으로 변환.
176.TO_NUMBER  expr을 NUMBER 데이터형의 값으로 변환.
177.TO_SINGLE_BYTE  multibyte문자를 그에 상응하는 single-byte문자로 변환하여 char을 반환.
178.TO_TIMESTAMP  CHAR,VARCHAR2,NCHAR,NVARCHAR2 데이터형의 char을 TIMESTAMP 데이터형의 값으로 변환.
179.TO_TIMESTAMP_TZ  CHAR,VARCHAR2,NCHAR,NVARCHAR2데이터형의 char을 TIMESTAMP WITH TIME ZONE 데이터형으로 변환.
180.TO_YMINTERVAL  CHAR,VARCHAR2,NCHAR,NVARCHAR2 데이터형의 문자열을 INTERVAL YEAR TO MONTH 형태로 변경
182.TRANSLATE ... USING  char을 데이터 베이스 문자세트와 각국어 문자 센트사이의 변환에 대한 지정된 문자 세트로 변경.
189.UNISTR  텍스트 문자열을 인수로 취하고, 각국어 문자 세트로 반환.

8. LARGE OBJECT(LOB) 함수

함수명 설명
011.BFILENAME  서버 파일 시스템의 물리 LOB 바이너리 파일과 연관된 BFILE locator를 반환.
042.EMPTY_BLOB, EMPTY_CLOB  LOB 변수를 초기화하기 위하여 쓰이거나, 또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환.

9. 수집 함수

함수명 설명
014.CARDINALITY  nested table에서 원소의 수를 반환.
020.COLLECT  선택된 행으로부터 입력된 형태의 중첩 테이블을 생성.
103.POWERMULTISET  입력된 중첩(nested)테이블의 공백이 아닌 모든 부분집합(submultisets)을 소유한 중첩 테이블의 중첩된 테이블을 반환.
104.POWERMULTISET_BY_CARDINALITY  중첩 테이블과 cardinality(주어진 수학적 집합에서 요소들의 개수)를 취해서, 지정한 카디나리트의 중첩 테이블의 모든 비공백 부분집합(submultisets이라고 불리는)을 소유하는 중첩 테이블의 중첩테이블을 반환.
129.SET  중첩 테이블에서 중복을 배제하여 반환.

10. 계층 함수

함수명 설명
149.SYS_CONNECT_BY_PATH  루트로 부터 node로 열의 값 Path를 반환.

11. XML 함수

함수명 설명
039.DEPTH  상관 변수를 가지는 UNDER_PATH조건에 의해 지정된 PATH에서 레벨의 수를 반환.
043.EXISTSNODE  node의 존재여부를 확인하여 그 결과를 반환.
046.EXTRACT (XML)
XML 플래그먼트(fragment)를 포함한 XMLType 인스턴스를 반환.
047.EXTRACTVALUE  node의 스칼라 값을 반환.
098.PATH  지정된 자원에서 상대적인 경로를 반환.
151.SYS_DBURIGEN  특정 열 또는 행 오브젝트에 대한 DBURIType 데이터 타입의 URL을 생성.
155.SYS_XMLAGG  입력 받은 모든 문서를 하나의 XML문서를 통합.
156.SYS_XMLGEN  스칼라값,object type,xml type 인스턴스를 XML문서로 변형.
200.XMLAGG  XML fragment(조각)의 집합체를 취해서, 집계된 XML 문서를 반환.  GROUP BY 질의에서 XML 데이타를 그룹으로 분류 또는 집계하는 함수.
201.XMLCOLATTVAL  XML 단편(fragment)을 생성하고, 각각의 XML 단편(fragment)이 속성 name을 포함한 name열을 가지는 결과 XML으로 확장.
202.XMLCONCAT  둘 이상의 XML 값을 연결하는 함수.
203.XMLELEMENT  XMLType 타입의 instance를 반환.  관계형 값을 XML 요소로 변형시키는 함수.
204.XMLFOREST  각 인수의 파라미터를 XML로 변환하고, 변환된 인수를 연결한 XML 단편(fragment)을 반환. 관계형 값 목록으로부터 XML 요소의 목록(일명: '포리스트(forest)')을 생성하는 함수.
205.XMLSEQUENCE  XMLType에 있는 top-level 노드의 varray를 반환.  커서의 각 행에 대하여 XMLSequence 형태로써 XML문서를 반환.
206.XMLTRANSFORM  스타일 쉬트를 인스턴스로 적용하고, XMLType를 반환.

12. 인코딩 함수와 디코딩 함수

함수명 설명
036.DECODE  일반적인 프로그래밍 언어의 IF문을 SQL 문자 또는 PL/SQL안으로 끌여들여 사용하기 위하여 만들어진 오라클함수.
041.DUMP  지정한 데이터의 위치와 길이 등을 지정한 형식으로 반환.
097.ORA_HASH  주어진 표현에 대한 해쉬 값을 계산하는 함수.
198.VSIZE  expr의 내부 표현에서 바이트의 수를 반환.

13. NULL 함수

함수명 설명
019.COALESCE  나열된 값을 순차적으로 체크하여 NULL이 아닌 첫번째 인수를 반환.
068.LNNVL  조건의 한쪽 또는 양쪽 연산자가 NULL이 존재할 경우에, 조건문을 평가하기 위한 방법을 제공.
092.NULLIF  expr1과 expr2가 같으면, NULL값을 반환.
095.NVL  쿼리의 결과에서 NULL(공백으로 반환)값을 치환.
096.NVL2  지정한 표현이 NULL인지 여부에 근거하여 쿼리의 반환될 값을 판단할수 있다. expr1이 NULL이 아니라면, NVL2는 expr2를 반환한다. 만약 expr1인 NULL이라면, NVL2는 expr3을 반환.

14. 환경 함수 와 식별자 함수

함수명 설명
150.SYS_CONTEXT  문맥 namespace와 관련된 parameter의 값을 반환.
153.SYS_GUID  16바이트로 구성된 고유전역식별자(globally unique identifier,RAW 값)을 생성하여 반환.
154.SYS_TYPEID  피연산자(operand)의 대부분 지정한 형태의 typeid를 반환.
188.UID  세션 사용자의 유일한 식별하는 정수를 반환.(로그인 유저)
192.USER  VARCHAR2 형태를 가지는 세션 사용자(로그인 유저)의 이름을 반환.
193.USERENV  현재 세션에 대한 정보를 반환.


15. 집계 함수

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
Many (but not all) aggregate functions that take a single argument accept these clauses:
  • DISTINCT causes an aggregate function to consider only distinct values of the argument expression.
  • ALL causes an aggregate function to consider all values, including all duplicates.
For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL.
All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema hr:
SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;

AVG(MAX(SALARY))
----------------
           10925
This calculation evaluates the inner aggregate (MAX(salary)) for each group defined by the GROUP BY clause (department_id), and aggregates the results again.
The aggregate functions are:
함수명 설명
010.AVG  지정된 컬럼에 대한 조건을 만족하는 행중에서 Null을 제외한 평균을 반환.
020.COLLECT  선택된 행으로부터 입력된 형태의 중첩 테이블을 생성.
024.CORR  수치 쌍에 대한 상관 계수를 반환.
025.CORR_*  (CORR 참조)는 Pearson's 상관계수를 계산.
028.COUNT  쿼리에 의해 반환된 행의 수를 반환.
029.COVAR_POP  number조합의 세트의 모집단 공분산을 반환.
030.COVAR_SAMP  number쌍의 세트의 표본 공분산을 반환.
031.CUME_DIST  값의 그룹에 있는 값의 누적 분포치를 계산.
038.DENSE_RANK  ORDER BY절에 사용된 컬럼이나 표현식에 대하여 순위를 부여하는데 RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 증가된 값을 돌려준다
048.FIRST  주어진 소트 지정에 대해서 FIRST 또는 LAST로서 순위를 주어서 행의 세트로부터 값의 세트에 운영하는 집계와 분석 함수.
053.GROUP_ID  지정된 GROUP BY 결과로부터 중복된 그룹을 구별.
054.GROUPING  ROLLUP이나 CUBE 연산자와 함께 사용하여 GROUPING 함수에 기술된 컬럼이 그룹핑 시 즉, ROLLUP이나 CUBE 연산시 사용이 되었는지를 보여 주는 함수.
055.GROUPING_ID  행과 관련되는 GROUPING 비트 벡터에 대응되는 수치를 반환.
061.LAST  행을 서열화 시켜서 마지막 행을 추출.
075.MAX  인수중에서 최대값을 반환.
076.MEDIAN  중앙값 또는 값의 정렬후에 중앙값이 보간된 값을 반환.
077.MIN  인수중에서 최소값을 반환.
099.PERCENT_RANK  그룹 수에 대한 값의 순위 퍼센트를 반환.
100.PERCENTILE_CONT  연속된 분포 모델을 가정한 역 분포 함수(inverse distribution function).
101.PERCENTILE_DISC  이산 분포 모형을 가정하는 역 분포 함수.
108.RANK  값의 그룹에서 값의 순위를 계산.
117.REGR_ (Linear Regression) Functions  선형회귀함수는 정규 최소 제곱 회귀 선상을 수치 쌍의 세트에 적합.
135.STATS_BINOMIAL_TEST  단지 두개의 유효한 값이 존재하는 이분 변수(두개의 배타적인 값을 가지는 변수)에 대해서 이용되는 정확 확률 테스트.
136.STATS_CROSSTAB  교차분석(crosstab)은 두개의 명목 변수를 분석하는 방법.
137.STATS_F_TEST  STATS_F_TEST함수는 두개의 분산이 유의한 차가 있는지 테스트.
138.STATS_KS_TEST  두개의 표본이 같은 모집단에 속하고 있는지 또는 같은 분포를 가지는 모집단에 속하고 있는지 테스트 하는 Kolmogorov-Smirnov함수.
139.STATS_MODE  가장 큰 빈도를 가지는 값을 반환.
140.STATS_MW_TEST  A Mann Whitney test는 2개의 독립 표본을 비교.
141.STATS_ONE_WAY_ANOVA  일원분산분석 함수(STATS_ONE_WAY_ANOVA)는 분산의 다른 2개 추정치 비교에 의해 통계적 유의성에 대한 평균(그룹 또는 변수에 대한)의 유의한 차를 검증.
142.STATS_T_TEST_*   t검정에서는, 평균치의 차이의 유의성을 측정.
143.STATS_WSR_TEST  대응쌍표본의 윌콕스 부호 순위 검증을 수행하며,표본간의 차이가 zero로부터 유의한 차이가 있는지 검정.
144.STDDEV  Number의 조합인 expr의 표본표준편차를 반환.
145.STDDEV_POP  모집단 표준 편차를 계산하고, 모집단 분산의 제곱근값을 반환.
146.STDDEV_SAMP  누적 표본 표준편차를 계산하고, 표본 분산의 제곱근값을 반환.
148.SUM  expr의 값의 합을 반환.
195.VAR_POP  Null값들을 제거한후에 Number 세트의 모집단 분산을 반환.
196.VAR_SAMP  null들을 제거한후에 number의 세트의 표본분산을 반환.
197.VARIANCE  expr의 분산을 반환.


16. 분석 함수


Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

analytic_function::=
Description of analytic_function.gif follows
Description of the illustration analytic_function.gif


analytic_clause::=
Description of analytic_clause.gif follows
Description of the illustration analytic_clause.gif


query_partition_clause::=
Description of query_partition_clause.gif follows
Description of the illustration query_partition_clause.gif


order_by_clause::=
Description of order_by_clause.gif follows
Description of the illustration order_by_clause.gif


windowing_clause ::=
Description of windowing_clause.gif follows
Description of the illustration windowing_clause.gif

The semantics of this syntax are discussed in the sections that follow.

analytic_function
Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).

arguments
Analytic functions take 0 to 3 arguments. The arguments can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that datatype. The return type is also that datatype, unless otherwise noted for an individual function.

analytic_clause
Use OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
Notes on the analytic_clause:
  • You cannot specify any analytic function in any part of the analytic_clause. That is, you cannot nest analytic functions. However, you can specify an analytic function in a subquery and compute another analytic function over it.
  • You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION .

query_partition_clause
Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr. If you omit this clause, then the function treats all rows of the query result set as a single group.
To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses) or a partitioned outer join (in the outer_join_clause), use the lower branch of the syntax (with parentheses).
You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.
If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause, then the function computations are parallelized as well.
Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.

order_by_clause
Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions except PERCENTILE_CONT and PERCENTILE_DISC (which take only a single key), you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.
Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Whenever the order_by_clause results in identical values for multiple rows, the function returns the same result for each of those rows. Please refer to the analytic example for SUM for an illustration of this behavior.

Restriction on the ORDER BY Clause
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.

ASC | DESC
Specify the ordering sequence (ascending or descending). ASC is the default.

NULLS FIRST | NULLS LAST
Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.

windowing_clause
Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).

ROWS | RANGE
These keywords define for each row a window (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.
  • ROWS specifies the window in physical units (rows).
  • RANGE specifies the window as a logical offset.
You cannot specify this clause unless you have specified the order_by_clause.
The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.

BETWEEN ... AND
Use the BETWEEN ... AND clause to specify a start point and end point for the window. The first expression (before AND) defines the start point and the second expression (after AND) defines the end point.
If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.

UNBOUNDED PRECEDING
Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.

UNBOUNDED FOLLOWING
Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.

CURRENT ROW
As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the end point cannot be value_expr PRECEDING.
As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the start point cannot be value_expr FOLLOWING.

value_expr PRECEDING or value_expr FOLLOWING
For RANGE or ROW:
  • If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING.
  • If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING.
If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.
If you specified ROWS:
  • value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.
  • If value_expr is part of the start point, then it must evaluate to a row before the end point.
If you specified RANGE:
  • value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Please refer to "Literals " for information on interval literals.
  • You can specify only one expression in the order_by_clause
  • If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE datatype.
  • If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Analytic functions are commonly used in data warehousing environments. In the list of analytic functions that follows, functions followed by an asterisk (*) allow the full syntax, including the windowing_clause.
함수명 설명
010.AVG *  지정된 컬럼에 대한 조건을 만족하는 행중에서 Null을 제외한 평균을 반환.
024.CORR *  수치 쌍에 대한 상관 계수를 반환.
028.COUNT *  쿼리에 의해 반환된 행의 수를 반환.
029.COVAR_POP *  number조합의 세트의 모집단 공분산을 반환.
030.COVAR_SAMP *  number쌍의 세트의 표본 공분산을 반환.
031.CUME_DIST  값의 그룹에 있는 값의 누적 분포치를 계산.
038.DENSE_RANK  ORDER BY절에 사용된 컬럼이나 표현식에 대하여 순위를 부여하는데 RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 증가된 값을 돌려준다
048.FIRST  주어진 소트 지정에 대해서 FIRST 또는 LAST로서 순위를 주어서 행의 세트로부터 값의 세트에 운영하는 집계와 분석 함수.
049.FIRST_VALUE *  값의 정렬된 세트에서 첫번째 값을 반환.
060.LAG   현재 행을 기준으로 이전 값을 참조하는 함수.
061.LAST  행을 서열화 시켜서 마지막 행을 추출.
063.LAST_VALUE *  윈도우에서 정렬된 값중에서 마지막 값을 반환.
064.LEAD   현재 행을 기준으로 이후의 값을 참조하는 함수.
075.MAX *  인수중에서 최대값을 반환.
077.MIN *  인수중에서 최소값을 반환.
091.NTILE  순서화된 데이터를 expr에 의해 지정된 bucket의 수로 분한하여, 각 행을 적절한 bucket 번호를 할당. 출력 결과를 사용자가 지정한 그룹 수로 나누어 출력하는 함수.
099.PERCENT_RANK  그룹 수에 대한 값의 순위 퍼센트를 반환.
100.PERCENTILE_CONT  연속된 분포 모델을 가정한 역 분포 함수(inverse distribution function).
101.PERCENTILE_DISC  이산 분포 모형을 가정하는 역 분포 함수.
108.RANK  값의 그룹에서 값의 순위를 계산.
109.RATIO_TO_REPORT  값의 세트의 합에 대한 값의 비율을 계산.
117.REGR_ (Linear Regression) Functions *  선형회귀함수는 정규 최소 제곱 회귀 선상을 수치 쌍의 세트에 적합.
122.ROW_NUMBER   분할별로 정렬된 결과에 대해 순위를 부여하는 기능.  1로 시작하는 order_by_clause에서 지정된 행의 순위 순서로, 적용되는 각 행에 unique 순서를 할당.
144.STDDEV *  Number의 조합인 expr의 표본표준편차를 반환.
145.STDDEV_POP *  모집단 표준 편차를 계산하고, 모집단 분산의 제곱근값을 반환.
146.STDDEV_SAMP *  누적 표본 표준편차를 계산하고, 표본 분산의 제곱근값을 반환.
148.SUM *  expr의 값의 합을 반환.
195.VAR_POP *  Null값들을 제거한후에 Number 세트의 모집단 분산을 반환.
196.VAR_SAMP *  null들을 제거한후에 number의 세트의 표본분산을 반환.
197.VARIANCE *  expr의 분산을 반환.

17. Object 참조 함수

함수명 설명
040.DEREF  인수 expr의 오브젝트 참조를 반환.
074.MAKE_REF  object 인식자가 주 키로 근거하고 있는 object 테이블에서 object view의 행 또는 object 표의 행에 대한 REF를 생성.
112.REF  인수로써 오브젝트 테이블 또는 오브젝트 뷰의 행과 연관된 상관 변수(테이블 별명)를 취한다
113.REFTOHEX  인수 expr을 16진수로 변환.
194.VALUE  object 테이블에 저장된 object instance를 반환.

18. 모델 함수

함수명 설명
034.CV  포뮬러의 좌측 항에 정의된 multi-cell reference를 우측 항으로 복사하는 기능을 제공.  우측 항 계산을 위해 좌측 항의 값 이용하기.
059.ITERATION_NUMBER  델 규칙에 따라 완료된 반복을 나타내는 정수를 반환.
105.PRESENTNNV  cell_reference가 존재하고 NULL이 아닌 경우, model_clause이 실행되기 전에 expr1을 반환.
106.PRESENTV  cell_reference가 존재할때 expr1을 반환한다. 그 이외에는 expr2를 반환.
107.PREVIOUS  각 iteration의 초기에 cell_reference의 값을 반환.

19. 기타 단일행 함수

함수명 설명
152.SYS_EXTRACT_UTC  협정 세계시간 UTC (Coordinated Universal Time—formerly Greenwich Mean Time)을 추출.
190.UPDATEXML  XMLType인스턴스와 XPath값 쌍을 취하고, 업데이트된 값을 가지는 XMLType 인스턴스를 반환.
작성자: 시간: 댓글 없음:
피드 구독하기: 글 (Atom)