오라클 테이블(TABLE) 이란? 생성 및 수정

오라클 테이블(TABLE) 이란? 생성 및 수정

8. 테이블(Table)

8.1 테이블(Table)이란?

테이블은 Schema Objects의 하나로 관계형 데이터베이스를 구성하는 기본 데이터 구조로서 행과 열의 구조를 가지며 이 테이블을 이용하여 데이터를 입력, 수정, 삭제, 추출 등을 하게된다.

Schema Object란? Schema는 Schema Objects의 모임이며 오라클에서 사용자를 스키마라고 부른다. 스키마 오브젝트에는 TABLES, INDEXES, VIEWS, SEQUENCES, SYNONYM, CLUSTERS, DATABASE LINK, PROCEDURES, FUNCTIONS, PACKAGE등이 해당 된다.

8.1.1 Create Table

테이블을 생성하기 위해서는 우선 Create Table 시스템 권한과 테이블스페이스에 대해 저장 공간을 사용할 권한 또는 Unlimited TableSpace 시스템 권한이 있어야 한다. 테이블 생성은 테이블에 대한 구조(칼럼 및 데이터타입, 제약조건)를 정의하고 저장공간을 할당하는 과정이다. 이 테이블을 생성하기 위해서는 DDL(Data Definition Language)명령중 하나인 Create Table 명령어를 사용한다.

[기본문법]

CREATE [GLOBAL TEMPORARY]TABLE [SCHEMA.]table_name

(

column1 datatype [ DEFAULT expr ][constrinats],

column2 datatype [ DEFAULT expr ][constrinats],

...

);

GLOBAL TEMPORARY : 임시테이블을 의미하며 구조는 모든 세션에서 볼 수 있지만 데이터는 임시테이블을 생성한 세션에서만 확인 가능하다.

SCHEMA : 오라클사용자 계정

table_name : 생성하고자 하는 테이블 이름

column1, column2 : 칼럼이름

datatype : 칼럼의 데이터 타입

DEFAULT expr : 칼럼에 값이 안들어 오는 경우 사용되는 칼럼의 기본값

constraints : 칼럼의 제약조건(NOT NULL등)

-- name, address, age 컬럼을 가지는 AddrBook1 테이블을 생성하시오.(age는 기본값을 0으로, name 칼럼은 not null로 제약조건을 작성하시오.) SQL> create table addrbook1 (

name varchar2(10) not null, address varchar2(50), age number default 0 ); 테이블이 생성되었습니다. -- 작성된 테이블은 tab이라는 딕셔너리뷰로 확인 가능하다. SQL> select * from tab where tname like 'A%'; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- ADDRBOOK1 TABLE -- user_tables라는 딕셔너리 뷰로도 확인 가능하다. SQL> select table_name from user_tables where table_name = 'ADDRBOOK1'; TABLE_NAME -------------- ADDRBOOK1 -- user_catalog에서도 확인 가능 하다. SQL> select * from user_catalog where table_name = 'ADDRBOOK1'; TABLE_NAME TABLE_TYPE ------------------------------ ----------- ADDRBOOK1 TABLE -- AddrBook1 테이블의 구조를 확인하자. SQL> desc addrbook1 이름 널? 유형 ----------------------------------------- -------- ---------------------------- NAME NOT NULL VARCHAR2(10) ADDRESS VARCHAR2(50) AGE NUMBER

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

Create Table에서 Sub Query를 이용하여 다른 테이블의 데이터, 구조를 기본으로 테이블을 생성할 수 있다. 서브쿼리는 Create Table에서 지정한 컬럼과 개수, 데이터 타입이 맞아야 하며 Create Table에서 칼럼명을 기술하지 않으면 서브쿼리에서 SELECT되는 칼럼명이 테이블의 칼럼명이 되고 서브쿼리 테이블의 무결정 제약조건 중 NOT NULL 제약조건만 복제되므로 Primary Key, Foreign Key 제약조건 등은 다시 생성해야 한다.

CREATE TABLE table [column,,,] AS Sub Query;

-- 서브쿼리에서 AddrBook1 테이블을 이용하여 AddrBook2 테이블을 생성하는데 AddrBook1의 데이터 포함하여 생성하고 칼럼이름은 AddrBook1의 칼럼 이름을 그대로 이용하시오 SQL> insert into addrbook1 (name, address, age) values ('1길동','서울1',24); 1 개의 행이 만들어졌습니다. SQL> insert into addrbook1 (name, address, age) values ('2길동','서울2',22); 1 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다. SQL> create table addrbook2 as select name, address, age from addrbook1; 테이블이 생성되었습니다. SQL> select * from addrbook2; -- 서브쿼리에서 AddrBook1 테이블을 이용하여 AddrBook3 테이블을 생성하는데 AddrBook1의 데이터 포함하지 않고 구조만 복사하고 칼럼이름은 irum, addr, nai로 생성하시오 SQL> create table addrbook3(irum, addr, nai) as select name, address, age from addrbook1 where 1 = 0; 테이블이 생성되었습니다. -- 테이블의 구조만 복사하기 위해 WHERE절에 항상 거짓인 조건을 기술하였다. SQL> select * from addrbook3; SQL> desc addrbook3

