Chapter 2 Oracle 객체 - PLT 2.1 Table

PLT 2.1 Table

테이블은 실제로 데이터들이 저장되는 곳 이라고 생각하면 쉽게 이해 할 수 있습니다.  CREATE TABLE명령어를 이용해서 테이블을 생성 할 수 있습니다. 다음은 테이블에 관한 설명입니다.

1.   테이블은 오라클 데이타베이스의 기본적인 데이타 저장 단위 입니다.

2.   데이타베이스 테이블은 사용자가 접근 가능한 모든 데이타를 보유하며 레코드와 컬럼으로 구성 됩니다.

3.   관계형 데이타베이스가 아닌 예전의 데이타 베이스 용어에서는 파일과 테이블이, 필드와 컬럼이, 그리고 레코드와 행이 동일시 되었습니다.

4.   테이블은 시스템내에서 독립적으로 사용되길 원하는 엔티티를 표현할수 있습니다. 예를 들면, 회사에서의 고용자나 제품에 대한 주문은 테이블로 표현 가능합니다.

5.   테이블은 두 엔티티간의 관계를 표현할 수 있습니다. 즉 테이블은 고용자와 그들의 작업 숙련도 혹은 제품과 주문과의 관계를 표현하는데 사용될 수 있습니다.

6.   테이블내에 있는 외래 키 (ForeIgn Key)는 두 엔티티 사이의 관계를 표현하는데 사용됩니다.

7.   비록 "테이블" 이라는 말이 더 많이 사용되지만 테이블의 형식어는 "릴레이션" 입니다.

컬  럼 : 테이블의 각 컬럼은 엔티티의 한 속성을 표현한다. 행(ROW, 레코드)  : 테이블의 데이타는 행에 저장됩니다

테이블 생성시 제한사항과 고려할점 1.   테이블 이름과 컬럼은 항상 알파벳 문자로 시작해야 하며 A~Z까지의 문자, 0~9까지의 숫자 그리고 $,#,_(Under Bar)를 사용할 수 있습니다. (★공백 사용 불가능) 2.   테이블의 컬럼 이름은 30자를 초과할 수 없고, 예약어를 사용할 수 없습니다. 3.   오라클 테이블 한 계정 안에서 테이블 이름은 다른 테이블 이름과 달리 유사해야 합니다. 4.   한 테이블 안에서 컬럼이름은 같을 수 없으며 다른 테이블에서의 컬럼이름과는 같을 수  있습니다.

테이블 생성 문법

CREATE TABLE[schema.] table_name (             Column datatype, … ) [TABLESPACE tablespace] [PCTFREE integer] [PCTUSED integer] [INTRANS integer] [MAXTRANS integer] [STORAGE storage-clause] [LOGGING | NOLOGGING] [CACHE | NOCACHE];
schema :	테이블의 소유자
table_name:	테이블 이름
column:	컬럼의 이름
datatype:	컬럼의 데이터 유형
TABLESPACE:	테이블이 데이터를 저장 할 테이블스페이스를 지정 합니다.
PCTFREE :	블럭내에 이미 존재하고 있는 Row에 Update가 가능하도록 예약시켜 놓는 블럭의 퍼센트 값을 지정 합니다.
PCTUSED :	테이블 데이터가 저장될 블록의 행 데이터 부분의 크기를 퍼센트지로 지정 합니다. PCTFREE에 의해 지정된 크기만큼 Block이 차면 PCTUSED 값보다 작아져야 새로운 행 삽입이 가능  합니다.
INITRANS :	하나의 데이터 블록에 지정될 초기 트랜잭션의 값을 지정합니다. (기본값은1)
MAXTRANS:	하나의 데이터 블록에 지정될 수 있는 트랜잭션 최대 수를 지정 합니다. (기본값은 255)
STORAGE:	익스텐트 스토리지에 대한 값을 지정 합니다.
LOGGING:	테이블에 대해 이후의 모든 작업이 리두 로그 파일 내에 기록 되도록 지정합니다. (default)
NOLOGGING:	리두 로그 파일에 테이블의 생성과 특정 유형의 데이터 로드를  기록하지 않도록 지정 합니다.
CACHE :	전체 테이블 스캔(full table scan)이 수행될 때 읽어 들인 블록이 버퍼 캐쉬 내의 LRU 리스트의 가장 근에 사용된 것의 자리에 위치 하도록 지정 합니다.
NOCACHE :	전체 테이블 스캔(full table scan)이 수행될 때 읽어 들인 블록이 버퍼 캐쉬 내의 LRU 리스트의 가장 최근에 사용 되지 않은 것의 자리에 위치하도록 지정 합니다.

