[Oracle] 데이터 타입

오라클의 자료는 문자열, 숫자, 날짜, 2진 자료형으로 구분

문자열자료

• ' '  안에 표현된 자료를 문자열자료라고 함
• 문자열자료를 저장하기 위한 데이터 타입은 고정형과 가변형으로 구분

고정길이 문자열	CHAR
가변길이 문자열	VARCHAR, VARCHAR2, NVARCHAR LONG, CLOB 등

N : 국제표준코드 변형

CHAR

컬럼명 CHAR ( 크기 [BYTE | CHAR] ) ;

- '크기'는 정수로 기술
- 'BYTE | CHAR' : '크기'가 BYTE인지 CHAR( 글자수) 인지 판별
- 'CHAR (글자수)'가 사용되더라도 한글 저장시 2000BYTE를 초과할 수 없음
- DEFAULT는 BYTE

• 고정길이 문자열을 저장
• 최대 2000BYTE까지 저장 가능
• 남는 공간은 공백으로 채워짐
• 한글, 한 글자는 3BYTE가 필요함
• 보통 기본키나 길이가 고정된 자료(우편번호, 주민등록번호 등)에 주로 사용 > 제한사항 

---

사용 예시

---

CREATE TABLE TEMP01(
	COL1 CHAR(20 BYTE),
	COL2 CHAR(20 CHAR),
	COL3 CHAR(20) );

INSERT INTO TEMP01(COL1,COL2,COL3) VALUES('대전시 중구 846','대전시 중구 계룡로 846',
                                          '대전시 중구');

 

INSERT INTO TEMP01(COL1,COL2,COL3) VALUES('대전시 중구 계룡로 846','대전시 중구 계룡로 846',
                                          '대전시 중구 계룡로 846');

명령의 1 행에서 시작하는 중 오류 발생 -
INSERT INTO TEMP01(COL1,COL2,COL3) VALUES('대전시 중구 계룡로 846','대전시 중구 계룡로 846' '대전시 중구 계룡로 846')
오류 보고 -
ORA-12899: value too large for column "JIN"."TEMP01"."COL1" (actual: 30, maximum: 20)
ORA-12899: value too large for column "JIN"."TEMP01"."COL3" (actual: 30, maximum: 20)

-- 10 BYTE 초과
-- (30BYTE, 14글자, 30BYTE) 이므로 COL1과 COL3을 변경

   >  제대로 입력되었는지 확인하기 (SELECT)

SELECT * 
	FROM TEMP01;

* : ALL

SELECT LENGTHB(COL1) AS "컬럼1",
       LENGTHB(COL2) AS "컬럼2",
       LENGTHB(COL3) AS "컬럼3"
	FROM TEMP01;

LENGTHB : 문자열의 길이를 byte단위로 구하는 함수

LENGTH : 순수한 문자열의 길이를 반환

COL1, COL3 : 고정길이 타입으로 20을 선언하여 20 BYTE값이 나옴
COL2 : 20 CHAR (실제 점유 30BYTE + 공백 6BYTE)

VARCHAR2

컬럼명 VARCHAR2 (크기 [ BYTE | CHAR ] ) ;

• 가변길이 문자열을 저장
• 최대 4000BYTE까지 저장 가능
• 데이터가 저장되고 남는 공간은 시스템에 반환됨 (공간 부족 시 에러)
• 한글 한 글 자는 3BYTE가 필요
• VARCHAR, NVARCHAR, NVARCHAR2 와 기능은 동일
• NVARCHAR, NVARCHAR2 는 UTF-8, UTF-16형식으로 데이터를 변환하여 저장 (국제 표준 형식)

---

사용 예시

---

CREATE TABLE TEMP02(
	COL1 CHAR(100),
	COL2 VARCHAR2(100),
	COL3 VARCHAR2(100 BYTE),
	COL4 VARCHAR2(100 CHAR) );

INSERT INTO TEMP02 VALUES('IL POSTINO BOYHOOD','IL POSTINO BOYHOOD',
                          'IL POSTINO BOYHOOD', 'IL POSTINO BOYHOOD');

SELECT * FROM TEMP02;

CHAR은 고정길이라 여백을 모두 채움

LONG

컬럼명 LONG

• 가변길이 문자열을 저장
• 최대 2GB(Giga Byte) 까지 저장 가능
• 데이터가 저장되고 남는 공간은 시스템에 반환 됨 (공간 부족시 에러)
• 한글 한 글자는 3BYTE가 필요
• 한 테이블에 하나의 LONG타입 컬럼만 정의 할 수 있음
• 기능 개선 서비스가 중단됨 => CLOB (Character Large OBject)로 기능이 개선되어 제공
• 일부 기능(함수)은 사용할 수 없음 

---

사용예시

---

CREATE TABLE TEMP03 (
	COL1 VARCHAR2(4000),
    -- COL2 LONG,
	COL3 LONG);

CREATE TABLE TEMP03 (
	COL1 VARCHAR2(4000),
	COL2 LONG,
	COL3 LONG);

