오라클 내장 함수 1

오라클 내장 함수

---

<PRE>오라클의 함수에는 단일행 함수와 복수행 함수(그룹함수)가 있다.

---

숫자 함수(number function)

ABS	ACOS	ASIN	ATAN
ATAN2	BITAND	CEIL	COS
COSH	EXP	FLOOR	LN
LOG	MOD	POWER	ROUND(number)
SIGN	SIN	SINH	SQRT
TAN	TANH	TRUNC(number)	WIDTH_BUCKET

---

문자 함수(character function)

CHR	CONCAT	INITCAP	LOWER
LPAD	LTRIM	NLS_INITCAP	NLS_LOWER
NLSSORT	NLS_UPPER	REPLACE	RPAD
RTRIM	SOUNDEX	SUBSTR	TRANSLATE
TREAT	TRIM	UPPER	ASCII
INSTR	LENGTH, LENGTHB, LENGTHC, LENGTH2, LENGTH4

---

날짜와 날짜 처리함수(date, datetime function)

ADD_MONTHS	CURRENT_DATE	CURRENT_TIMESTAMP	DBTIMEZONE
EXTRACT(datetime)	FROM_TZ	LAST_DAY	LOCALTIMESTAMP
MONTHS_BETWEEN	NEW_TIME	NEXT_DAY	NUMTODSINTERVAL
NUMTOYMINTERVAL	ROUND(date)	SESSIONTIMEZONE	SYS_EXTRACT_UTC
SYSDATE	SYSTIMESTAMP	TO_DSINTERVAL	TO_TIMESTAMP
TO_TIMESTAMP_TZ	TO_YMINTERVAL	TRUNC(date)	TZ_OFFSET

---

데이터 형변환 함수(conversion function)

ASCIISTR	BIN_TO_NUM	CAST	CHARTOROWID
COMPOSE	CONVERT	DECOMPOSE	HEXTORAW
NUMTODSINTERVAL	NUMTOYMINTERVAL	RAWTOHEX	RAWTONHEX
ROWIDTOCHAR	ROWIDTONCHAR	TO_CHAR(character)	TO_CHAR(datetime)
TO_CHAR(number)	TO_CLOB	TO_DATE	TO_DSINTERVAL
TO_LOB	TO_MULTI_BYTE	TO_NCHAR(character)	TO_NCHAR(datetime)
TO_NCHAR(number)	TO_NCLOB	TO_NUMBER	TO_SINGLE_BYTE
TO_YMINTERVAL	TRANSLATE ... USING	UNISTR

---

기타함수(miscellaneous single row function)

BFILENAME	COALESCE	DECODE	DEPTH
DUMP	EMPTY_BLOB EMPTY_CLOB	EXISTSNODE	EXTRACT(XML)
EXTRACTVALUE	GREATEST	LEAST	NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID	NLS_CHARSET_NAME	NULLIF	NVL
NVL2	PATH	SYS_CONNECT_BY_PATH	SYS_CONTEXT
SYS_DBURIGEN	SYS_EXTRACT_UTC	SYS_GUID	SYS_TYPEID
SYS_XMLAGG	SYS_XMLGEN	UID	UPDATEXML
USER	USERENV	VSIZE	XMLAGG
XMLCOLATTVAL	XMLCONCAT	XMLFOREST	XMLSEQUENCE
XMLTRANSFORM	XMLELEMENT	CASE

---

그룹함수집계(Aggregate) 함수

AVG	CORR	COUNT	COVAR_POP
COVAR_SAMP	CUME_DIST	DENSE_RANK	FIRST
GROUP_ID	GROUPING	GROUPING_ID	LAST
MAX	MIN	PERCENTILE_CONT	PERCENTILE_DISC
PERCENT_RANK	RANK	REGR function	STDDEV
STDDEV_POP	STDDEV_SAMP	SUM	VAR_POP
VAR_SAMP	VARIANCE	GROUPING SETS

윈도우(windowing) 분석 함수

윈도우 분석함수

부분 합을 계속 더해 나감, sum은 전체 합계를 구함

---

객체 참조 함수

DEREF

MAKE_REF

REF

REFTOHEX

VALUE

PseudoColumn을 의미하는 것

ROWID	Each row in the database has an address
ROWNUM	테이블에서 select 되어진 행의 순서번호
LEVEL	테이블에서 행(row)의 계층관계를 가리키는 일련번호 순서

</PRE>

출처 http://cafe.naver.com/youllisys/19  (IT 개발자의 카페)