[Oracle] DDL(Data Definition Language)

SQL에서 데이터 정의어는 데이터베이스의 3층 스키마를 정의하는 등, 데이터베이스 관리자는 데이터 정의어를 사용하여 구축하고자 하는 데이터베이스 여러 개체(Object)를 정확하게 기술해야 합니다.

 

1. 개체(Object)

데이터베이스의 중요한 객체는 다음과 같습니다.

Object	설 명
Table	행과 열로 구성된 2차원 테이블로 데이터를 저장하는 개체
View	하나 이상의 테이블로부터 유도된 데이터의 부분집합 개체
Index	빠른 검색을 위해 사용하는 개체
Sequence	순차적인 숫자 값을 생성하는 개체

 

2. 테이블

2.1 테이블의 생성

관계형 데이터베이스에서 테이블은 데이터를 저장할 수 있는 개체 테이블의 생성을 위해서는 CREATE TABLE, 변경을 위해서는 ALTER TABLE, 삭제를 위해서는 DROP TABLE 명령을 사용합니다. 다음은 테이블의 생성과 관련된 예문입니다.

CREATE TABLE TEST1
(
    TEST_UID  VARCHAR2(10),
    TEST_DATE DATE
 );

 

데이터베이스 내에 새로운 테이블을 생성할 때 CREATE TABLE 명령을 이용하며, 형식은 다음과 같습니다.

CREATE TABLE table-name
(
    column-name1	data-type	default-value,
    column-namen	data-type   default-value
    constraints
);

또는

CREATE TABLE table-name
AS SELECT column-name, [column-name, ] ... FROM ... WHERE...
;

생성한 테이블은 다음과 같이 확인 할 수 있습니다.

DESC 테이블이름;

 

2.2 데이터 속성(Type)

오라클에서 열을 정의할 때 주로 사용되는 데이터의 속성은 다음 표와 같습니다.

타 입	설 명
NUMBER(n,m)	숫자 데이터에 대한 정의로 n은 자리수, m은 소수이하 자리수를 정의하는데 사용
CHAR(n)	문자 데이터에 대한 정의에 사용
VARCHAR2(n)	가변길이 문자데이터에 대한 정의에 사용
DATE	날짜 데이터에 대한 정의에 사용
LONG	2GB의 가변길이 문자 데이터에 대한 정의에 사용
TIMESTAMP	년,월,일,시,분,초 6자리 소수부 초 형태로 시간정보를 정의에 사용
LOB	4GB의 텍스트, 동영상, 이미지, 사운드 등에 대한 정의에 사용

 

2.3 테이블 변경

테이블에 새로운 열을 추가하는 작업으로 형식은 다음과 같습니다.

ALTER TABLE table-name
ADD (
		column-name1	date-type,
        column-name2	date-type,
        .....................
    );

다음은 T_DEPT 테이블에 주소 열을 삽입하는 예입니다.

ALTER TABLE t_dept
ADD(
		addr varchar2(50)
   );   

 

다음은 기존 테이블의 열의 구조를 변경하는 작업으로 형식은 다음과 같습니다.

ALTER TABLE table-name
MODIFY	(	
			column-name1	date-type,
            column-name2	data-type,
            .....................
        );

다음은 T_DEPT 테이블에 주소 열의 구조를 변경하는 예입니다.

ALTER TABLE t_dept
MODIFY (
			addr varchar2(60)
       );

 

다음은 기존 테이블에 열을 삭제하는 작업으로 형식은 다음과 같습니다.

ALTER TABLE table-name
DROP	(
			column-name1, [column-name2......]
        );

다음은 T_DEPT 테이블에 주소 열을 삭제하는 예입니다.

ALTER TABLE t_dept
DROP	(
			addr
        );

 

기존 데이터베이스내의 테이블의 이름을 변경할 경우 RENAME 명령을 사용하며, 형식은 다음과 같습니다.

RENAME OLD_TABLE-NAME TO NEW_TABLE-NAME;

RENAME t_dept to tmp_dept;

 

데이터베이스의 테이블을 삭제할 경우 DROP TABLE 명령을 사용하며, 형식은 다음과 같습니다.

DROP TABLE table-name

 

3. 제약조건

관계모델의 제약조건은 데이터베이스 상태(인스턴스)가 항상 만족해야할 기본 규칙입니다. 제약조건은 일반적으로 테이블을 정의할 때 명시되며, 이 후 테이블에 데이터가 삽입, 삭제, 수정 등의 연산으로 변하더라도 항상 만족해야 합니다.

 

3.1 제약조건의 종류

● 도메인 제약 조건 : 각 열의 값은 반드시 해당 도메인에 속하는 원자 값이어야 한다.

 

