[Oracle] 함수

단일 행 함수

• 행 당 하나의 결과를 반환
• SELECT, WHERE, ORDER BY절에서 사용
• 데이터 타입 변경 가능
• 중첩 가능

 

문자 함수

함수	설명
CHR(65) / ASCII('A')	문자 / ASCII
LOWER('HELLO WORLD') / UPPER('hello world')	소문자 / 대문자
INITCAP('the quick brown fox')	첫 글자만 대문자로 변환
LPAD('123', 5, '0') 00123 / RPAD('123', 5, '0') 12300	왼쪽 / 오른쪽에서 문자열
LTRIM(' hello') / RTRIM('hello ')	왼쪽 / 오른쪽 공백 제거
REPLACE('hello world', 'l', 'z') hezzo worzd	문자열 치환
SUBSTR('hello world', 2, 5) / SUBSTRB	문자열 잘라내기 / byte 기준
LENGTH('hello world') / LENGTHB	문자열 길이 / byte 기준
INSTR('hello world', 'l') 3 / INSTRB	문자열의 위치를 찾음. 없으면 0 (>0: 왼쪽부터 -1: 오른쪽부터) / byte 기준

 

 

숫자 함수

함수	설명
CEIL(2.3) 3	크거나 같은 가장 작은 정수
MOD(10, 3)	10 // 3
POWER(2, 3)	거듭제곱
ROUND(2.345, 2) 2.35	반올림
TRUNC(2.345, 2) 2.34	버림
SIGN 1 0 -1	양수 / 음수 구분

 

 

 

날짜 함수

함수	설명
SYSDATE	현재 날짜와 시간
LAST_DAY('01-JAN-2023') 2023-01-31	해당 월의 마지막 날짜
MONTHS_BETWEEN('01-JAN-2023', '01-OCT-2022') 3.96774193548387	두 기간 사이의 개월 수
ADD_MONTHS('01-JAN-2023', 3) 2023-04-01	월수 더하고 빼기
NEXT_DAY('01-JAN-2023', 'FRIDAY') 2023-01-06	다음 첫 번째 해당 요일의 날짜
TRUNC(date, format)	포맷으로 명시된 단위로 잘라낸 결과
ROUND(date, format)	포맷으로 명시된 단위로 반올림(정오)

 

 

 

+ 날짜 연산

연산	결과
DATE + NUMBER	날짜에 해당 일수를 더한 결과를 날짜 형태로 반환
DATE - NUMBER	날짜에 해당 일수를 뺀 결과를 날짜 형태로 반환
DATE1 - DATE2	두 날짜 사이의 경과 일수를 숫자로 반환
DATE + NUMBER/24	날짜에 해당 시간을 더하여 날짜 형태로 반환

 

 

변환 함수

함수	설명
TO_CHAR(숫자 또는 날짜, 포맷)	문자로 치환
TO_NUMBER(문자열)	숫자로 치환
TO_DATE(문자열)	날짜로 치환

 

 

TO_CHAR 숫자 포맷

n	format	format
1000	9,999.99	9G999D99
1234567.890	9,999,999.999	9G999G999D999
3.5	9,999.99	9G999D99
1234	999,999	999G999

 

 

TO_CHAR 날짜 포맷

format	format sample
‘YYYYMMDD’	'20230728’
‘YYYY/MM/DD’	‘2023/07/28’
‘YYYY-MM-DD’	‘2023-07-28’
‘YYYY/MM/DD HH24MISS’	‘2023/07/28 054412’
‘YYYY/MM/DD HHMISS AM’	‘2023/07/28 054434 오전’
‘YYYY/MM/DD HH24:MI:SS’	‘2023/07/28 05:45:08’
‘YYYY/MM/DD HH24:MI:SS.SSSSS’	‘2023/07/28 05:45:50.20750’

 

기타 함수

함수	설명
NVL(컬럼이름, 대체값)	Null 처리 함수
NVL2(a,b,c)	a ≠ null인 경우 b 리턴, = null이면 c 리턴
DECODE(a, b, c, d) a == b, c 아니면 d
DECODE(a,b,c,d,e,f) a == b면 c, a == d면 e 아니면 f	연속 조건문
USERENV('SESSIONTIMEZONE')	환경 값
GREATEST(1, 2, 3) / LEAST(1, 2, 3)	최대값 / 최소값
UID / USER	User ID / User 명

 

 

집계 함수

Aggregate function

• 전체 집합 또는 그룹으로 분류된 집합에 작용합니다.
• GROUP BY 로 소그룹별 기준을 정하고, SELECT 절에서 집계 함수를 사용합니다.
  • SELECT 에선 GROUP BY에서 사용한 속성과 집계함수만 쓸 수 있습니다.
  • WHERE 절에서 사용할 수 없습니다. (참고→ 절 수행 순서)
  • GROUP BY 절에선 SELECT 절과 달리 alias를 사용할 수 없습니다.
  • HAVING 절은 GROUP BY절의 기준 항목이나 소그룹의 집계 함수를 이용하여 조건을 표시할 수 있습니다.

수행 순서 : GROUP BY 행 분류 → aggregate function 그룹에 적용 → HAVING 조건에 일치하는 행만 출력

• 사용 시 일반 컬럼을 select 절에 함께 사용하지 못합니다. 단, group by 에 나온 컬럼은 가능.

함수	사용 목적
COUNT(*)	null을 포함한 행의 수 출력
COUNT(표현식)	표현식의 값이 null인 것을 제외한 행의 수 출력
SUM([distinct|all]표현식)	null을 제외한 표현식 값의 합계 출력
AVG([distinct|all]표현식)	null을 제외한 표현식 값의 평균 출력(null 제외하고 나눔)
MAX([distinct|all]표현식)	표현식의 최대값을 출력(문자, 날짜도 사용 가능)
MIN([distinct|all]표현식)	표현식의 최소값을 출력(문자, 날짜도 가능)
STDDEV([distinct|all]표현식)	표현식의 표준편차를 출력
VARIANCE([distinct|all]표현식)	표현식의 분산을 출력
RANK(3000) within GROUP(ORDER BY sal desc)	그룹 내의 전체 값을 대상으로 각 값의 순위를 구함.