오늘의 요약
5. DML ( Data Manipulation Language )
(1) 설명
테이블내의 데이터를 '입력', '수정', '삭제' 하게 하는 SQL
cf) 우선 테이블을 복사하자 ( copy1.sql )
(2) 종류
1) insert 문
SQL> insert into DEPT2 values(50, '회계부', '서울');
SQL> insert into EMP2
values(8000, '박서하', '개발자', 7839, SYSDATE, 4000, null, 50);
SQL> select * from EMP2 where EMPNO>=8000;
SQL> insert into EMP2(EMPNO, ENAME) values(9000, '허유민');
Err> insert into EMP2(EMPNO, ENAME) values(9000, '성진희');
2) update 문
SQL> update EMP2 set ENAME='이소담', SAL=6000 where EMPNO=9000;
SQL> update EMP2 set EMPNO=9999 where EMPNO=9000; --PK컬럼변경(O)
Err> update EMP2 set EMPNO=8000 where EMPNO=9999; --8000이미존재
SQL> update EMP2 set DEPTNO=50 where EMPNO=9999; --가능
Err> update EMP2 set DEPTNO=70 where EMPNO=9999; --불가(70번부서는 존재X)
3) delete 문
SQL> delete from EMP2 where EMPNO=9999;
SQL> delete from DEPT2 where DEPTNO=50; -- on delete cascade; 옵션에서만 가능
Err> delete from DEPT2 where DEPTNO=20; --불가: 바라보는 자식 데이터가 존재하기 때문에
SQL> delete from DEPT2 where DEPTNO=40; --가능
6. TCL( Transaction Control Language )
(1) 설명
DML(insert, update, delete)문이 실행되어 DBMS에 '저장'되거나,
'되돌리기' 위해서 실행해야 하는 SQL문
(2) Transaction
1) 정의
분리되서는 안될 (논리적)'작업 단위'
2) 시작
<1> DBMS에 처음 접속했을 때
<2> 하나 or 여러개의 DML문을 실행한 후 commit 또는
rollback 을 수행한 직후
3) 끝
<1> commit 또는 rollback 실행되는 순간
예외) rollback to a;
<2> DDL 이나 DCL 문이 실행되는 순간
세션1>
SQL> insert into DEPT2 values(1, 'a', 'b'); --DML
SQL> update DEPT2 set DNAME='A' where DEPTNO=1; --DML
SQL> create table AA(no number); --DDL
세션2>
SQL> select * from DEPT2;
<3> DB가 (정상/비정상) 종료될 때
세션1>
SQL> delete from DEPT2 where DEPTNO=1;
SQL> 왼쪽 상단의 X버튼 클릭해서 세션종료 -- 비정상종료
세션2>
SQL> select * from DEPT2; -- commit 되지 않음
세션1>
SQL> delete from DEPT2 where DEPTNO=1;
SQL> exit; 또는 quit; --정상종료
세션2>
SQL> select * from DEPT2; -- commit 되었음
세션1> DML 작업중
SQL> insert into DEPT2 values(1, '가', '나');
세션2> DBMS 종료
SQL> sqlplus system/java1019 as sysdba
SQL> shutdown immediate; --DB서비스 종료
SQL> startup; --DB서비스 시작
세션3>
SQL> select * from DEPT2; -- commit 되지 않음( rollback )
<4> 작업중인 Connection(세션)을 끊을 때
세션1>
SQL> insert into DEPT2 values(1, '가', '나');
SQL> conn system/java1019
세션2> SQL> select * from DEPT2; -- commit 되었음
(3) TCL의 종류
1) commit
DML문의 결과를 영구적으로 DBMS에 반영하는 SQL문
2) rollback
DML문의 결과를 (이전 Transaction 까지) 취소하는 SQL
3) savepoint
트랜젝션을 구성하는 DML의 한 지점을 표시하는 저장점
세션1>
SQL> insert into DEPT2 values(1, 'a1', 'b1');
SQL> insert into DEPT2 values(2, 'a2', 'b2');
SQL> savepoint a;
SQL> insert into DEPT2 values(3, 'a3', 'b3');
SQL> savepoint b;
SQL> insert into DEPT2 values(4, 'a4', 'b4');
SQL> rollback to a;
Err> rollback to b; -- 안됨( 이미 늦음 )
세션2>
SQL> select * from DEPT2; -- 1과 2번만 확인됨
(4) TCL 관련 특성
1) READ CONSISTENCY (읽기 일관성 - '해당 row'에 대해서)
어떤 사용자가 변경 중인 행을 '다른 사용자'가 변경 할 수 없게 하는 기술로써
변경 중인 사용자에 의해 commit 이나 rollback 이 실행된 후 변경가능한 특성
<예>
세션1> update DEPT2 set DNAME='가' where DEPTNO=1;
세션2> update DEPT2 set DNAME='나' where DEPTNO=1; --보류
세션1> commit; 또는 rollback;
세션2> 1 행이 갱신되었습니다
2) LOCK (잠금현상 - '해당 table'에 대해서)
<예>
세션1> update DEPT2 set DNAME='개발부', LOC='경기'; -- 테이블 전체 lock
세션2> update DEPT2 set LOC='서울' where DEPTNO=1; -- 보류
cf) lock 해제 방법
-> commit; 또는 rollback;
7. DDL ( Data Definition Language )
(1) 설명
데이터베이스 내의 객체(Object)를 '생성'하고 '변경'하고, '삭제'하기 위해서
사용되는 SQL문
(2) 객체(Object)
1) table
2) index
3) view
4) sequence
5) synonym
6) session ( 오라클로 접근하는 하나의 인터페이스[접근통로] )
7) user
...
(3) DDL 종류
1) create : 객체를 생성할 때
SQL> create table DDLTEST(
NO number(4) primary key,
NAME varchar2(10),
ADDR varchar2(20)
);
2) alter : 객체를 변경할 때
( 옵션: add, add constraint, modify, set unused column, drop column,
rename column, drop constraint, disable constraint, enable constraint )
<1> add
SQL> insert into DDLTEST values(1000, '가', '나');
SQL> insert into DDLTEST values(2000, '다', '라');
SQL> alter table DDLTEST add(TEL varchar2(20));
SQL> insert into DDLTEST values(3000, '마', '바', '사');
SQL> insert into DDLTEST values(4000, '아', '자', '차');
SQL> select * from DDLTEST;
<2> modify
SQL> alter table DDLTEST modify(
NAME varchar2(15), ADDR varchar2(25)
);
SQL> desc DDLTEST
<3> drop column
SQL> alter table DDLTEST drop column NO; --PK컬럼도 삭제 가능
SQL> desc DDLTEST
제약조건확인> select CONSTRAINT_NAME, CONSTRAINT_TYPE
from user_constraints where TABLE_NAME='DDLTEST';
3) drop : 객체를 삭제할 때
SQL> drop table DDLTEST;
SQL> show recyclebin;
SQL> purge recyclebin;
cf) 오라클 휴지통 (recyclebin)
- 테이블 조회
show recyclebin;
- 비우기
purge recyclebin;
- 복원
flashback table EMP2 to before drop;
- 휴지통에 남기지않고 바로삭제
drop table EMP2 purge;
- 특정 테이블만 비우기
purge table DEPT2;
4) rename : 객체이름 변경할 때
SQL> rename DDLTEST to DDLTEST2;
SQL> select * from tab;
5) comment : 객체에게 주석을 부여할 때
<1> 테이블 주석
SQL> comment on table DDLTEST2 is 'DDL테스트용 테이블';
SQL> desc user_tab_comments; -- 데이터 딕셔너리
SQL> select COMMENTS from user_tab_comments where TABLE_NAME='DDLTEST2';
<2> 컬럼 주석
SQL> comment on column DDLTEST2.NAME is '회원이름';
SQL> desc user_col_comments; -- 데이터 딕셔너리
SQL> select COMMENTS from user_col_comments where TABLE_NAME='DDLTEST2';
6) truncate : 모든 행을 잘라낼 때
SQL> truncate table DDLTEST;
SQL> select * from DDLTEST;
cf) delete문과의 차이점
- 되돌릴 수 없음
- where절을 사용할 수 없음 
휘발방지