8.1.2 테이블 구조 변경

생성된 테이블의 구조를 변경하기 위해서는 DDL(Data Definition Language)명령인 ALTER TABLE 명령을 사용하며 주로 컬럼타입의 변경, 길이의 변경, 칼럼 추가, 칼럼 삭제 등과 같은 일을 한다.

칼럼 추가

[기본 형식]

ALTER TABLE table_name ADD (column_name data_type [DEFAULT expr]…

n 칼럼을 추가할 테이블에 이미 데이터가 들어 있다면 추가되는 칼럼은 not null 정의는 불가능하며 기존 행의 추가 칼럼은 NULL로 입력된다.

n 추가되는 칼럼은 테이블의 맨뒤 칼럼으로 추가되며 DEFAULT값은 지정 가능하다.

-- 이전에 작성한 AddrBook2 테이블에 tel(varchar2(20)) 칼럼을 추가하시오. -- addrbook2 테이블에는 이미 데이터행이 있으므로 NOT NULL 제약조건으로 칼럼 추가는 불가능 SQL> alter table addrbook2 add (tel varchar2(20) not null); alter table addrbook2 add (tel varchar2(20) not null) * 1행에 오류: ORA-01758: 테이블은 필수 열을 추가하기 위해 (NOT NULL) 비어 있어야 합니다. SQL> alter table addrbook2 add (tel varchar2(20)); 테이블이 변경되었습니다. --추가되는 칼럼에 기본값 지정은 가능하다. --먼저 이미 추가된 칼럼을 삭제 후 SQL> alter table addrbook2 drop column tel; 테이블이 변경되었습니다. -- 다시 기본값을 주면서 추가하자. 기존 데이터의 tel 칼럼은 기본값으로 지정한 값으로 바뀐다. SQL> alter table addrbook2 add (tel varchar2(20) default '000-000-0000'); 테이블이 변경되었습니다. SQL> select * from addrbook2; NAME ADDRESS AGE TEL ----- -------------------- ---------- ---------------- 1길동 서울1 24 000-000-0000 2길동 서울2 22 000-000-0000

칼럼 변경

테이블에 존재하는 칼럼에 대해 칼럼 길이, 기본값 지정, 테이터타입에 대해 변경을 하는 것으로 아래와 같은 형식을 가진다.

[기본 형식]

ALTER TABLE table_name MODIFY (column_name data_type [DEFAULT expr]…)

n 구조를 변경 할 칼럼에 기존 데이터가 없다면 타입이나 크기의 변경이 자유롭다.

n 칼럼 길이 변경의 경우 기존 데이터가 있으면 기존데이터의 길이보다 크거나 같을 경우만 길이변경이 가능하다. (기존 데이터의 길이 미만으로 변경 불가능 하다.)

n 숫자타입은 정밀도(scale) 증가 가능하다.

n 기존 데이터가 있는 경우 타입에 대한 변경은 VARCHAR2, CHAR만 가능하다.

n 만약 DEFAULT값을 변경하는 경우라면 기존 데이터는 영향이 없으며 이후 입력되는 데이터부터 기본값이 지정된다.

SQL> column address format a20 SQL> select * from addrbook2; NAME ADDRESS AGE TEL ----- -------------------- ---------- -------------------- 1길동 서울1 24 000-000-0000 2길동 서울2 22 000-000-0000 SQL> alter table addrbook2 modify name varchar2(5); 테이블이 변경되었습니다. SQL> alter table addrbook2 modify name varchar2(4); alter table addrbook2 modify name varchar2(4) * 1행에 오류: ORA-01441: 일부 값이 너무 커서 열 길이를 줄일 수 없음 -- tel칼럼 값이 null이지만 insert시 지정이 되었으므로 기본값으로 되지는 않는다. SQL> insert into addrbook2 (name, address, age, tel) values ('3길동','서울3',27, null); --아래의 경우 tel 칼럼이 입력시 빠졌으므로 기본값의 적용을 받는다. SQL> insert into addrbook2 (name, address, age) values ('4길동','서울4',29); SQL> commit; 커밋이 완료되었습니다. SQL> select * from addrbook2; NAME ADDRESS AGE TEL ----- -------------------- ---------- ---------------- 1길동 서울1 24 000-000-0000 2길동 서울2 22 000-000-0000 3길동 서울3 27 4길동 서울4 29 000-000-0000 --이번에는 tel칼럼의 기본값을 변경해 보자. 변경사항은 새로 입력되는 데이터부터 지정된다. SQL> alter table addrbook2 modify tel default '999-999-9999'; 테이블이 변경되었습니다. SQL> insert into addrbook2 (name, address, age) values ('5길동','서울5',55); 1 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다. SQL> select * from addrbook2; NAME ADDRESS AGE TEL ----- -------------------- ---------- -------------------- 1길동 서울1 24 000-000-0000 2길동 서울2 22 000-000-0000 3길동 서울3 27 4길동 서울4 29 000-000-0000 5길동 서울5 55 999-999-9999

SET UNUSED(column_name) : 대용량 테이블의 큰 사이즈 칼럼의 경우 당장 삭제 하기에는 시스템에 부담이 되어 미래에 삭제하기 위해 사용 안 한다고 표시하는 것으로 한번 UNUSED로 지정된 칼럼은 다시 사용한다고 되돌리기는 불가능하고 DESC 명령어로 칼럼이 나타나지 않으며 DML 사용시 해당 칼럼은 사용 불가능 하다.

DROP UNUSED COLUMN : SET UNUSED(칼럼) 로 지정된 칼럼을 삭제한다.

SQL> alter table addrbook2 set unused (tel); 테이블이 변경되었습니다. SQL> desc addrbook2; 이름 널? 유형 ----------------------------------------- -------- -------------------- NAME NOT NULL VARCHAR2(5) ADDRESS VARCHAR2(50) AGE NUMBER SQL> insert into addrbook2 (name, address, age, tel) values ('6길동','서울6',66, null); insert into addrbook2 (name, address, age, tel) * 1행에 오류: ORA-00904: "TEL": 부적합한 식별자 -- SET UNUSED로 지정된 칼럼은 alter table…drop unused column으로 삭제 가능하다. SQL> alter table addrbook2 drop unused column ; 테이블이 변경되었습니다. SQL> desc addrbook2; 이름 널? 유형 ----------------------------------------- -------- -------------- NAME NOT NULL VARCHAR2(5) ADDRESS VARCHAR2(50) AGE NUMBER

칼럼 삭제

[기본 형식]

ALTER TABLE table_name DROP COLUMN column_name

SQL> alter table addrbook2 add tel varchar2(20); 테이블이 변경되었습니다. SQL> insert into addrbook2 (name, address, age, tel) 2 values ('6길동','서울6',66, null); 1 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다. SQL> alter table addrbook2 drop column tel; 테이블이 변경되었습니다. SQL> desc addrbook2;

칼럼 이름 변경

[기본 형식]

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name

old_column_name : 이름을 바꾸고자 하는 칼럼

new_column_name : 새 이름

SQL> alter table addrbook2 add tel varchar2(20); 테이블이 변경되었습니다. SQL> insert into addrbook2 (name, address, age, tel) values ('7길동','서울7',77, null); 1 개의 행이 만들어졌습니다. SQL> alter table addrbook2 rename column tel to tel2; 테이블이 변경되었습니다. SQL> desc addrbook2; SQL> alter table addrbook2 rename column tel2 to tel; 테이블이 변경되었습니다. SQL> desc addrbook2;

8.1.3 테이블 이름변경 및 삭제, 주석추가

[기본 형식]

RENAME old_table_name TO new_table_name

DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS]

