database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 11334 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 11334
오라클 기본강좌(1)-테이블 관리
작성자
정재익(advance)
작성일
2002-07-12 21:26
조회수
22,171

원본 출처 : http://www.tech-farms.com/TFmanager/read.php?id=oracle1lect&page=1&category=&keyfield=&keyword=&select_arrange=head_no&desc=asc&no=2

 

1. 테이블 관리

가. 데이터 유형

데이터 유형의 종류

아래 예문의 데이터 유형 중에는 Column의 데이터 유형은 문자, 숫자, DATE가 있습니다

 

SQL> create table member(no number not null,

1 name varchar2(10) not null, tel varchar2(15), addr varchar2(50),

2 jumsu number, code number not null, gaip date);

Table created.

 

 

(1) 문자열

·Char(size) : (size)byte 고정길이 문자 data

·Varchar2(size) : (size)byte 가변길이 문자 data

(들어가는 데이터의 양만큼 가변적으로 사용하므로 char보다 유용하게 바이트를 줄일 수 있습니다.)

 

(2) 숫 자

·Number : 최대 38자까지의 정수, 실수, 숫자

·Number(size) : (size)byte까지의 정수 숫자 기억

·Number(p,s) : p=전체자릿수(소수점을 제외한), s=소수점이하 자리 수

 

정수/실수 숫자 기억

346.98 : number(10,3) =======>결과=346.98

234.378 : number(10,2) =======>결과= 234.38 (반올림이 됨)

 

(3) 날 짜

·Date : BC 4712 1,1부터 AD 4712 12,31 까지 표현 가능

 

(4) Long

·긴 문자열을 저장할 수 있는 데이터 타입으로 2 G byte까지의 자료를 기억할 수 있습니다.

·단 하나의 테이블에 하나만 만들 수 있습니다.

·index키로 사용할 수 없습니다.

·SQL 함수나, select시, where조건, order by, group by절에는 사용불가능

·제약조건에 사용할 수 없습니다.

 

(5) Raw와 Long raw

·image, video, sound등 동화상 자료를 저장할 때 쓰입니다.

·테이블 당 하나만 만들 수 있습니다.

·최대 2G(giga) 바이트까지 저장이 가능합니다.

 

 

나. 명명법

(1) 테이블 명과 column명은 반드시 문자로 시작하고 최대 30자까지만 허용됩니다.

(2) 이름은 문자 A-Z, a-z, 0-9, _(밑줄), $, #만으로 이루어져야 합니다.

(3) 동일한 사용자가 소유한 다른 객체의 이름과 중복되지 않도록 해야 합니다.

(4) Reserved word는 쓸 수 없습니다.

 

 

 

다. 제약조건 추가, 삭제

(1) NOT NULL 제약조건

column에 not null의 제약조건을 명시하면 그 column에 null값을 가질 수 없습니다.

member 테이블의 name column에 10byte의 가변길이 문자를 가지며not null조건을 적용합니다

SQL> CREATE TABLE MEMBER(.....,

NAME VARCHAR2(10) NOT NULL, ......);

 

다음 예는member 테이블의 tel column에 15byte의 문자를 갖는 가변길이 문자 column인 TEL column에 NOT NULL 제약조건을 적용하며 이 제약조건의 이름은 MEMBER_TEL_NN입니다.

SQL> CREATE TABLE MEMBER(....., TEL VARCHAR2(15)

CONSTRAINT MEMBER_TEL_NN NOT NULL, .......);

 

(2) UNIQUE 제약조건

·column이나 column의 조합을 고유키로 지정합니다.

·테이블에는 이 키에 대해 같은 값을 갖는 행이 하나밖에 없습니다.

·키가 한 column에 대한 것이라면 null 값을 쓸 수 있습니다.

·column이나 테이블 레벨에서 정의할 수 있습니다.

·조합된 경우에는 테이블 레벨에서 정의할 수 있습니다.

·UNIQUE 인덱스는 unique column에 대해 자동적으로 생성됩니다.

 

(3) PRIMARY KEY 제약조건

·테이블에 대한 primary key를 생성합니다.

·테이블 당 하나의 primary key만을 정의.

·테이블의 각 행을 고유하게 식별하는 column이나 column의 집합.

·column이나 column의 조합에 대한 유일성을 강화하고 column이 null값을 배제.

