[Oracle] DDL 문을 사용하여 테이블 생성 및 관리를 해보자 :)

-데이터베이스 객체

 

오라클 데이터베이스는 여러 데이터 구조를 포함할 수 있습니다. 각 구조는 데이터베이스 개발 과정의 구축 단계에서 생성될 수 있도록 데이터베이스 설계 시에 기본적인 틀을 만들어야 합니다.

 

• 테이블: 데이터를 저장합니다.

• 뷰: 하나 이상의 테이블에 있는 데이터의 부분 집합입니다.

• 시퀀스: 숫자 값을 생성합니다.

• 인덱스: 일부 질의 성능을 향상시킵니다.

• 동의어: 객체에 다른 이름을 부여합니다.

 

-이름 지정 규칙

 

테이블 이름 및 열 이름은 다음 규칙을 따라야 합니다.

 

• 문자로 시작해야 합니다.

• 길이는 1–30자 사이여야 합니다.

• A–Z, a–z, 0–9, _, $, #만 포함할 수 있습니다.

• 동일한 유저가 소유한 다른 객체의 이름과 중복되지 않아야 합니다.

• Oracle 서버 예약어는 사용할 수 없습니다.

 

-CREATE TABLE 문

 

• 다음이 필요합니다.

– CREATE TABLE 권한

– 저장 영역

• 다음을 지정합니다.

– 테이블 이름

– 열 이름 , 열 데이터 유형 및 열 크기

 

SQL CREATE TABLE 문을 실행하여 데이터를 저장할 테이블을 생성합니다. DDL 문 중 하나인 이 명령문은 오라클 데이터베이스 구조를 생성, 수정 또는 제거하는 데 사용되는 SQL 문의 일종입니다. 이러한 명령문은 데이터베이스에 즉시 적용되고 데이터 딕셔너리에 정보를 기록하기도 합니다. 테이블을 생성하려면 유저는 CREATE TABLE 권한이 있어야 하며 객체를 생성할 저장 영역이 있어야 합니다. DBA(데이터베이스 관리자)는 DCL(데이터 제어어) 문을 사용하여 유저에게 권한을 부여합니다.

 

-다른 유저의 테이블 참조

 

• 다른 유저가 소유한 테이블은 유저의 스키마에 없습니다.

• 이러한 테이블에는 소유자의 이름을 접두어로 사용해야 합니다.

 

스키마는 데이터의 논리적 구조 또는 스키마 객체의 모음입니다. 스키마는 데이터베이스 유저가 소유하며 해당 유저와 동일한 이름을 가지고 있습니다. 각 유저는 단일 스키마를 소유합니다. 스키마 객체에는 테이블, 뷰, 동의어, 시퀀스, 내장 프로시저, 인덱스, 클러스터 및 데이터베이스 링크가 있으며 SQL을 사용하여 스키마 객체를 생성 및 조작할 수 있습니다.

 

-DEFAULT 옵션

 

• 삽입 시 열의 기본값을 지정합니다.

• 리터럴 값, 표현식 또는 SQL 함수는 올바른 값입니다.

• 다른 열의 이름이나 의사 열은 잘못된 값입니다.

• 기본 데이터 유형은 열 데이터 유형과 일치해야 합니다.

 

CREATE TABLE hire_dates (id

NUMBER(8), hire_date DATE DEFAULT SYSDATE);

 

테이블을 정의할 때 열에 기본값이 제공되도록 DEFAULT 옵션을 사용하여 지정할 수 있습니다. 이 옵션은 열에 대한 값이 없는 행이 삽입되는 경우 해당 열에 null 값이 입력되는 것을 방지합니다. 기본값으로 리터럴, 표현식 또는 SYSDATE 및 USER와 같은SQL 함수를 사용할 수 있지만 다른 열의 이름이나 NEXTVAL 또는 CURRVAL과 같은 의사 열은 사용할 수 없습니다. 기본 표현식은 열의 데이터 유형과 일치해야 합니다.

 

