[Oracle] Oracle 내장함수 종류

Oracle 함수에 대하여 정리해보자!!

오라클 SQL에서 함수란 데이터베이스 오브젝트로 정의해서 만들어 둔 뒤 필요할 때마다 이 함수들을 호출해서 사용하는 것이다. 오라클에서 사용되는 함수는 작성자에 따라 내장 함수와 사용자 정의 함수로 구분할 수 있다. 필자는 오라클에서 제공되는 내장함수에 대하여 정리하였으며, 참고하여 적절하게 사용하길 바란다. 

 

내장함수	오라클에서 자체 제공되는 함수로써 정식명칭은 sql함수
사용자 정의함수	개발자나 DBA등 오라클 사용자들이 필요에 따라 직접 작성한 함수

* 오라클에서는 기능적인 측면에서 함수와 비슷한 저장 프로시저라는 것이 있다. 함수가 어떤 연산이나 작업을 수행하고 난 뒤 그 결과를 반환하는데 반해 프로시저는 작업을 수행하기만 할 뿐 결과를 반환하지 않는다. 

1. sql함수가 사용될 수 있는 위치 

SQL함수는 SQL문장에서 특정 위치에서만 사용이 가능하며 함수가 사용될 수 있는 곳은 다음과 같다.

 -SELECT

-WHERE절

-START WITH절

-HAVING절

-INSERT문의 INTO절

-UPDATE문의 SET절

2. 내장함수 종류 

1. 숫자형 함수 

 - ABS(n)

파라미터 형	n: 숫자
설명	n의 절대값을 반환
예제	SELECT ABS(-0.123123) FROM DUAL
예제 답	0.123123

-ROUND(n,i)

파라미터 형	n: 소수점 자리 숫자 i: 정수 (i가 없을 경우 0을 반환한다.)
설명	n을 소숫점 이하 I+1번째 자리에서 반올림한 결과 반환
예제	SELECT ROUND(3.123123,0) FROM DUAL
예제 답	3

 

 

-CEIL(n)

파라미터 형	n: 숫자
설명	n과 같거나 큰, 가장 작은 정수를 반환
예제	SELECT CEIL(3.123123) FROM DUAL
예제 답	4

 

-FLOOR(n)

파라미터 형	n: 숫자
설명	n과 같거나 작은, 가장 큰 정수를 반환
예제	SELECT FLOOR(3.123123) FROM DUAL
예제 답	3

 

 

-MOD(n,m)

파라미터 형	n: 숫자 m: 숫자
설명	n을 m으로 나눈 나머지 값을 반환
예제	SELECT MOD(777,4) FROM DUAL
예제 답	1

 

 

-POWER(n,m)

파라미터 형	n: 숫자 m: 숫자
설명	n의 m 제곱값을 구하는 함수
예제	SELECT POWER(3,2) FROM DUAL
예제 답	9

 

-SQRT(n)

파라미터 형	n: 숫자
설명	n의 제곱근 값을 반환하는 함수
예제	SELECT SQRT(4) FROM DUAL
예제 답	2

 

2. 문자형 함수

-CONCAT(char1,char2)

파라미터 형	char1 : 문자열 char2: 문자열 (문자열: CHAR, VARCHAR2, NCHAR, NVARCHAR, CLOB, NCLOB 타입 가능)
설명	파라미터로 들어오는 두 문자열을 연결하여 그 결과를 반환
예제	SELECT CONCAT(‘Hello’,‘Oracle’) FROM DUAL
예제 답	HelloOracle

 

 

-LOWER(char)

파라미터 형	char : 문자열 (문자열: CHAR, VARCHAR2, NCHAR, NVARCHAR, CLOB, NCLOB 타입 가능)
설명	char의 모든 문자를 소문자로 반환
예제	SELECT LOWER(‘HELLOORACLE’) FROM DUAL
예제 답	hellooracle

 

 

-UPPER(char)

파라미터 형	char : 문자열 (문자열: CHAR, VARCHAR2, NCHAR, NVARCHAR, CLOB, NCLOB 타입 가능)
설명	char의 모든 문자를 대문자로 반환
예제	SELECT UPPER(‘hellooracle’) FROM DUAL
예제 답	HELLOORACLE

 

 

-SUBSTR(char,position,length)

