[Oracle Database] 오라클 함수

06-01 오라클 함수

SELECT에서 사칙연산하기

더하기 +, 빼기 -, 곱하기 *, 나누기 /  가능하나 나머지는 %가 아닌 mod 사용

 

 

함수란?

특정 결과 데이터를 얻기 위해 어떤 값이나 데이터를 입력하는데 그 값에 따라 가공 또는 연산의 과정을 거쳐 결과값이 나옴. 즉, 오라클 함수는 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어

 

오라클 함수의 종류

오라클에서 기본으로 제공하고 있는 내장 함수(built-in function)와 사용자가 필요에 의해 직접 정의한 사용자 정의 함수(user-defined function)으로 나뉨

 

내장 함수의 종류

내장 함수는 입력 방식에 따라 데이터 처리에 사용하는 행이 나뉨

데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수를 단일행 함수(single-row function)

여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수를 다중행 함수(multiple-row function)

 

문자형 함수

대/소문자를 바꿔 주는 LOWER, UPPER, INITCAP 함수

⚙ LOWER

괄호 안 문자 데이터를 모두 소문자로 변환

 

LOWER(문자열)

 

 

⚙ UPPER

괄호 안 문자 데이터를 모두 대문자로 변환

 

UPPER(문자열)

 

 

⚙ INITCAP

괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환 후 반환

 

INITCAP(문자열)

 

 

문자열 길이를 구하는 LENGTH 함수

특정 문자열의 길이를 구할 때 LENGTH 함수 사용

LENGTHB 함수는 문자열 데이터 길이가 아닌 바이트 수를 반환하는 함수

 

 

※ DUAL 테이블이란, 오라클의 최고 권한 관리자 계정인 SYS 소유의 테이블로 SCOTT 계정도 사용할 수 있는 더미(dummy) 테이블. 임시 연산이나 함수의 결과 값 확인 용도로 종종 사용

 

문자열 일부를 추출하는 SUBSTR 함수

⚙ SUBSTR

SUBSTR(문자열 데이터, 시작위치, 추출 길이)
문자열 데이터의 시작 위치부터 추출 길이만큼 추출,
시작 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 시작

SUBSTR(문자열 데이터, 시작위치)
문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출
시작 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 시작

 

▼ smhrd 값만 출력하기

 

▼ 주민등록번호에서 생년월일 추출

 

⚙ INSTR

문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수

문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지를 알고자 할 때 사용

 

INSTR( 대상 문자열 데이터
           , 위치를 찾으려는 부분 문자
           , 위치 찾기를 시작할 대상 문자열 데이터 위치
           , 시작 위치에서 찾으려는 문자가 몇 번째인지 지정 )

 

⚙ TRIM

특정 문자열을 지우는 TRIM, LTRIM, RTRIM 함수

원본 문자열 데이터를 제외한 나머지 데이터는 모두 생략 가능. 삭제할 문자가 생략될 경우에는 기본적으로 공백을 제거. 삭제 옵션은 왼쪽에 있는 글자를 지우는 LEADING, 오른쪽에 있는 글자를 지우는 TRAILING, 양쪽의 글자를 모두 지우는 BOTH 사용

 

TRIM(문자열)

 

특정 문자를 다른 문자로 바꾸는 REPLACE 함수

⚙ REPLACE

특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 경우 사용

REPLACE(문자열 데이터 또는 열 이름, 찾는 문자, 대체할 문자)

 

▼ 연락처에서 - 제외하고 출력

 

⚙ LPAD, RPAD

데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수

LPAD와 RPAD는 각각 Left Padding, Right Padding을 의미. 데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우 나머지 공간을 특정 문자로 채우는 함수

LPAD(문자열 데이터, 데이터의 자릿수, 빈 공간에 채울 문자)
RPAD(문자열 데이터, 데이터의 자릿수, 빈 공간에 채울 문자)

LPAD

 

RPAD

 

두 문자열 데이터를 합치는 CONCAT 함수

두 개의 문자열 데이터를 하나의 데이터로 연결해 주는 역할

|| 연산자와 유사함 

 

 

06-03 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수

특정 위치에서 반올림하는 ROUND 함수

특정 숫자를 반올림하되 반올림할 위치 지정 가능

반올림할 위치 지정하지 않으면 소수점 첫째 자리에서 반올림한 결과 반환

0에서 음수로 내려가면 자연수 쪽으로 한 자리씩 위로 반올림

 

ROUND(숫자, 반올림 위치)

소수점 세째 자리까지 표기

 

자연수 첫째자리에서 반올림

 

특정 위치에서 버리는 TRUNC 함수

숫자를 버림 처리하는 함수

ROUND 함수와 마찬가지 방식으로 버림 처리할 자릿수 지정 가능

반올림할 위치 지정하지 않으면 소수점 첫째자리에서 버림 처리

 

 

지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수

CEIL 함수는 입력된 숫자에 가장 가까운 큰 정수를 반환

FLOOR는 입력된 숫자에서 가장 작은 정수를 반환

 

숫자를 나눈 나머지 값을 구하는 MOD 함수

오라클에서는 나머지를 구할 때 MOD 함수 사용 (자바에서는 % 사용가능하나 오라클 데이터베이스에서는 사용 불가)

 

06-04 날짜 데이터를 다루는 날짜 함수

 

연산	설명
날짜 데이터 + 숫자	날짜 데이터보다 숫자만큼 일수 이후의 날짜
날짜 데이터 - 숫자	날짜 데이터보다 숫자만큼 일수 이전의 날짜
날짜 데이터 - 날짜 데이터	두 날짜 데이터 간의 일수 차이
날짜 데이터 + 날짜 데이터	연산 불가능

 

SYSDATE

입력시 바로 출력이 되며, 현재 시간을 초단위까지 날짜형 리터럴로 출력

 

실무에서는 아래와 같이 날짜를 조작하기도 함

▼ 3일후 날짜값 구하기

돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수

입력받은 날짜형 값에 대해 돌아오는 요일(NEXT_DAY) 혹은 해당 월의 마지막 날짜(LAST_DAY)를 출력

 

 

몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수

특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환하는 함수

입력받은 날짜형 값에 대해 숫자형 리터럴만큼 개월 수를 가감하여 출력

 

▼ 5년후 입사일시 구하기

 

두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수

두 개의 날짜 데이터를 입력하고 두 날짜 간의 개월 수 차이를 구하는 데 사용

 

 

06-05 자료형을 변환하는 형 변환 함수

각 데이터에 지정된 자료형을 바꿔주는 함수

날짜 → 문자형변환과 문자 → 날짜형변환을 자주 사용

 

날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수

날짜, 숫자 데이터를 문자 데이터로 변환

주로 날짜 데이터에서 문자 데이터로 변환하는데 많이 사용

 

▼ 입사일시에서 연도 추출

 

문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수

연산을 하기 전에 먼저 형변환이 발생함

형 변환 우선순위 : 문자형 → 숫자형 → 날짜형

 

SELECT 이름 + 1000 FROM 직원 ;

명령을 실행하면 문자형인 이름을 숫자형으로 형변환 하려고 함

SELECT TO_NUMBER(이름) + 1000 FROM 직원 ;

그러나 이는 불가함으로 오류 발생

 

 

가능한 형변환

 

문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수