[Oracle SQL] DDL (Data Definition Language)

DDL(Data Definition Language)

CREATE

CREATE [GLOBAL TEMPORARY] TABLE [schema.]table
	(colunm datatype [DEFAULT expr][, ...,]);

Table을 생성한다.

 

GLOBAL TEMPORARY : 임시 Table을 생성하고, 모든 Session에서 볼 수 있다. (입력된 Data는 해당 Session에서만 확인 가능)

schema : 소유자 이름과 동일하다.

table, column : table 이름, column 이름

DEFAULT expr : INSERT 문에서 값을 생략했을 때 해당 Column에 사용될 기본 값

datatype : Column Data Type 및 길이

 

DEFAULT 옵션

데이터 insert 시 열(Column)의 기본 값을 지정한다. 따라서 Data Type이 일치해야 한다.

만약 hiredate DATE DEFAULT SYSDATE로 SYSDATE를 DEFAULT 값으로 준다면, insert 시 hiredate에 특정 값을 넣지 않는 이상 자동적으로 SYSDATE 값이 들어간다.

리터럴(literal) 값, 표현식, SQL 함수 등의 값이 들어갈 수 있다. 다른 칼럼의 이름 혹은 의사 칼럼(Pseudo Column)은 DEFAULT 값으로 설정할 수 없다.

 

CREATE TABLE employee(
    id NUMBER(10),
    name VARCHAR2(10),
    address VARCHAR2(100),
    phone VARCHAR2(14),
    hiredate DATE Default sysdate
)

 

Subquery 사용하여 CREATE

CREATE Table table [column(, column, ...)] AS subquery

CREATE 문과 AS subquery 옵션을 결합하여 Table을 생성하고, 행을 삽입할 수 있다.

 

CREATE TABLE employee_temp
AS
    SELECT id, name, address, phone, hiredate
    FROM employee
    WHERE name = '홍길동';

 

ALTER

Table을 수정하는 DDL문으로, 새로운 칼럼을 추가하거나 기존 칼럼을 수정할 수 있다.

ALTER TABLE table
ADD (column datatype [DEFAULT expr] [, column datatype], ...)

ALTER TABLE table
MODIFY (column datatype [DEFAULT expr] [, column datatype], ...)

ALTER TABLE table
DROP (column[, column, ...])

/* 예시 */
ALTER TABLE employee ADD (grade VARCHAR2(9) CHECK (grade IN ('일반', '관리자')));

ADD 절 사용 시 새로운 칼럼을 추가할 수 있고, 생성된 새로운 칼럼은 마지막 칼럼이 된다.

 

MODIFY 절 사용 시 칼럼의 Data 유형, 크기, 기본 값을 변경할 수 있다.

숫자 컬럼의 너비 또는 전체 자릿수를 증가시킬 수 있다.

칼럼이 Null 값만 포함하거나 행이 없을 때 칼럼의 너비를 줄일 수 있다.

칼럼이 Null 값만 포함할 때 Data Type을 변경할 수 있다.

칼럼이 Null 값만 포함하거나 칼럼 크기를 변경하지 않는다면 CHAR, VARCHAR2 Type은 상호 변경이 가능하다.

칼럼의 기본 값을 변경하면 변경 이후에 삽입되는 데이터의 칼럼에만 적용된다.

 

DROP 절 사용 시 칼럼을 삭제할 수 있다. 삭제된 칼럼은 복구할 수 없다.

삭제할 칼럼에 Data가 있는지 없는지에 상관없이 삭제할 수 있고, 한 번에 한 칼럼만 삭제할 수 있다.

삭제하는 칼럼이 삭제된 이후에도 하나 이상의 칼럼이 존재해야 한다.

 

DROP

DROP TABLE table;

Table의 모든 Data 및 구조를 삭제한다.

DROP 문은 테이블을 생성한 사용자 또는 DROP ANY TABLE 권한을 가진 사용자만이 사용할 수 있다.

DROP 문 실행 시 보류 중인 Transaction을 모두 Commit 하고, 해당 명령문은 Rollback 할 수 없다. (9i 이상부터 Flashback 가능)

모든 Index가 삭제되고, 테이블에 관련된 View 및 Sysnonym은 존재하지만 사용할 수 없다.

 

RENAME

RENAME employee to emp;

Table, View, Sequence, Synonym 등 객체 이름을 변경할 수 있다. 이때, 사용자가 객체의 소유자여야 한다.

 

TRUNCATE

TRUNCATE TABLE emp;

Table의 모든 행을 삭제하고, Table이 사용한 저장 공간을 해제한다.

DML문인 DELETE와는 다르게 TRUNCATE는 DDL문이므로 Rollback이 불가능하다.

 

COMMENT

COMMENT ON TABLE table | COLUMN table.column IS 'text';

Table 또는 Column에 주석을 추가할 수 있다.

주석은 Data Dictionary View를 통해 볼 수 있다.

ALL_COL_COMMNETS, USER_COL_COMMENTS, ALL_TAB_COMMENTS, USER_TAB_COMMNETS

 

 

[부록] Oracle DB Table

사용자 Table은 사용자가 생성 및 유지 관리하는 Table 모음으로 사용자 정보를 포함한다.

Data Dictionary는 DB가 생성 및 유지 관리하는 Table 모음으로 DB 정보를 포함하며, SYS User 소유, View를 제공한다.

 

Oracle DB Table에 붙어있는 접두어가 해당 Table이 의미를 포함한다.

USER_ : 사용자가 소유한 객체에 관한 View

ALL_ : 사용자가 액세스 할 수 있는 객체에 관한 View

DBA_ : DBA Role을 할당받은 User만 액세스 가능

V$ : 동적 성능 View, DB 성능(상태) 및 Lock에 관한 정보

 

예를 들어 USER_ 에 관한 테이블들은 다음과 같다.

USER_TABLES : 사용자가 소유한 테이블 정보

USER_OBJECTS : 사용자가 소유한 객체 정보(Table, View, Synonym, Sequence,...)

USER_CATALOG : 사용자가 소유한 객체의 유형 정보

USER_TAB_COLUMNS : 사용자가 소유한 Table Column 정보

USER_INDEXES : 사용자가 소유한 Index  정보

USER_TAB_COMMENTS : 사용자가 소유한 Table 주석 정보

USER_COL_COMMENTS : 사용자가 소유한 Table Column 주석 정보

USER_CONSTRAINTS : 사용자가 소유한 제약조건(Copnstraint) 정보