[2회차-①] ORACLE (내장 함수)

강의명 : 오픈프레임워크 활용 디지털융합 SW엔지니어 양성 과정

강의 날짜 : 21.04.27

 

<내장함수>

 

DB에는 DUAL테이블이라고 하는 가상 테이블이 존재한다.

이 테이블은 산술 연산이나 가상 컬럼의 값을 한 번만 출력하고 싶을 때 사용하는 테이블이다.

이 테이블은 DEFAULT로 DUMMY라고 하는 속성(컬럼)을 가지고 있다.

그 안에 데이터가 들어가 있지는 않다.

 

* NULL? 컬럼은 NULL값의 불가능 여부를 묻는 컬럼이다.

값이 없으면 NULL값 삽입이 가능하다.

 

1. 수치 함수

함수	설명
FLOOR	소수점 이하는 모두 버림
CEIL	소수점을 기준으로 올림
ROUND	표시 자리수 기준 반올림
TRUNC	표시 자리수 기준 버림

 

* ROUND 함수

 

① ROUND (345.678, 1)

→ 345.7

[1] 자리까지 표기하고 그 밑에서 반올림

 

② ROUND (345.678)

→ 346

DEFAULT 자리 값이 0이기 때문에 [0]자리까지 표기하고 그 밑에서 반올림

 

③ ROUND (345.678, -1)

→ 350 

[-1] 자리까지 표기하고 밑에서 반올림. 단위는 맞춰야하므로 그 밑은 0으로 표기한다.

 

 

* TRUNC 함수

 

① TRUNC (345.678, 1)

→ 345.6

[1] 자리까지 표기하고 그 밑에서 버림

 

② TRUNC (345.678)

→ 345

DEFAULT 자리 값이 0이기 때문에 [0]자리까지 표기하고 그 밑에서 버림

 

③ TRUNC (345.678, -1)

→ 340 

[-1] 자리까지 표기하고 밑에서 버림. 단위는 맞춰야하므로 그 밑은 0으로 표기한다.

 

2. 나머지 함수 MOD

 : JAVA의 %연산자와 같은 역할을 한다.

 

 

MOD(a, b)

: a를 b로 나눠서 나온 나머지를 반환한다.

배수 계산할 때 많이 사용되므로 숙지할 것.

 

 

3. 날짜 함수

 

* SYSDATE 함수

소괄호가 생략된 날짜 함수이다.

현재 날짜을 반환해준다.

 

회원가입시 고객이 가입 버튼을 누르면 SYSDATE함수를 함께 호출해서 DB에 담게 하는 등으로 사용된다.

 

 

* 날짜 함수 연산

날짜 함수는 작은 따옴표(' ')로 묶는 형태이지만 수치 계산이 가능하다는 점에 주의해야 한다.

 

 

월 단위까지 표시하므로 일 단위에서 반올림한다.

한달 평균은 30일이고 , 

1 ~ 15 : 버림

16 ~ 30 : 올림

 

일 단위에서 반올림하므로 일 단위의 기본값은 01일이 된다.

 

 

* YEAR

만약 연 단위로 표시한다면, 월 단위에서 반올림한다.

월은 12개월이므로 

1월 1일 ~ 6월 30일 : 버림

7월 1일 ~ 12월 31일 : 올림

 

 

4. 문자 처리

문자열 처리 함수	설명
LOWER	문자열 소문자 처리
UPPER	문자열 대문자 처리
INITCAP	문자열 단어의 첫 글자만 대문자 처리
CONCAT	문자열 결합
LENGTH	글자 수 반환
LENGTHB	바이트 수 반환
SUBSTR	문자열 글자 추출
SUBSTRB	문자열 바이트 추출
INSTR	문자열에서 인덱스 반환
INSTRB	바이트 단위 문자열에서 인덱스 반환
LPAD	왼쪽을 채움
RPAD	오른쪽을 채움
LTRIM	왼쪽 해당 문자열 제거
RTRIM	오른쪽 해당 문자열 제거
TRIM	양쪽 해당 문자열 제거

 

* LOWER (문자열 소문자 처리)

 

* UPPER (문자열 대문자 처리)

 

* INITCAP (단어 첫 글자만 대문자 처리)

 

* CONCAT (문자열 결합) : JAVA의 concat메소드와 기능이 같다.

 

* LENGTH와 LENGTHB : 영단어의 경우

영어와 숫자는 1바이트씩 차지한다.

 

* LENGTH와 LENGTHB : 한글의 경우

유일하게 받침이 있는 한글은 1바이트로 표현하는데 무리가 있어서 3바이트를 제공하는 UTF-8코드를 사용한다.

그러므로 한글의 한 글자는 3바이트를 차지하는 것이다.

 

* SUBSTR (문자열 추출)

SUBSTR(문자열, 시작 인덱스, 글자 개수)

 

*RDBMS의 최초 인덱스 번호 : 1

 JAVA의 최초 인덱스 번호 : 0

 

* SUBSTRB (바이트 단위 문자열 추출)

 

 

문자열 처리 함수는 글자 단위, 바이트 단위가 존재한다.

