[DBMS/Oracle] 오라클 함수 (문자, 숫자, 날짜, 형변환 등)

 

오라클의 문자함수, 숫자함수, 날짜함수(날짜 연산, 포맷형식, 날짜형식), 형변환함수(숫자형식), NULL처리함수(NVL,NVL2), DECODE함수, CASE문에 대해 알아보자.

 

함수 function

특정한 결과값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어를 의미한다.

내장 함수 built-in function	이미 완성된 상태로 사용이 간편하다.
사용자 정의 함수 user-defined function	제작에 시간이 소요되지만 원하는 기능을 구현할 수 있다.

 

 

내장함수 종류

단일 행 함수 single-row function	데이터가 한 행씩 입력되고, 입력된 한 행당 결과가 하나씩 나오는 함수이다.
다중 행 함수 multiple-row function	여러 행이 입력되어 , 하나의 행으로 결과가 나오는 함수이다.

*다루는 자료형에 따라 세분화된다.

 

문자함수

UPPER(문자열)	괄호 안의 문자 데이터를 모두 대문자로 변환하여 반환한다.
LOWER(문자열)	괄호 안의 문자 데이터를 모두 소문자로 변환하여 반환한다.
INITCAP(문자열)	괄호 안의 문자 데이터를 첫글자는 대문자로, 나머지 문자를 소문자로 변환하여 반환한다.
LENGTH(문자열)	괄호 안의 문자 데이터의 문자열의 길이를 반환한다.
LENGTHB(문자열)	괄호 안의 문자 데이터의 바이트 수를 반환한다.
SUBSTR(문자열 데이터, 시작 위치, 추출 길이)	문자열 데이터의 시작 위치부터 추출 길이만큼 추출한다. 시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작한다.
SUBSTR(문자열 데이터, 시작 위치)	문자열 데이터의 시작 위치부터 문자열 데이터의 끝까지 추출한다. 시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 시작한다.
INSTR(문자열 데이터, 위치를 찾으려는 문자, (시작 위치(선택/기본값1), (시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택/기본값1))	문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지 알고자 할 때 사용한다. 문자열 데이터와 찾으려는 문자열 값은 필수이며, 나머지 조건은 선택이다.
REPLACE(문자열 데이터, 찾는 문자, (대체할 문자(선택))	문자열 데이터 안에 포함된 문자를 다른 문자로 대체한다. 대체할 문자를 입력하지 않으면 해당 문자열에서 삭제된다.
LPAD(문자열 데이터, 데이터 자리수, 채울문자(선택))	Left Padding 데이터 길이가 지정한 자리 수 보다 작을 경우 나머지 공간을 특정 문자로 채운다. 채울 문자를 지정하지 않을 경우 공백 문자가 들어간다.
LPAD(문자열 데이터, 데이터 자리수, 채울문자(선택))	Right Padding 데이터 길이가 지정한 자리 수 보다 작을 경우 나머지 공간을 특정 문자로 채운다. 채울 문자를 지정하지 않을 경우 공백 문자가 들어간다.
CONCAT(문자열, 문자열)	두 문자열을 합친다. || 연산자와 유사한 결과값을 보여준다.
TRIM(삭제옵션(선택), 삭제할문자(선택)) FROM 문자열 데이터	LEADING(왼쪽에 있는 글자), TRAILING(오른쪽에 있는 글자), BOTH(양 쪽) 옵션이 있다. 삭제할 문자가 생략될 경우 공백이 제거되며, 기본옵션은 BOTH이다.
LTRIM(문자열 데이터, (삭제할 문자 집합(선택)))	왼쪽의 지정문자를 삭제하는데 사용되며, 삭제할 문자를 지정하지 않을 경우 공백이 제거된다. 삭제할 문자를 여러 개 지정할 수 있다.
RTRIM (문자열 데이터, (삭제할 문자 집합(선택)))	오른쪽의 지정문자를 삭제하는데 사용되며, 삭제할 문자를 지정하지 않을 경우 공백이 제거된다. 삭제할 문자를 여러 개 지정할 수 있다.

*DUAL 테이블로 쉽게 확인 가능하다.

*DUAL 테이블 : 더미(dummy)테이블로 임시 연산이나 함수의 결과 값 확인용도로 사용된다.

 

 

숫자 함수

ROUND(숫자, 반올림위치(선택)	지정된 숫자의 특정 위치에서 반올림한 값을 반환한다. 반올림 위치를 지정하지 않으면 소수점 첫 번째 자리에서 반올림이 수행된다.
TRUNC(숫자, 내림위치(선택)	지정된 숫자의 특정 위치에서 버림한 값을 반환한다. 버림 위치를 지정하지 않으면 소수점 첫 번째 자리에서 버림이 수행된다.
CEIL(숫자)	지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환한다.
FLOOR(숫자)	지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환한다.
MOD(나눗셈이 될 숫자, 나눌 숫자)	지정된 숫자를 나눈 나머지 값을 반환한다.

 

 

날짜 함수

SYSDATE	현재 날짜 및 시간이다.
ADD_MONTHS(날짜, 더할 개월 수)	입력한 날짜에서 입력한 개월만큼 이후의 날짜를 반환한다.
MONTHS_BETWEEN(날짜, 날짜)	두 날짜 데이터 간의 날짜 차이를 개월 수로 계산하여 반환한다.
NEXT_DAY(날짜, 요일)	특정 날짜를 기준으로 돌아오는 요일의 날짜를 반환한다.
LAST_DAY(날짜)	특정 날짜가 속한 달의 마지막 날짜를 반환한다.
ROUND(날짜, 반올림기준 포맷(선택)	포멧 모델을 기준으로 날짜를 반올림한다.
TRUNC(날짜, 버림기준 포맷 (선택)	포멧 모델을 기준으로 날짜를 버림한다.

 

날짜 연산

날짜 + 숫자	날짜 데이터보다 숫자만큼 일수 이후의 날짜가 연산된다.
날짜 - 숫자	날짜 데이터보다 숫자만큼 일수 이전의 날짜가 연산된다.
날짜 - 날짜	두 날짜 데이터 간의 일수 차이가 연산된다.
날짜 + 날짜	연산 불가, 지원하지 않는다.

 

포맷 형식

CC, SCC	네 자리의 연도의 끝 두 자리를 기준으로 사용한다. (2016년이면 2050 이하이므로, 반올림하면 2001년이 된다.)
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	날짜 데이터의 해당 연, 월, 일의 7월 1일을 기준으로 사용한다. (2016년 7월 1일이라면 반올림하여 2017년이 된다.)
IYYY, IYY, IY, I	ISO 8601에서 제정한 날짜 기준년도 포맷을 기준으로 사용한다.
Q	각 분기의 두 번째 달의 16일을 기준으로 사용한다.
MONTH, MON, MM, RM	각 달의 16일을 기준으로 사용한다.
WW	해당 연도의 몇 주(1~53번째 주)를 기준으로 사용한다.
IW	ISO 8601에서 제정한 날짜 기준 해당 연도의 주를 기준으로 사용한다.
W	해당 월의 주를 기준으로 사용한다.
DDD, DD, J	해당 일의 정오를 기준으로 사용한다.
DAY, DY, D	한 주가 시작되는 날짜를 기준으로 사용한다.
HH, HH12, HH24	해당 일의 시간을 기준으로 사용한다.
MI	해당 일 시간의 분을 기준으로 사용한다.

 

날짜 형식

CC	세기
YYYY, RRRR	연 (4자리 숫자)
YY, RR	연 (2자리 숫자)
MM	월 (2자리 숫자)
MON	월 (언어별 월 이름 약자)
MONTH	월 (언어별 월 이름 전제)
DD	일 (2자리 숫자)
DDD	1년 중 며칠 (1~366)
DY	요일 (언어별 요일 이름 약자)
DAY	요일 (언어별 요일 이름 전체)
W	1년 중 몇 번째 주 (1~53)
NLS_DATE_LANGAGE = 언어	해당 언어로 설정

 

시간 형식

HH24	24시간으로 표현한 시간
HH, HH12	12시간으로 표현한 시간
MI	분
SS	초
AM, PM, A.M, P.M	오전, 오후 표시

 

 

 

형변환 함수

TO_CHAR(날짜, 문자형태, 언어지정(선택))	날짜 데이터를 문자 데이터로 반환한다.
TO_CHAR(숫자, 지정형식)	숫자 데이터를 문자 데이터로 반환한다.
TO_NUMBER(문자열, 숫자형태)	문자 데이터를 숫자 데이터로 반환한다.
TO_DATE(문자열, 지정형식)	문자 데이터를 날짜 데이터로 반환한다.

 

숫자 지정형식

9	숫자의 한 자리를 의미한다. (빈자리를 채우지 않는다.)
0	빈 자리를 0으로 채움을 의미한다.
$	달러 표시를 붙여서 출력한다.
L	로컬 지역 화폐 단위 기호를 붙여서 출력한다.
.	소수점을 표시한다.
,	천 단위의 구분 기호를 표시한다.

 

 

 

NULL처리 함수

NVL(NULL이 될 수도 있는 데이터, NULL일 경우 대신할 값)	NULL일 수도 있는 데이터가 NULL을 반환하면 대신할 데이터를 대신 반환한다. NULL이 아니라면 해당 값을 그대로 반환한다.
NVL2(NULL이 될 수도 있는 데이터, 앞 데이터가 NULL이 아닐 경우 반환, 앞 데이터가 NULL일 경우 반환)	NULL일 수도 있는 데이터가 NULL이 아닌 값이 나오면 NULL이 아닌 값일 때 보여줄 데이터를 반환하고, NULL이라면 NULL일 때 보여줄 데이터를 반환한다.

 

 

DECODE 함수

java의 조건문과 비슷한 함수로, 기준이 되는 데이터를 지정한 후 해당 데이터 값에 따라 다른 결과를 내보내는 함수이다.

 

[예시]

emp테이블에서 직책이 manager인 사람은 급여의 10%인상, salesman인 사람은 5%인상, analyst인 사람은 그대로, 나머지는 3%인상된 급여를 보고 싶다.

SELECT EMPNO, ENAME, JOB, SAL
	DECODE(JOB,
    	'MANAGER', SAL *1.1,
        'SALESMAN', SAL *1.05,
        'ANALYST', SAL,
        SAL * 1.03) AS UPSAL
FROM EMP;

 

 

 

CASE문

DECODE 함수와 마찬가지로 특정 조건에 따라 반환할 데이터를 설정할 때 사용한다.

기준데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE함수와 달리 각 조건에서 사용하는 데이터가 서로 상관 없어도 가능하며, 다양한 조건을 사용할 수 있다.

(범용성이 더 높다)

 

문법

CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
	WHEN [조건1], THEN [조건 1의 결과값이 TRUE일 때 반환할 결과]
    WHEN [조건2], THEN [조건 2의 결과값이 TRUE일 때 반환할 결과]
    ELSE [위 조건과 일치하는 경우가 없을 때 반환할 결과]
END