ORACLE SQL

단일행 함수

마이구미2 2021. 2. 26. 18:53

테이블 없이 연산하기

SELECT 함수 FROM DUAL;

 

숫자함수

 

ROUND

지정한 소수점 자리로 반올림

ROUND(1987.6543) = 1988
ROUND(1987.6543, 2) = 1987.65
ROUND(1987.6543, 1) = 1987.7
ROUND(1987.6543, 0) = 1987
ROUND(1987.6543, -1) = 1990
ROUND(1987.6543, -2) = 2000
ROUND(1987.6543, -3) = 2000
ROUND(1912.1234, -2) = 1900
ROUND(1912.1234, -3) = 2000

 

TRUNC

지정한 소수점 자리까지 남기고 값을 버림

TRUNC(1987.6543) = 1987
TRUNC(1987.6543, 2) = 1987.65
TRUNC(1987.6543, -1) = 1980

 

CEIL

지정값보다 큰 수 중 가장 작은 정수

FLOOR

지정값보다 작은 수 중 가장 큰 정수

CEIL(6.22) = 7
FLOOR(6.22) = 6

 

MOD

M을 N으로 나눈 나머지

MOD(11, 4) = 3

 

문자함수

 

LOWER

문자열 전체 소문자 반환

LOWER('koREA') = korea

 

UPPER

문자열 전체 대문자 반환

UPPER('koREA') = KOREA

 

INITCAP

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

INITCAP('koREA') = KoREA

 

CONCAT

문자 두 개 연결

CONCAT('밥을', '먹었다') = 밥을 먹었다
CONCAT(CONCAT('나는', '밥을'), '먹었다') = 나는 밥을 먹었다

 

SUBSTR

문자열의 일부 추출

SUBSTR(문자열데이터, 시작위치, 추출길이)

SUBSTR('치즈돈까스', 2, 3) = 즈돈까
SUBSTR('치즈돈까스', 2, 6) = 즈돈까스

 

LENGTH

글자 수를 인수로 변환

LENGTHB

글자 수를 바이트수로 변환

LENGTH('돈까스') = 3
LENGTHB('돈까스') = 6

 

INSTR

문자열 중 특정 문자의 위치 반환

INSTR(문자열데이터, 특정문자, 시작위치(기본값: 1), 특정문자위치(기본값: 1))

INSTR('아빠곰은뚱뚱해', '은') = 4
INSTR('아빠곰은뚱뚱해', '뚱') = 5
INSTR('아빠곰은뚱뚱해', '아', 2) = 0
INSTR('아빠곰은뚱뚱해', '뚱', 2, 2) = 6
INSTR('아빠곰은뚱뚱해', '뚱', 6, 2) = 0

 

REPLACE

특정 문자를 다른 문자로 대체

REPLACE([문자열데이터|컬럼명], 특정문자, 대체문자(기본값: 삭제))

REPLACE('아빠곰은뚱뚱해', '뚱', '*') = 아빠곰은**해
REPLACE('아빠곰은뚱뚱해', '뚱') = 아빠곰은해

 

LPAD | RPAD (Left Padding | Right Padding)

길이를 지정하여 빈 공간을 특정 문자로 채움

LPAD([문자열데이터|컬럼명], 총데이터길이, 채움문자(기본값: ' '))
RPAD([문자열데이터|컬럼명], 총데이터길이, 채움문자(기본값: ' '))

LPAD('햄버거', 5, '*') = **햄버거
RPAD('햄버거', 5, '*') = 햄버거**

LPAD('햄버거', 5) =   햄버거
RPAD('햄버거', 5) = 햄버거

-- 마스킹 [010-1234-5678 => 010-1234-****]
RPAD(SUBSTR('010-1234-5678', 1, 9), LENGTH('010-1234-5678'), '*') = 010-1234-****
SUBSTR('010-1234-5678', 1, 9) || LPAD('*', LENGTH('010-1234-5678') - 9, '*') = 010-1234-****

 

TRIM | LTRIM | RTRIM

특정 문자 제거

TRIM(삭제옵션(기본값: BOTH) 제거문자(기본값: ' ') FROM 문자열데이터)

TRIM(' 다시합창하자구 ') = 다시합창하자구
TRIM('다' FROM '다시합창하자구') = 시합창하자구
TRIM('다' FROM '다시합창합시다') = 시합창합시