한글은 3바이트를 차지하므로 유의할 것.

 

* 모든 언어 공통

- 문자열을 인덱스로 처리

- 왼쪽부터 바라보는 양의 인덱스

- 역으로 바라보는 음의 인덱스

 

* INSTR (인덱스 반환)

DATABASE라는 문자열에서 B의 인덱스를 반환해준다. 

RDBMS는 인덱스가 1부터 시작하므로 인덱스 5를 반환해준다.

 

DATABASE라는 문자열에서 A라는 문자를 찾는데,

3번 인덱스부터 탐색을 시작하며 2번째로 만나는 A의 인덱스를 반환한다.

 

* LPAD (문자열 채움)

PAD는 채워넣다는 의미를 가진다.

DATABASE라는 문자열을 20자리 크기에 오른쪽을 기준으로 넣고,

빈 나머지 왼쪽 부분을 '$'로 채워 넣으라는 의미이다.

RPAD는 반대라고 생각할 수 있다.

 

* TRIM (문자열 제거)

TRIM은 자바와 비슷하게 문자열에서 인수로 넘긴 문자에 해당하는 것을 양 끝에서 찾아 삭제해주는 연산이다.

LTRIM, RTRIM은 각각 왼쪽에서, 오른쪽에서 그 연산을 진행한다.

 

 

5. 자료형 변환

 

* 날짜형 자료를 문자형 자료로 변환해주는 함수는 TO_CHAR함수이다.

 

SYSDATE라는 DATE 자료형 데이터를 'YYYY-MM-DD'라는 형식에 맞춰서 문자열 자료형으로 바꿔주었다.

 

날짜 FORMAT	의미
YYYY	연도
MM	월
DD	일
D	요일
DAY	요일(각 나라에 맞춰 그 언어로 나옴)
HH24	시간(24시간제)
HH	시간(12시간제)
MI	분
SS	초
AM or PM	오전, 오후

 

* TO_DATE

'2014/1/2'이라는 문자열을 TO_DATE함수로 DATE 자료형 데이터로 바꿔주었다.

 

숫자를 날짜 자료형 자료로 변환할 수 있다.

 

 

* MONTHS_BETWEEN

해당 날짜 사이의 월 차이를 계산하여 반환해 준다.

 

 

* 숫자형 자료를 문자열로 변환

 

FOTMAT	의미
0	유효한 값을 찍은 후, 무효한 자리에 0을 집어넣는 것
9	유효한 값만 찍고, 무효한 자리는 공백

 

123456이라는 NUMBER자료형를 '000,000,000' 형식에 맞춰서 문자열로 변환해준다.

그 후 123,456을 찍은 후 남은 자리는 0으로 채워준다.

 

123,456이라는 NUMBER 자료형를 '999,999,999' 형식에 맞춰서 문자열로 변환해준다.

그 후 123,456을 찍은 후 남은 후 남은 자리(무효한 자리)는 공백으로 채워준다.

 

 

* 화폐 단위를 찍어주는 기호 L

정수를 문자열로 변환할 때 형식 앞에 L을 찍으면 해당 나라에 맞는 화폐 단위를 찍어준다.

화폐 단위와 유효 숫자 사이에 공백은 무시한다.

 

 

* TO_NUMBER 

숫자형 자료로 변환해주는 함수이다.

문자열 자료를 형식 인자에 맞춰서 숫자로 변환해준다.

형식 인자의 , 를 기준으로 문자열을 분해하고 유효 숫자를 채우는 형식인자에 맞춰서 NUMBER로 변환한다.

 

 

6. 오라클의 자료형

자료형	설명
NUMBER	정수와 실수를 모두 NUMBER로 표현
VARCHAR2	문자열
DATE	날짜형

* NUMBER는 정수와 실수를 모두 표현할 수 있는 자료형이다.

NUMBER(총 자리 수, 소수점 이하 자리수) : DOUBLE

NUMBER(총 자리 수) : INTEGER

 

* MYSQL이 VARCHAR, 오라클이 VARCHAR2!!

 

 

7. NVL

 

NULL은 연산이 불가능하다.

그러므로 NULL값을 처리해주는 함수가 필요하다.

 

NVL(컬럼명, 대체값)

첫 번째 인자는 처리할 대상을 의미한다.

해당 컬럼 안에 있는 값이 NULL이면 대체값으로 값을 대체해주고,

만약 NOT NULL이면 자기가 가지고 있는 값으로 표현한다.

 

연산이 불가능했던 NULL을 연산에 포함시키기 위해 이러한 처리를 해준다.

 

JAVA와 연동하여 NULL EXCEPTION이 안 뜨게 하려면 활용하는 것이 좋다.

 

 

8. DECODE 함수

: JAVA의 switch-case 혹은 삼항연산자와 닮았다.

 

마지막에 어느 곳에도 속하지 않는 경우의 DEFAULT값도 넣어줄 수 있다.

 

 

9. CASE WHEN함수

 

 

if문 중첩과 비슷하다.

ELSE 뒤에 DEFAULT값을 줄 수 있고,

END로 함수를 끝내며 그 뒤에 별칭을 줄 수 있다.