[ Oracle ] 오라클 함수

 

 

현 포스팅은 세세하게 함수에 대해 알아보기 보다 간단 요약해 참고하기위한 포스팅임.

 

 

 

함수란?

"어떤 입력값을 받아서 정해진 루틴에 의해 작업을 하고 결과값을 만들어서 출력해주는 것"

 

함수는 여러 건의 데이터를 한 번에 하나씩만 처리하는 단일행 함수​와

 

여러 건의 데이터를 동시에 입력받아 결과값 한 건을 만들어주는 복수행 함수가 있다.

 

 

단일행 함수는 숫자 함수, 문자처리 함수, 날짜 함수, 형변환 함수 등이 있다.

 

단일행 함수에 대해 먼저 살펴보자.

 

 

 

 

1. 단일행 함수 - 숫자 함수

ABS

절대 값을 구하는 함수 ( 방향은 없고 크기만 있는 것 )

//return 10
SELECT ABS(-10) FROM DUAL;

//return 15
SELECT ABS(0015) FROM DUAL;

​

FLOOR

소수점 아래를 버리는 함수

//return 7
SELECT FLOOR(7.6) FROM DUAL;

//return 7
SELECT FLOOR(7.2) FROM DUAL;

​

ROUND

특정 자릿수에서 반올림하는 함수

//지정한 자릿수 이하에서 반올림하는 함수
SELECT ROUND(대상, 자릿수) FROM DUAL;

//35 출력
SELECT ROUND(34.5678) FROM DUAL;

//소숫점 이하 3번째 자리에서 반올림 ( 34.57 출력 )
SELECT ROUND(34.5678, 2) FROM DUAL;

//소숫점을 기준으로 해당 자리에서 반올림 ( 30 출력 )
SELECT ROUND(34.5678, -1) FROM DUAL;

 

TRUNC

특정 자릿수에서 잘라내는 함수

//34.56 출력
SELECT TRUNC(34.5678, 2) FROM DUAL;

//30 출력
SELECT TRUNC(34.5678, -1) FROM DUAL;

//34 출력
SELECT TRUNC(34.5678) FROM DUAL;

 

MOD

나누기 연산을 한 후 나머지를 결과로 되돌려 주는 함수

//1 출력
SELECT MOD(27, 2) FROM DUAL;

//2 출력
SELECT MOD(27, 5) FROM DUAL;

//6 출력
SELECT MOD(27, 7) FROM DUAL;

 

 

 

 

2. 단일행 함수 - 문자처리 함수

UPPER

대문자로 변환하는 함수

//return "HELLO"
SELECT UPPER('hello') FROM DUAL;

​

LOWER

소문자로 변환하는 함수

//return "hello"
SELECT LOWER('HELLO') FROM DUAL;

​

INITCAP

이니셜만 대문자로 변환하는 함수

//return "Hello"
SELECT INITCAP('hello') FROM DUAL;

 

​

LENGTH

문자 길이를 구하는 함수

//return 5
SELECT LENGTH('HELLO') FROM DUAL;

​

SUBSTR / SUBSTRB

문자열 일부만 추출하는 함수

SUBSTR : 대상 문자열이나 컬럼의 자료에서 시작 위치부터 선택 개수만큼의 문자 추출

SUBSTRB : 명시된 개수만큼의 문자가 아닌 바이트 수를 잘라냄

//return "ELLO"
SELECT SUBSTR('HELLO', 2);

//return "ELL"
SELECT SUBSTR('HELLO', 2, 3);

//return "녕하세요"
SELECT SUBSTRB('안녕하세요', 4);

​

INSTR

특정 문자의 위치를 구하는 함수.

찾는 문자가 없으면 0 을 반환하며 왼쪽에서 오른쪽으로 스캔함.

시작지점에 음수를 쓰면 오른쪽에서 왼쪽으로 스캔함.

INSTR('문자열', '찾고 싶은 문자열', 시작위치, 몇 번째 인덱스);

