DBMS- 오라클 함수 1편

직접 내용 정리하고 만든 예이니 퍼가실 때는 출처를 남겨주세요 :)

※ 들어가기 전 공지사항

DBMS - 개요 편을 참고해 계정을 생성해주시고, 

DBMS - 개요

DBMS-Table&레코드와 시퀀스 편을 봐주시고 와주셔야 합니다.

DBMS-Table&레코드와 시퀀스

 

---

실습

1. 아래 조건에 부합하는 테이블을 만들어주세요.

테이블명 : test

번호(no) - 숫자형태

이름(name) - 고정 문자열 최대 한글 6글자

나이(age) - 숫자형태

주소(address) - 가변 문자열 최대 20글자

2. 아래 조건에 부합하는 시퀀스를 만들어주세요.

시퀀스 명 : test_seq

캐시 사이즈는 0, 시작 번호 5, 증가 간격 10으로 설정

3. 작업을 저장해주세요.

4. test를 삭제해주세요.

5. test_seq를 삭제해주세요.

conn oracle/oracle;
create table test(no number, name char(6),age number,address nvarchar2(20));
create sequence test_seq nocache start with 5 increment by 10;
commit;
drop table test;
drop sequence test_seq;

---

오라클 함수

▷ 실습 하기 전 알아야 할 개념부터 정리해보겠습니다.

• 정의 : 오라클 내부에서 기본적으로 제공해주는 함수
• 사용방법 : select문을 이용해 기능이 필요할 때마다 부릅니다.
• 종류 : 숫자함수, 문자함수, 날짜함수, 변환함수, NULL 함수, 수치함수

참고로, DUAL 테이블은 임시 테이블로서, 어떤 결과를 출력하기 위해서 임시적으로 만들어지는 테이블입니다.

(1) 숫자함수

ABS(n)	n의 절댓값을 구하는 함수입니다.
ceil(n)	실수값 n보다 같거나 큰 정수를 반환하는 함수입니다.
floor(n)	실수값 n보다 작거나 같은 정수를 반환하는 함수입니다.
round(n,i)	- n은 실수나 열 이름, i은 정수(반올림 할 자리 값) - n이 실수일 경우 n의 소수점 자리수를 i번째까지 출력하는 함수입니다. - i+1번째에서 반올림하는 함수입니다. - i을 입력하지 않으만 소수점 첫째 자리에서 반올림 합니다.
trunc(n,i)	- round랑 비슷하나, i+1번째에서 내림하는 함수입니다.
power(n1,n2)	n1을 n2번 거듭제곱하는 함수입니다.
mod(n1,n2)	n1을 n2로 나눈 나머지 값을 보여주는 함수로, reminder함수랑 동일합니다.
sign(n)	- n이 양수인지 음수인지 판별하는 함수입니다. - 양수면 1을 반환 음수면 -1을 반환 0이면 0을 반환합니다.

*ROUND와 turnc 함수에 i번째 위치를 알고 싶다면 아래의 예를 참고해주시길 부탁드립니다.

(2) 문자함수

문자열을 관리할 수 있는 함수입니다.

initcap(char)	문자열의 첫글자는 대문자로, 나머지는 소문자로 변환하는 함수입니다.
lower(char)	대문자를 소문자로 변환해주는 함수입니다.
upper(char)	소문자를 대문자로 변환해주는 함수입니다.
concat(char1, char2)	char1과 char2 붙여주는 함수입니다.
substr(char,pos,len)	char의 pos번째 문자부터 len길이만큼 잘라서 반환하는 함수입니다.
substrb(char,pos,byte)	char의 pos번째 문자부터 byte 길이만큼 잘라서 반환하는 함수입니다. 참고로, 한글은 한글자당 3byte입니다.
length(char)	문자열의 길이를 구하는 함수입니다.
lengthb(char)	문자열의 byte길이를 구하는 함수입니다.
ltrim(char1,char2)	- char1의 좌측부터 char2를 찾아서 삭제 후 변환하는 함수로, 1번만 삭제합니다. - char2를 생략해도 되지만 생략시 디폴트 값으로 ''으로 지정되어 있습니다. - char2를 넣으면 char2로 시작해야만 삭제를 합니다.
rstrim(char1, char2)	ltrim과 비슷하지만 왼쪽이 아니라 오른쪽부터 검색
trim(char)	키워드를 통해 왼쪽(leading) 오른쪽(trailing) 양쪽(both)을 지정하면 공백을 제거할 수 있습니다. 디폴트값은 양쪽입니다.
LPAD(char1,n,char2)	char1의 왼쪽부터 char2를 채웁니다. 이 때, n은 문자열의 자릿수를 말합니다. char2를 생략하게 되면 ''가 디폴트값입니다.
RPAD(char1,n,char2)	LPAD와 동일하지만 오른쪽부터 채웁니다.
ASCII(char)	문자의 아스키코드표의 정수값을 반환하는 함수입니다. 참고로, 컴퓨터는 정수값만 처리합니다. 그래서 컴퓨터에서 문자를 사용하기 위해 문자마다 고유한 정수값을 정해놓아 문자 사용시 정수값만 인식하게 됩니다. 문자열을 넣게 되면 아스키코드값이 나옵니다. 또한 유니코드(아스키코드+다른나라 문자 )가 나오게 되면 DB에서 사용하는 문자체계(DBCDIC)를 반환하게 됩니다.
decode(input1,search1,result1,result2)	쉽게 설명하면 if~else문을 볼 수 있으며, input1에 들어온 문자열을 search1과 비교하여 맞으면 result1를 반환하고 다르면 result2를 반환하는 함수입니다.

