[ORACLE] 5일차 : 데이터 정의 언어(Data Denfinition Language)

 

데이터 정의어

데이터 정의 언어(DDL,Data Definition Language)는 데이터 베이스 구조,데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어. 

 

종류

• CREATE문 : TABLE,VIEW,INDEX등을 정의(생성)한다.
• ALTER문 : 생성된 테이블 등의 객체를 수정한다.
• DROP문 : 테이블 등의 객체를 삭제한다. 
• RENAME문 : 생성된 객체의 이름을 변경한다.
• TRUNCATE문 : 테이블의 모든 테이터를 삭제한다. 

 

식별자

테이블명,컬럼명과 같이 사용자에 의해 부여한 이름을 식별자라 한다.

• 반드시 문자로 시작해야 한다.
• 1~30까지 가능하다. 
• A~Z까찌의 대소문자, 0~9까지의 숫자, 특수기호('_','$','#')만 포함 가능하다.
• 오라클에서 사용되는 예약어는 사용할 수 없으며,객체명은 다른 객체명과 중복이 불가능하다.
• 공백을 허용하지 않는다.

 

데이터 타입

오라클에서 문자,숫자,날짜 자료형 등의 여러 종류의 데이터를 식별하는 타입으로서, 해당 자료형에 대한 가능한 값, 해당 자료형에서 수행 할 수 있는 명령어, 데이터의 형태, 의미, 크기와 해당 자료형의 값이 저장되는 방식이다. 특정 칼럼을 정의할 때 선언한 데이터 유형은 그 컬럼이 받아들일 수 있는 자료의 유형을 규정한다. 

 

문자형 데이터 타입

 

숫자형 데이터 타입

 

 

날짜형 데이터 타입

 

대용량 데이터 타

 

 

 

CREATE

 

기본형

CREATE TABLE 테이블명
(
컬럼명  타입[(크기)]  [제약조건]
,컬럼명  타입[(크기)]  [제약조건]
);

 

테이블 생성 형식

CREATE TABLE test
(
    num NUMBER(10) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    birth DATE,
    city VARCHAR2(30)
);

 

테이블 목록 확인

SELECT * FROM tab;

 

테이블 컬럼 확인

SELECT * FROM col WHERE tname ='TEST';
DESC test;

 

 

가상 컬럼 

virtual column 

디스크에 저장되지 않는 가상 컬럼은 수식이나 함수를 계산하여 요청시 가상 컬럼 값을 반환한다. 가상 컬럼 수식에는 SYSDATE등 동적인 결과를 추력하는 함수는 사용 할 수 없다. 다른 테이블의 컬럼을 이용한 가상 컬럼은 생성 할 수 없다. 구분을 명확하게 하기 위해 항상 `GENERARED` 키워드가 제공된다. 

 

CREATE TABLE demo
(
    hak VARCHAR2(30) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL,
    kor NUMBER(3) NOT NULL,
    eng NUMBER(3) NOT NULL,
    mat NUMBER(3) NOT NULL,
    tot NUMBER(3) GENERATED ALWAYS AS(kor+eng+mat) VIRTUAL,
    ave NUMBER(4,1) GENERATED ALWAYS AS((kor+eng+mat)/3) VIRTUAL
);
--기존 객체와 동일한 이름이 존재하면 에러

 

가상 컬럼 확인 방법 

SELECT * FROM col WHERE tname ='DEMO';

SELECT * FROM cols WHERE table_name ='DEMO';

 

subquery

CREATE TABLE에서 subuery를 이용하여 다른 테이블의 데이터, 구조를 기본으로 테이블을 생성할 수 있다. 서브 쿼리 컬럼의 개수와 CREATE TABLE에서 지정한 컬럼의 개수가 일치해야 한다. CREATE TABLE에서 컬럼명을 기술하지 않으면 서브 쿼리에서 SELECT되는 컬럼명이 테이블의 컬럼이 된다. 서브 쿼리 테이블의 무결성 제약조건 중 NOT NULL 제약조건만 복제되므로 PRIMARY KEY, FOREIGN KEY 제약조건 등은 다시 생성해야 한다. 

 

기본 형식

CREATE  TABLE  테이블명 [(컬럼명, 컬럼명,...)] AS subquery;

 