오류 보고 -
ORA-01754: a table may contain only one column of type LONG
01754. 00000 - "a table may contain only one column of type LONG"
*Cause: An attempt was made to add a LONG column to a table which already had a LONG column.             Note that even if the LONG column currently in the table has already been marked unused,              another LONG column may not be added until the unused columns are dropped.
*Action: Remove the LONG column currently in the table by using the ALTER TABLE command.

    INSERT INTO TEMP03 VALUES('BANANA APPLE PERSIMMON','BANANA APPLE PERSIMMON');
    
    SELECT * FROM TEMP03;

SELECT SUBSTR(COL1, 5, 10) AS "VARCHAR2"
--     SUBSTR(COL3, 5, 10) AS "LONG"
--	 LONG 타입은 너무 크기 때문에 사용할 수 없음 (오류)
	FROM TEMP03;

CLOB (Character Large OBject)

컬럼명 CLOB

• 가변길이 문자열 저장
• 최대 4GB (Giga Byte) 까지 저장 가능
• 데이터가 저장되고 남는 공간은 시스템에 반환됨 (공간 부족시 에러)
• 한글 한 글자는 3byte가 필요
• 한 테이블에 복수개의 CLOB 컬럼 정의 할 수 있음
• 일부 기능(함수)은 DBMS_LOB API지원을 받아 사용 가능

---

사용 예시

---

CREATE TABLE TEMP04 (
	COL1 VARCHAR2(4000),
	COL2 CLOB,
	COL3 CLOB);

 

INSERT INTO TEMP04(COL1,COL2) VALUES('BANANA APPLE PERSIMMON','BANANA APPLE PERSIMMON');
 	--넣을 곳 지정 (COL3 제외)    
SELECT * FROM TEMP04;

SELECT MOD((TO_DATE('20240307') - TO_DATE('00010101'))-1, 7) FROM DUAL;​

(요일 출력하는 것)

SELECT SUBSTR(COL1,5,10) AS "VARCHAR2",     --(이름, 시작위치, 갯수)
	--DBMS_LOB.SUBSTR(COL2,5,10) AS "LOB",  --(이름, 갯수, 시작위치) 과거에 썻던 방식
	SUBSTR(COL2,5,10) AS "CLOB"
	FROM TEMP04;

 

DMS 구절까지 실행한 결과

    SELECT LENGTHB(COL1) AS "VARCHAR2",
           LENGTHB(COL2) AS "CLOB"
        FROM TEMP04;

ORA-22998: CLOB or NCLOB in multibyte character set not supported 22998. 00000 - "CLOB or NCLOB in multibyte character set not supported"
*Cause: A CLOB or NCLOB in a fixed-width or varying-width multibyte character set was passed to a             SQL character function which does not support multibyte LOB data.
*Action: Use DBMS_LOB functions such as DBMS_LOB.INSTR() and DBMS_LOB.SUBSTR() or                   use PLSQL DBMS_LOB.READ/WRITE to access LOB data.

 

    SELECT LENGTHB(COL1) AS "VARCHAR2",
           DBMS_LOB.GETLENGTH(COL2) AS "CLOB_LENGTH"
    --     LENGTHB(COL2) AS "CLOB"
        FROM TEMP04;

숫자자료타입

NUMBER

컬럼명 NUMBER [ (정밀도 | * [스케일] ) ]

  - '정밀도' : 전체 자리수 (1~38) (precision)
  - '스케일' : 소숫점 이하의 자리수
  - '스케일' 이 생략되면 '0'으로 간주
  - 저장은 '스케일' + 1 번째 자리에서 반올림하여 '스케일'번째 까지 저장 (정수자리수 : 정밀도 - 스케일)
  - '스케일'이 음수이면 정수자리수에서 반올림 함
  - 값의 표현 범위 : 1.0E-130 ~ 9.9999...E+125

---

사용예시

---

입력 값	선언	기억되는 값
12345.6789	NUMBER	12345.6789
12345.6789	NUMBER (*,2)	12345.68
12345.6789	NUMBER (6,2)	오류
12345.6789	NUMBER(7,2)	12345.68
12345.6789	NUMBER(6,0)	12346
12345.6789	NUMBER(6)	12346
12345.6789	NUMBER(6,-2)	12300

NUMBER(소숫점을 포함한 전체 자리수, 소숫점 이하의 자리수)

* : 최댓값 (38)

 (전체 자리수를 넘지 않으면 빈 공간으로 남아있음 COL5~7 : 5자리수)

 CREATE TABLE TEMP05 (
    COL1    NUMBER,
    COL2    NUMBER(*,2),
    COL3    NUMBER(6,2),
    COL4    NUMBER(7,2),
    COL5    NUMBER(6,0),
    COL6    NUMBER(6),
    COL7    NUMBER(6,-2) );

INSERT INTO TEMP05 VALUES (12345.6789 ,12345.6789 ,12345.6789 ,
 			12345.6789 ,12345.6789 ,12345.6789 ,12345.6789 );

오류 보고 -
ORA-01438: value larger than specified precision allowed for this column  // 3번째

COL3 데이터 값 변경 12345.6789 → 45.6789

INSERT INTO TEMP05 VALUES (12345.6789 ,12345.6789 ,45.6789 ,
		12345.6789 ,12345.6789 ,12345.6789 ,12345.6789 );
        
SELECT * FROM TEMP05;