테이블 관리(By Oracle)

□ 테이블 생성은 테이블에 대한 구조를 정의하고, 데이터를 저장하기 위한 공간을 할당하는 과정
□ 테이블에 대한 구조 정의는 테이블을 구성하는 칼럼의 데이터 타입과 무결성 제약조건을 정의하는 과정

□ 테이블 이름 정의방법

  ○ 문자(A-Z, a-z)로 시작, 30자 이내
  ○ 문자(a-z,A-Z), 숫자(0-9), 특수문자(_,$,#) 사용 가능
  ○ 대소문자 구별 없음, 소문자로 저장하려면 단일 인용부호 이용
  ○ 동일 사용자가 소유한 다른 객체의 이름과 중복 불가
  ○ 서로 다른 테이블에서 동일한 데이터를 저장하는 칼럼 이름은 가능하면 같은 이름을 사용
  ○ 필요에 따라 언제든지 테이블 생성 가능
  ○ 완성된 설계도에 따라 테이블을 생성 권장

CREATE [GLOBAL TEMPORARY] TABLE [schema.] table
(column datatype[DEFAULT expression][column_constraint clause]
[,…]);

 

□ Default 옵션

  ○ 칼럼의 입력 값이 생략될 경우에 NULL 대신에 입력되는 기본 값을 지정하기 위한 기능
  ○ 기본값 : 리터럴 값, 표현식, SQL함수 , SYSDATE, USER를 사용
  ○ 칼럼이나 의사칼럼(NEXTVAL, CURRVAL)은 사용할 수 없음

addr varchar2(100) DEFAULT ‘KOREA’

 

□ DESC[RIBE] 명령어

  ○ 테이블의 생성 여부와 테이블의 구조를 확인하기 위한 명령어
  ○ 칼럼 이름, 데이터 타입과 크기, NOT NULL 무결성 제약조건

DESC[RIBE] table

 

□ 서브쿼리를 이용한 테이블 생성

  ○ CREATE TABLE 명령문에서 서브쿼리 절을 이용하여 다른 테이블의 구조와 데이터를 복사하여 새로운 테이블 생성 가능
  ○ 서브쿼리의 출력 결과가 테이블의 초기 데이터로 삽입
  ○ CREATE TABLE 명령문에서 지정한 칼럼 수와 데이터 타입과 반드시 일치
  ○ 칼럼 이름을 명시하지 않을 경우 서브쿼리 칼럼 이름과 동일
  ○ 무결성 제약조건은 NOT NULL 조건만 복사
    - 기본 키, 참조 키와 같은 무결성 제약조건은 사용자의 재정의 필요 
  ○ 디폴트 옵션에서 정의한 값은 그대로 복사

CREATE TABLE table
[column[, column,…]]
AS subquery;

 

□ 테이블 구조 복사

  ○ 서브쿼리를 이용한 테이블 생성시 데이터는 복사하지 않고 기존 테이블의 구조만 복사 가능
  ○ 서브퀴리의 WHERE 조건절에 거짓이 되는 조건을 지정하여 출력 결과 집합이 생성되지 않도록 지정

create table addr_fourth
as select id, name, from address
where 1 = 2;

 

□ 서브쿼리를 이용한 테이블 생성

  ○ 주소록 테이블에서 id, name 칼럼만 복사하여 addr_third 테이블을 생성하라

CREATE TABLE addr third
as select id, name from address;

 

□ 테이블 구조변경

  ○ ALTER TABLE 명령문 이용
  ○ 칼럼 추가, 삭제, 타입이나 길이의 재정의와 같은 작업
  ○ ALTER TABLE … ADD 명령문 사용
  ○ 추가된 칼럼은 테이블의 마지막 부분에 생성, 위치 지정 불가능
  ○ 추가된 칼럼에도 기본 값을 지정 가능
  ○ 수정할 테이블에 기존 데이터가 존재하면 칼럼 값은 NULL로 입력
  ○ ALTER TABLE table ADD (column datatype [DEFAULT expression][, column datatype]…);

ALTER TABLE ADDRESS
ADD (BIRTH DATE);

 

□ 테이블 칼럼 삭제

  ○ 테이블 내의 특정 칼럼과 칼럼의 데이터를 삭제
  ○ ALTER TABLE … DROP COLUMN 명령문 사용
  ○ 2개 이상의 칼럼이 존재하는 테이블에서만 삭제 가능
  ○ 하나의 칼럼 삭제 명령문은 하나의 칼럼만 삭제 가능

  ○  ALTER TABLE table_name DROP column;

ALTER TAVLE ADDRESS DROP COLUMN COMMENTS;

 

□ 테이블 칼럼 변경

  ○ 테이블에서 칼럼의 타입, 크기, 기본 값 변경 가능
  ○ ALTER TABLE … MODIFY 명령문 이용
  ○ 기존 칼럼에 데이터가 없는 경우
    - 칼럼 타입이나 크기 변경이 자유로움
  ○ 기존 데이터가 존재하는 경우
    - 타입 변경은 CHAR와 VARCHAR2만 허용
    - 변경한 칼럼의 크기가 저장된 데이터의 크기보다 같거나 클경우 변경 가능
    - 숫자 타입에서는 정밀도 증가 가능
  ○ 기본 값의 변경은 변경 후에 입력되는 데이터부터 적용

ALTER TABLE ADDRESS
MODIFY PHONE VARCHAR(50);

 

□ 테이블 DLFMA QUSRUD

  ○ RENAME 명령문 사용
    - 객체의 이름을 변경하는 DDL 명령문
    - 뷰, 시퀀스, 동의어 등과 같은 데이터베이스 객체의 이름 변경 가능

  ○ RENAME OLD_TABLE TO NEW_TABLE

RENAME ADDR_SECOND TO CLIENT_ADDRESS;

 

□ 테이블 삭제

  ○ 기존 테이블과 데이터를 모두 삭제
  ○ DROP TABLE 명령문 사용
  ○ 삭제된 테이블 칼럼에 대해 생성된 인덱스도 함께 삭제
  ○ 삭제된 테이블과 관련된 뷰와 동의어 ‘invalid’ 상태
  ○ 삭제할 테이블의 기본 키나 고유 키를 다른 테이블에서 참조하고 있는 경우 삭제 불가능
    - 참조하는 테이블(자식 테이블)을 먼저 삭제
    - DROP TABLE 명령문 마지막에 CASCADE CONSTRAINTS 옵션을 사용하여 무결성 제약조건을 동시에 삭제
  ○ DROP TABLE [schema.] table [cascade constraints]
    * cascade constraints : 삭제 대상 테이블의 기본 키나 고유 키를 참조하는 무결성 제약조건을 동시에 삭제하기 위한 옵션

DROP TABLE ADDR_THIRD;

 

□ TRUNCATE 명령문

  ○ 테이블 구조는 그대로 유지하고, 테이블의 데이터와 할당된 공간만 삭제
  ○ 테이블에 생성된 제약조건과 연관된 인덱스, 뷰, 동의어는 유지
  ○ DELETE 명령문과의 차이
    - 기존 데이터만 삭제하는 명령이며, ROLLBACK 가능
    - WHERE 절을 이용하여 특정 행만 삭제 가능
  ○ TRUNCATE 명령문
    - 기존 데이터 삭제뿐 아니라, 물리적인 저장 공간까지 반환
    - DDL 문이므로 ROLLBACK 이 불가능
    - WHERE 절을 이용하여 특정 행만 삭제하는 것이 불가능

  ○ TRUNCATE TABLE [schema.] table

SELECT * FROM CLIENT_ADDRESS;

 

□ 데이터 사전의 종류

  ○ 다수의 사용자가 동일한 데이터를 공유
  ○ 읽기 전용 뷰로 구성
  ○ 데이터베이스 관리자나 사용자에게 데이터 사전에 저장된 정보조회 허용
  ○ 용도에 따라 USER, ALL, DBA 접두어를 사용하여 분류

--USER 데이터 사전 뷰
SELECT TABLE_NAME FROM USER_TABLES;

--ALL 데이터 사전 뷰
SELECT OWNER, TABLE_NAME FROM ALL_TABLS;

--DBA 데이터 사전 뷰
SELECT OWNER, TABLE_NAME FROM DBA_TABLES

--사용자 테이블 정보 조회
SELECT TABLE_NAME, TABLESPACE_NAME, MIN_EXTENTS, MAX_EXTENTS FROM USER_TABLES
WHERE TABLE_NAME LIKE 'ADDR%';

--USER OBJECT
SELECT OBJECT_NAME, OBJECT_TYPE, CREATED FROM USER_OBJECTS
WHERE OBJECT_NAME LIKE 'ADDR%' AND OBJECT_TYPE = 'TABLE';

--USER CATALOG
DESC USER_CATALOG