[Oracle] SQL 키워드, 식별자, 자료형

💡SQL 키워드와 식별자

---

대소문자 구분

-- 대소문자 구분 X
select * from tabs;
SELECT * FROM tabs;

-- 대소문자 구분 O
select * from tabs where table_name = 'JOBS';
select * from tabs where table_name = 'jobs';

SQL은 키워드와 식별자에 대소문자를 구분하지 않으며, 상수인 경우에만 대소문자를 구분한다.

 

키워드와 식별자 구분

파란색 영문자는 키워드(문법)를 의미하며, 검은색 영문자는 식별자를 의미한다. 이는 상황에 따라 조금씩 다를 수 있지만, 대부분의 경우 이러한 형식으로 나온다.

Alt + ' (작은 따옴표) 단축키로 표기법을 자동 변환할 수 있다.

 

DB Object 식별자 길이

식별자 생성

create table aaa(
    num number
);

create table aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(
    num number
);

create table aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(
    num number
);

Table AAA이(가) 생성되었습니다.

Table AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA이(가) 생성되었습니다.

명령의 207 행에서 시작하는 중 오류 발생 -
create table aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(
    num number
)
오류 보고 -
ORA-00972: identifier is too long
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 128 bytes was specified,
           or a password identifier longer than 30 bytes was specified.
*Action:   Specify at most 128 bytes for identifiers,
           and at most 30 bytes for password identifiers.

DB Object 식별자는 최대 30byte까지(30자) 만들 수 있다.

이름이 30자인 테이블은 만들어지지만 31자부터는 만들어지지 않는다.

 

💡ANSI-SQL 자료형

---

오라클 자료형

관계형 데이터베이스에는 변수가 없는데, SQL이 프로그래밍 언어가 아니라 대화형 언어(DB와 대화를 목적으로 하는 언어)이기 때문이다.

자료형은 데이터를 저장하는 규칙으로서, 테이블을 정의할 때 사용한다.

오라클의 자료형은 테이블의 컬럼에 어떤 형태의 데이터가 들어가는지를 알 수 있게 정의해야 할 때 사용한다.

 

🔹숫자형

- 정수, 실수

• number: 정수 & 실수
• number(precision): 전체 자릿수 (정수)
• number(precision, scale): 전체 자릿수, 소수 이하 자릿수 (실수)

오라클에서 모든 숫자 타입은 number 하나만 사용한다.

 

number의 특징

1. number는 유효자리 38자리 이하의 숫자를 표현하는 자료형이며, 5~22byte를 사용한다.

2. 숫자는 1x10^-130 ~ 9.9999x10^125 까지의 범위를 표현할 수 있다.

3. number는 그냥 사용하기도 하지만, 괄호 안에 숫자의 자릿수를 넣기도 한다.

 

🔹문자형

- 문자, 문자열

• char
• nchar
• varchar2
• nvarchar2
• clob, nclob

 

char의 특징

1. 고정 자릿수 문자열로, 공간(컬럼)의 크기가 불변하다.

2. char는 char(n)로 사용할 경우 최대 n자리 문자열 방식으로 값을 저장할 수 있으며, 여기엔 char(n byte)로 byte가 생략되어 있어서 n바이트 만큼 저장이 가능하다.

3. char(n char)로 작성하면, n글자만큼 저장할 수 있다.

4. char는 최소 1바이트, 최대 2000바이트를 저장할 수 있으며, 2000바이트를 넘으면 그 값들을 따로 저장할 수 있는 기능이 존재한다.

 

nchar의 특징

1. nchar(n)로 사용할 경우 최대 n글자 문자열로 저장할 수 있다. 기본적으로 바이트가 아니라 글자수로 저장한다.

2. nchar는 최소 1글자, 최대 1000글자를 저장할 수 있다.

3. nchar가 원래 있었던 기능이고, char(n char)가 이후에 만들어졌다.

 

varchar2의 특징

1. 가변 자릿수 문자열로, 공간(컬럼)의 크기가 가변적이다.

