2. [Oracle] 변수타입, DDL_CREATE 구문 사용하기

오늘은 SQL의 DDL(Data Definition Language) 중 CREATE 문을 사용할 것이다.

그전에 앞서서 오라클에서 사용하는 변수의 Type에 대해 알아보도록 하겠다.

 

* char형 -> char형은 문자열을 나타내 주는데 고정된 길이를 가지고 있다. 2000BYTE까지 저장 가능

(한글 한 글자는 3BYTE), 기억공간이 남으면 왼쪽에 공백이 삽입된다.

주로 기본키 정의에 사용한다.

 

* varchar2형 -> varchar2형은 마찬가지로 문자열을 나타내 주는데 가변 길이를 가지고 있다.

최대 4000BYTE까지 저장 가능하다.

이 차이가 무엇이냐면 예를 들어 char(4)로 선언을 하게 되면 그 변수는 무조건 4byte의 크기를 가지게 된다.

하지만 varchar2 같은 경우에는 varchar2(4)로 선언을 하게 되고 그 변수에 1byte의 크기만 사용하게 되면

그 변수는 1byte의 크기만 가지게 된다. 이와 같이 가변 길이는 입력된 변수에 맞춰서 차지하는 용량이 적어지게 된다.

 

* long형 -> varchar2와 마찬가지로 가변 길이 문자열 저장을 해준다.

최대 2GB까지 저장 가능하고, 한 테이블에 오직 하나만 사용이 가능하다. 최근엔 CLOB로 대체된다.

SELECT문의 SELECT 절, UPDATE문의 SET절, INSERT문의 VALUES절에 사용 가능하다.

변수 크기를 따로 지정해 주지 않는다.

 

* clob형 -> lob는 Large OBject의 약자로 대용량 데이터를 저장해 줄 때 사용하는 변수 타입이다.

long과 비교하여 최대 4GB까지 저장 가능하고, 한 테이블에 오직 하나만 사용 가능하던 long과 다르게 제한이 없다.

일부 기능은 DBMS_LOB API의 지원을 받아야 한다.

변수 크기를 따로 지정해 주지 않는다.

 

* number형 -> 다른 언어에선 int, float, double 등으로 나뉘어서 사용하지만

ORACLE에서는 오직 number형을 숫자로 사용한다.

number형은 크기를 지정해주지 않으면 10^27까지 나타내 주기 때문에 크기 지정을 해주지 않아도 된다.

칼럼명 NUMBER (정밀도, 스케일)

- 표현 범위 (1.0E-130 ~ 9.999.. 99 E125)

- 정밀도 : 전체 자릿수(1~38) , * 을 사용하면 시스템에서 알아서 조절해줌

- 스케일 : 소수점 이하의 자릿수, 스케일을 입력하지 않거나 0을 주면 정수부 분만 저장이 됨.

스케일이 음의 정수라면 정수 부분의 스케일 위치에서 자리 반올림이 발생한다.

입력 값	선언	저장값
1234.5678	NUMBER	1234.5678
1234.5678	NUMBER(5,2)	에러
1234.5678	NUMBER(6,2)	1234.57
1234.5678	NUMBER(*,2)	1234.57
1234.5678	NUMBER(6,-1)	1230

스케일이 정밀도 보다 큰 경우

스케일 - 정밀도는 소수점 이하에 존재해야 할 0의 개수를 의미한다.

입력 값	선언	저장값
0.2345	NUMBER(4,5)	에러
0.02345	NUMBER(4,5)	0.02345
0.2345	NUMBER(3,5)	에러
0.002345	NUMBER(3,5)	0.00235
0.002345	NUMBER(4,6)	0.002345

 

* date형 -> date는 날짜를 나타내 준다. 크기 지정이 필요 없다. 초 단위까지 나타내 준다.

년 원 일 까지 나타내기 위해서 반드시 8자리까지는 입력이 필요하다. 그렇지 않으면 날짜가 제대로 들어가지지 않는다.

시/분/초 도 입력하고 싶으면 반드시 6자리 시분초를 다 써주거나 혹은 아예 써주지 않아야 한다.

SYSDATE를 사용해주면 시스템에서 제공하는 현제 날짜 정보를 준다.

+와 - 연산이 가능하다(일수 단위).

시분초까지 값을 확인할 때는 SELECT TO_CHAR(COL1,'YYYY-MM-DD HH24:MI:SS') FROM 테이블; 으로 확인해주어야 한다. 

 

* timestamp형 -> timestamp도 date와 마찬가지로 날짜를 나타내 주고 크기 지정이 필요 없다.

시간대역(TIME ZONE) 정보를 포함한 날짜 정보를 저장해준다. 밀리세컨드 단위까지 나타내 준다.

TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE 타입 제공한다.

10억분의 1초 단위를 사용한다. 

TIMESTAMP : 시간대 정보가 없다.

TIMESTAMP WITH TIME ZONE : 시간대 정보(대륙명/도시명) 제공한다.

TIMESTAMP WITH LOCAL TIME ZONE : 서버가 설치된 시간대정보로 보통 TIMESTAMP와 동일하다.

 

* RAW형 -> 상대적으로 작은 2진 자료를 저장해주고, 인덱스 처리가 가능하다. 오라클에서는 변환 또는 해석작업을 수행하지 않는다. 최대 2000BYTE 까지 저장 가능하다. 16진수와 2진수 저장이 가능하다.

문자열 자체로 저장될 수 없다.

 

* BFILE형 -> 2진자료를 저장해준다. 대상이 되는 2진자료는 데이터베이스 밖에 저장하고,

데이터베이스에는 경로와 파일명만 저장해준다.

4GB까지 저장이 가능하다.

디렉토리(저장 위치) 별칭은 30BYTE 파일명은 256BYTE까지 사용 가능.

디렉토리 객체를 생성해줘야 한다.

CREATE DIRECTORY 객체명 AS 절대경로명;

INSERT INTO 테이블명 VALUES(BFILENAME('객체명','파일명'));

파일은 객체명이 있는 절대경로에 있어야 함.

 

* BLOB형 -> 2진자료를 저장해준다. 대상의 2진 자료가 테이블 내부에 저장되어진다. (BFILE형은 경로, BLOB는 직접)

4GB 까지 저장 가능하다.

일반 SQL문으로 데이터 삽입이 불가능(익명 블록 사용)

 

DDL(Data Definition Language)은 데이터 정의어다.

데이터 정의어라는 것은 데이터를 저장할 껍데기, 즉 테이블을 만들어주거나, 삭제하거나 혹은 변경해주는 역할을 한다.

이중에서도 오늘 사용할 CREATE는 테이블을 만들어주는 명령어로서 동작한다.

우선 위의 SQL워크시트를 클릭하거나 ALT + F10을 이용해 워크시트를 만들어보자.

그렇게 워크시트를 만들었으면 CREATE 문을 작성해볼 것이다.

 

