[2장] SQL 단일 행 함수(2) - 숫자함수, 날짜함수, 형변환함수, 일반 함수

 
​
5. 숫자 함수 : 함수에 입력되는 값이 숫자인 함수
​ 

이  름	의  미	사용 예
ROUND	주어진 숫자를 반올림 한 후 출력함	ROUND(12.345,2) → 12.35
TRUNC	주어진 숫자를 버림한 후 출력함	TRUNC(12.345,2) → 12.34
MOD	주어진 숫자를 나누기 한 후 나머지 값을 출력함	MOD(12,10) → 2
CEIL	주어진 숫자와 가장 근접한 큰 정수 출력함	CEIL(12.345) → 13
FLOOR	주어진 숫자와 가장 근접한 작은 정수 출력함	FLOOR(12.345) → 12
POWER	주어진 숫자1의 숫자2 승을 출력함	POWER(3,2) → 9

 

​

​ 

 (1) ROUND 함수 : 주어진 숫자를 반올림 한 후 출력   [문법] ROUND(숫자, 원하는 자리 수)

​

​ (2) TRUNC 함수 : 주어진 숫자를 버림 한 후 출력   [문법] TRUNC(숫자, 원하는 자리 수)

​

 (3) MOD, CEIL, FLOOR 함수

​

      ​

 

 

 (4) POWER 함수 : 주어진 숫자1의 숫자2 승을 출력함   [문법] POWER(숫자1, 숫자2)

​

​

​

​

​6. 날짜 함수

​

​​- 10월 23일 + 3 은 10월 26일로 생각합니다.
- 10월 23일 – 3 은 10월 20일로 생각합니다.
- 10월 23일 – 10월 20일 은 3일 차이 난다 라고 생각합니다.

위 세가지 경우에서 알 수 있듯이 일반적으로 날짜 + 숫자 = 날짜로 인식하며, 날짜 - 숫자 = 날짜로 인식합니다.

그리고 날짜 - 날짜 = 숫자로 인식합니다.

 

함 수 명	의  미	결  과
SYSDATE	시스템의 현재 날짜와 시간	날짜
MONTHS_BETWEEN	두 날짜 사이의 개월 수	숫자
ADD_MONTHS	주어진 날짜에 개월을 더함	날짜
NEXT_DAY	주어진 날짜를 기준으로 돌아오는 날짜 출력	날짜
LAST_DAY	주어진 날짜가 속한 달의 마지막 날짜 출력	날짜
ROUND	주어진 날짜를 반올림	날짜
TRUNC	주어진 날짜를 버림	날짜

 
 

​

​ (1) SYSDATE 함수 : 현재 시스템의 시간을 출력해 주는 함수

​

 

 

​

​

​ (2) MONTHS_BETWEEN 함수 : 두 날짜를 입력 받아서 두 날짜 사이의 개월 수를 출력하는 함수 (윤달 계산을 못함)

1) 두 날짜 중에 큰 날짜를 먼저써야 양수가 나옴

2) ​두 날짜가 같은 달에 속해 있으면 특정 규칙으로 계산된 값이 나옴,

    단 1개월이 29일, 30일, 31일 일 경우 모두 값이 다르게 계산

3) 두 날짜가 모두 해당 월의 마지막 날이거나 처음 날이면 개월 수 가 정수 값으로 나옴

​4) 두 날짜 중 하나는 시작일이고 하나는 마지막 일이면 특정 규칙으로 계산된 값이 나옴

 

​

​​ (3) ADD_MONTHS 함수 : 주어진 날짜에 숫자만큼의 달을 추가하는 함수

​

 

 

​ 

 (4) NEXT_DAY 함수 : 주어진 날짜를 기준으로 돌아오는 가장 최근 요일의 날짜를 반환해주는 함수

​

 

 

 

 (5) LAST_DAY 함수 : 주어진 날짜가 속한 달의 가장 마지막 날을 출력해주는 함수​

​

 

 

 

 (6) 날짜의 ROUND, TRUNC 함수

 

 
​ ​ - 날짜의 ROUND는 낮(정오) 12:00:00 인데 주어진 날짜가 이 시간을 넘어설 경우에 다음 날짜로 출력하고

     이 시간이 안될 경우에는 당일로 출력합니다. 반면 날짜의 TRUNC 함수 는 무조건 당일로 출력합니다.

​

 

​

​

​7. 형 변환 함수

​

​- 오라클에서 사용하는 데이터 타입

​​

데이터 타입	설  명
CHAR(n)	고정길이의 문자를 저장합니다. 최대값은 2000 bytes
VARCHAR2(n)	변하는 길이의 문자를 저장합니다. 최대값은 4000 bytes
NUMBER(p,s)	숫자 값을 저장합니다.p 는 전체 자리수로 1-38 자리까지 가능하고 s는 소수점 이하 자리수로 -84 ~ 127 자리까지 가능
DATE	총 7Byte로 BC 4712년 1월 1일부터 AD 9999년 12월 31일까지의 날짜를 저장
LONG	가변 길이의 문자를 저장하며 최대 2GB 까지 저장
CLOB	가변 길이의 문자를 저장하며 최대 4GB 까지 저장
BLOB	가변 길이의 바이너리 데이터를 최대 4GB 까지 저장
RAW(n)	원시 이진 데이터로 최대 2000 bytes 까지 저장
LONG RAW(n)	원시 이진 데이터로 최대 2GB까지 저장
BFILE	외부 파일에 저장된 데이터로 최대 4GB 까지 저장

 