//'HELLO WORLD'에서 'DE'를 찾아라
//return 0
SELECT INSTR('HELLO WORLD', 'DE') FROM DUAL;

//'HELLO WORLD'에서 'LL'을 찾아라
//return 3
SELECT INSTR('HELLO WORLD', 'LL') FROM DUAL;

//'HELLO WORLD'에서 4번째 인덱스부터 찾기 시작해서 'L'을 찾아라
//return 4
SELECT INSTR('HELLO WORLD', 'L', 4) FROM DUAL;

//'HELLO WORLD'에서 4번째 인덱스부터 찾기시작해서 2번째로 나오는 'L'을 찾아라
//return 10
SELECT INSTR('HELLO WORLD', 'L', 4, 2) FROM DUAL;

​

LPAD / RPAD

특정 기호로 채우는 함수

LPAD : 오른쪽 정렬 후 왼쪽의 빈 공간에 특정 문자를 채움

RPAD : 왼쪽 정렬 후 오른쪽의 빈 공간에 특정 문자를 채움

//return "#########ORACLE"
SELECT LPAD('ORACLE', 숫자, '#') FROM DUAL;

//return "ORACLE#########"
SELECT RPAD('ORACLE', 숫자, '#') FROM DUAL;

 

LTRIM / RTRIM

공백 문자를 삭제하는 함수

LTRIM : 문자열 왼쪽의 공백 문자들을 삭제

TRIM : 문자열 오른쪽의 공백 문자들을 삭제

​

TRIM/LTRIM/RTRIM

특정 문자 또는 공백을 잘라내는 함수

컬럼이나 대상 문자열에서 특정 문자가 첫 번째나 마지막에 위치해 있으면 해당 특정 문자를 잘라낸 후 남은 문자열만 반환.

제거할 문자를 입력하지 않으면 공백을 제거한다.

LTRIM : 문자열 왼쪽부터 시작해서 제거해 나간다.

TRIM : 문자열 오른쪽부터 시작해서 제거해 나간다.

//return 'HELLO'
SELECT TRIM('     HELLO  ');

//return 'HELLO'
SELECT TRIM('a' FROM 'aaHELLOaaaa');

//return 'HELLO  '
SELECT LTRIM('     HELLO  ');

//return 'HELLOaaaa'
SELECT LTRIM('a' FROM 'aaHELLOaaaa');

//return '     HELLO'
SELECT RTRIM('     HELLO  ');

//return 'aaHELLO'
SELECT RTRIM('a' FROM 'aaHELLOaaaa');

 

 

 

 

 

3. 단일행 함수 - 날짜 함수

SYSDATE

현재 날짜를 반환하는 함수

//return 2020/10/28 15:39:35
SELECT SYSDATE FROM DUAL;

​

날짜의 연산

//두 날짜 사이의 기간
날짜 - 날짜

//날짜로부터 숫자만큼 일자를 더함
날짜 + 숫자

//날짜로부터 숫자만큼 일짜를 뺌
날짜 - 숫자

//두 날짜 사이의 개월 수를 구하는 함수
MONTH_BETWEEN(DATE1, DATE2)

//특정 개월 수를 더한 날짜를 구하는 함수
ADD_MONTH(DATE1, DATE2)

//해당 날짜부터 시작해 명시된 요일을 만나면 해당되는 날짜를 반환
NEXT_DAY(DATE, 요일)

//해당 일이 속한 달의 마지막 날짜를 반환하는 함수
LAST_DAY(DATE)

 

 

 

 

4. 단일행 함수 - 형변환 함수

TO_CHAR

날짜형 혹은 숫자형을 문자형으로 변환

//return 0000123456 ( 남은 자리에 0을 채움 )
SELECT TO_CHAR(123456, '0000000000') FROM DUAL;

//return 123,456 ( 남은 자리에 딱히 채우지 않음 )
SELECT TO_CHAR(123456, '999,999,999') FROM DUAL;

 