·column이나 테이블 레벨에서 정의.

·Composite primary key는 테이블 레벨에서 생성합니다.

·UNIQUE 인덱스는 primary key column에 대해 자동적으로 생성합니다.

 

(4) FOREIGN KEY 제약조건

·동일 테이블이나 테이블간의 primary key나 foreign key간의 관계를 설정.

·foreign key 값은 parent 테이블간의 값과 일치하거나 null이어야 합니다.

·자식 테이블에 정의하고, 참조되는 column을 갖는 테이블은 부모테이블입니다.

 

(5)CHECK 제약조건

·각 행이 만족해야 하는 조건을 정의.

 

DROP 절이 있는 ALTER TABLE 명령을 사용, DROP절의 CASCADE 옵션은 종속적인 다른 제약조건도 삭제시킵니다.

ALTER TABLE 테이블 명

DROP PRIMARY KEY | UNIQUE (column)

CONSTRAINT 제약조건 [CASCADE];

 

SQL> alter table member

2 drop primary key cascade;

테이블이 변경되었습니다.

 

ADD나 DROP 절이 있는 ALTET TABLE 명령을 써서 테이블에 대한 제약조건을 추가하거나 삭제할 수 있습니다. (반드시 상대 테이블의 기본키가 지정되어 있어야 외래키(foreign key)로 수정이 됩니다.)

ALTER TABLE 테이블 명

ADD [CONSTRAINT 제약조건명]

type (column);

 

SQL> alter table member

2 add constraint member_code_fk

3 foreign key (code)

4 references circle(code);

테이블이 변경되었습니다.

 

SQL> desc member

이름 Null? 유형

------------------------------- -------- ----

NO NOT NULL NUMBER

NAME NOT NULL VARCHAR2(10)

TEL VARCHAR2(15)

ADDR VARCHAR2(50)

JUMSU NUMBER

CODE NOT NULL NUMBER

GAIP DATE

 

(6)제약조건 Enable, Disable시키기

ALTER TABLE 테이블 명

DISABLE | ENABLE CONSTRAINT 제약조건 [CASCADE];

 

라. 테이블 생성, 삭제

(1) 테이블 생성

CREATE TABLE 명령을 실행하여 데이터를 저장할 테이블구조를 만든 다음 display합니다.

CREATE TABLE 테이블이름

(column datatype [DEFAULT 식] [column제약조건].

.....

[테이블 제약조건]);

SQL> create table member

2 (no number constraint member_no_nn not null,

3 name varchar2(10) constraint member_name_nn not null,

4 tel varchar2(15),

5 addr varchar2(50),

6 jumsu number,

7 code number constraint member_code_nn not null,

8 gaip date);

SQL> desc member

이름 Null? 유형

------------------------------- -------- ----

NO NOT NULL NUMBER

NAME NOT NULL VARCHAR2(10)

TEL VARCHAR2(15)

ADDR VARCHAR2(50)

JUMSU NUMBER

CODE NOT NULL NUMBER

GAIP DATE

테이블이 생성되었습니다.

 

 

(2) 테이블 명 변경과 TRUNCATE

RENAME 명령은 테이블, 뷰, sequence, synonym의 이름을 수정하는 데에 사용하며, TRUNCATE 명령은 테이블의 모든 행을 삭제하고 테이블이 사용한 저장 공간을 해제하는데 사용합니다.

RENAME 명령

RENAME 이전이름 TO 새로운 이름;

 

rename 명령을 실행하여 member 테이블 이름을 다른 menber 테이블 이름으로 수정 한다음 display합니다.

SQL> rename emp to renber;

테이블명이 바뀌었습니다.

 

TRUNCATE 명령

TRUNCATE TABLE 테이블명;

 

SQL> rename member to renber;

테이블명이 바뀌었습니다.

SQL> select * from renber;

NO NAME TEL ADDR JUMSU CODE GAIP

----------------------------------------------------------------

1 이정석 324-7912 서울 종로구 창신동 14-1 80 103 99/09/01

2 강호식 298-4871 서울 관악구 신림3동 45-2 70 104 98/08/29

..........

15 이석준383-3939 성남시 분당구 내정동 4-5 99 102 96/08/30

19 개의 행이 선택되었습니다.

 

 