파라미터 형	char : 문자열 position : 숫자(char의 position 위치값) length: 반환할 문자열 갯수
설명	position위치에 있는 char의 문자열부터 length갯수만큼 값을 반환
예제	SELECT SUBSTR(‘hellooracle’,2,3) FROM DUAL
예제 답	ell

 

 

-REPLACE(char,search_string,replace_string)

파라미터 형	char : 문자열 search_string : 문자열 replace_string : 문자열
설명	char문자열에서 search_string 값으로 들어온 문자를 replace_string값으로 대체
예제	SELECT REPLACE(‘Hello MYSQL’, ‘MYSQL’,‘ORACLE’) FROM DUAL
예제 답	Hello ORACLE

 

 

 

-TRIM([leading, trailing, both][trim_character] [FROM] trim_source)

파라미터 형	leading : 왼쪽 문자열 공백 값 혹은 문자열 제거 시 입력 (필수아님) trailing : 오른쪽 문자열 공백 값 혹은 문자열 제거 시 입력 (필수아님) both : 양쪽 문자열 공백 값 혹은 문자열 제거 시 입력 (필수아님) trim_character : 생략하고 싶은 문자 입력 (필수가 아니며 문자열이 아닌 문자만 입력) trim_source : 문자열
설명	문자열의 왼쪽이나 오른쪽 혹은 양쪽 모두에서 (leading, trailing, both) 지정된 문자나 공백을 제거한 결과를 반환
예제	SELECT TRIM( BOTH ‘T’ FROM ‘THELLO ORACLEWORLDTT’) FROM DUAL
예제 답	HELLO ORCLEWORLD

 

 

-LENGTH(char)

파라미터 형	char : 문자열
설명	char의 문자열 길이를 반환
예제	SELECT LENGTH(‘HELLOORACLE’) FROM DUAL
예제 답	11

 

 3. 날짜형 함수 

- SYSDATE, CURRENT_DATE

설명	현재 날짜를 반환함
예제	SELECT SYSDATE FROM DUAL SELECT CURRENT_DATE FROM DUAL
차이점	SYSDATE와 CURRENT_DATE함수는 같은 날짜를 반환하기도 하지만 CURRENT_DATE함수는 현재 세션의 시간대를 기준으로 현재 날짜를 반환하며 SYSDATE는 오라클 서버에 접속되어 있는 세션의 시간대에 따라서 그 시간이 달라질 수 있다. * 세션이란 오라클을 사용하기 위해 오라클 서버에 접속되어있는 상태를 말한다.

 

 

 4. null관련함수 

 

-NVL(exp1,exp2)

설명	exp1이 null이면 exp2를 반환하며 exp1이 null이 아닐 경우 exp1 반환
예제	SELECT NVL(null,24) FROM DUAL
예제 답	24

 

 

-NVL2(exp1,exp2,exp3)

설명	exp1이 null이면 exp3를 반환하며 exp1이 null이 아닐 경우 exp2 반환
예제	SELECT NVL2(null,24,0) FROM DUAL
예제 답	0

 

 

-NULLIF(exp1,exp2)

설명	exp1과 exp2를 비교하여 이 둘이 같으면 NULL, 같지않으면 exp1을 반환
예제	SELECT NULLIF(24,24) FROM DUAL
예제 답	null

 

 5. 변환함수 

 -TO_CHAR(datetime,‘format’) 혹은 TO_CHAR(number,‘format’)