2. varchar2(n)로 작성할 경우 최대 n자리 문자열로 저장할 수 있으며, varchar2(n char)로 작성할 경우 최대 n글자 문자열로 저장할 수 있다.

3. varchar2는 최소 1바이트, 최대 4000바이트를 저장할 수 있다.

4. 오라클에서 varchar, varchar2가 존재하는데, varchar2를 만들 때 기존에 쓰던 varchar를 없앨 수는 없어서 숫자 2를 붙이게 되었다.

5. varchar2를 읽을 때에는 variable char, 바캐릭터, 바차라고 읽기도 한다.

6. varchar2와 char는 물리적으로 데이터를 저장하는 방식이 다르다.

 

nvarchar2의 특징

1. nvarchar2(n)로 작성할 경우 최대 n자리 문자열로 저장할 수 있다.

2. nvarchar2는 최소 1글자, 최대 2000글자를 저장할 수 있다.

 

clob, nclob의 특징

1. character large object로, 대용량 텍스트를 저장할 때 사용한다.

2. 최대 128TB를 저장할 수 있다.

3. 참조형이므로 엑세스 속도가 느리다.

문자열을 쪼개서 따로 저장하는 게 더 빠르기 때문에 잘 사용하지 않는다.

 

문자형의 종류는 다양하지만, 대부분 varchar2로 통일하여 사용한다.

 

🔹날짜/시간형

• date
• timestamp
• interval

 

date의 특징
1. '년월일시분초' 형식으로 저장한다.
2. 7byte로 저장한다.
3. 기원전 4712년 1월 1일 ~ 9999년 12월 31일 

timestamp의 특징
1. '년월일시분초' 형식으로 저장하는 건 date와  똑같다.

2. 밀리초와 나노초도 저장할 수 있다.

interval의 특징
3. 시간을 저장할 때 사용하며, 틱값으로 저장된다.

 

날짜/시간형 자료형 중에 가장 많이 사용하는 것은 date이다.

 

🔹이진 데이터형

• blob

이진 데이터형의 특징

1. 비 텍스트 데이터이다.
2. 이미지, 영상, 음악, 실행 파일, 압축 파일 등..
3. 회사 등에서 잘 사용하지 않는다.
4. 게시판(첨부파일), 회원가입(사진) 등에서 사용하며, 파일명만 저장(문자열)하고 실제 파일은 다른 경로에 저장하여 이름을 비교하여 가져오는 방식으로 사용할 수 있다.

5. blob의 경우 최대 128TB를 저장할 수 있다.

 

🔸결론

1. 숫자는 number 자료형을 사용한다.
2. 문자는 varchar2, char 자료형을 사용한다.
3. 날짜는 date 자료형을 사용한다.

 

💡테이블 생성 ~ 삭제 살펴보기

---

테이블 선언 (생성)

create table 테이블명(
    컬럼 선언,
    컬럼 선언,
    컬럼 선언
    컬럼명 자료형
);

create table tblType(
    num number
);

Table TBLTYPE이(가) 생성되었습니다.

식별자 앞에 타입 접두어를 붙이는 표기법을 사용하였다. 이는 헝가리안 표기법으로, 이 경우 만드는 게 테이블이기 때문에 tbl을 붙였다.

표기법 패턴에 대해서는 아래 글을 참고하도록 하자.

 

[Java] 변수 명명법 패턴: 파스칼 표기법, 캐멀 표기법, 스네이크 표기법, 케밥 표기법, 헝가리언

 

데이터 추가

insert into 테이블 (컬럼) values (값);

insert into tblType (num) values (100); -- 정수 리터럴

1 행 이(가) 삽입되었습니다.

생성한 tblType 테이블의 (num)컬럼에 (100)의 정수값을 추가하였다.

 

데이터 가져오기

select * from tblType;

데이터의 집합이라고 해서 결과 테이블, 또는 결과셋(ResultSet)이라고 부른다.

오라클은 모든 식별자를 대문자로 저장하기 때문에 TBLTYPE 테이블이 만들어진 것과, NUM 컬럼에 100 데이터가 들어간 것을 확인할 수 있다.

 

