[Oracle] Oracle의 ANSI-SQL 자료형

1. 숫자형

- 정수, 실수

a. number

• (유효자리)38자리 이하의 숫자를 표현하는 자료형
• 12345678901234567890123456789012345678
• 5~22byte
• 1x10^-130 ~ 9.9999x10^125 (→ 모든 숫자를 다 표현할 수 있다. 정도로 기억!)
• number: 정수 & 실수
• number(precision): 전체 자릿수 → 실수를 넣어도 정수 타입으로 반환(소수 이하 반올림 됨.)
• number(precision, scale): 전체 자릿수, 소수 이하 자릿수 → 실수 저장 가능

 

2. 문자형

- 문자, 문자열(char와 String 구분 없음!)

- char vs nchar → n의 의미? (national)

- char vs varchar → var의 의미? (variable) → 저장 공간 차이

a. char

• 고정 자릿수 문자열 → 공간(컬럼)의 크기가 불변
• char(n): 최대 n자리 문자열, n(바이트)
• char(n char)
• 최소 크기: 1바이트
• 최대 크기: 2000바이트

b. nchar

• n: national → 오라클 인코딩과 상관 없이 해당 컬럼을 UTF-16(영어, 한글 모두 2바이트)으로 동작
• nchar(n): 최대 n자리 문자열, n(문자수)
• 최소 크기: 1글자
• 최대 크기: 1000글자

c. varchar2

• variable char → “바캐릭터”, “바차” 라고 부른다.
• 가변 자릿수 문자열 → 공간(컬럼)의 크기가 가변
• varchar2(n): 최대 n자리 문자열, n(바이트)
• varchar2(n char)
• 최소 자릿수: 1
• 최대 자릿수: 4000

 

Oracle에는 varchar도 있고 varchar2도 있다. 다른 DBMS에서 사용하는 varchar가 기존 오라클이 사용하고 있던 varchar와 이름이 똑같아서 Oracle에서는 varchar2라고 표현한다.

 

d. nvarchar2

• n: national → 오라클 인코딩과 상관 없이 해당 컬럼을 UTF-16(영어, 한글 모두 2바이트)으로 동작
• 가변 자릿수 문자열 → 공간(컬럼)의 크기가 가변
• nvarchar2(n): 최대 n자리 문자열, n(문자수)
• 최소 자릿수: 1글자
• 최대 자릿수: 2000글자

e. clob, nclob

• 대용량 텍스트
• character large object
• 최대 128TB까지 저장 가능
• 참조형(위의 case들과 비교해볼 때 비교적 느림)

 

과거 국룰 vs 현재 자료형 사용 Case 비교
[과거 국룰]
1. 고정 자릿수 문자열 → 주민등록번호, 전화번호 → char
2. 가변 자릿수 문자열 → 주소, 자기소개 → varchar2
[현재]
1. 고정/가변 → varchar2
⇒ 시간이 흐르고 Oracle이 발전이 되면서 char과 varchar2의 속도가 비슷해졌기 때문에 현재 실무에서는 고정/가변 둘 다 varchar2를 사용한다고 한다.

 

3. 날짜/시간형

a. date(많이 사용)

• 년월일시분초
• 7byte
• 기원전 4712년 1월 1일 ~ 9999년 12월 31일

b. timestamp

• 년월일시분초 + 밀리초 + 나노초

c. interval

• 시간
• 틱값 저장용

4. 이진 데이터형

• 비 텍스트 데이터
• 이미지, 영상, 음악, 실행파일, 압축파일 등…
• 잘 사용 안 함 → 입출력 속도가 무겁기 때문

 

이진 데이터형 대안 사용 예시
게시판(첨부파일), 회원가입(사진) 데이터를 저장하는 경우 → 파일명만 저장(문자열) + 사진은 다른 폴더에 모아서 저장해 놓고 나중에 파일명으로 해당 폴더에서 찾도록 하는 식으로 많이 한다고 한다.

 

a. blob

• 최대 128TB까지 저장 가능

 

---

결론

1. 숫자 → number

2. 문자 → varchar2 + char

3. 날짜 → date

※ Oracle은 boolean이 없다.

 

(참고) Java의 자료형
1. 숫자 → int + long, double, boolean
2. 문자열 → String
3. 날짜 → Calendar

 