파라미터 형	datetime : DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE 등 날짜형 데이터 타입   날짜형 데이터 시 문자형 타입으로 변환시 사용하는 format 종류 종류 설명 -,/,:,;,. 각종기호 AD,A.D 서기 BC,B.C 기원전 AM,A.M 오전 표시기호 PM,P.M 오후 표시기호 yyyy,yyy,yy,y 연도 syyyy 기원전 연도로 – 기호와 함께 사용가능 YEAR 서술형 문자형태 연도표시 MONTH,MON 월을 서술형 이름으로 표시 MM 01~12형태로 월을 표시 D 주중의 일(1~7) DAY 일을 서술형 이름으로 표시 DD 1~31일 형태로 표시 DDD 1~365일 형태로 표시 HH 1~12시 형태로 표시 HH12 1~12시 형태로 표시 HH24 0~23시 형태로 표시 MI 0~59분 형태로 표시 SS 0~59초 형태로 표시   number : NUMBER과 같은 숫자형 데이터 타입   숫자형 데이터시 문자형 타입으로 변환시 사용하는 format 종류 종류 예 설명 ,(콤마) 9,999 특정 위치에 콤마를 추가. 하나의 포맥 모델에 여러개의 콤마가 가능하지만 소숫점 이하 자리수에는 추가할 수 없음 .(소숫점_) 99.99 소숫점 $ $999 달러표시 0 0999,9990 숫자의 맨 앞이나 뒤에 0을 붙임 9 9999 숫자를 의미. 실제 숫자 개수와 같거나 크게 명시해도 상관 없으나 작게 명시하면 안됨 B B999 실수형 문자에서 정수부분이 0일 경우 이를 공백으로 표시. C C999 명시된 위치에 ISO 통화기호를 반환. EEEE 9.9EEEE 가장 뒤에 위치하며 scientific notation법으로 표시.(ex: SELECT TO_CHAR(123.4567,‘9.9EEEE’) FROM DUAL -> 1.2E+01 ) MI 9999MI 가장 뒤에 위치하며 숫자가 음수일 경우는 – 양수일 경우는 공백이 표시 PR 9999PR 가장 뒤에 위치하며 음수일 경우에 숫자 전체에 <>를 붙이고, 양수일 경우 숫자 앞뒤로 공백 표시 RN,rn RN, rn 로마숫자로 표시. 단 표시할 수 있는 숫자는 1~3999로 RN으로 명시했을 경우 대문자로, rn으로 명시한 경우에는 소문자로 표시됨.
설명	datetime 혹은 number 에 들어간 날짜형, 숫자형 데이터 타입을 varchar2타입으로 변환하여 결과 반환
예제	SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd:HH24') FROM DUAL SELECT TO_CHAR(123,‘$999’) FROM DUAL
예제 답	2013-05-27:13 $123

 

 

-TO_DATE(char, format)

파라미터 형	char : CHAR, VARCHAR2, NCHAR, NVARCHAR2 타입의 문자열 데이터 타입 format : TO_CHAR에 날짜형 format방식과 동일
설명	char로 들어온 문자데이터를 날짜형 데이터으로 반환
예제	SELECT TO_DATE('19901106','yyyy-mm-dd:HH24') FROM DUAL
예제 답	1990-11-06 오전 12:00:00

 

 

 

 7. 조건 함수

 

 -DECODE(exp,search1, result1, search2, result2, ..... , default_result)

파라미터 형	exp: 표현식(컬럼, 문자상수, 함수 , 서브쿼리, 다른 DECODE함수 가능) search1, search2, ... : 표현식 결과 값 result1, result2 .. : search n 표현식에 대한 result n 값 반환 * exp와 search 표현식은 데이터 타입이 같아야 함
설명	exp 표현식에 대한 searchn 표현식 결과 값이 맞을 경우 resultn값이 반환 (IF-ELSE구성)
예제	유저번호(user_seq) 유저이름(user_name) 학과(user_depart) 100 윤연진 컴퓨터공학 200 송재무 컴퓨터공학 300 김은비 산업디자인 [ 유저테이블(USER) ] 학과가 컴퓨터 공학인 유저는 업무가 웹 개발로 산업디자인인 유저는 웹디자인으로 명단을 작성하고 싶다. SELECT user_name, DECODE(user_depart,‘컴퓨터공학’,‘웹개발’,‘산업디자인’,웬디자인‘) as task FROM USER
예제 답	유저이름(user_name) 업무(task) 윤연진 웹개발 송재무 웹개발 김은비 웹디자인

 

 << 함수는 아니나 기능이 유사하여 정의>>

-CASE

