오라클 함수2

구분		형식	예	결과값	설명
단일행함수	숫자함수	MOD(m,n)	SELECT MODE(10,3) FROM DUAL	1	m을 n으로 나누었을 때 나머지를 반환. 홀수인지 짝수인지 판단할때 사용
		ROUND(m,n)	SELECT ROUND(18.354, 2) FROM DUAL	18.35	m을 소수점n+1자리에서 반올림한 결과를 반환.
			SELECT ROUND(18.354, -1)  FROM DUAL	20
		WIDTH_BUCKET(expr, min_value, max_value, num_buckets)	SELECT WIDTH_BUCKET(92,0,100,10) FROM DUAL	10	어떤 값의 최소에서부터 최대값을 설정하고 bucket을 지정하여 임의의 값이 지정된 범위 내에서 어느 위치에 있는지를 반환.
		CEIL(n)	SELECT CEIL(13.11) FROM DUAL	14	올림한 후 정수를 반환.
		FLOOR(n)	SELECT FLOOR(13,11) FROM DUAL	13	내림한 후 정수를 반환.
		ABS(n)	SELECT ABS(-123) FROM DUAL	123	절대값을 반환.
		NANVL(m,n)	SELECT NANVL('123',0) FROM DUAL	123	입력값 m이 숫자가 아니라면 n을 반환하고, 숫자라면 m을 반환.
		TRUNC(n,[,m])	SELECT TRUNC(12.34, 1) FROM DUAL	12.3	인수n 소수점자리 m이하 절삭.
		POWER(m, n)	SELECT POWER(3,2) FROM DUAL	3	m의 n제곱값 반환.
		GREATEST(m, n, ….)	SELECT GREATEST(1,2,3,4,5,6,7) FROM DUAL	7	최대값 반환.
		LEAST(m, n, …)	SELECT LEAST(1,2,3,4,5,6,7) FROM DUAL	1	최소값 반환.
	문자함수	LOWER(char)	SELECT LOWER('STRING') FROM DUAL	string	입력된 문자 값을 소문자로 변환.
		UPPER9char)	SELECT UPPER('string') FROM DUAL	STRING	입력된 문자 값을 대문자로 변환.
		SUBSTR(string, position, substring_length)	SELECT SUBSTR('string', 1, 3) FROM DUAL	str	문자열에서 일부 문자값을 선택적으로 반환.
			SELECT SUBSTR('string', 0, 3) FROM DUAL	str
			SELECT SUBSTR('string', -1, 3) FROM DUAL	g
			SELECT SUBSTR('string', 2) FROM DUAL	tring
		ASCII(char)	SELECT ASCII('A') FROM DUAL	65	문자의 ASCII값을 반환.
		CHR(n [USING NCHAR_CS])	SELECT CHR(65) FROM DUAL	A	10진수 n에 대응하는 아스키코드를 반환.
		LENGTH(char)	SELECT LENGTH('학교') FROM DUAL	2	문자열 char의 길이를 반환.
			SELECT LENGTHB('학교') FROM DUAL	4
		LTRIM(char [, set])	SELECT LTRIM('              it') FROM DUAL	it	문자열 char좌측으로부터 set으로 지정된 모든 문자를 제거.
		RTRIM(char [, set])	SELECT RTRIM('it             ') FROM DUAL	it	문자열 char우측으로부터 set으로 지정된 모든 문자를 제거.
		LPAD(expr1, n [, expr2])	SELECT LPAD('string', 10, '*') FROM DUAL	****string	지정된 자리수 n으로부터 expr1을 채우고, 왼편의 남은 공간에 expr2를 채움.
		RPAD(expr1, n [, expr2])	SELECT RPAD('string', 10, '*') FROM DUAL	string****	지정된 자리수 n으로부터 expr1을 채우고, 오른편의 남은 공간에 expr2를 채움.
		REPLACE(expr, str1 [, str2])	SELECT REPLACE('string', 'i', '*') FROM DUAL	str*ng	문자열 expr에서 str1문자를 str2문자로 바꾼 후 결과를 반환.
		TRANSLATE(expr, str1 [, str2])	SELECT TRANSLATE('abcabcabcabc', 'b', '■') FROM DUAL	a■ca■ca■ca■c	문자열 대체(REPLACE와 같은 기능이나 스트링단위가 아닌 문자단위)
		INITCAP(char)	SELECT INITCAP('string') FROM DUAL	String	첫글자만 대문자, 나머지글자는 소문자로 변환.
		CONCAT(char1, char2)	SELECT CONCAT('good', ' morning') FROM DUAL	good morning	char1, char2 문자열 조합하여 반환.
		INSTR(char1, char2)	SELECT INSTR('string','r') FROM DUAL	3	문자열char1에서 char2문자의 위치를 반환.
	날짜함수	SYSDATE	SELECT SYSDATE FROM DUAL	2012-04-12 오후 3:58:20	시스템의 설정된 날짜값을 반환.
		ADD_MONTHS(date, integer)	SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL	2012-05-12 오후 3:58:46	지정한 날짜 중 해당 월에 정수를 더한 값을 반환. 만일 결과가 나온 달이 현재 일수보다 작은 일수를 갖고 있는 달로 변경되면 그 달의 마지막 날을 반환
		LAST_DAY(date)	SELECT LAST_DAY(SYSDATE) FROM DUAL	2012-04-30 오후 3:59:07	해당하는 월의 마지막 일을 반환.
		MONTHS_BETWEEN(date1, date2)	SELECT MONTHS_BETWEEN('2012/04/01','2012/05/01') FROM DUAL	-1	날짜와 날짜 사이의 기간을 '월(month)'로 나타냄. 만일 두 날짜가 그 달의 마지막이라면 정수를 반환하고 그렇지 않으면 한달을 31로 계산한 분수값을 반환
		NEXT_DAY(date, char)	SELECT NEXT_DAY(SYSDATE, '일요일') FROM DUAL	2012-04-15 오후 3:59:52	해당일을 기준으로 명시된 요일의 다음 날짜를 반환.
	변환함수	TO_CHAR(datetime, 'fmt')	SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL	2012-04-12 16:09:47	DATE관련 데이터타입을 VARCHAR2 데이터타입으로 변환.
		TO_CHAR(number, 'fmt')	SELECT TO_CHAR(20000,'$999,999') FROM DUAL	$20,000	NUMBER타입을 VARCHAR2 데이터타입으로 변환.
			SELECT TO_CHAR(20000,'999,999') FROM DUAL	20,000
		CASE({expr | MULTISET (subquery) } AS type_name)			데이터형식이나 collection 형식을 다른 데이터 형식이나 collection 형식으로 변환.
		TO_DATE(char [, fmt [,'nlsparam']])	SELECT TO_DATE('20120412', 'YYYYMMDD') FROM DUAL	2012-04-12	char을 날짜형 데이터 타입값으로 변환.
		TO_NUMBER(expr [, fmt [,'nlsparam']])	SELECT TO_NUMBER('1') FROM DUAL	1	expr을 NUMBER데이터형의 값으로 변환.
		TO_TIMESTAMP(datetime)	SELECT TO_TIMESTAMP(SYSDATE) FROM DUAL	2012-04-12 오전 12:00:00.	문자열을 timestamp형식으로 변환.
	기타함수	DECODE(expr, search, result, default)			expr과 각 search값을 비교하여 같으면 result값, 같지 않으면 default값을 반환.
		NVL(expr1, expr2)	SELECT NVL('',0) FROM DUAL	0	expr1이 NULL값인 경우 expr2의 값을 반환.
		NVL2(expr1, expr2, expr3)	SELECT NVL2('','NULL 아님','NULL') FROM DUAL	NULL	expr1이 NULL값이 아닐 경우 expr2, NULL일 경우 expr3 값을 반환.