RENAME 명령어 : 테이블의 이름을 변경한다.

--AddrBook3테이블의 이름을 AddrBook4로 변경하시오. SQL> rename addrbook3 to addrbook4; 테이블 이름이 변경되었습니다.

DROP TABLE명령어 : 테이블 정의 및 데이터 삭제

n 삭제되는 테이블은 반드시 사용자 계정에서 생성한 테이블이어야 하며 다른 스키마계정의 테이블이라면 DROP ANY TABLE시스템 권한이 있어야 삭제 가능하다.

n 데이터 딕셔너리로부터 테이블 정의가 삭제되며 테이블에 로우(ROW) 및 연관되어 생성된 인덱스, 트리거도 함께 삭제된다.

n 삭제되는 테이블과 연관된 스키마 오브젝트(뷰 및 PLSQL함수 및 프러시저등)는 삭제되지는 않지만 ‘INVALID’ 상태가 된다. 동의어의 경우 사용 시 오류가 발생된다.

n 만약 삭제되는 테이블의 주키(Primary Key) 또는 고유키(UNIQUE KEY)를 자식 테이블에서 참조하고 있다면 기본적으로 삭제 불가능하며 자식테이블을 먼저 삭제하거나 DROP TABLE 명령어 마지막에 CASCADE CONSTRAINTS 조건을 줘서 삭제해야 한다.

