[SQL-오라클] SQL 함수 - 문자, 숫자, 날짜, 변환, 일반

SQL 함수

- 칼럼의 값이나 데이터 타입을 변경하는 경우 사용

- 숫자, 날짜 데이터의 출력 형식 변경하는 경우 사용

- 하나 이상의 행에 대한 집계를 수행하는 경우 사용

 

SQL 함수 유형

1) 단일행 함수

테이블에 저장되어 있는 개별 행을 대상으로 함수를 적용하여 하나의 결과를 반환하는 함수

문자, 숫자, 날짜 데이터 타입에 적용할 수 있는 함수와 데이터 타입을 변환하기 위한 함수 존재

 

2) 복수행 함수

조건에 따라 여러 행을 그룹화하여 그룹별로 결과를 하나씩 반환하는 함수

 

 

---

단일행 함수

- 데이터 값을 조작하는데 주로 사용

- 행별로 함수를 적용하여 하나의 결과를 반환하는 함수

-

여러 개의 인수를 사용할 수 있고, 상수/변수/칼럼이름/표현식을 인수로 사용 가능

-

SQL 명령여문의 select절, where절, order by절에서 사용 가능

- 중첩하여 사용 가능

단일행 함수명 (칼럼명|문자 또는 표현식, [함수인수1, 함수인수2, ... ])

 

 

---

 

문자 함수

- 문자 데이터를 입력학여 문자나 숫자를 결과로 반환

- 대소문자 변환 함수, 문자조작 함수, 문자열길이 반환 함수 ...

 

1) 대소문자 변환 함수

종류	의미	사용예
INITCAP	문자열의 첫 번째 문자만 대문자로 변환	INICAP('student') -> Student
LOWER	문자열 전체를 소문자로 변환	LOWER('STUDENT') -> student
UPPER	문자열 전체를 대문자로 변환	UPEER('student') -> STUDENT

 

> INITCATP

첫 번째 영문자를 대문자로 변환

initcap(문자열|칼럼명)

 

> LOWER

문자열 전체를 소문자로 변환

lower(문자열|컬럼명)

 

> UPPER

문자열 전체를 대문자로 변환

upper(문자열|컬럼명)

---

2) 문자열 길이 반환 함수

종류	의미	사용예
LENGTH	문자열 길이를 반환	LENGTH('홍길동') -> 3
LENGTHB	문자열 바이트 수 반환	LEGNTHB('홍길동') -> 6

 

> LENGTH

문자열의 길이 반환

length(문자열|칼럼명)

 

> LENGTHB

문자열의 바이트 수 반환

lengthb(문자열|칼럼명)

---

3) 문자조작 함수

종류	의미	사용예
CONCAT	두 문자열을 결합, || 와 동일	concat('sql', 'plu') -> sqlplus
SUBSTR	특정 문자 또는 문자열 일부 추출	substr('sql*plus', 5, 4) -> plus
INSTR	특정 문자가 출현하는 첫 번째 위치 반환	instr('sql*plust', '*') -> 4
LPAD	왼쪽 정렬후 왼쪽에 지정 문자 삽입	lpad('sql', 5, '*') -> **sql
RPAD	오른 쪽정렬후 왼쪽에 지정 문자 삽입	rpad('sql', 5, '*') -> sql***
LTRIM	왼쪽의 지정 문자 삭제	ltrim('*sql', '*') -> sql
RTRIM	오른쪽의 지정 문자 삭제	rtrim('sql*', '*') -> sql

 

> SUBSTR

문자열의 일부를 추출

substr(문자열|칼럼명, 시작위치m, 추출개수n)

▪ 문자열에서 m번째 문자부처 n개의 문자 추출

▪ m이 음수면 시작위치는 문자열의 마지막

▪ n을 생랼하면 m부터 마지막 문자까지 추출

 

> INSTR

사용자가 지정한 특정 문자가 포함된 위치 반환 

위치는 바이트가 아닌 문자열에서의 순서 의미

일반적으로 INSTR 함수는 다른 함수와 중첩하여 사용

