ORACLE에서 자주 쓰는 함수 모음

1. 집계 함수

max(column)	최대값을 구하는 함수
min(column)	최소값을 구하는 함수
avg(column)	평균값을 구하는 함수
sum(column)	합계를 구하는 함수
mod(a, b)	a를 b로 나눈 나머지값을 구하는 함수
count(*)	총 레코드 수를 세는 함수, null값도 포함
count(컬럼명)	컬럼명을 기준으로 총 레코드 수를 세는 함수, null값은 제외

 
2. 문자열 함수

lower('Hello World')	hello world, 해당 컬럼의 값을 소문자로 변환하는 함수
upper('Hello World')	HELLO WORLD, 해당 컬럼의 값을 대문자로 변환하는 함수
inicap('go go go!')	Go Go Go!, 첫글자와 공백이후 첫글자는 대문자로 변환하는 함수
concat('Helllo', 'World')	Hello World, 두개의 문자열을 연결하는 함수
substr('HelloWorld', 6)	World, 문자열의 6번째자리부터 전체를 추출
substr('HelloWorld', 1, 5)	Hello, 문자열의 1번째자리부터 5자까지 추출하는 함수
length('oracle')	6, 문자열의 총 길이를 세는 함수
instr('HelloWorld', 'W')	6, 문자열에서 특정문자의 위치를 세는 함수 만약 특정문자가 문자열에 없다면 0 반환
lpad('SQLPLUS', 10, '*')	***SQLPLUS,  해당길이에 부족한 부분은 왼쪽부터 특정문자로 채우는 함수
rpad('SQLPLUS', 10, '*')	SQLPLUS***,  해당길이에 부족한 부분은 오른쪽부터 특정문자로 채우는 함수
trim(' SQLPLUS')	SQLPLUS, 공백을 제거하는 함수
ltrim('*SQLPLUS', '*')	SQLPLUS, 좌측부터 문자열에서 해당 문자를 제거하는 함수
rtrim('SQLPLUS*', '*')	SQLPLUS, 우측부터 문자열에서 해당 문자를 제거하는 함수
replace('SEVLTL', 'L', 'EN')	SEVENTEN, 문자열에서 해당 문자를 다른 문자로 바꾸어주는 함수
nvl(expr1, expr2)	expr1이 null이면 expr2 값으로 반환하는 함수
nvl2(expr1, expr2, expr3)	expr1과 null1을 비교해 null이 아니면 expr2, null이면 expr3을 반환하는 함수
nullif(expr1, expr2)	expr1과 expr2를 비교하여 같으면 null, 다르면 expr1을 반환하는 함수
coalesce(expr1, expr2, ... exprn)	expr1~exprn 목록에서 첫번째로 null이 아닌 expr을 반환
||	결합연산자 ex) select uname || '의 평균은 ' || aver || '입니다' from sungjuk;

 

3. 숫자 함수

abs(-7)	7, 절대값을 계산하는 함수
mod(1500, 200)	100, 1500 / 200 의 나머지를 반환하는 함수
ceil(1.123) ; 2	소수점 첫째자리에서 해당 값을 올림 처리한 정수를 반환하고 해당 값보다는 크지만 가장 근접하는 최소값을 구하는 함수
ceil(-1.623) ; -1
floor(1.123)	소수점 첫째자리에서 해당 값을 내림 처리한 정수를 반환하고 해당 값보다는 작지만 가장 근접하는 최대값을 구하는 함수
floor(-1.123)
round(n, m)	해당 숫자 n에서 m자리까지 반올림하는 함수
trunc(n. m)	해당 숫자 n에서 m자리까지 버림하는 함수
to_number	문자열을 숫자형으로 바꿔준다
select to_number('123')+1 from dual;    -- 124   select '100'+1 from dual;                        -- 101, 내부적으로 to_number()가 호출됨

 

4. 날짜 함수

sysdate	현재 날짜, 시간 ex) select sysdate from dual;
extract(year from sysdate)	sysdata에서 년월일 추출하기(year, month, day)
months_between(sysdate, to_date('2014-12-11'))	첫번째 날짜에서 두번째 날짜 사이 개월 수를 반환하는 함수
to_date()	문자열을 날짜형으로 변환
add_months(sysdate, 5)	해당 날짜에서 개월 수를 더한 날짜를 반환하는 함수
next_day(sysdate, '금요일')	해당 날짜에서 최초로 도래하는 해당 요일의 날짜를 반환하는 함수
last_day(sysdate)	해당 날짜가 포함되어 있는 달의 마지막 날짜를 반환하는 함수

 

5. 형변환 함수

TO_CHAR(원래 날짜, '원하는 모양') - 숫자와 날짜를 문자로 변환해 주는 두가지 기능

TO_NUMBER('1')

TO_DATE('문자','날짜포맷')

 

7. CASE WHEN 함수

CASE WHEN publicNo = '1' THEN 'm'
     WHEN publicNo = '2' THEN 'f'
     ELSE 'Unknown'
END AS gender

≫ if, else 와 비슷한 기능, DECODE 함수보다 복잡한 조건식에 사용, CASE 표현식에서 ELSE 부분은 생략이 가능하며, 만족하는 조건이 없으면 NULL을 리턴 (CASE WHEN 조건 THEN 리턴값)

 

8. UNION , UNION ALL

SELECT GENDER, ID FROM INFO1
UNION
SELECT NAME, ID FROM INFO2

≫ 조회한 다수의 SELECT 문을 하나로 합치고싶을때 사용

- UNION 은 결과를 합칠때 중복되는 행은 하나만 표시

- UNION ALL 은 중복제거를 하지 않고 모두 합쳐서 표시

 

9. MyBatis - CDATA

<![CDATA[부등호]]>

≫ 태그 닫힘이나 열림으로 인식하지 않고 부등호 그대로 인식하도록 할때 사용

 

10. REGEXP_REPLACE

REGEXP_REPLACE(tel_no, '(.{3})(.+)(.{4})', '\1-\2-\3') --휴대폰
REGEXP_REPLACE(tel_no, '(02|.{3})(.+)(.{4})', '\1-\2-\3') --일반 전화번호 포함

≫ 정규식 함수를 통해 포맷 형식으로 출력할때 사용

 

11. CHR(10),CHR(13)

REPLACE(REPLACE(HTML,CHR(10),''),CHR(13),'')

≫ 자바로 치면 CHR(13)은 \r 이고 CHR(10)은 \n에 해당 (CLOB타입 같은 줄바꿈이있는 문장에 사용)

 

12. ROWNUM, ROW_NUMBER()

SELECT ROWNUM, ID FROM MEM
SELECT ID FROM MEM WHERE ROWNUM = 1

SELECT ROW_NUMBER() OVER(ORDER BY ID) AS NO, ID FROM MEM

≫ ROWNUM : 조회된 순서대로 값을 매긴다

≫ ROW_NUMBER() : ORDER BY 순서대로 값을 매긴다