테이블 생성 예제

SQL>CREATE TABLE EMP2(        EMPNO      NUMBER    CONSTRAINT   emp_pk_empno   PRIMARY KEY,          - (컬럼)         (데이터타입)                              (제약조건)        ENAME     VARCHAR2(20),        JOB        VARCHAR2(40),        MGR        NUMBER,        HIREDATE     DATE,        SAL         NUMBER,        COMM      NUMBER,        DEPTNO     NUMBER)        PCTFREE 20        PCTUSED 50 ; 테이블이 생성되었습니다. SQL>CREATE TABLE DEPT2(        DEPTNO   NUMBER CONSTRAINT dept_pk_deptno PRIMARY KEY,        DNAME   VARCHAR2(40),        LOC     VARCHAR2(50)) ; 테이블이 생성되었습니다.

테이블 생성시 주의사항

1.       테이블 이름을 지정하고 각 컬럼들은 괄호 "()" 로 묶어 지정합니다.

2.       컬럼뒤에 데이터 타입은 꼭 지정되어야 합니다.

3.       각 컬럼들은 콤마","로 구분되고, 항상 끝은 세미콜론";" 으로 끝납니다.

4.       한 테이블 안에서 컬럼 이름은 같을 수 없으며 다른 테이블에서의 컬럼 이름과는 같을 수 있습니다.

유저가 소유한 테이블보기

USER_TABLES 데이터사전을 조회 하면 유저가 소유한 테이블을 확인 할 수 있습니다. -- SQL*Plus에서 실행해 보세요.. SQL>SELECT table_name FROM  USER_TABLES; TABLE_NAME ------------ BONUS CRETABLE DEPT DUMMY EMP EMP2 SALGRADE 위와 같이 테이블 목록이 조회 됩니다.

제약조건 (Constraint)

제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용해 놓는 거라 생각하면 됩니다. 간단하게 테이블 안에서 데이터의 성격을 정의하는 것이 바로 제약조건 입니다.

• 데이터의 무결성 유지를 위하여 사용자가 지정할 수 있는 성질 입니다.

• 모든 CONSTRAINT는 데이터 사전(DICTIONARY)에 저장 됩니다.

• 의미 있는 이름을 부여했다면 CONSTRAINT를 쉽게 참조할 수 있습니다.

• 표준 객체 명명법을 따르는 것이 좋습니다.

• 제약조건은 테이블을 생성할 당시에 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER)명령어를 통해서도 추가가 가능합니다.

• NOT NULL제약조건은 반드시 컬럼 레벨에서만 정의가 가능합니다.

NOT NULL 조건 : 컬럼을 필수 필드화 시킬 때 사용합니다.

SQL> CREATE TABLE emp(        ename VARCHAR2(20)  CONSTRAINT emp_nn_ename NOT NULL );        이런식으로 하면 ename 컬럼에는 꼭 데이터를 입력해야만 합니다.        여기서 emp_nn_ename은 (테이블이름_제약조건이름_컬럼이름) 형식으로        CONSTRAINT NAME을 정의 합니다.        CONSTRAINT NAME은 USER_CONSTRAINTS 뷰(VIEW)를 통해서 확인할 수 있습니다. SQL> SELECT CONSTRAINT_NAME         FROM USER_CONSTRAINTS         WHERE TABLE_NAME ='EMP' ;          CONSTRAINT_NAME          -----------------------          emp_nn_ename           이런 식으로 제약사항의 이름을 확인할수 있습니다.

UNIQUE 조건

데이터의 유일성을 보장(중복되는 데이터가 존재할 수 없습니다.) 자동으로 index가 생성됩니다. SQL> ALTER TABLE emp        ADD CONSTRAINT emp_uk_deptno UNIQUE (deptno) ; 테이블이 변경되었습니다. 이런식으로 하면 deptno 컬럼에 중복된 데이터가 들어갈 수 없습니다. à 제약 조건의 삭제 SQL>ALTER TABLE emp       DROP CONSTRAINT emp_uk_deptno ; 테이블이 변경되었습니다.