종류	CASE는 기본형, 검색형으로 두가지 형태로 사용됨
파라미터 형	기본형 : CASE 다음에 비교대상이 되는 표현식(expr)이 위치하며 이 표현식이 WEHN 이하와 같을 경우(expr=search n) result n을 출력 CASE expr WHEN search1 THEN result1 WHEN search2 THEN result2 ... ELSE default_result END   검색형 : 기본형보다 다양한 비교조건을 사용가능. CASE WHEN 다음에 searchn(조건문)에 대한 result n 값을 출력 CASE WHEN search1 THEN result1 WHEN search2 THEN result2 .... ELSE default_result END
설명	CASE는 함수가 아닌 표현식의 일종이나 기능적인 측면에서 DECODE와 유사하여 첨가하였다. (IF-ELSE 구성)
예제	[ 유저테이블(USER) ] 유저번호(user_seq) 유저이름(user_name) 학과(user_depart) 100 윤연진 컴퓨터공학 200 송재무 컴퓨터공학 300 김은비 산업디자인 학과가 컴퓨터 공학인 유저는 업무가 웹 개발로 산업디자인인 유저는 웹디자인으로 명단을 작성하고 싶다.   [기본형] SELECT user_name, CASE user_depart WHEN ‘컴퓨터공학’ THEN ‘웹개발’ WHEN ‘산업디자인’ THEN ‘웹디자인’ ELSE ‘청소’ END AS TASK FROM USER [검색형] SELECT user_name. CASE WHEN user_depart = ‘컴퓨터공학’ THEN ‘웹개발’ WHEN user_depart = ‘산업디자인’ THEN ‘웹디자인’ ELSE ‘청소’ END AS TASK FROM USER
예제 답	유저이름(user_name) 업무(task) 윤연진 웹개발 송재무 웹개발 김은비 웹디자인

 

 

 

 

 7. 그룹핑 함수

- ROLLUP

설명	그룹핑된 결과에 그룹별 합계정보를 추가함 = group by절에 있는 컬럼들을 오른쪽에서 왼쪽 차례로 그룹들을 생성하고 각 그룹에 맞는 합계정보를 추가함
예제	[ 유저테이블(USER) ] 유저번호(user_seq) 유저이름(user_name) 학과(user_depart) 100 윤용진 컴퓨터공학 200 송잼은 컴퓨터공학 300 김윤비 산업디자인   [ 업무일지 테이블 (BUSINESS_WORK) ] 유저번호(user_seq) 업무일지 확인유무 (busi_yn) 업무일지 날짜 (busi_date) 100 y 2013-05-30 200 n 2013-05-30 100 y 2013-05-31 300 n 2013-05-31 200 y 2013-05-31 학과별 유저가 작업한 업무일지대한 합계를 구하여라 SELECT U.USER_NAME,U.USER_DEPART, COUNT(U.user_name) AS busi_work FROM USER U JOIN BUSINESS_WORK B ON U.USER_SEQ=B.USER_SEQ GROUP BY ROLLUP(U.USER_DEPART,U.user_NAME)
예제 답	학과(user_depart) 유저이름(user_name) 업무일지 작성 개수(busi_work) 산업디자인 김윤비 1 산업디자인 null 1 컴퓨터공학 송잼은 2 컴퓨터공학 윤용진 2 컴퓨터공학 null 4 null null 5

 

 

 

- CUBE

설명	그루핑된 컬럼의 모든 가능한 조합에 대한 합계 정보를 추가할 수 있음
예제	[ 유저테이블(USER) ] 유저번호(user_seq) 유저이름(user_name) 학과(user_depart) 100 윤용진 컴퓨터공학 200 송잼은 컴퓨터공학 300 김윤비 산업디자인   [ 업무일지 테이블 (BUSINESS_WORK) ] 유저번호(user_seq) 업무일지 확인유무 (busi_yn) 업무일지 날짜 (busi_date) 100 y 2013-05-30 200 n 2013-05-30 100 y 2013-05-31 300 n 2013-05-31 200 y 2013-05-31 유저별 작업한 업무일지대한 합계를 구하여라 SELECT U.USER_DEPART, U.USER_NAME, COUNT(U.USER_DEPART) AS busi_work FROM USER U JOIN BUSINESS_WORK B ON U.USER_SEQ=B.USER_SEQ GROUP BY CUBE(U.USER_DEPART,U.user_NAME)
예제 답	학과(user_depart)유저 이름(user_name) 업무일지 작성 개수(busi_work) null null 5 null 김윤비 1 null 송잼은 2 null 윤용진 2 산업디자인 null 1 산업디자인 김윤비 1 컴퓨터공학 null 4 컴퓨터공학 송잼은 2 컴퓨터공학 윤용진 2