---

 

자료형 적용 예시

테이블 선언(생성)

CREATE TABLE 테이블명 (
    컬럼 선언,
    컬럼 선언,
    컬럼 선언,
    컬럼명 자료형
);

 

테이블 선언 시 적용 예시

CREATE TABLE tblType (
	num number
	num number(3) -- -999 ~ 999 (정수로 반환)
	num number(4, 2) -- -99.99 ~ 99.99 (앞의 자릿수 '4'는 정수의 자릿수가 아니라 실수까지 포함한 전체의 자릿수를 의미한다.)
	
	txt char(10) -- 최대 10바이트까지의 문자열(문자열도 집어넣을 수 있는 최대 길이가 정해져있는데 그 길이를 우리가 정할 수 있다.)
	txt char(10 byte) -- byte를 훨씬 더 많이 사용한다.
	txt char(10 char) -- 최대 10글자까지의 문자열

	txt varchar2(10)

	regdate date
);


DROP TABLE tblType;

 

char vs varchar2 비교

-- char vs varchar2
CREATE TABLE tblType (
	txt1 char(10), -- 죽을 때까지 10바이트를 유지한다. 따라서 글자 수가 전부 다 같은 column에만 사용! (나중에 이 정보를 Java에서 불러올 때 공백도 데이터로 치기 때문에 공백을 제거하는 코드 작성이 필요하다.)
	txt2 varchar2(10) -- 10바이트로 잡아놨지만 들어간 데이터가 3바이트면 남은 7바이트를 회수한다.
);

INSERT INTO tblType (txt1, txt2) VALUES ('abc', 'abc');

-- 결과
-- txt1 = 'abc       ' (공백도 데이터로 저장)
-- txt2 = 'abc' (3바이트만 저장)

 

데이터 추가

INSERT INTO 테이블 (컬럼) VALUES (값);

 

데이터 추가 시 적용 예시

INSERT INTO tblType (num) VALUES (100); -- 정수 리터럴(따옴표 없이 숫자로 나열하면 숫자 데이터로 인식. 자바와 동일하다.)
INSERT INTO tblType (num) VALUES (3.14); -- 실수 리터럴
INSERT INTO tblType (num) VALUES (3.99); -- 반올림 O
INSERT INTO tblType (num) VALUES (1234); -- ORA-01438: value larger than specified precision allowed for this column (위에서 생성한 num컬럼의 자료형이 num(4,2) 또는 num(3)이었기 때문에 오류가 발생한다. num(6,2) 또는 num(4)로 수정 시 정상적으로 데이터 추가가 가능하다.)
INSERT INTO tblType (num) VALUES (999);
INSERT INTO tblType (num) VALUES (-999);
INSERT INTO tblType (num) VALUES (99.99);
INSERT INTO tblType (num) VALUES (-99.99);

데이터를 넣을 때마다 행이 추가된다고 이해하면 된다!

 

SQL의 암시적 형변환

• Java: Strong Type Language
• SQL: Weak Type Language
• SQL의 암시적인 형변환이 자주 일어난다.

 

암시적 형변환 예시

create table tblType (
    txt char(10)
);

INSERT INTO tblType (txt) VALUES (1234567890); -- 1234567890(number) > '1234567890'(char)
INSERT INTO tblType (txt) VALUES ('홍길동'); -- 문자 리터럴

 

주의사항

1. 오라클은 모든 식별자를 대문자로 저장한다.

2. 오라클 인코딩 → UTF-8이 기본이다. ☞ 영어(1 byte), 한글(3 byte)

INSERT INTO tblType (txt) VALUES ('abcdabcabc');
INSERT INTO tblType (txt) VALUES ('abcdabcabcd'); -- ORA-12899: value too large for column "HR"."TBLTYPE"."TXT" (actual: 11, maximum: 10)
INSERT INTO tblType (txt) VALUES ('홍길동입니다.'); -- ORA-12899: value too large for column "HR"."TBLTYPE"."TXT" (actual: 19, maximum: 10)

 

데이터 가져오기

• 결과 테이블(Result Table), 결과셋(ResultSet) 이라고 한다.

SELECT * FROM tblType;

 

*글 작성에 참고한 내용: 학원 쌤의 열정적인 수업