CHECK 조건 : 컬럼의 값을 어떤 특정 범위로 제한할 수 있습니다.

SQL>ALTER TABLE emp       ADD CONSTRAINT emp_ck_comm       CHECK (comm >= 1 AND comm <= 100 ; 테이블이 변경되었습니다.     comm컬럼은 체크조건에서 제한을 하고 있으므로 1에서 100까지의 값만을 가질수 있습니다.     또 체크 조건에서는 IN 연산자를 사용할 수 있습니다. -- 제약 조건의 삭제 SQL>ALTER TABLE emp        DROP CONSTRAINT emp_ck_comm ; 테이블이 변경되었습니다. SQL> ALTER TABLE emp        ADD CONSTRAINT emp_ck_comm        CHECK (comm  IN (10000,20000,30000,40000,50000)) ; 테이블이 변경되었습니다.     comm 컬럼은 10000,20000,30000,40000,50000의 값만을 가질수 있습니다.

DEFAULT(컬럼 기본값) 지정 : 데이터 입력시에 입력을 하지 않아도 지정된 값이 입력될 수 있습니다.

SQL>CREATE TABLE emp(          hiredate DATE DEFAULT  SYSDATE ) ;     이런식으로 하면 hiredate 컬럼에 INSERT를 하지 않아도 오늘 날짜가 들어갑니다.

PRIMARY KEY 지정

기본키는 UNIQUE 와 NOT NULL의 결합과 같습니다. 기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있습니다. 이를 참조 무결성이라 합니다. UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며 그 이름은 기본 키  제약 조건의 이름과 같습니다.

SQL>CREATE TABLE emp(        empno NUMBER CONSTRAINT emp_pk_empno PRIMARY KEY ) ;     이런식으로 하면 empno 컬럼에 UNIQUE 제약조건과 NOT NULL제약조건을 가지게 됩니다.

INDEX KEY :

검색 키로서 검색 속도를 향상 시킴니다. UNIQUE,PRIMARY KEY 생성시 자동적으로 생김니다.

FOREIGN KEY(외래 키)지정

기본키를 참조하는 컬럼 또는 컬럼들의 집합입니다. 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터형이 일치해야 합니다. 이를 어기면 참조무결성 제약에 의해 테이블을 생성할 수 없습니다. 외래키에 의해 참조되고 있는 기본 키는 삭제할수 없다. ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제 될 때 같이 삭제됩니다.

SQL>ALTER TABLE emp ADD CONSTRAINT emp_fk_deptno        FOREIGN KEY (deptno) REFERENCES dept(deptno)        테이블이 변경되었습니다. 이런식으로 하면 emp 테이블의 deptno 컬럼은 dept 테이블에 deptno 컬럼을 참조하는 외래키를 가지게 됩니다.

제약 조건의 확인

USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있습니다.

USER_CONSTRAINTS　: 유저가 소유한 모든 제약 조건을 불 수 있습니다.

이 두 개의 데이터사전을 참조 하면 됩니다.

SQL> SELECT  SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,　　        　　　　 DECODE(B.CONSTRAINT_TYPE,                                         'P','PRIMARY KEY',           　　　　　　　 　　 'U','UNIQUE KEY',          　　　　　　　　　 　'C','CHECK OR NOT NULL',                                        'R','FOREIGN KEY') CONSTRAINT_TYPE,　　         　　　　 A.CONSTRAINT_NAME　　 CONSTRAINT_NAME　　        FROM　　USER_CONS_COLUMNS　 A,　 USER_CONSTRAINTS　 B　　        WHERE　 A.TABLE_NAME = UPPER('&table_name')　　            AND　 A.TABLE_NAME = B.TABLE_NAME　　            AND　 A.CONSTRAINT_NAME = B.CONSTRAINT_NAME　　        ORDER BY 1; 테이블 명을 입력 하면 됩니다.

테이블 컬럼의 관리

테이블의 컬럼은 ADD, MODIFY, DROP연산자를 통해서 관리 할 수 있습니다.

• ADD 연산자  : 테이블에 새로운 컬럼을 추가 할 때 사용 합니다.

SQL>ALTER TABLE emp ADD (addr VARCHAR2(50));

VARCHAR2의 데이터 형을 가지는 addr 컬럼이 emp 테이블에 추가 됩니다.

• MODIFY 연산자 : 테이블의 컬럼을 수정 하거나 NOT NULL컬럼으로 변경 할 수 있습니다.

SQL>ALTER TABLE emp MODIFY (ename VARCHAR2(50));

SQL>ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL) ;