instr(문자열|칼럼명, '찾을문자' [,시작위치m [, 몇번째n])

▪ 문자열에서 찾을문자가 처음 나타나는 위치 반환

▪ m 생략시 처음부터, n 생략시 첫번째 값 (찾고자 하는 문자가 여러개 들어 있을 때 그 중 몇 번째) 

 

> LPAD, RPAD

문자열이 일정한 크기가 되도록 왼쪽 또는 오른쪽에 지정한 문자를 삽입

lpad(문자열|칼럼명, 바이트m [,특정문자]) #왼쪽
rpad(문자열|칼럼명, 바이트m [,특정문자]) #오른쪽

▪ 바이트m만큼 공백을 왼쪽 또는 오른쪽에 특수문자 삽입

▪ 특수문자 없으면 공백 삽입

 

> LTRIM, RTRIM

문자열에서 특정 문자를 삭제

함수의 인수에서 삭제할 문자 지정하지 않으면, 문자열의 앞뒤 부분에 있는 공백 삭제

ltrim(문자열|칼럼명, 바이트m [,특정문자]) #왼쪽
rtrim(문자열|칼럼명, 바이트m [,특정문자]) #오른쪽

▪ 문자열의 왼쪽/오른쪽에서 지정된 특정문자 삭제

 

 

---

 

숫자 함수

- 숫자 데이터 처리

종류	의미	사용예
ROUND	지정한 자리 이하에서 반올림	round(123.17, 1) -> 123.2
TRUNC	지정한 자리 이하에서 절삭	trunc(123.17, 1) => 123.1
MOD	m을 n으로 나눈 나머지	mod(12, 10) -> 2
CEIL	지정한 값보다 큰 수 중에서 가장 작은 정수	ceil(123.17) -> 124
FLOOR	지정한 값보다 작은 수 중에서 가장 큰 정수	floor(123.17) -> 123

 

> ROUND

지정한 자리 이하에서 반올림한 결과 값을 반환

ROUND(a, b)는 a를 소수점 이하 b+1 자리에서 반올림하여 b자리까지 출력

round(표현식|컬럼명, 자리수n)

▪ 소수점 이하 n+1자리에서 반올림

▪ 자리수n까지 표현하는 것!!!

▪ 자리수n 값이 없으면 소수점 이하 출력 x ( = 소수점 이하 반올림 -> 정수)

▪ 자리수n 값 음수면 소수점 왼쪽 b자리에서 반올림

 

> TRUNC

지정한 소수점 자리수 이하를 절삭한 결과 값을 반환

TRUNC(a, b)는 a를 소수점 이하 b+1 자리에서 절삭하여 b자리까지 출력

trunc(표현식|칼럼명, 자리수n)

▪ 소수점 이하 n+1자리에서 절삭

▪ 자리수n까지 표현하는 것!!!

▪ 자리수n 값이 없으면 소수점 이하 출력 x 

▪ 자리수n 값 음수면 소수점 왼쪽 b자리에서 절삭

 

 

+) 자리수

ex) 소수점 셋째 자리에서 반올림  = > round(컬럼명, 2)

소수점 자리 n까지 표현하는 것이라 외우면 쉬움

셋째 자리에서 반올림하면 둘째 자리까지 나타내는 것이므로  round(컬럼명, 2)

... n번째 자리에서 반올림 -> round(컬럼명, n-1)

 

> MOD

나누기 연산 후에 나머지를 출력

mod(m, n)

▪ m을 n으로 나눈 나머지

+ ) 짝수 : mod(m,n) = 0  /  홀수 : mod(m,n) = 1

 

> CEIL

지정한 숫자보다 크거나 같은 수 중 가장 작은 정수

ceil(표현식|칼럼명)

▪ ceil 천장  -> 보다 큰 숫자 중 가장 작은

ex) ceil(19.7) => 20 

 

> FLOOR

지정한 숫자보다 작거나 같은 수 중 가장 큰 정수

floor(표현식|칼럼명)

▪ floor 바닥  -> 보다 작은 숫자 중 가장 큰

ex) floor(19.7) => 19

 

---

 

날짜 함수

- 날짜 데이터 타입에 사용

- 오라클에서는 날짜 데이터를 세기, 연도, 월, 일, 시, 분, 초를 숫자 형식으로 저장

 

1) 날짜 계산

날짜 데이터에 더하기, 뺴기의 연산

- 날짜에서 숫자를 더하거나 뺀 결과 날짜

- 날짜에서 날짜를 빼면 두 날짜 사이의 기간 계산

- 기본적인 단위는 일(day)단위

- 시간연산 숫자/24

종류	결과	의미
날짜 + 숫자	날짜	날짜에 일수를 가산하여 날짜 계산
날짜 - 숫자	날짜	날짜에 일수를 감산하여 날짜 계산
날짜 - 날짜	일수	날짜와 날자를 감산하여 일수 계산
날짜 + 숫자/24	날짜	날짜에 시간을 가산하여 날짜 계산

 

2) 날짜 함수