-- 삭제옵션 (BOTH: 양쪽제거 | LEADING: 왼쪽제거 | TRAILING: 오른쪽제거)
TRIM(BOTH '다' FROM '다시합창합시다') = 시합창합시
TRIM(LEADING '다' FROM '다시합창합시다') = 시합창합시다
TRIM(TRAILING '다' FROM '다시합창합시다') = 다시합창합시
TRIM(TRAILING '다' FROM '다시합창합시다 ') = 다시합창합시다

LTRIM(문자열데이터, 제거문자열(기본값: ' '))
RTRIM(문자열데이터, 제거문자열(기본값: ' '))

LTRIM(' 다시합창합시다 ', '다') = 다시합창합시다
LTRIM('다시합창합시다', '다') = 시합창합시다
LTRIM('다시합창합시다', '다시') = 합창합시다
LTRIM('다시합창합시다', '다합') = 시합창합시다

RTRIM(' 다시합창합시다 ', '다') =  다시합창합시다
RTRIM('다시합창합시다', '다') = 다시합창합시
RTRIM('다시합창합시다', '다시') = 다시합창합
RTRIM('다시합창합시다', '시다') = 다시합창합시다
RTRIM('다시합창합시다', '다합') = 다시합창합시

 

날짜함수

 

날짜데이터의 연산

날짜데이터 + 숫자: 숫자만큼 일수 이후의 날짜

날짜데이터 - 숫자: 숫자만큼 일수 이전의 날짜

날짜데이터 - 날짜데이터: 두 날짜데이터 간의 일수 차이

날짜데이터 + 날짜데이터: 연산 불가

 

SYSDATE

시스템의 현재 날짜와 시간 확인

SYSDATE = 2018-07-03 오후 11:47:43
SYSDATE + 1 = 2018-07-04 오후 11:47:43
SYSDATE - 1 = 2018-07-02 오후 11:47:43

 

ADD_MONTHS

특정 날짜에 지정한 개월 수 이후 날짜 반환

ADD_MONTHS(날짜데이터, 더할개월수(정수))

SYSDATE = 21/02/26
ADD_MONTHS(SYSDATE, 4) = 21/06/26
ADD_MONTHS(SYSDATE, 120) = 31/06/26

 

MONTHS_BETWEEN

두 날짜 사이의 개월 계산

MONTHS_BETWEEN(날짜데이터1, 날짜데이터2)

HIREDATE = 20/07/12
SYSDATE = 21/02/26
MONTHS_BETWEEN(HIREDATE, SYSDATE) = -7.45
MONTHS_BETWEEN(SYSDATE, HIREDATE) = 7.45
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) = 7

 

NEXT_DAY

특정 날짜를 기준으로 돌아오는 요일의 날짜 반환

LAST_DAY

특정 날짜가 속한 달의 마지막 날짜 반환

NEXT_DAY(날짜데이터, 요일문자)
LAST_DAY(날짜데이터)

SYSDATE = 21/02/26
NEXT_DAY(SYSDATE, '월요일') = 21/03/01
LAST_DAY(SYSDATE) = 21/02/28

 

- 날짜데이터 기준 포맷 값

  포맷 모델 기준 단위
