(1) 설명
1) DBMS 내의 객체(Object)를 '생성', '변경', '삭제'를 위한 SQL
(2) 객체(Object)
1) table
2) index
3) view
4) sequence
5) synonym
6) session
7) user
...
(3) 종류
1) create: 객체를 생성할 때
<1> 생성
ex) create table DDLTEST(
NO number(4) constraint DDLTEST_PK primary key, -- (-9999~9999)
ID varchar2(12),
PWD varchar2(12)
);
<2> 제약조건확인
ex) select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints
where TABLE_NAME='DDLTEST';
<3> 복사방법1
ex) create table DDLTEST2(
NO number(4) constraint DDLTEST2_PK primary key,
ID varchar2(12),
PWD varchar2(12)
);
insert into DDLTEST2 values(-9999, 'a', 'b');
insert into DDLTEST2 values(9999, 'a', 'b');
commit;
<4> 복사방법2>
ex) create table DDLTEST3 as select * from DDLTEST;
alter table DDLTEST3 add constraint DDLTEST3_PK primary key(NO);
2) alter: 객체를 변경할 때
<1> 옵션: add, modify, rename column, drop column, add constraint,
drop constraint, disable constraint, enable constraint
<2> add
ex) alter table DDLTEST add(ADDR varchar2(20));
ex) insert into DDLTEST values(1000, 'scott', 'tiger', 'seoul');
ex) select * from DDLTEST;
<3> modify
ex) alter table DDLTEST modify(ID varchar2(15), PWD varchar2(15));
ex) desc DDLTEST
<4> rename column
ex) alter table DDLTEST rename column PWD to PASS;
ex) desc DDLTEST
<5> drop column
ex) alter table DDLTEST drop column ADDR;
ex) select * from DDLTEST;
<6> drop constraint
ex) alter table DDLTEST drop constraint DDLTEST_PK;
ex) desc DDLTEST
ex) select CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints
where TABLE_NAME='DDLTEST';
3) drop: 객체를 삭제할 때
ex) drop table EMP3;
ex) select tname from tab;
ex) purge recyclebin; -- 휴지통비우기
ex) flashback table EMP3 to before drop; -- 휴지통에서 복구
4) rename: 객체 이름 변경시
ex) rename DDLTEST2 to DDLTEST22;
ex) select tname from tab;
5) comment: 객체에 대한 주석 저장시
<1> 테이블 주석
ex) comment on table DDLTEST is 'DDL테스트용 테이블';
ex) select TABLE_NAME, COMMENTS from user_tab_comments
where TABLE_NAME='DDLTEST';
<2> 컬럼 주석
ex) comment on column DDLTEST.ID is '회원 아이디';
ex) select TABLE_NAME, COLUMN_NAME, COMMENTS from user_col_comments
where TABLE_NAME='DDLTEST';
6) truncate: '모든' 행(row)을 삭제시
ex) truncate table DDLTEST3;
ex) select * from DDLTEST3;
7) delete 문과의 차이점
<1> 되돌릴 수 없음 ( rollback 불가!! )
<2> where절을 사용할 수 없다.
<3> 속도가 빠르다.
'SQL > Oracle' 카테고리의 다른 글
Oracle 09. DCL ( Data Control Language ) (0) | 2022.04.02 |
---|---|
Oracle 07. TCL ( Transaction Control Language ) (0) | 2022.03.31 |
Oracle 06. DML ( Data Manipulation Language ) (0) | 2022.03.31 |
Oracle 05. Join & SubQuery (0) | 2022.03.28 |
Oracle 04. DQL - 함수 ( Function ) (0) | 2022.03.25 |