● 키 제약 조건  : 테이블에는 테이블의 각 레코드(튜플)를 유일하게 식별할 수 있는 기본키를 가져야한다.

 

● 무결성 제약 조건

 - 엔티티 무결성 : 기본키는 널값을 가질 수 없으며 레코드(튜플)를 유일하게 식별할 수 있어야 한다.

 - 참조 무결성 : 외래키와 관련이 있으며, 참조되는 테이블의 외래키 값은 반드시 참조되는 테이블의 기본키 값으로 존재하여야 한다.

 

이를 오라클에서는 5가지 유형으로 구현하였습니다.

제약 조건	설 명
NOT NULL	열에 NULL 값을 허용하지 않음
UNIQUE KEY	열 또는 열의 조합이 유일성을 가져야 함
PRIMARY KEY	열 또는 열의 조합이 NULL값이 아니며, 유일성을 가져야 함
FOREIGN KEY	다른 테이블의 열을 참조되는 테이블에 값이 존재하여야 함
CHECK	열에 들어갈 값에 대한 조건을 명시함

 

3.1.1 NOT NULL

해당 열에 NULL 값이 저장되어서는 안 되는 경우 사용하며, 기본키 제약조건은 기본적으로 NOT NULL 조건을 만족하여야 합니다.

 

3.1.2 UNIQUE KEY

기본키가 아니더라도 어떤 열의 값이 유일한 값을 유지하여야 할 때 사용하며, 자동으로 인덱스가 만들어집니다.

 

3.1.3 PRIMARY KEY

테이블에 대한 식별자를 생성해 주며, 기본키는 한 테이블에 단 한 개만 존재하여야 하고, 인덱스를 자동으로 생성해 줍니다. 

 

3.1.4 FOREIGN KEY

참조 무결성을 유지하기 위해 자식 테이블의 열을 외래키로 선언합니다.

 

3.1.5 CHECK

어떤 열의 값에 조건을 제시하며, 조건에 맞지 않는 값은 입력될 수 없습니다.

 

3.2 제약 조건의 변경

제약조건을 추가할 경우, ALTER TABLE 명령을 사용하며 형식은 다음과 같습니다.

ALTER TABLE table-name
ADD	CONSTRAINT constrait name;

다음은 제약조건을 추가하는 예입니다.

ALTER TABLE tmpa_emp
ADD (constraint pk_tmpa_emp primary key(empno));

 

제약조건을 삭제할 경우, 형식은 다음과 같습니다.

ALTER TABLE table-name
DROP CONSTRAINT constraint_name;

다음은 제약조건을 삭제하는 예입니다.

ALTER TABLE t_emp
DROP CONSTRAINT f_emp_deptno cascade;

 

제약 조건을 비활성화하는 경우, 형식은 다음과 같습니다.

ALTER TABLE table-name
DISABLE|ENABLE CONSTRAINT constraint_name;

다음은 제약조건을 비활성화하는 예입니다.

ALTER TABLE t_emp
DISABLE CONSTRAINT n_emp_job;

다음은 비활성화된 제약조건을 다시 활성화하기 위한 예입니다.

ALTER TABLE emp
ENABLE CONSTRAINT n_emp_job;

4. 뷰(VIEW)

뷰는 SQL의 질의문으로 정의되는 데이터베이스내의 "가상 테이블"입니다. 사용자들에게는 데이터가 있는 테이블(베이스 테이블)처럼 보이나 실제로는 데이터는 존재하지 않으며 사용할 때 데이터 베이스 테이블로부터 유도되어 운영됩니다. 이러한 뷰는 갱신에 대한 문제점은 있으나 다음과 같은 장점을 갖습니다.

 

● 뷰는 데이터베이스의 논리적 데이터 독립성을 제공한다.

 

● 뷰를 통하여 사용자에게 한정된 데이터만을 조작할 수 있게 함으로써 데이터의 보완 기능을 강화한다.

 

뷰의 정의 및 삭제를 위한 형식은 다음과 같습니다.