CREATE TABLE emp2 AS 
        SELECT empNO,name,sal,bonus,sal+bonus pay FROM emp; 
        --제약조건은 NOT NULL만 복사되고 나머지는 복사되지 않는다

 

컬럼명을 지정하여 복사

CREATE TABLE emp3(eno,name,birth) AS
    SELECT empNO,name,TO_DATE(SUBSTR(rrn,1,6),'RRMMDD')
    FROM emp
    WHERE TO_DATE(SUBSTR(rrn,1,6),'RRMMDD')>='1990-01-01';

 

구조만 복사

CREATE TABLE emp4 AS
    SELECT * FROM emp
    WHERE 1=2;

 

 

ALTER 

기존의 테이블 구조를 변경하는 DDL명령, 테이블에 대한 구조 변경은 컬럼의 추가, 수정 삭제시 사용된다. 기존의 테이블에 새로운 컬럼을 추가한다. 새로 추가되는 컬럼은 테이블 가장 마지막에 추간된다. 이전에 등록한 행(데이터)이 존재하는 경우, 이전에 등록된 행의 추가된 컬럼 값은 NULL로 추가된다. 

 

ALTER TABLE test ADD(dept VARCHAR2(30),sal NUMBER(3) NOT NULL);
DESC test;

 

가상 컬럼 추가

ALTER TABLE emp5 ADD(birth DATE GENERATED ALWAYS 
                    AS(TO_DATE(SUBSTR(rrn,1,6),'RRMMDD'))VIRTUAL);

 

 

ALTER TABLR ~MODIFY

테이블에 존재하는 컬럼의 데이터 타입, 크기등을 변경하는 명령어. 해당 컬럼에 데이터가 존재하지 않는 경우 데이터 타입, 크기를 변경할 수 있다. 해당 컬럼에 데이터가 존재하는 경우 데이터 타입은 변경할 수 없고, 크기는 존재하는 데이터의 길이보다 크거나 같아야 한다. 

 

ALTER TABLE 테이블명 MODIFY (컬럼명 자료형(크기));

 

ALTER TABLE test MODIFY(sal NUMBER(8));
DESC test;

 

 

ALTER TABLE ~ RENAME COLUMN

테이블에서 존재하는 컬럼의 이름을 변경한다.

ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 새로운컬럼명

ALTER TABLE emp3 RENAME COLUMN eno TO empNO;
DESC emp3;

 

ALTER TABLE ~ DROP COLUMN

테이블에서 존재하는 컬럼을 삭제한다. 

 

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

 

ALTER TABLE emp3 DROP COLUMN dept;
DESC emp3;

 

 

ALTER TABLE ~ SET UNUSED 

컬럼을 삭제하지는 않지만, 컬럼의 사용을 논리적으로 제한하는 옵션.즉, 논리적으로 컬럼을 삭제하는 옵션이다. 테이블에 저장된 내용이 많은 경우 콜롬울 삭제하면 오랜 시간이 소요되며, 삭제 되는 동안 테이블은 LOCK이 설정되어 다른 사용자가 이용할 수 없다. 이럴 경우 컬럼의 사용을 논리적으로 제한하고 테이블 사용이 없는 시간대에 해당 컬럼을 삭제할 수 있다. 

 

ALTER TABLE emp2 SET UNUSED (pay); --논리적으로 삭제,회복 불가

 

 

ALTER TABLE ~ SET UNUSED에 의해 논리적으로 삭제되는 컬럼의 정보 확인 논리적으로 삭제된 컬럼은 컬럼명을 확인하거나 복귀할 수 없지만 어떤 테이블에 몇 개의 컬럼이 UNUSED 되었는지 다음의 딕셔너리를 통해 확인 할 수 있다.

ALTER TABLE emp2 DROP UNUSED COLUMNS;
SELECT * FROM user_unused_col_tabs;

 

DROP 