TO_DATE

문자형을 날짜형으로 변환

기본적으로 'YY/MM/DD' 형식으로 '년/월/일' 을 나타낸다

//return 2020-10-28
SELECT TO_DATE('20201028', 'YYYY-MM-DD') FROM DUAL;

​

TO_NUMBER

숫자형으로 변환

//return 1234
SELECT TO_NUMBER("1234") FROM DUAL;

​

NVL

NULL을 NULL이 아닌 0이나 다른 값으로 변환하기 위해 사용하는 함수

//ex) EMP에 NAME이 null이라고 가정할 때
//return 0
SELECT NVL(NAME, 0) FROM EMP;

​

DECODE

여러 가지 경우에 대해 선택할 수 있도록 하는 함수 ( SWITCH CASE 문과 같은 기능 )

//DEPTNO 중 10은 ACCOUNTING로, 20은 RESEARCH로, 30은...
DECODE(DEPTNO, 10, 'ACCOUNTING',
               20, 'RESEARCH',
               30, 'SALES',
               40, 'OPERATIONS') AS DNAME

 

CASE

여러 가지 경우에서 하나를 선택하는 함수

DECODE는 조건이 일치하는 경우에만 적용, CASE는 다양한 비교 연산자를 이용해 조건 제시, 범위 지정

쉽게 말해 DECODE는 'a는 b로' 라면 CASE는 'a이면 b를' 이라 보면 된다.

//IF ELSE 와 유사
CASE 표현식 
     WHEN 조건1 THEN 결과1
     WHEN 조건2 THEN 결과2
     WHEN 조건3 THEN 결과3
     ELSE 결과n
END

 

 

위처럼 단일행 함수가 존재하며,

 

다음으로는 여러 건의 데이터를 동시에 입력받아 결과값 한 건으로 만들어주는 복수행 함수를 살펴보자.

​

복수행 함수로는 그룹 함수, GROUP BY절, HAVING절이 있다

 

 

 

 

1. 복수행 함수 - 그룹 함수

- 하나 이상의 행을 그룹으로 묶어 연산해 하나의 결과를 나타내는 함수

- 주의 : SELECT문에서 그룹 함수를 사용하는 경우 그룹 함수를 적용하지 않은 단순 컬럼은 올 수 없음.

=> 산출되는 ROW(행)의 수가 달라 둘을 매칭시킬 수 없음

​

SUM

- 해당 컬럼 값들에 대한 총합을 구하는 함수

- 원래 NULL을 들고 있는 컬럼과 연산하면 결과도 NULL이 되지만 SUM은 그룹 함수이기에 NULL인 것은 제외하고 연산함

​

AVG

- 해당 컬럼 값들에 대한 평균을 구하는 함수

- SUM과 마찬가지로 NULL인 것은 제외하고 연산

​

MAX / MIN

- MAX : 해당 컬럼 값들 중 최대값을 구함

- MIN : 해당 컬럼 값들 중 최소값을 구함

​

COUNT

- 테이블에서 조건을 만족하는 행의 개수를 구함

- NULL인 것은 제외하고 연산

 

 

 

 

2. 복수행 함수 - GROUP BY절

- 어떤 컬럼을 기준으로 그룹화하여 한 눈에 알아보기 쉽게 하기 위한 함수

SELECT 컬럼명, 그룹함수 FROM 테이블명 WHERE 조건(연산자) GROUP BY 컬럼명;

 

 

 

 

3. 복수행 함수 - HAVING절

- GROUP BY절에 의해 생성된 결과 중 원하는 조건에 부합하는 자료만 보고자 할 때 사용

- 쉽게 말해 평상시 단순 컬럼 조회나 일반 자료만을 검색할 때 쓰이는 조건절은 WHERE,

그룹 함수를 적용해서 나온 결과값에 대해 쓰이는 조건절은 HAVING.