집합함수		AVG(expr)	SELECT AVG(SALARY) FROM EMPLOYEES	6461.68224299065	평균값을 반환.
		AVG(expr) OVER(analytical_clause)	SELECT DEPARTMENT_ID, AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID) FROM EMPLOYEES WHERE DEPARTMENT_ID IN ('10','20','30')		분석함수로 사용함. analytical_clause조건의 평균값을 반환.
		RANK(expr) WITHIN GROUP(ORDER BY expr)	SELECT RANK(3000) WITHIN GROUP(ORDER BY SALARY DESC) FROM EMPLOYEES	82	전체값을 대상으로 각 값의 순위를 반환.
		RANK() OVER(query_partition_clause order_by_clause)	SELECT EMPLOYEE_ID, RANK() OVER(ORDER BY SALARY) FROM EMPLOYEES		특정값이 아닌, 값 하나하나의 순위를 반환.
		SUM	SELECT SUM(SALARY) FROM EMPLOYEES	691400	합계를 반환.
		MIN	SELECT MIN(SALARY) FROM EMPLOYEES	2100	최소값을 반환.
		MAX	SELECT MAX(SALARY) FROM EMPLOYEES	2400	최대값을 반환.
		COUNT	SELECT COUNT(*) FROM EMPLOYEES	107	전체 행의 수를 반환.
		STDDEV	SELECT STDDEV(SALARY) FROM EMPLOYEES		표준편차값을 반환.
		VSIZE	SELECT VSIZE(SALARY) FROM EMPLOYEES		바이트수를 반환.