truncate 실행명령어를 실행하여 테이블들 중에 mm테이블 이름을 삭제하여 display할 경우 "선택된 레코드가 없습니다."라고 메시지가 나옵니다..

SQL> truncate table mm;

테이블이 잘렸습니다.

SQL> select * from mm;

선택된 레코드가 없습니다.

 

(3)테이블 삭제

(가)DROP TABLE 명령은 table의 자체를 삭제.

(나)테이블을 삭제하면 테이블의 모든 데이터 및 관련된 모든 인덱스가 없어짐.

(다)CASCADE CONSTRAINTS 옵션은 종속적인 참조 무결성 제약조건도 삭제.

DROP TABLE 테이블 [CASCADE CONSTRAINT];

SQL> DROP TABLE MEMBER;

테이블이 삭제되었습니다.

 

 

마. 테이블 구조 변경

다음은 테이블의 행 삽입, 수정, 삭제에 필요한 실행 명령어입니다.

(1) RENAME : 데이터베이스 객체의 이름 수정

(2) TRUNCATE : 테이블의 모든 행 삭제

(3) COMMENT : 데이터 객체에 대한 주석을 작성

(가)Column 추가

ADD절이 있는 ALTER TABLE 명령을 써서 테이블에 column을 추가합니다.

ALTER TABLE 테이블 명

ADD (column 데이타유형[DEFAULT 식][NOT NULL]

[. column 데이타유형]....);

 

SQL> alter table circle

2 add (kochi_name varchar2(8));

Table altered.

 

 

(나)Column 수정

column의 datatype, 크기, default 값, NOT NULL column 제약조건에 대한 추가정보를 수정할 수 있습니다.

ALTER TABLE 테이블명

MODIFY (column datatype [DEFAULT 식] [NOT NULL]

[. column datatypw]....);

SQL> alter table circle

2 modify (kochi_name varchar2(50));

Table altered.

 

바. 데이터 조작(DML)

데이터베이스에 데이터를 추가, 수정, 삭제를 하려 할 때 DML문을 사용합니다.

(1) 데이터 조작 명령어와 트랜잭션 제어 명령어

(가)INSERT : 테이블에 새로운 행 추가

(나)UPDATE : 테이블의 행 수정

(다)DELETE : 테이블의 행 삭제

(라)COMMIT : 저장되지 않은 모든 수정사항을 DB에 저장

(마)SAVEPOINT : Savepoint 설정

(사)ROLLBACK : 저장되지 않은 모든 수정사항을 취소

 

(2) 테이블에 새로운 행 삽입(INSERT)

member 테이블에 새로운 행을 삽입하여 display합니다.

INSERT INTO 테이블 [(column [, column...])]

VALUES (값[,값....]);

 

※여기서, column은 테이블의 column 명이고, 값은 column에 해당하는 값입니다.

SQL> insert into member

2 (no, name, tel, addr, jumsu, code, gaip)

3 values (15, '장하림', '348-3873', '전남 여수시 노대면 오진리 29', 98, 105,

4 to_date('99_09-14', 'yy-mm-dd'));

1 개의 행이 만들어졌습니다.

 

SQL> select * from member;

NO NAME TEL ADDR JUMSU CODE GAIP

---------------------------------------------------------------

1 이정석 324-7912 서울 종로구 창신동 14-1 80 103 99/09/01

........

17 김영수 291-8374 충남 논산시 광석면 신당리 60 99 111 99/09/02

15 장하림 3488-3873 전남 여수시 노대면 오진리 29 98 105 99/09/14

 

(3) 특정 날짜와 시간 값 입력

이정석이란 회원의 정보를 입력한다. 날짜 값을 삽입할 때 대개 DD-MON-YY 형식을 사용하여 날짜를 아래와 같이 입력한 다음 실행하여 출력하여보자.

(TO_DATE함수를 이용)

SQL> insert into member values(1,'이정석','324-7912','서울 종로구 창신동 14-1',

80,103,to_date('09-sep-99','dd-mon-yy'));

1 row created.

SQL> select * from member;

NO NAME TEL ADDR JUMSU CODE GAIP

-------------------------------------------------------------

1 이정석 324-7912 서울 종로구 창신동 14-1 80 103 09-SEP-99

 

 

(4) NULL값 삽입

(Implicit한 방법)

