[ DB /Oracle ] DML | SELECT - 함수 (단일행 함수/ 그룹 함수)

 

단일행  함수

• 단일행 함수 : 각각의 데이터를 한건씩 처리
• 복수행 함수 : 여러건의 데이터를 한꺼번에 처리 후 1개의 결과로 처리 그룹함수, 집계함수 라고도 불림

 

1. null 값 설정 : nvl , nvl2

• nvl (null일때 바꿔줄 값)
• nvl2 (e,null이아닐때, null일때)

SELECT e.FIRST_NAME,
		e.SALARY,
		**nvl**(e.COMMISSION_PCT,0)
FROM EMPLOYEES e
WHERE e.SALARY < 14000 AND e.SALARY > 10000
ORDER BY e.SALARY DESC;

 

 

2. 문자 함수

Function	설명
CONCAT(s1,s2)	s1과 s2의 결합
INITCAP(s)	첫글자만 대문자로 변경
LOWER(s)	소문자로 변경
UPPER(s)	대문자로 변경
LPAD(s1,n,s2)	문자열의 왼쪽 채움(길이:n,채움문자:s2)
RPAD(s1,n,s2)	문자열의 오른쪽 채움(길이:n,채움문자:s2)
CHR(n)	ASCII값이 n인 문자 변환
REPLACE(s,p,r)	문자열 치환, s에 있는 p문자열을 r로 치환
SUBSTR(s,m,n)	부분 문자열,m번째부터 길이 n인 문자열 변환
TRANSLATE(s,from,to)	s에서 from문자열의 각 문자를 to 문자열의 각 문자로 변환
ASCII(s)	ASII값 변환
INSTR(s1,s2,m,n)	문자열 검색,s1의 m번째 부터 s2문자열이 나타나는 n번째 위치 반환
LENGTH(s)	문자열 길이 반환

 

2-1. INITCAP(컬럼명)

영어의 첫 글자만 대문자로 출력하고 나머지는 전부 소문자로 출력하는 함수

SELECT e.EMAIL , **INITCAP(**e.EMAIL**)**,DEPARTMENT_ID  
FROM EMPLOYEES e;

 

2-2. SUBSTR(컬럼명, 시작위치, 글자수)

select first_name, substr(first_name,1,3), substr(first_name,-3,2)
from employees
where department_id = 100;

• 주어진 문자열에서 특정길이의 문자열을 구하는 함수
• 양수인 경우 왼쪽à오른쪽으로 검색해서 글자수 만큼 추출
• 음수인 경우 오른쪽à왼쪽 검색을 한 후 왼쪽à오른쪽으로 글자수 만큼 추출

 

2-3. LPAD(컬럼명, 자리수, ‘채울문자’) / RPAD(컬럼명, 자리수, ‘채울문자’)

select e.FIRST_NAME , 
**lpad**(e.FIRST_NAME ,10,'*'), 
**rpad**(e.FIRST_NAME ,10,'*')
from EMPLOYEES e ;

• LPAD() :왼쪽 공백에 특별한 문자로 채우기
• RPAD() :오른쪽 공백에 특별한 문자로 채우기

 

2-4.REPLACE (컬럼명, 문자1, 문자2)

• 컬럼명에서 문자1을 문자2로 바꾸는 함수

select first_name, 
**replace**(first_name, 'a', '*') 
from employees
where department_id =100;

 

 

3. 숫자함수

Funtion	설명	Ex	Result
ABS(n)	절대값	ABS(-5)	5
CEIL(n)	n보다 크거나 같은 최소 정수	CEIL(-2.4)	-2
FLOOR(n)	n보다 작거나 같은 최대 정수	FLOOR(-2.4)	-3
MOD(n)	나머지	MOD(13,2)	1
POWER(m,n)	m의 n승	POWER(2,3)	8
ROUND(m,n)	소수점 아래 n 자리까지 반올림	ROUND(4.567,2)	4.57
TRUN(m,n)	소수점 아래 n자리 미만 버림	TRUN(4.567.2)	4.56
SIGN(n)	부호(1,0,-1)	SIGN(-10)	-1

 