​

​​

​┌ 묵시적(자동) 형 변환 : 오라클이 자동으로 형 변환을 시킴

└ 명시적(수동) 형 변환 :​ 사용자가 수동으로 지정하여 줌

 

​

 

- 숫자 + '문자'를 계산했지만 자동으로 문자를 숫자로 바꾼 후 연산을 수행 함

- 내부적으로 SELECT 1 + to_number('1') FROM dual;을 수행      => 묵시적 형 변환

 

​​​

​

 

 

 

 

​

​ (1) TO_CHAR 함수(날짜를 문자로 형 변환하기)   [문법] TO_CHAR(원래 날짜, '원하는 모양')

​​

년도

YYYY - 연도를 4자리로 표현

RRRR - 2000년 이후 Y2K 버그로 인해 등장한 날짜 표기법 (권장)

YY - 연도를 끝에 2자리만 표시 (예 : 14)

RR - 연도를 마지막 2자리만 표시 (예 : 14)

YEAR - 연도의 영문 이름 전체를 표시

 

월

MM - 월을 숫자 2자리로 표현 (예 : 10)

MON - 유닉스용 오라클에서 월을 뜻하는 영어 3글자로 표시 (예 : OCT)

MONTH - 월을 뜻하는 영어 이름 전체를 표시

 

일

DD - 일을 숫자 2자리로 표시 (예 : 15)

DAY - 요일에 해당하는 명칭을 표시 유닉스=영문 , 윈도우=한글

DDTH - 몇 번째 날인지 표시

 

시간

HH24 - 하루를 24시간으로 표시

HH - 하루를 12시간으로 표시

MI - 분 표시

SS - 초 표시

 

 

​

​ (2) TO_CHAR 함수 (숫자를 문자로 형 변환하기)

 

종  류	의   미	사 용  예	결  과
9	9 하나당 1 자리를 의미	TO_CHAR(1234,'99999')	1234
0	빈자리를 0으로 표시	TO_CHAR(1234,'099999')	001234
$	$ 표시를 붙여서 표시	TO_CHAR(1234,'$9999')	$1234
.	소수점 이하를 표시	TO_CHAR(1234,'9999.99')	1234.00
,	천 단위 구분기호를 표시	TO_CHAR(12345,'99,999')	12,345

 

 

 (3) TO_NUMBER 함수   [문법] TO_NUMBER('1')

​

 (4) TO_DATE 함수   [문법] TO_DATE('문자', '날짜 포맷')

​

​

​

​

8. 일반 함수

​​

 (1) NVL 함수 : NULL 값을 만나면 다른 값으로 치환해서 출력하는 함수   [문법] NVL(칼럼, 치환할 값)

​

 ​* 치환값이 숫자일 경우

​​NVL(sal , 0) -> sal 컬럼의 값이 null 일 경우 null 대신 0 으로 치환하라
NVL(sal , 100) -> sal 컬럼의 값이 null 일 경우 null 대신 100 으로 치환하라

* 치환값이 문자일 경우

NVL( job , ‘무직’) -> job 값이 null 일 경우 ‘무직’ 으로 치환하라

*치환값이 날짜일 경우

NVL(hiredate,’sysdate’) -> hiredate 값이 없을 경우 sysdate 날짜로 치환하라

​

 

​ (2) NVL2 함수 : COL1의 값이 NULL 아니면 COL2를, NULL이면 COL3을 출력   [문법] NVL2(COL1, COL2, COL3)

 

​

​ (3) DECODE 함수 (=IF문, 오라클에만 있는 함수)

​

유형 1) ​A 가 B 일 경우 '1'  을 출력하는 경우

​

 

 

 

 

 ​유형 2) A 가 B 일 경우 '1' 을 출력하고 아닐 경우 '2' 를 출력하는 경우

​

​

 

​

​

​유형 3) A 가 B 일 경우 '1' 을 출력하고 A 가 C 일 경우 '2' 를 출력하고 둘 다 아닐 경우 '3' 을 출력하는 경우

 

 

 

 

 

유형 4) A 가 B 일 경우 중에서 A 가 C 를 만족하면 '1'을 출력하고 A 가 C가 아닐 경우 '2' 를 출력 하는 경우

            ( DECODE 함수 안에 DECODE 함수가 중첩되는 경우 )

 

 

​

유형 5) A 가 B 일 경우 중에서 A 가 C 를 만족하면 '1'을 출력하고 A 가 C가 아닐 경우 '2' 를 출력 하고

          A 가 B가 아닐 경우 '3' 을 출력하는 경우 ( DECODE 함수 안에 DECODE 함수가 중첩되는 경우 )

 

 

 

 

 

 (4) CASE 표현식

     : DECODE 함수는 주로 = 인 값을 처리하는데 사용이 됩니다. 즉 크거나 작은 조건을 처리 할 경우

       DECODE 함수는 불편한 부분이 많이  있는데 이런 부분을 CASE 표현 식에서는 쉽게 처리가 가능합니다.

 

 

[문법] CASE  조건  WHEN  결과1  THEN  출력1

                 [WHEN 결과2  THEN  출력2]

                 ELSE  출력3

END "컬럼명"

 

 

 
​

​

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.88-112​​