CREATE VIEW view_name
	[(column-name [, column-name] ...
AS SELECT column-name [, column-name] ... FROM ... WHERE ...;

DROP VIEW view-name

이때 SELECT를 사용하므로 두 개 이상의 테이블로부터 유도되는 조인뷰도 가능합니다. 생성된 뷰를 삭제하려면 DROP VIEW를 사용합니다. 뷰는 하나의 테이블로 생성되는 단일 뷰와 2개 이상의 테이블로 생성되는 조인뷰가 있습니다.

 

4.1 단순 뷰

단순 뷰는 단일 베이스테이블로부터 유도된 뷰입니다. 다음은 뷰를 생성하는 예시입니다.

CREATE OR REPLACE VIEW v_emp1
AS SELECT * FROM emp WHERE DEPTNO = 20;

4.2 조인 뷰

조인뷰는 2개 이상의 베이스테이블로부터 유도된 뷰입니다.

CREATE OR REPLACE VIEW v_emp2
AS SELECT empno, ename, mgr, hiredate, sal, dept.dname
FROM EMP, DEPT
WHERE EMP.deptno = dept.deptno;

4.3 인라인 뷰

인라인 뷰는 검색문을 작성할 때 FROM절에 테이블의 이름이 아닌 SELECT 문으로 정의된 뷰입니다. 다음 예문은 부서별 사원 중 급여를 가장 많이 받는 사원의 정보를 부서명을 검색합니다.

SELECT a.empno, a.ename, a.sal, a.dname
FROM EMP a, 
		(
       		SELECT deptno, max(sal) maxsal
        	FROM EMP
            GROUP BY deptno
        ) b,
     DEPT c
WHERE a.deptno = b.deptno and a.sal = b.maxsal and a.deptno = c.deptno
ORDER BY a.deptno, a.deptno

4.4 TOP-N 부질의

TOP-N 질의는 게시판에 최근 몇 개의 게시글을 표현하는 등 전체 데이터에서 몇 개만 검색 할 때 사용합니다. 이런 경우 인라인 뷰에 ORDER BY절을 이용하여 정렬한 후 해당되는 몇 개의 행만을 검색하여 사용하며, 형식은 다음과 같습니다.

SELECT column-names
FROM   (
			SELECT column-names
            FROM table-name
            ORDER BY column-names
       )
WHERE ROWNUM <= n;

다음은 EMP 테이블에서 급여를 가장 많이 받는 5명의 사원번호, 이름, 급여를 검색하는 예시입니다.

SELECT empno, ename, sal
FROM (
		SELECT empno, ename, sal
        FROM EMP
        ORDER BY sal desc, empno
     )
WHERE rownum <= 5;

5. 인덱스

인덱스는 데이터베이스의 빠른 검색을 위하여 정의되며, 한 개 이상의 열을 인덱스로 정의할 수 있습니다. 일반적으로 책에 한글 인덱스와 영문 인덱스가 같이 있는 것을 흔히 볼 수 있듯이 어떤 테이블에 대해서 여러 개의 인덱스를 정의할 수 있습니다. 그러나 책에서 인덱스를 사용하면 책이 페이지가 늘어나듯이 데이터베이스에서도 저장 공간을 더 사용하게 됨으로 인덱스를 정의할 때에는 인덱스의 활용도를 고려하여야 합니다. 예를 들어 EMP 테이블에서 사원번호는 기본키이므로 자동으로 인덱스가 정의되어지며, 이름으로 검색하는 질의가 많을 경우 이름으로 인덱스를 정의하면 이름으로 검색할 경우 검색 속도를 향상시킬 수 있습니다. 그러나 부서번호로 검색하는 경우가 극히 적다면 부서번호를 인덱스로 정의할 경우 활용도 떨어지며, 기억공간은 낭비됩니다. 

 

인덱스 생성과 삭제 형식은 다음과 같습니다.

CREATE INDEX index-name ON table-name(expression)
DROP INDEX index-name

6. SEQUENCE

어떤 대학에서 신입생 모집을 위해 원서 접수를 받을 때, 각 학과별로 신입생들에게 연속적인 수험번호를 부여해야 한다면, 오라클 데이터베이스에서 제공하는 시퀀스 개체를 이용할 수 있습니다. 시퀀스는 어떤 연속적인 숫자 값을 자동적으로 증가시켜 제공합니다. 시퀀스를 생성하면, 사용자는 시퀀스 호출만으로 연속적인 번호를 이용할 수 있으며, 형식은 다음과 같습니다.

CREATE SEQUENCE sequence-name
	INCREMENT BY n
    START WITH n
    MAXVALUE n | NOMAXVALUE
    MINVALUE n | NOMINVALUE
    CYCLE | NO CYCLE
    CACHE | NOCACHE

DROP SEQUENCE sequence-name

 

이 떄 INCREMENT BY 는 시퀀스의 증분값 n을 정의하며, START WITH n은 시퀀스으 시작값을, MAXVALUE n은 시퀀스의 최대값을, MINVALUE n은 시퀀스의 최소값을, CYCLE은 시퀀스 값이 최대값 후 다시 초기값으로 시작할지를, CACHE는 시퀀스 값을 메모리상에서 관리하는 것을 지정하게 됩니다.

 

다음은 시퀀스 사용 예입니다.

CREATE SEQUENCE seq1
INCREMENT BY 3
START WITH 1000
MAXVALUE 10000;

SELECT seq1.nextval FROM DUAL;

RUN

SELECT seq1.nextval FROM DUAL;