날짜 데이터를 입력하여 날짜 또는 기간을 결과 값으로 반환

날짜 계산의 기본단위는 일이지만, 날짜 함수를 이용하여 월 단위 계산 가능

종류	의미	결과
SYSDATE	시스템의 현재 날짜	날짜
MONTHS_BETWEEN	날짜와 날짜사이의 개월 계산	숫자
ADD_MONTHS	날짜에 개월을 더한 날짜 계산	날짜
NEXT_DAY	날짜후의 첫 요일의 날짜 계산	날짜
LAST_DAY	월의 마지막 날짜 계산	날짜
ROUND	날짜 반올림	날짜
TRUNC	날짜 절삭	날짜

 

> SYSDATE

시스템에 저장된 현재 날짜 반환

초 단위까지 반환

 

> MONTHS_BETWEEN

날짜와 날짜 사이의 개월 수 출력

months_between(종료날짜, 시작날짜)

▪ 종료날짜와 시작날짜 사이의 개월 수 계산

 

> ADD_MONTHS

특정 개월 수를 더한 날짜 출력

add_months(날짜, 개월 수)

▪ 날짜에 개월 수 더한 날짜

 

> LAST_DAY

해당 날짜가 속한 달의 마지막 날짜 반환

last_day(날짜)

▪ 날짜가 속한 달의 마지막 날짜

 

> NEXT_DAY

해당 일을 기준으로 명시된 요일의 다음 날짜 반환

next_day(날짜, '요일')

▪  날짜 이후 첫번째 '요일'의 날짜 계산

 

> ROUND (반올림)

일 : 정오를 넘으면 다음 날 자정(12:00:00), 넘지 않으면 그날 자정 출력

월 : 15일 이상이면 다음 달 1일, 넘지 않으면 그달 1일 출력

년 : 6월 넘으면 다음 해 1월 1일, 넘지 않으면 그해 1월 1일 출력

round(날짜[,'포멧'])

 

> TRUNC (절삭)

일 : 그날 자정(00:00:00) 출력

월 : 그 달 1일 출력

년 : 그 해 1월 1일 출력

trunc(날짜[,'포맷'])

 

---

 

데이터 타입 변환

- 숫자나 날짜 타입을 문자와 함께 결합하거나, 보고서 양식 맞추기 위해 사용

- 숫자, 날짜 -> 문자    /    문자 -> 숫자      /     문자 -> 날짜로 변환

 

1) 묵시적인 데이터 타입 변환

정확한 연산을 위해 오라클에서 데이터 타입을 내부적으로 변환하는 경우

내부적인 변환으로 인해 SQL 명령문 검색 속도 느려질 수 있음

where a=b에서 비교되는 데이터 타입이 다른 경우

A의 데이터 타입	B의 데이터 타입	변환 결과
NUMBER	VARCHAR2  또는 CHAR	B가 NUMBER 타입으로 변환
VARCHAR2  또는 CHAR	NUMBER	A가 NUMBER 타입으로 변환

▪  문자 타입의 숫자 타입으로 변환은 문자열이 숫자로 구성된 경우만 가능

 

2) 명시적인 데이터 타입 변환

사용자가 데이터 타입 변환함수를 통해 명시적으로 데이터 타입 변환

종류	의미	사용예	결과
TO_CHAR	숫자/날짜 -> 문자	TO_CHAR('05/03', 'YYYY-MM')	2005-03
TO_NUMBER	문자 -> 숫자	TO_NUMBER(1000, '9,999')	1,000
TO_DATE	문자 -> 날짜	TO_DATE('05/03', 'YYYY-MM')	2005-03

 

> TO_CAHR (날짜 -> 문자)

날짜나 숫자를 문자로 변환

to_char(숫자|날짜, '포맷')

 

● 날짜 출력 형식 변환

퍼스널 오라클 날짜 표준 형식 'YY/MM/DD'

날짜에 대한 출력 형식을 별도로 지정하지 않으면 표준 형식으로 출력

출력 형식 변경하거나 구체적인 시간 정보를 출력하기 위해 TO_CHAR 함수 사용

 

● 시간 표현 형식

날짜 데이터에서 포함하고 있는 시간 정보를 표시하기 위해 사용

시간을 지정하지 않을 경우 기본값은 '12:00 AM'

종류	의미
AM 또는 PM	오전(AM)과 오후(PM) 시각 표시
A.M 또는 P.M	오전(A.M)과 오후(P.M) 시각 표시
HH 또는 HH12	시각(1-12)
HH24	24시각(0-23)
MI	분
SS	초

 