-테이블 생성

 

CREATE TABLE dept

(deptno NUMBER(2),

dname VARCHAR2(14),

loc VARCHAR2(13),

create_date DATE DEFAULT SYSDATE);

 

예제에서는 네 개의 열(DEPTNO, DNAME, LOC, CREATE_DATE)을 가진DEPT 테이블을 생성합니다. CREATE_DATE 열은 기본값을 가지고 있습니다. INSERT 문에 값이 제공되지 않으면 시스템 날짜가 자동으로 삽입됩니다. 테이블이 생성되었는지 확인하려면 DESCRIBE 명령을 실행합니다. 테이블 생성은 DDL 문 작업이기 때문에 이 명령문이 실행되면 자동 커밋이 발생합니다.

 

-데이터 유형

데이터 유형	설명
VARCHAR2( size)	가변 길이 문자 데이터
CHAR( size)	고정 길이 문자 데이터
NUMBER( p,s)	가변 길이 숫자 데이터
DATE	날짜 및 시간 값
LONG	가변 길이 문자 데이터(최대 2GB)
CLOB	문자 데이터(최대 4GB)
RAW and LONG RAW	원시 이진 데이터
BLOB	바이너리 데이터(최대 4GB)
BFILE	외부 파일에 저장된 바이너리 데이터(최대 4GB)
ROWID	테이블에 있는 행의 고유한 주소를 나타내는 base-64 숫자 체계

 

-Datetime 데이터 유형

 

데이터 유형	설명
TIMESTAMP	소수 표시 초 단위의 날짜로 시간을 저장할 수 있습니다. 소수 표시 초 값뿐만 아니라 DATE 데이터 유형의 년, 월, 일, 시, 분 및 초를 저장합니다. 이 데이터 유형은 WITH TIMEZONE, WITH LOCALTIMEZONE 등과 같은 여러 변형이 있습니다.
INTERVAL YEAR TO MONTH	년, 월 간격으로 시간을 저장할 수 있습니다. 년과 월로만 유효한 부분을 나타내는 두 datetime 값 사이의 차이를 표시하는 데 사용됩니다.
INTERVAL DAY TO SECOND	일, 시, 분, 초 간격으로 시간을 저장할 수 있습니다. 두 datetime 값 사이의 정밀한 차이를 나타내는 데 사용됩니다.

 

-제약 조건 포함

 

• 제약 조건은 테이블 레벨에서 규칙을 강제 적용합니다.

• 제약 조건은 테이블에 종속 관계가 있는 경우 삭제를 방지합니다.

• 유효한 제약 조건 유형은 다음과 같습니다.

– NOT NULL : 열에 null 값을 포함할 수 없음을 지정합니다.

– UNIQUE : 테이블의 모든 행에 대해 값이 고유해야 하는 열 또는 열 조합을 지정합니다.

– PRIMARY KEY : 테이블의 각 행을 고유하게 식별합니다.

– FOREIGN KEY : 특정 테이블의 열과 참조 테이블의 열 간에 참조 무결성을 설정하고 적용하여 한 테이블 의 값이 다른 테이블의 값과 일치하도록 합니다.

– CHECK : 참이어야 하는 조건을 지정합니다.

 

-제약 조건 지침

• 유저가 제약 조건의 이름을 지정하거나 Oracle 서버가 SYS_Cn 형식을 사용하여 이름을 생성할 수 있습니다.

• 다음 시점 중 하나에서 제약 조건을 생성합니다.

– 테이블이 생성되는 시점

– 테이블 생성 후

• 열 또는 테이블 레벨에서 제약 조건을 정의합니다.

• 데이터 딕셔너리에서 제약 조건을 확인합니다.

 

-제약 조건 정의

• 구문

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]

[column_constraint],

...

[table_constraint][,...]);

 

• 열 레벨 제약 조건 구문

column [CONSTRAINT constraint_name] constraint_type,

 

• 테이블 레벨 제약 조건 구문

column,...