삭제되는 테이블은 반드시 사용자 계정에서 생성한 테이블이어야 하며 다른 스키마계정의 테이블은 DROP ANY TABLE 시스템 권한이 있어야 삭제 가능하다. 데이터 딕셔너리로부터 테이블 정의가 삭제되며 테이블에 로우(ROW)및 연관되어 생성된 인덱스,트리거도 함께 삭제된다. 만약 삭제되는 테이블의 기본키(PRIMARY KEY) 또는 고유키(UNIQE KEY)를 다른 테이블에서 참조하고 있는 경우에는 삭제가 불가능하며, 참조하는 테이블을 먼저 삭제하거나 DROP TABLE명령어 마지막에 CASCADE CONSTRAINTS 조건을 줘서 삭제해야 한다. 

 

DROP TABLE 테이블;  -- 휴지통에 임시보관
DROP TABLE 테이블 PURGE;  -- 휴지통에 임시보관 하지 않고 바로 삭제
DROP TABLE 테이블명 CASCADE CONSTRAINTS PURGE;
-- 테이블과 그 테이블을 참조하는 FOREIGN KEY의 제약조건을 동시에 삭제

 

 

CASCADE CONSTRAINTS 

삭제된 테이블의 기본 및 고유키를 참조하는 모든 참조 무결성 제한 조건을 삭제한다. 이 절을 생략하고 해당 참조 무결성 제한 조건이 존재하면 데이터베이스는 오루를 리턴 하고 테이블을 삭제 하지 않는다.

 

PURGE

PURGE를 지정하면 데이터베이스는 테이블 및 해당 종속 오브젝트를 휴지통에 넣지 않고 삭제한다. PURGE 옵션을 사용하여 삭제한 테이블은 복구 할 수 없다.

 

 

RENAME

테이블, 뷰, 시퀀스 또는 개인 동의어(private sysnonym)의 이름을 변경한다. 이름이 변경된 테이블을 참조하는 뷰, 동의어 및 저장 프로시저 및 함수와 같이 이름이 바뀐 객체에 의존하는 모든 객체는 무효화 된다.

 

RENAME 옛이름 TO 새이름;
RENAME emp3 TO test;

 

 

휴지통(RECYCLEBIN) 

실제로 삭제된 객체 (objects)에 대한 정보가 포함된 데이터 사전 테이블. 삭제된 테이블 및 인덱스, 제한 조건, 중첩된 테이블 등과 같은 연관된 오브젝트는 제거되지 않고 공간을 차지한다. 휴지통에서 특별히 제거 될 때까지 또는 테이블 스페이스 공간 제한으로 인해 데이터베이스에서 제거 해야 하는 상황이 발생할 때까지 사용자 공간 할당량을 계속 계산한다. 

 

--test 테이블 삭제 
DROP TABLE test; -- 휴지통 
SELECT * FROM tab;

--휴지통 확인
SELECT * FROM RECYCLEBIN;

 

 

FLASHBACK TABLE 

DROP TABLE에 의해 삭제된 테이블을 이전 상태로 복원한다. DROP TABLE에서 PURGE옵션울 사용하여 삭제한 테이블 복구 할 수 없다. 

 

-- 삭제전 이름으로 복원 
-- FLASHBACK TABLE 삭제전이름 TO BEFORE DROP;
-- FLASHBACK TABLE "BIN$ 이름" TO BEFORE DROP;

-- 이름을 변경해서 
-- FLASHBACK TABLE 삭제전이름 TO BEFORE DROP RENAME TO 새로운 이름;

FLASHBACK TABLE test TO BEFORE DROP; -- 동일한 이름이 두개이상이면 마지막으로 삭제된것 복원 

FLASHBACK TABLE test TO BEFORE DROP; -- 동일한 이름이 두개이상이면 마지막으로 삭제된것 복원

 

 

TRUNCATE

테이블의 정의는 삭제하지 않고 데이터(로우)만 삭제한다. 테이블의 모든 행, 물리적인 저장공간을 삭제하며 테이블과 관련된 Storage Parameter를 테이블 처음 상태로 초기화 한다. 테이블과 연관된 인덱스, 동의어, 뷰 등은 그대로 유지된다. 테이블의 데이터를 삭제하는 가장 빠른 방법이며 DDL 이므로 ROLLBACK은 되지 않는다. WHERE절을 이용하여 특정 로우만 삭제하는 것은 불가능 하며 해당 테이블에 DELETE명령과 관련하여 트리거가 생성되어 있더라도 트리거는 동작하지 않는다.

 

TRUNCATE TABLE test;
        --내용만 모두 삭제, 자동 COMMIT