3-1. 숫자함수 – ROUND(숫자, 출력을 원하는 자리수)

select round(123.346, 2) "r2",
round(123.456, 0) "r0",
round(123.456, -1) "r-1"
from dual;

 

 

4. 날짜함수

Funtion	설명
ADD_MONTHS(d,n)	d날짜에 n달을 더함
LAST_DAY(d)	d의 달의 마지막 날
MONTHS_BETWEEN(d1,d2)	d1과 d2 사이의 달수
NEW_TIME(d,z1,z2)	z1타임존의 d에서 z2 타임존의 날짜 생성
NEXT_DAY(d,day)	d날 이후 첫 day요일의 날짜
ROUND(d,fmt)	fmt에 따른 날짜반올림
TRUNC(d,fmt)	fmt에 따른 날짜 내림
SYSDATE	현재 날짜 반환

 

 

4-1.  D 날짜에 N 날짜 더하기

ADD_MONTHS('11-JAN-23',6)
==> Result : '11-JUL-23'

 

4-2. D1 날짜와 D2 날짜 사이의 달 수

MONTHS_BETWEEN('01-SEP-24','11-JAN-23')
==>Result : 19.677419

 

 

5. 날짜 → 문자열로 변환 : TO_CHAR(날짜,'출력 모양') 

출력모양	의미	결과
'YYYY'	연도를 4자리로 표현	2023
'YY'	연도를 2자리로 표현	23
'MM'	월을 숫자로 표현	07
'MON'	유닉스에서는 월을 영문으로 표현 윈도우에서는 월을 한글로 표현	JUL / 7월
'MONTH'	월을 뜻하는 이름 전체를 표현	JULY 7월
'DD'	일을 숫자 2자리로 표현	15
'DAY'	유닉스에서는 요일을 영문으로 표현 윈도우에서는 요일을 한글로 표현	SUN /토요일
'DDTH'	몇 번째 날인지 표현	15TH
'HH24'	하루를 24시간으로 표현	19
'HH'	하루를 12시간으로 표현	07
'MI'	분으로 표현	56
'SS'	초로 표현	41

 

 

6. 숫자형 → 문자형으로 변환 TO_CHAR(숫자 ,'출력모양')

종류	의미	사용예	결과
9	9의 개수 자리 수 만큼 표시 (5자리까지 표시)	to_char(9876,'99999')	9876
0	빈자리를 0으로 채우기	to_char(9876,'099999')	009876
$	$ 표시를 붙여서 표시	to_char(9876,'$99999')	$9876
.	소수점 이하를 표시	to_char(9876,'99999.99')	9876.00
,	천 단위 구분기호를 표시	to_char(9876,'99,999')	9,876

 

 

---

 

 

그룹 함수

• 여러행으로부터 하나의 결과값을 반환
• 집계함수, 그룹함수, 복수행 함수

1. count( )

함수에 입력되는 데이터의 총 건수를 구하는 함수

• count(*) → NULL 포함
• count(컬럼명) → NULL포함하지 않음.

 

2. avg( )

평균값을 반환합니다.

• 입력된 값들의 평균값을 구하는 함수
• 주의: null 값이 있는 경우 빼고 계산함 – nvl 함수와 같이 사용

  select count(*), sum(salary), avg(nvl(salary,0))
  from employees
  

• avg(nvl(salary,0))

 

 

3. max( ) / min( )

최대값 / 최소값을 반환합니다.

• 입력된 값들중 가장 큰값/작은값 을 구하는 함수
• 여러건의 데이터를 순서대로 정렬 후 값을 구하기때문에 데이터가 많을 때는 느리다 (주의해서 사용)

 

4. sum( )

합계를 반환합니다