● 기타 날짜 표현 형식

날짜 출력 형식에서 하이픈(-), 슬래시(/), 콤마(,)와 같은 특수 문자도 포함 가능

종류	의미
"text"	결과와 함께 출력할 문자열을 인용 부호로 표시
TH	서수로 표시
SP	숫자(기수)를 영문으로 표시
SPTH 또는 THSP	서수를 영문으로 표시

 

 

> TO_CHAR (숫자 -> 문자)

다양한 출력 형식을 지정하여 숫자를 문자 데이터로 변환

 

 

> TO_NUMBER

숫자로 구성된 문자열을 숫자 데이터로 변환

to_number(문자열)

 

> TO_DATE

숫자와 문자로 구성된 문자열을 날짜 데이터로 변환

날짜 타입과 문자 타입을 비교할 때 문자 타입을 날짜 타입으로 통일하기 위해 사용

문자열을 날짜 데이터로 변환하여 날짜 데이터 타입 칼럼에 저장하기 위해 사용

to_date(문자, '포맷')

 

---

 

중첩 함수

- 함수를 여러 개 겹쳐서 사용하는 방법

- 단일행 함수는 중첩하여 실행 가능

- 중첩된 함수의 순서 : 맨 안쪽에 있는 함수부터 실행하여 결과 값을 가장 가까운 바깥쪽 함수의 인수로 넘겨 처리

F3(F2(F1(컬럼명, 인수1), 인수2), 인수3)

▪  F1 함수의 결과 값은 F2의 인수로, F2의 결과 값은 F3의 인수로 사용

 

---

 

 

일반 함수

- NULL 관련 함수 : NVL, NVL2, NULLIF, COALESCE 

- DECODE, CASE

 

NULL 관련 함수

> NVL

NULL을 0또는 다른 값으로 변환하기 위한 함수

NVL 함수를 이용해 NULL을 숫자나 문자로 대체한 후 연산 수행 가능

NVL(칼럼exp1, 대체값exp2)

▪  exp1 : NULL을 포함하는 칼럼 또는 표현식

▪  exp2 : NULL을 대체하는 값

▪  exp1과 exp2는 반드시 동일하나 데이터 타입이어야 함

 

> NVL2

첫 번째 인수 값이 NULL이 아니면 두 번째 인수 값 출력

첫 번째 인수 값이 NULL이면 세 번째 인수 값 출력

nvl2(칼럼, null아닐때값, null일때값)

 

> NULLIF

두 개의 표현식을 비교하여 값이 동일하면 NULL 반환,

일치하지 않으면 첫 번째 표현식의 값 반환

nullif(일치x때값exp1, exp2)

▪ exp1, exp2 동일 - null   /  일치x - exp1

 

> coalesce

인수 중에서 NULL이 아닌 첫 번째 인수 반환

N개의 인수 중에서 첫 번째 인수 값이 NULL이면 두 번째 값을 출력,

두 번째 인수 값이 NULL이면 세 번째 인수 값 출력

coalesce(exp1, exp2, exp3, ... , expN)

▪ exp1 : exp1이 null이 아니면 exp1 반환

▪ exp2 : exp1이 null이고 exp2가 null 아니면 exp2 반환

▪ expN : exp1부터 expN-1까지 null이고 expN이 null 아니면 expN 반환

 

 

 

DECODE 함수

- 오라클에서만 유용한 SQL 함수

- IF문이나 CASE문으로 표현되는 복잡한 알고리즘을 하나의 SQL 명령문으로 간단하게 표현

- 비교 연산자 '='만 가능

decode(표현식|칼럼명, 찾을값1, 반환값1 [찾을값2, 반환값2, ..] [, 기본값])

▪ 표현식 또는 칼럼의 값이 찾을값1과 일치하면 반환값1 반환, 찾을값2와 일치하면 반환값2 반환

▪ 일치하는 값이 없거나 null인 경우에는 기본값 반환

▪ 기본값이 없을 경우 null 반환

 

 

 

CASE 함수

- DECODE함수 기능 확장

- 산술 연산, 관계 연산, 논리 연산과 같은 다양한 비교 연산 가능

- when절에서 표현식을 다양하게 정의 가능

case when 표현식|칼럼명 조건1 then 결과1
     [when 표현식|칼럼명 조건2 then 결과2
      when 표현식|칼럼명 조건3 then 결과3
      else 기본값]
end

 

 

 

 

 

 

---

참고도서 : 오라클 중심의 SQL배움터, 우용태 외 공저, 생능출판사, 2007년