날짜를 생략하고 새로운 가입정보를 입력합니다. 날짜는 INSERT 절에 나열하지 않았기 때문에 NULL값이 날짜(gaip)에 자동적으로 입력된다.

SQL> insert into member(no,name,tel,addr,jumsu,code)

2 values (3,'이순민','392-9012','대전시 중구 무수동 301-2',95,101);

1 row created.

 

 

(Explicit한 방법)

다른 방법으로, NULL키워드를 이용하여 null 값을 명시적으로 줄 수 있습니다.

SQL> insert into member

2 values (2,'강호식','298-4871','서울시 관악구 신림3동 45-2',70,103,null);

1 row created.

 

(5)테이블에 추가한 내용 확인

SQL> select no, name, tel, addr,

2 code,gaip

3 from member

4 where no=4;

NO NAME TEL ADDR CODE GAIP

------------------------------------------------------------------

4 김희수 391-9192 대전시 동구 가오동 300-050 102 11-SEP-99

 

 

(6) 특정 날짜와 시간 값 입력

날짜 값을 삽입할 때 대개 YY-MM-DD을 사용합니다. 이때 입력되는 세기는 현대세기 시간은 자정(00:00:00)입니다. 만약 여러분이 다른 세기와 특정시간을 입력하고자 한다면 TO_DATE함수를 이용합니다.

 

member 테이블에 회원에 대한 정보를 입력합니다. gaip_date를 1999년 9월 12일로 합니다.(시간은 생략)

SQL> insert into member(no,name,tel,addr,jumsu,code,gaip)

2 values(17,'김영수','291-8374','충남 논산시 광석면 신당리 60',

3 99,111,to_date('99-09-02','YY-MM-DD'));

1 개의 행이 만들어졌습니다.

 

 

(7) 치환변수를 이용한 행 입력

member 테이블에 회원에 대한 정보를 입력하는데 있어 날짜는 직접 넣고 나머지 값은 치환변수를 써서 대화식으로 행을 입력한 다음 display합니다.

SQL> select * from member;

NO NAME TEL ADDR JUMSU CODE GAIP

------------------------------------------------------------------

1 이정석 324-7912 서울 종로구 창신동 14-1 80 103 99/09/01

2 강호식 298-4871 서울 관악구 신림3동 45-2 70 104 98/08/29

...........

15 장하림 348-3873 전남 여수시 노대면 오진리 29 98 105 99/09/14

18 이길찬 876-7638 대구시 내동구 동내동 34-98 93 103 96/06/22

19 개의 행이 선택되었습니다.

 

(8)행의 수정(UPDATE)

SQL> insert into member(no, name, tel, addr, jumsu, code, gaip)

2 values (&no, '&name', '&tel', '&addr', &jumsu, &code, '&gaip');

no의 값을 입력하십시오: 18

name의 값을 입력하십시오: 이길찬

tel의 값을 입력하십시오: 876-7638

addr의 값을 입력하십시오: 대구시 내동구 동내동 34-98

jumsu의 값을 입력하십시오: 93

code의 값을 입력하십시오: 103

gaip의 값을 입력하십시오: 96-06-22

구2:values (&no, '&name', '&tel', '&addr', &jumsu, &code, '&gaip')

신2:values(18, '이길찬', '876-7638', '대구시 내동구 동내동 34-98', 93, 103, '96-06-22')

1 개의 행이 만들어졌습니다.

 

UPDATE {테이블}

SET column = 값 [.column = 값...]

[WHERE 조건];

여기서 WHERE 절은 옵션으로 있지만 생략되면 테이블의 모든 행이 변경되기 때문에 필요에 따라 사용하기 바랍니다.

 

회원번호가 3인 회원을 777-3862번으로 전화번호를 변경하는 UPDATE문 을 작성하고 display합니다.

SQL> update member

2 set tel='777-3862'

3 where no=3;

1 row updated.

 

SQL> select no,name,tel,addr

2 from member

3 where no in(2,3);

 

NO NAME TEL ADDR

------------------------------------------------

2 강호식 298-4871 서울시 관악구 신림3동 45-2

3 이순민 777-3862 대전시 중구 무수동 301-2

 

(9) 행 삭제(DELETE)

WHERE 절이 생략되면 테이블의 모든 행이 삭제되며 다른 테이블에서 참조되어지는 PRIMARY KEY를 포함하고 있는 행을 삭제하려고 하면 error가 발생합니다.