[CONSTRAINT constraint_name] constraint_type (column, ...),

 

• 열 레벨 제약 조건 예제

CREATE TABLE employees(

employee_id NUMBER(6)

CONSTRAINT emp_emp_id_pk PRIMARY KEY,

first_name VARCHAR2(20),

...);

 

• 테이블 레벨 제약 조건 예제

CREATE TABLE employees(

employee_id NUMBER(6),

first_name VARCHAR2(20),

...

job_id VARCHAR2(10) NOT NULL,

CONSTRAINT emp_emp_id_pk

PRIMARY KEY (EMPLOYEE_ID));

 

제약 조건은 대개 테이블과 동시에 생성됩니다. 제약 조건은 테이블 생성 후에 테이블에 추가할 수 있고 일시적으로 비활성화할 수도 있습니다. 예제에서는 모두 EMPLOYEES 테이블의 EMPLOYEE_ID 열에 Primary key 제약 조건을 생성합니다.

1. 첫번째 예제는 열 레벨 구문을 사용하여 제약 조건을 정의합니다.

2. 두번째 예제는 테이블 레벨 구문을 사용하여 제약 조건을 정의합니다.

 

-NOT NULL 제약 조건

 

NOT NULL 제약 조건은 열이 null 값을 포함하지 않도록 보장합니다. NOT NULL 제약 조건이 없는 열은 기본적으로 null 값을 포함할 수 있습니다. NOT NULL 제약 조건은 열 레벨에서 정의되어야 합니다. EMPLOYEES 테이블에서 EMPLOYEE_ID 열은 Primary key로 정의되었으므로 NOT NULL을 상속합니다. 그렇지 않으면 LAST_NAME, EMAIL, HIRE_DATE 및 JOB_ID 열에 NOT NULL 제약 조건이 적용됩니다.

 

-UNIQUE 제약 조건

 

UNIQUE Key 무결성 제약 조건에서는 하나의 열 또는 여러 열에 있는 모든 값(키)이 고유해야 합니다. 즉, 테이블의 두 행은 지정된 열 또는 열 집합에서 중복된 값을 가질 수 없습니다.

UNIQUE Key 제약 조건 정의에 포함된 열 또는 열 집합을 Unique key라고 합니다. UNIQUE 제약 조건은 두 개 이상의 열로 구성되며 이러한 열 그룹을 조합 Unique Key라고 합니다.

UNIQUE 제약 조건에서는 동일한 열에 대해 NOT NULL 제약 조건을 정의하지 않는 한, null을 입력할 수 있습니다. 실제로 null은 어떤 값과도 같지 않기 때문에 모든 행에서 NOT NULL 제약 조건이 없는 열에 대해 null을 포함할 수 있습니다 . 열 또는 조합UNIQUE Key의 모든 열에서 null은 항상UNIQUE 제약 조건을 충족합니다.

 

-PRIMARY KEY 제약 조건

 

PRIMARY KEY 제약 조건은 테이블에 대해 Primary key를 생성합니다. 각 테이블에는 하나의 Primary key만 생성할 수 있습니다. PRIMARY KEY 제약 조건은 테이블의 각 행을 고유하게 식별하는 열 또는 열 집합입니다. 이 제약 조건은 열 또는 열 조합에 고유성을 적용하고 Primary key에 속하는 열이 null 값을 포함할 수 없도록 합니다.

 

-FOREIGN KEY 제약 조건

FOREIGN KEY(또는 참조 무결성) 제약 조건은 열 또는 열 조합을 Foreign key로 지정하고 동일한 테이블이나 다른 테이블의 Primary key 또는 Unique key와의 관계를 설정합니다.

 

CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),

 

FOREIGN KEY 제약 조건은 열 또는 테이블 제약 조건 레벨에서 정의될 수 있습니다. 조합 Foreign key는 테이블 레벨 정의를 사용하여 생성되어야 합니다. 제약 조건의 이름은 EMP_DEPT_FK입니다. 제약 조건이 단일 열을 기반으로 하는 경우 Foreign key는 열 레벨에서도 정의될 수 있습니다.

 