**아스키코드표를 참고하시고 싶으신 분들은 JAVA-제어문(feat.파일 입출력) 에 나온 링크를 참고해주시길 부탁드립니다.

JAVA-제어문(feat.파일 입출력)

 

 

▷ 이제 실습해보겠습니다.

1. 계정을 연결해주세요.

* 모든 함수를 사용할 때는 임시테이블을 이용해주세요.

2. -12의 절댓값을 구해주세요.

3. 6.12보다 같거나 큰 정수를 구한 뒤 ceil로 저장하고, 6.52보다 작거나 같은 정수를 구한 뒤 floor로 저장하여 보여주세요.

4. 6.12345를 소수점 첫째 자리에서 반올림 해주세요.

5. 6.12345를 소수점 첫째 자리에서 버림해주세요.

6. 2를 6번 거듭제곱해주세요.

7. 31를 6으로 나눈 값을 나머지값을 보여주세요.

8. 양수인지 음수인지를 판별하는 함수를 통해 1를 반환해주세요.

9. STEP의 첫글자는 대문자이고 나머지는 소문자로 변환해주세요.

10. STEP을 소문자로 변환해주세요.

11. step을 대문자로 변환해주세요.

12. 'IT'와 ' journey'를 붙여주세요.

13. 'abcdef'에서 1번째 문자부터 3의 길이만큼을 반환해주세요.

14. '가나다라'에서 한 글자 '가'만 출력해주세요.

15. '가나다라'의 길이를 구해주세요.

16. '가나다라'의 byte 길이를 구해주세요.

17. 'step'에서 s를 지워주세요.

18. 'step'에서 p를 지워주세요.

19. '            IT journey     ' 의 양쪽 공백을 지워주세요.

20. 출력하면 !step이 나오게 해주세요.

21. 출력하면 @jour 이 나오게 해주세요.

22. !의 아스키코드표의 정수값을 반환해주세요.

23. step과 star을 비교하여 맞으면 같다를 다르면 다르다를 반환해주세요.

정답은 순서대로입니다.

conn oracle/oracle;
select abs(-12) from dual;
select ceil(6.12) as ceil, floor(6.52) as floor from dual;
select round(6.12345) from dual;
select trunc(6.12345) from dual;
select power(2,6) from dual;
select mod(31,6) from dual;
select sign(6) from dual; 
select initcap('STEP') from dual;
select lower('STEP') from dual;
select upper('step') from dual;
select concat('IT',' journey') from dual;
select substr('abcdef',1,3) from dual;
select substrb('가나다라',1,3) from dual;
selcet length('가나다라') from dual;
selcet lengthb('가나다라') from dual;
select ltrim('step','sa') from dual;
select rtrim('step','ap') from dual;
select trim('            IT journey     ') from dual;
select lpad('step',5,'!') from dual;
select rpad('@',5,'journey') from dual;
select decode('step','star','같다','다르다') from dual;

 

 

▷ 이어서 오라클 함수에 대해 더 알아보겠습니다.

(3) 날짜함수

sysdate	현재시간 현재 시스템 기준을 알려주는 함수입니다.
current_date	현재시간 접속한 지역기준을 알려주는 함수입니다.
systimestamp	시스템 기준으로 자세한 시간 정보를 알려주는 함수입니다.
current_timestamp	접속한 지역기준으로 자세한 시간 정보를 알려주는 함수입니다.
add_months(date,int)	날짜(date)에 int 수만큼 월을 더하거나 뺀 날짜를 반환하는 함수입니다.
months_between(date1,date2)	두 날짜 사이의 개월수를 반환하는 함수입니다.
last_day(date)	date의 월 말일을 반환하는 함수입니다.
next_day(date,char)	date 기준으로 char에 명시한 요일의 날짜를 반환하는 함수입니다. char에 한글로는 일(요일)~토(요일) 또는 1~7입니다.

(4) 변환함수

to_char(char or date,format)	숫자나 날짜를 format형식에 맞는 문자로 변환하는 함수입니다.
to_number(date,format)	date를 format 형식에 맞게 숫자로 변환하는 함수입니다. 참고로, foramt을 넣으면 에러가 발생하는 경우가 많아 거의 안씁니다. 쓰일 때에는 문자형식의 데이터를 정수처리해야 할 때 입니다.
to_date(date,format)	문자를 형식에 맞는 문자로 변환하는 함수입니다.

*참고로, 오라클 숫자 형식인 format에 대해 알아봅시다.

요소	기술	예	결과
9	숫자 위치 (9의 수는 출력폭을 결정합니다.)	999999	1234
0	0을 출력합니다.	099999	001234
$	달러기호	$999999	$1234
L	지역 원화 기호	L999999	FF1234
.	명시한 위치에 소숫점	999999.99	1234.00
,	명시한 위치에 콤마	999,999	1,234
Ml	오른쪽에 마이너스 기호(음수값)	999999Ml	1234-
PR	음수를 괄호로 묶습니다.	999999PR	<1234>
EEEE	과학적 부호표기(형식은 4개의 E를 명시해야 합니다.	99.999EEEE	1.234E+03
V	10을 n번 곱합니다.(이때, n은 V뒤의 9의 수입니다.)	9999V99	123400
B	0의 값을 0이 아닌 공백으로 출력합니다.	B9999.99	1234.00