분석함수		FIRST_VALUE(expr) OVER (analytic_clause)	SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY, FIRST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) FROM EMPLOYEES	각부서의 최고급여가 반환.	정렬된 값들 중 첫번째 값을 반환.
		LAST_VALUE(expr) OVER (analytic_clause)	SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY, LAST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) FROM EMPLOYEES	각부서의 최저급여가 반환.	윈도우에서 정렬된 값중에서 마지막 값을 반환.
		COUNT(expr) OVER (analytic_clause)	SELECT EMPLOYEE_ID, SALARY, COUNT(*) OVER (ORDER BY SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 50	해당부서 직원에 대해 각 직원의 급여보다 같거나 적게 받는 사람에 대한 누적 합을 반환.	조건을 만족하는 행의 수를 반환.
		SUM(expr) OVER (analytic_clause)	SELECT EMPLOYEE_ID, LAST_NAME, SALARY, SUM(SALARY) OVER (ORDER BY EMPLOYEE_ID) FROM EMPLOYEES	급여 누적 합을 반환.	조건을 만족하는 행의 합을 반환.
		ROW_NUMBER() OVER ([query_partition_clause] order_by_clause)	SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_NAME, SALARY, ROW_NUMBER() OVER (PARTITION BY D.DEPARTMENT_ID ORDER BY SALARY DESC) AS RANK FROM   EMPLOYEES E JOIN DEPARTMENTS D ON     D.DEPARTMENT_ID = E.DEPARTMENT_ID	부서별로 급여를 많이 받는 순서대로 반환.	ORDER BY 절에서 지정된 행의 순위 순서로 각 행에 고유한 순서를 1부터 할당.
		RANK() OVER (order_by_clause)	SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_NAME, SALARY, RANK() OVER (PARTITION BY D.DEPARTMENT_ID ORDER BY SALARY DESC) AS RANK FROM   EMPLOYEES E JOIN DEPARTMENTS D ON     D.DEPARTMENT_ID = E.DEPARTMENT_ID WHERE  DEPARTMENT_NAME = 'Shipping'	Shipping'부서를 대상으로 급여를 많이 받는 순으로 정렬된 값 반환.	값의 그룹에서 값의 순위를 계산.
		DENSE_RANK() OVER ([query_partition_clause] order_by_clause)	SELECT EMPLOYEE_ID, LAST_NAME, DEPARTMENT_NAME, SALARY, DENSE_RANK() OVER (PARTITION BY D.DEPARTMENT_ID ORDER BY SALARY DESC) AS RANK FROM   EMPLOYEES E JOIN DEPARTMENTS D ON     D.DEPARTMENT_ID = E.DEPARTMENT_ID WHERE  DEPARTMENT_NAME = 'Shipping'		값의 그룹에서 값의 순위를 계산. RANK와는 달리 같은 순위가 둘 이상 있어도 다음 순위는 1만 증가하여 반환.
정규표현식 (오라클10g에 새로도입. 기존함수의 기능확장.)		REGEXP_LIKE(srcstr, pattern [, match_option])	SELECT PRODUCT_NAME FROM   PRODUCT_INFORMATION WHERE  REGEXP_LIKE(PRODUCT_NAME, 'SS[^P]')	제품이름중 'SS'다음 'P'를 포함하지 않은 제품이름 반환.	LIKE 연산자와 유사하며, 표현식패턴을 수행하여 일치하는 값을 반환.
			SELECT PRODUCT_NAME FROM   PRODUCT_INFORMATION WHERE  REGEXP_LIKE(PRODUCT_NAME, 'SS[PS]')	제품이름중 'SS'다음 'P'나 'S'를 포함하는 제품이름 반환.
		REGEXP_SUBSTR(srcstr, pattern [, position [, occurrence [, match_option]]])	SELECT CUST_EMAIL, REGEXP_SUBSTR(CUST_EMAIL, '[^@]+') FROM   CUSTOMERS WHERE  NLS_TERRITORY = 'SWITZERLAND'	고객의 이메일중 아이디만 반환.('@'이 아닐때까지 읽음)	SUBSTR함수기능확장. 주어진 문자열을 대상으로 정규표현식패턴을 수행하여 일치하는 하위 문자열을 반환.
		REGEXP_REPLACE	SELECT REGEXP_REPLACE('Oracle is the  information company','(){2,}','') FROM DUAL	두번째 매개변수 위치에 두문자 이상의 공백이 발생하는 경우({2,}) 공백을 한 문자의 크기로 일정하게 설정('')하여 반환.	주어진 문자열을 대상으로 정규표현식패턴을 조사하여 다른 문자로 대체.
			SELECT PHONE_NUMBER, REGEXP_REPLACE(PHONE_NUMBER, '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1)\2-\3') FROM   EMPLOYEES	515.123.4567--->(515) 123 - 4567 형식으로 변환.
		REGEXP_INSTR			정규표현을 만족하는 부분의 위치를 반환.

 

* 출처 : (주)프리렉 '10g로 시작하는 오라클 SQL&PL/SQL'(김태근 저)  외