-FOREIGN KEY 제약 조건: 키워드

 

• FOREIGN KEY: 테이블 제약 조건 레벨에서 하위 테이블의 열을 정의합니다.

• REFERENCES: 테이블 및 상위 테이블의 열을 식별합니다.

• ON DELETE CASCADE: 상위 테이블의 행이 삭제될 때 하위 테이블의 종속 행을 삭제합니다 .

• ON DELETE SET NULL: 종속 Foreign key 값을 null로 변환합니다.

 

-CHECK 제약 조건

 

• 각 행이 충족해야 하는 조건을 정의합니다.

• 다음 표현식은 허용되지 않습니다.

– CURRVAL, NEXTVAL, LEVEL, ROWNUM 의사 열에 대한 참조

– SYSDATE, UID, USER, USERENV 함수에 대한 호출

– 다른 행의 다른 값을 참조하는 query

 

..., salary NUMBER(2)

CONSTRAINT emp_salary_min

CHECK (salary > 0),...

 

단일 열에서 해당 정의의 열을 참조하는 여러 CHECK 제약 조건을 가질 수 있습니다. 열에 정의할 수 있는CHECK 제약 조건의 수에는 제한이 없습니다.

 

-제약 조건 위반

 

열에 제약 조건이 있는 경우 제약 조건 규칙을 위반하려고 하면 오류가 반환됩니다. 예를 들어, 무결성 제약 조건이 적용된 값이 있는 레코드를 갱신하려고 하면 오류가 반환됩니다. 또한 다른 테이블에서 Foreign key로 사용되는 Primary key를 포함한 행은 삭제할 수 없습니다.

 

-Subquery를 사용하여 테이블 생성

 

• CREATE TABLE 문과 AS subquery 옵션을 결합하여 테이블을 생성하고 행을 삽입합니다.

CREATE TABLE table [(column, column...)] AS subquery;

• 지정된 열 개수와 subquery 열 개수를 일치시킵니다.

• 열 이름과 기본값을 가진 열을 정의합니다.

 

CREATE TABLE dept80

AS

SELECT employee_id, last_name,

salary*12 ANNSAL,

hire_date

FROM employees

WHERE department_id = 80;

 

예제에서는 부서 80에서 근무하는 모든 사원의 세부 정보가 담긴 DEPT80이라는 테이블을 생성합니다. DEPT80 테이블의 데이터는 EMPLOYEES 테이블에서 가져옵니다. DESCRIBE 명령을 사용하여 데이터베이스 테이블이 있는지 확인하고 열 정의를 검사할 수 있습니다.

 

-읽기 전용 테이블

 

ALTER TABLE 구문을 사용하여 다음을 수행할 수 있습니다.

• 테이블을 읽기 전용 모드로 설정하여 테이블을 유지 관리하는 동안 DDL 문 또는DML 문에 의한 변경을 방지합니다. • 테이블을 다시 읽기 /쓰기 모드로 설정합니다.

 

ALTER TABLE employees READ ONLY;

-- perform table maintenance and then

-- return table back to read/write mode

ALTER TABLE employees READ WRITE;

 

테이블이 READ-ONLY 모드이면 테이블에 영향을 주는 DML 문 또는SELECT ...FOR UPDATE 문을 실행할 수 없습니다. 테이블의 데이터를 수정하지 않는 한 DDL 문은 실행할 수 있습니다. 테이블과 연결된 인덱스에 대한 작업은 테이블이 READ ONLY 모드일 때 가능합니다. 읽기 전용 테이블을 읽기/쓰기 모드로 되돌리려면 READ/WRITE를 지정합니다.

 

-테이블 삭제

• 테이블을 Recycle bin으로 이동

• PURGE 절이 지정되면 테이블 및 해당 데이터를 완전히 제거

• 종속 객체 무효화 및 테이블의 객체 권한 제거

 

DROP TABLE dept80;