세기 CC, CCC 네 자리 연도의 끝 두 자리를 기준으로 사용
(2016년이면 2050 이하이므로, 반올림할 경우 2001년으로 처리
년도 SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y 날짜데이터의 해당 연, 월, 일의 7월 1일을 기준
(2016년 7월 1일 일 경우, 2017년으로 처리)
년도 IYYY, IYY, IY, I ISO 8601에서 제정한 날짜 기준년도 포맷을 기준
분기 Q 각 분기의 두번째 달의 16일 기준
월 MONTH, MON, MM, RM 각 달의 16일 기준
주차 WW 해당 연도의 몇 주(1~53번째 주)를 기준
주차 IW ISO 8601에서 제정한 날짜 기준 해당 연도의 주(week)를 기준
주차 W 해당 월의 주(1~5번째 주)를 기준
일, 일차 DDD, DD, J 해당 일의 정오(12:00:00)를 기준
요일 DAY, DY, D 한 주가 시작되는 날짜를 기준
시간 HH, HH12, HH24 해당일의 시간을 기준
분 MI 해당일 시간의 분을 기준

 

형 변환 함수

 

TO_CHAR

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

TO_NUMBER

문자 데이터를 숫자 데이터로 변환

TO_DATE

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

 

숫자데이터
(NUMBER)		 →
←		 문자데이터
(CHARATER)		 →
←		 날짜데이터
(DATE)

* 문자 데이터를 중심으로 숫자 또는 날짜 데이터의 변환 가능

 

TO_CHAR(날짜데이터, '문자형태', 'NLS_DATE_LANGUAGE = 언어')

SYSDATE = 21/02/26
TO_CHAR(SYSDATE, 'YYYY-MM-DD') = 2021-02-26
TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = ENGLISH') = FEBRUARY
TO_CHAR(SYSDATE, 'DY', 'NLS_DATE_LANGUAGE = ENGLISH') = FRI
TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') = 10:03:25 오전
TO_CHAR(283.568, '$999.99') = $283.57


TO_NUMBER(문자열데이터, '숫자형태')

TO_NUMBER('1879.5') = 1879.5
TO_NUMBER('1879.5', '9999.9') = 1879.5
-- 오류 나는 경우
TO_NUMBER('1879.5', '9999.999) = 1879.5
TO_NUMBER('200') + TO_NUMBER('300') = 500


TO_DATE(문자열데이터, '날짜형태')

TO_DATE('19970323', 'YY-MM-DD') = 97/03/23

 

- TO_CHAR 형식 지정

형식 설명
CC 세기
YYYY, RRRR 연(4자리 숫자)
YY, RR 연(2자리 숫자)
MM 월(2자리 숫자)
MON 월(언어별 월 이름 약자)
MONTH 월(언어별 월 이름 전체)
DD 일(2자리 숫자)
DDD 1년 중 며칠 (1~366)
DY 요일(언어별 요일 이름 약자)
DAY 요일(언어별 요일 이름 전체)
W 1년 중 몇 번째 주 (1~53)
HH24 24시간으로 표현한 시간
HH, HH12 12시간으로 표현한 시간
MI 분
SS 초
AM, PM, A.M., P.M. 오전, 오후 표시
9 숫자의 한 자리 의미(빈 자리 채우지 않음)
0 빈 자리를 0으로 채움을 의미
$ 달러($) 표시를 붙여서 출력
L L(Locale) 지역 화폐 단위 기호를 붙여서 출력
. 소수점 표시
, 천 단위의 구분 기호 표시

 

NULL 처리 함수

 

NVL

대상의 값이 NULL인 경우 지정값 반환, NULL이 아닌 경우 대상값 반환

NVL2

대상의 값이 NULL인 경우 지정값2 반환, NULL이 아닌 경우 지정값1 반환

NULLIF

데이터1과 데이터2가 동일한 경우 NULL 반환, 동일하지 않은 경우 데이터1 반환

NVL(데이터|컬럼명, 지정값)
NVL2(데이터|컬럼명, 지정값1, 지정값2)
NULLIF(데이터1, 데이터2)

NULLIF('A', 'A') = (null)
NULLIF('A', '1') = 'A'

NVL(NULLIF('A', 'A'), '두개가 다르면 NULL') = 두개가 다르면 NULL
NVL(NULLIF('A', '1'), '두개가 다르면 NULL') = A

 

COALESCE

NULL이 아닌 가장 앞에 위치한 데이터 반환

COALESCE(데이터1, 데이터2, 데이터3, ... )

COALESCE(NULL, NULL, NULL, NULL, 100, 200, NULL) = 100

 

DECODE함수

데이터와 조건N이 일치하는 경우 반환값N 반환, 아무 조건과 일치하지 않는 경우 반환값(N+1) 반환

DECODE([데이터|컬럼명|연산|함수],
	조건1, 반환값1,
    조건2, 반환값2,
    ...
    조건N, 반환값N,
    반환값(N+1))
 
-- DECODE와 NVL|NVL2
DECODE('A', null, 0, 'A') = NVL('A', 0)
DECODE('A', null, 'X', 'O') = NVL2('A', 'X', 'O')

 

CASE문

조건N이 참인 경우 반환값N 반환, 아무 조건도 참이 아닌 경우 반환값(N+1) 반환

CASE [데이터|컬럼명|연산|함수]
WHEN 조건1 THEN 반환값1
WHEN 조건2 THEN 반환값2
...
WHEN 조건N THEN 반환값N
ELSE 반환값(N+1)

 

* DECODE 와 CASE 비교 

DECODE는 함수, CASE는 STATEMENT

DECODE는 등가 연산만 가능, CASE는 비교 연산 가능

조건별로 동일한 자료형의 데이터를 반환해야 함

단순한 쿼리에서는 크게 성능 차이 없음

NULL과 NULL 비교 시 DECODE는 참, CASE는 거짓을 반환 (CASE문으로 NULL 비교 연산 시 쿼리 작성 주의)