데이터 삭제하기

drop table tblType;

Table TBLTYPE이(가) 삭제되었습니다.

 

💡자료형

---

숫자형 자료형

number(precision)

create table tblType(
    num number(3) -- -999 ~ 999
);

insert into tblType (num) values (999);
insert into tblType (num) values (-999);
insert into tblType (num) values (3.14); -- 실수 리터럴
insert into tblType (num) values (3.99); -- 반올림
insert into tblType (num) values (1234); -- X

select * from tblType;

number는 정수형이기 때문에 실수는 저장되지 않는다.

그리고 반올림되어 3.99는 4로 저장되며, 1234는 4자리이기 때문에 3자리로 자릿수를 지정한 경우 값이 저장되지 않는다.

 

number(precision, scale)

create table tblType(
    num number(4, 2) -- -99.99 ~ 99.99
);

insert into tblType (num) values (-99.99);
insert into tblType (num) values (99.99);
insert into tblType (num) values (100); -- X
insert into tblType (num) values (1234); -- X

select * from tblType;

number(4, 2)의 앞의 4는 정수 부분에만 해당하는 부분이 아니라 실수까지 포함하는 자릿수이며, 뒤의 2는 실수에 해당하는 자릿수이다.

 

문자형 자료형

char(n)

create table tblType(
    txt char(10) --최대 10바이트의 문자열
);

insert into tblType (txt) values (100); -- 100(number) > '100'(char) 형변환 발생
insert into tblType (txt) values ('Isaac');

Java는 Strong Type Language지만, SQL은 Weak Type Language이기 때문에 암시적인 형변환이 자주 일어난다.

 

insert into tblType (txt) values ('abcdefghij'); -- 10바이트
insert into tblType (txt) values ('abcdefghijk'); -- 11바이트
insert into tblType (txt) values ('안녕하세요'); -- 15바이트

1 행 이(가) 삽입되었습니다.


명령의 115 행에서 시작하는 중 오류 발생 -
insert into tblType (txt) values ('abcdefghijk')
오류 발생 명령행: 115 열: 35
오류 보고 -
SQL 오류: ORA-12899: value too large for column "HR"."TBLTYPE"."TXT" (actual: 11, maximum: 10)
12899. 00000 -  "value too large for column %s (actual: %s, maximum: %s)"
*Cause:    An attempt was made to insert or update a column with a value
           which is too wide for the width of the destination column.
           The name of the column is given, along with the actual width
           of the value, and the maximum allowed width of the column.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Examine the SQL statement for correctness.  Check source
           and destination column data types.
           Either make the destination column wider, or use a subset
           of the source column (i.e. use substring).

오라클 인코딩 방식은 UTF-8이므로 영어(1), 한글(3) 바이트로 10바이트까지 저장할 수 있다.

abcdefghij는 10바이트이므로 저장되었지만, abcdefghik는 11바이트이므로 오류가 발생했다.

 

varchar2(n)

create table tblType(
    txt1 char(10),
    txt2 varchar2(10)
);

insert into tblType (txt1, txt2) values('abc', 'abc');

select * from tblType;

txt1과 txt2에 실제로 들어간 데이터는 3바이트이다. 그런데 출력한 길이가 서로 다르다.🤔

char는 10바이트 크기로 잡아놨으면 데이터가 아무리 작더라도 10바이트를 유지하며, varchar2는 처음에 설정한 데이터와 상관없이 데이터를 남고 남은 데이터를 삭제한다.

그러므로 실제로 txt1에 들어간 데이터는 "abc       "이며, txt2에 들어간 데이터는 "abc"이다.

눈에 보이지 않는다고 데이터가 없다고 생각하면 안 된다. 따라서 char는 주민등록번호와 같이 데이터의 길이가 모두 같은 컬럼에만 쓰이는 편이다.

 

날짜/시간형 자료형

create table tblType(
    regdate date
);

insert into tblType (regdate) values ('2023-08-25');

select * from tblType;