--  1. CREATE : 오라클 객체 중 테이블을 생성하는 명령
--  CREATE TABLE 테이블명(
--      컬럼명1 타입[(크기)] [NOT NULL] [DEFAULT 값][,]
--      [컬럼명2 타입[(크기)] [NOT NULL] [DEFAULT 값][,]]
--                        :
--      [CONSTRAINT 기본키설정명 PRIMARY KEY(컬럼명1,컬럼명2...)[,]
--      [CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명1,컬럼명2...)
--       REFERENCES 테이블명(컬럼명1,컬렴명2...)[,]
--      [CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명1,컬럼명2...)
--       REFERENCES 테이블명(컬럼명1,컬렴명2...)[,]
--                        :                
--  );

 

CREATE 문은 위와 같은 방식으로 사용한다.

위의 형태에서 [] 괄호 안에 들어간 내용들은 상황에 따라 안 써줘도 상관없다는 것이다.

CREATE TABLE 테이블명으로 테이블명을 지정해주고,

그 내부에 속한 칼럼(열)을 선언해주어서 만들어준다.

이때 NOT NULL은 NULL값을 허용하지 않겠다는 명령어이다. 이게 무슨 말이냐 하면 NOT NULL이 선언된 변수에는

반드시 어떠한 데이터라도 들어가야 한다. 데이터가 들어가지 않게 된다면 에러가 나게 된다.

그리고 DEFAULT 같은 경우에는 NOT NULL에서 데이터를 입력하지 않는다면 DEFAULT에 설정된 값으로 자동 설정되게 해주는 명령어이다.

CONSTRAINT 명령어는 키를 설정해주겠다는 명령어이다. 

PRIMARY KEY 명령어로 기본키로 설정해주거나, 혹은

FOREIGN KEY 명령어로 외래 키로 설정해줄 수 있다. 

REFERENCES는 FOREIGN KEY 명령어로 외래 키로 지정된 키를 다른 테이블에서 참조해오겠다.라는 개념으로 사용된다.

 

기본적인 구성은 위와 같고 이번엔 직접 만들어 보겠다.

 

--  테이블명 : DEPT
--------------------------------------------------------------------
--  컬럼명     데이터타입(크기)   NULL    KEY     DEFAULT
--------------------------------------------------------------------
--  DEPT_ID    NUMBER(4)         N.N     P.K
--  DEPT_NAME  VARCHAR2(30)     
--------------------------------------------------------------------

 

위의 모양처럼 생긴 테이블을 만들어 볼 것이다.

 

CREATE TABLE DEPT(
    DEPT_ID NUMBER(3) NOT NULL,   -- P.K는 N.N로 해주지 않아도 자동으로 N.N이 적용됨
    DEPT_NAME VARCHAR2(30),
    -- P.K 설정
    CONSTRAINT PK_DEPT PRIMARY KEY(DEPT_ID)
);

 

별로 어려울 것 없이 직접 테이블 명 선언해주고, 칼럼명과 데이터 타입과 크기를 설정해주면 간단하게 만들어진다.

위의 NOT NULL 같은 경우 어차피 DEPT_ID를 기본키로 사용하였기 때문에 굳이 써주지 않아도 괜찮다.

 

CREATE TABLE EMP(
    EMP_ID CHAR(4) NOT NULL,
    EMP_NAME VARCHAR2(30),
    EMP_JOB VARCHAR2(30),
    DEPT_ID NUMBER(3),
    -- P.K 설정
    CONSTRAINT PK_EMP PRIMARY KEY(EMP_ID),
    -- F.K 설정
    CONSTRAINT FK_EMP_DEPT FOREIGN KEY(DEPT_ID)
        REFERENCES DEPT(DEPT_ID)
);

 

그럼 이번엔 좀 더 심화하여서 외래 키도 사용해보도록 하겠다.

여기서 사용된 외래 키는 DEPT_ID로서 방금 위에서 만든 테이블에서 키를 가져오겠다는 것이다.

 

CREATE TABLE EMP(
    EMP_ID CHAR(4) NOT NULL,
    EMP_NAME VARCHAR2(30),
    EMP_JOB VARCHAR2(30),
    DEPT_ID NUMBER(3),
    -- P.K 설정
    CONSTRAINT PK_EMP PRIMARY KEY(EMP_ID),
    -- F.K 설정
    CONSTRAINT FK_EMP_DEPT FOREIGN KEY(DEPT_ID)
        REFERENCES DEPT(DEPT_ID)
);

 

마찬가지로 별로 어려울 것 없이 코드를 짤 수 있었다.

여기서 주의해야 할 점은 외래 키로 사용할 DEPT_ID 도 반드시 선언을 해주어야지 인식을 할 수가 있다.

두 번째로는 외래 키 이름을 설정할 때 혹시라도 일어날 수 있는 중복을 방지해주기 위해서 

FK_테이블명_참조 테이블명 이런 식으로 이름을 지어주면서 중복을 방지해준다.

 

2개의 테이블만 더 생성해보겠다.

-- **사업장테이블
-- 테이블명 : SITE
-----------------------------------------------------
-- 컬럼명     데이터타입   크기   NULLABLE   KEY
-----------------------------------------------------
-- SITE_ID   CHAR        10    N.N        PK
-- SITE_NAME VARCHAR2    30
-- SITE_ADDR VARCHAR2    100
-- SITE_TEL  VARCHAR2    20
-- REMARKS   VARCHAR2    100
-----------------------------------------------------
CREATE TABLE SITE(
    SITE_ID CHAR(10),
    SITE_NAME VARCHAR2(30),
    SITE_ADDR VARCHAR2(100),
    SITE_TEL VARCHAR2(20),
    REMARKS VARCHAR2(100),
    -- P.K 설정
    CONSTRAINT PK_SITE PRIMARY KEY(SITE_ID)
);

 

-- **근무자테이블
-- 테이블명 : WORK
-----------------------------------------------------
-- 컬럼명     데이터타입   크기   NULLABLE   KEY
-----------------------------------------------------
-- SITE_ID   CHAR        10    N.N        PK/FK
-- EMP_ID    CHAR        4     N.N        PK/FK
-- IN_DATE   DATE 
-----------------------------------------------------
CREATE TABLE WORK(
    SITE_ID CHAR(10),
    EMP_ID CHAR(4),
    IN_DATE DATE,
    -- P.K 설정
    CONSTRAINT PK_WORK PRIMARY KEY(SITE_ID,EMP_ID),
    -- F.K 설정
    CONSTRAINT FK_WORK_SITE FOREIGN KEY(SITE_ID)
        REFERENCES SITE(SITE_ID),
    CONSTRAINT FK_WORK_EMP FOREIGN KEY(EMP_ID)
        REFERENCES EMP(EMP_ID)
);

 

바로 위 코드에선 한 가지만 조심해주면 된다. PK는 한 번에 2가지를 설정해줄 수 있고,

FK는 다른 테이블들에서 가져온 것이기에 각 테이블에 맞는 참조를 해줘야 한다는 점이다.