ename컬럼이 VARCHAR2 50자리로 수정 됩니다. 컬럼이 이미 테이터를 가지고 있을 경우 다른 데이터형으로 변경이 불가능합니다.

• DROP 연산자  : 테이블 컬럼을 삭제 하거나, 테이블의 제약 조건을 삭제 할 때 사용 합니다.

컬럼의 삭제 예제

SQL>ALTER TABLE table_name DROP COLUMN column_name

제약 조건의 삭제 예제

SQL>ALTER TABLE emp DROP PRIMARY KEY ;

CASCADE 연산자와 함께 사용하면 외래키에 의해 참조되는 기본키도 삭제될수 있습니다.

SQL>ALTER TABLE emp DROP CONSTRAINT emp_pk_empno CASCADE;

테이블 정보의 관리

기존 테이블의 복사

CREATE TABLE [schema.]table_name [LOGGING | NOLOGGING] […] AS subquery; à Sample CREATE TABLE emp2       AS       SELECT * FROM emp;

기존 테이블을 부분, 또는 완전히 복사할 때에 서브쿼리를 가진 CREATE TABLE 명령어를 사용해서 쉽게 테이블을 복사 할 수 있습니다. 하지만 제약 조건, 트리거, 그리고 테이블 권한은 새로운 테이블로 복사되지 않습니다. 제약조건은 NOT NULL제약조건만 복사 됩니다.

테이블 정보의 변경

ALTER TABLE[schema.] table_name [STORAGE storage-clause] [PCTFREE integer] [PCTUSED integer] [INITRANS integer] [MAXTRANS integer]

보통 테이블의 정보를 변경하는 이유는 스토리지 파라미터와 블록 활용파라미터를 변경하기 위해서 사용 합니다. 테이블 정보의 변경시 INITIAL의 값은 변경 할 수 없습니다. STORAGE-CLAUSE에 들어올 수 있는 스토리지 파라미터를 정리하면 아래와 같습니다.

- NEXT

   다음 번 생성될 익스텐트의 크기를 Byte단위로 지정합니다.

   이후의 익스텐트 크기는 PCTINCREASE만큼씩 증가 됩니다.

- PCTINCREASE

   마지막 생성된 익스텐트의 바로 다음에 생성될 익스텐트의 증가율을 퍼센트지로 지정 합니다.

- MINEXTENTS

   최초 생성되는 익스텐트의 수를 지정 합니다.

- MAXEXTENTS

   생성될 수 있는 최대 익스텐트의 수를 지정 합니다.

테이블의 테이블스페이스 변경

ALTER TABLE table_name MOVE TABLESPACE tablespace_name;

테이블의 TRUNCATE

TRUNCATE TABLE [schema.]table_name

• 테이블을 Truncate하면 테이블의 모든 행이 삭제되고 사용된 공간이 해제 됩니다.

• TRUNCATE TABLE은 DDL명령이므로 롤백 데이터가 생성되지 않습니다.

• DELETE명령으로 데이터를 지우면 롤백명렁어로 복구 할 수 있지만 TRUNCATE로 데이터를 삭제하면 롤백을 할 수가 없습니다.

• 행당 인덱스도 같이 잘려 나갑니다.

• 외래키가 참조중인 테이블은 TRUNCATE할 수 없습니다.

• TRUNCATE명령을 사용하면 삭제 트리거가 실행되지 않습니다.

DROP TABLE (테이블을 삭제할 때 사용)

DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS];

CASCADE CONSTRAINT : 외래키에 의해 참조되는 기본키를 포함한 테이블일 경우 기본키를 참조하던 외래 키 조건도 같이 삭제 됩니다.