[ORACLE] 데이터 정의언어 (DDL)

데이터 정의 언어(Data Definition Language) 🐶

0. DDL

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

종류 : CREATE, ALTER, DROP, RENAME, TRUNCATE

 

 

데이터 타입 

특정 컬럼을 정의할 때 선언한 데이터 유형은 그 컬럼이 받아들일 수 있는 자료의 유형을 정의함 

 

- 문자형 데이터 타입 

- 숫자형 데이터 타입 

- 날짜형 데이터 타입 

 

- 대용량 데이터 타입 

- LONG/RAW 데이터 타입 

- ROWID 데이터 타입 

VARCHAR2 : 4000

CLOB : 대용량의 텍스트 

NUMBER: 최대 유효자리 30

DATE : 크기를 쓰지 않아도 사용 가능

 

1. CREATE

1. 테이블 생성

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

 

 

2. 테이블 목록 확인 

SELECT * FROM tabs; -- 오라클은 대문자로 저장이 됨

 

3. 컬럼 및 컬럼의 타입 확인 

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

 

4. 가상 컬럼(virtual column)
디스크에 저장되지 않는 수식들을 저장 
SYSDATE 등 동적인 결과를 출력하는 함수는 사용❌
가상 컬럼은 값을 추가하거나 수정❌

 

형식

column [datatype] [GENERATED ALWAYS] AS (column_expression) 
	[VIRTUAL] [ inline_constraint [inline_constraint]... ]

 

예시

tot NUMBER(3) GENERATED ALWAYS AS(kor+eng+mat) VIRTUAL
    
ave NUMBER(3) GENERATED ALWAYS AS((kor+eng+mat)/3)VIRTUAL

 

5. subquery를 이용한 테이블 생성 
존재하는 테이블을 이용하여 테이블 작성하여  테이블 구조 및 값 복사 

 NOT NULL을 제외한 다른 제약 조건은 복사❌

 

기본형식

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

 

2. ALTER

1. ALTER TABLE ~ ADD
 기존의 테이블에 새로운 컬럼을 추가 

새로 추가되는 컬럼은 테이블 가장 마지막에 추가 

형식

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

예시

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

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

주의 데이터가 존재하는 테이블에  NOT NULL 속성의 컬럼을 추가❌

2.  컬럼 수정 ALTER TABLE ~ MODIFY

 

형식 

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

예시

ALTER TABLE test MODIFY(sal NUMBER(10));

3.  ALTER TABLE ~ RENAME COLUMN : 컬럼의 이름 변경                       

                                                                                                                                                                                                                                 형식

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

 예시              

ALTER TABLE test RENAME COLUMN num TO empNo;

                                                               

4. ALTER TABLE ~ DROP COLUMN : 컬럼 삭제 

데이터가 존재하면 데이터도 삭제 
형식

ALTER TABLE 테이블명 DROP COLUMN 컬럼명

예시

ALTER TABLE test DROP COLUMN dept;

6. ALTER TABLE ~ SET UNUSED
컬럼을 논리적으로 사용하지 못하도록 제한. 논리적으로 삭제
UNUSED에 의해 삭제된 컬럼 확인❌

 

ALTER TABLE emp1 SET UNUSED (pay);

 

UNUSED에 의해 삭제된 컬럼의 개수는 확인 가능 
SELECT * FROM user_unused_col_tabs;


7. ALTER TABLE ~ DROP UNUSED COLUMNS
unused에 의해 논리적으로 삭제된 컬럼을 실제로 삭제 

ALTER TABLE emp1 DROP UNUSED COLUMNS;

3. DROP

테이블 삭제
형식

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

4. RENAME

객체 이름 변경 

   RENAME 옛이름 TO 새이름;

 

 

5. RECYCLEBIN

1. 휴지통(RECYCLEBIN) 정보 확인
2. 삭제된 개체(objects)확인

SHOW RECYCLEBIN;
SELECT * FROM RECYCLEBIN;

3.휴지통의 테이블 복원 
원래 이름으로 복원

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

4. 휴지통 비우기

PURGE RECYCLEBIN;

5. TRUNCATE
테이블의 모든 레코드 삭제, 테이블 구조는 모두 삭제 X
자동 COMMIT 되므로 복원 불가 

TRUNCATE TABLE 테이블명;