n 즉 CASCADE CONSTRINTS 옵션은 삭제하는 테이블의 기본키나 고유키를 참조하는 참조 무결성 제약조건을 동시에 삭제하는 경우에 사용된다.

n 삭제되는 테이블에 할당된 EXTENTS는 테이블스페이스에 반환되며 다른 오브젝트 생성시 사용할 수 있게 된다.

n 플래시백 옵션으로 DROP TABLE 후 복구가 가능하다.

SQL> select * from addrbook2; NAME ADDRESS AGE TEL2 ----- -------------------- ---------- -------------------- 1길동 서울1 24 2길동 서울2 22 …… 5길동 서울5 55 7길동 서울7 77 7 개의 행이 선택되었습니다. SQL> alter table addrbook2 add primary key (name); 테이블이 변경되었습니다. SQL> create table sales ( name varchar2(10), goods varchar2(20), amount number ); --sales 테이블에 외래키 추가(addrbook2 테이블의 name 칼럼에 대해 참조 무결성 정의) SQL> alter table sales add constraints fk_addrbook2_sales_name foreign key(name) references addrbook2(name); 테이블이 변경되었습니다. SQL> insert into sales values ('1길동','수박',1000); 1 개의 행이 만들어졌습니다. SQL> insert into sales values ('2길동','참외',2000); 1 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다. SQL> drop table addrbook2; drop table addrbook2 * 1행에 오류: ORA-02449: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있습니다 SQL> drop table addrbook2 cascade constraints; 테이블이 삭제되었습니다. --삭제 한 테이블을 복구 SQL> flashback table addrbook2 to before drop; 플래시백이 완료되었습니다.

Truncate 명령어

테이블의 정의는 그대로 두고 데이터(행, 로우)만 잘라낸다.

[기본 형식]

TRUNCATE TABLE [schema.]table_name

n 테이블의 모든 행, 물리적인 저장공간을 삭제하며 테이블과 관련된 Storage Parameter를 테이블을 처음 만들때로 초기화 한다.

n 테이블과 연관된 인덱스, 동의어, 뷰 등은 그대로 유지된다.

n 테이블의 테이터를 삭제하는 가장 빠른 방법이며 DDL이므로 RollBack 되지 않는다.

n 테이블을 삭제하고 다시 만드는것보다 효율적인 방법이다.

n 테이블이 Trunacate명령을 실행하는 유저로 생성되었거나 DROP ANY TABLE 시스템 권한을 가지고 있어야 한다.

n WHERE절을 이용하여 특정 로우만 삭제하는 것은 불가능 하며 해당 테이블에 DELETE명령과 관련하여 트리거가 생성되어 있더라도 트리거는 동작하지 않는다.

SQL> select * from addrbook2; NAME ADDRESS AGE TEL2 ----- -------------------- ---------- -------------------- 1길동 서울1 24 2길동 서울2 22 6길동 서울6 66 3길동 서울3 27 4길동 서울4 29 5길동 서울5 55 7길동 서울7 77 7 개의 행이 선택되었습니다. SQL> delete from addrbook2; 7 행이 삭제되었습니다. SQL> select * from addrbook2; 선택된 레코드가 없습니다. SQL> rollback; 롤백이 완료되었습니다. SQL> truncate table addrbook2; 테이블이 잘렸습니다. SQL> rollback; 롤백이 완료되었습니다. SQL> select * from addrbook2; 선택된 레코드가 없습니다.

테이블, 칼럼에 주석 추가하기

[기본 형식]

COMMENT ON table_name IS comments

COMMENT ON COLUMN table_name.column_name IS comments

SQL> comment on table addrbook2 is '주소록테이블'; 주석이 생성되었습니다. SQL> comment on column addrbook2.name is '성명'; 주석이 생성되었습니다. SQL> column comments format a20; SQL> select * from user_tab_comments where comments is not null; TABLE_NAME TABLE_TYPE COMMENTS ------------------------------ ----------- -------------------- ADDRBOOK2 TABLE 주소록테이블 SQL> column column_name format a10; SQL> select * from user_col_comments where comments is not null; TABLE_NAME COLUMN_NAM COMMENTS ------------------------------ ---------- -------------------- ADDRBOOK2 NAME 성명