DELETE FROM 테이블 [WHERE 조건];

 

code번호가 104인 데이터를 모두 제거하여 display합니다.

①제거이전 데이타

SQL> select * from circle;

CODE NAME

--------------

101 수영부

104 정호수

②where 조건을 사용하여 제거

SQL> delete from circle where code=104;

1 row deleted.

 

③제거이후 데이타

SQL> select *from circle;

CODE NAME

------------

101 수영부

 

 

(10) 트랜잭션 처리

아직 영구적으로 반영(commit)되지 않은 DML문의 집합을 트랜잭션 또는 작업의 논리적 단위라고 합니다.

(가) 트랜잭션 유형

①데이타 조작(DML) : 여러 DML 문으로 구성

②데이타 정의(DDL) : 하나의 DDL 문으로 구성

③데이타 제어(DCL) : 하나의 DCL 문으로 구성

(나) Explicit 트랜잭션 제어문

COMMIT, SAVEPOINT, ROLLBACK 문을 이용하여 트랜잭션을 제어할 수 있습니다.

①COMMIT : 아직 저장되지 않은 모든 데이터 변경 사항을 DB에 저장하고 현재의 트랜잭션을 종료

②SAVEPOINT 이름 : 현재의 트랜잭션에 savepoint를 표시

③ROLLBACK [TO SAVEPOINT 이름] : 아직 저장되지 않은 모든 데이터 변경 사항을 무시(취소)하고 현재의 트랜잭션을 종료

 

(다) Implicit 트랜잭션 처리

①자동커밋 : DDL 명령이나 DCL 명령실행 : COMMIT나 ROLLBACK을 명시적으로 실행하지 않고 SQL*Plus를 정상 종료

②자동 롤백 : SQL*Plus의 비정상 종료나 시스템 실패

 

(라)변경된 사항의 저장(COMMIT) : 변경된 사항의 저장의 성공적인 종료를 나타내며 한 개의 트랜잭션(변경사항의 저장)을 구성하는 모든 SQL 문장들은 실행 완료됩니다.

SQL> commit;

Commit complete.

 

 

(마)변경된 사항의 취소(ROLLBACK) : 변경된 사항의 취소 즉 비 성공적이라고 할 수 있으며 변경된 사항의 데이터베이스의 갱신들을 철회하여 변경하기 전으로 데이터베이스 상태로 복귀합니다.

SQL> rollback;

Rollback complete.

 

 

(바) SAVEPOINT를 이용한 ROLLBACK

SAVEPOINT 명령을 써서 현재의 트랜잭션에 저장지점을 설정 할 수 있습니다. 따라서 변경된 사항을 작게 분할 할 수 있습니다. ROLLBACK TO SACEPOINT문을 써서 표시한 곳까지 변경된 사항을 최소할 수 있습니다.

SQL> delete member where no = 105;

SQL> savepoint point_a;

Savepoint created.

SQL> update member set jumsu = jumsu * 1.1;

SQL> rollback to point_a;

Rollback complete.

 

 

(사)문장 레벨의 롤백 : 문장의 실행 중 error가 발생하면 해당 문장만 롤백됩니다. 이전 DML 문장에 의해 수정사항은 롤백되지 않습니다. DDL 명령을 실행하기 전에 Server는 Implicit savepoint를 만들어 현재의 트랜잭션을 커밋하려 합니다. DDL 명령이 실패하면 트랜잭션은 커밋을 하기 때문에 이전 내용을 롤백할 수 없습니다. DDL 명령이 실패하면 트랜잭션은 커밋되지 않습니다.

[Top]
No.
제목
작성자
작성일
조회
11337오라클 기본강좌(3)-시퀀스 생성, 수정, 삭제
정재익
2002-07-12
13547
11336오라클 기본강좌(3)-함수
정재익
2002-07-12
17009
11335오라클 기본강좌(2)-SELECT
정재익
2002-07-12
12468
11334오라클 기본강좌(1)-테이블 관리
정재익
2002-07-12
22171
11333Oracle ODBC 설정법
정재익
2002-07-12
7699
11327Linux 용 오라클 제품관련 FAQ
정재익
2002-07-12
6508
11322EXPORT받은 DUMP 파일의 CHARACTER SET 확인 방법
정재익
2002-07-12
8828
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.050초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다