[Oracle] 데이터 갱신과 트랜잭션 제어

SQL 구조질의문

DQL : select 

DML : insert, delete, update

DDL(정의) : create, alter

TCL (Transaction Control Language) : commit, rollback

 

SQL + 제어구조 => PL/SQL (오라클에서만 나옴)

공통  SQL : ANSI-SQL ( 거의 모든 데이터베이스에서 실행되는 SQL ) 

 

insert

insert into 테이블명 

(컬1, 컬2, 컬3 ...) values (v1, v2, v3 ...)

컬럼명이 있으면, 명기된 컬럼 순서 

컬럼명이 생략되면, 테이블 구조순서 

where 조건절 ; 

 

data가 입력되지 않으면, null값이 채워짐 

-> null을 허용하는 경우 

 

insert select 문 

 

update

update 테이블명 set 

   컬1 = 값1, 컬2 = 값2 ,,,

where 조건절 ; 

<---> 잘못된 수정/입력/삭제를 복구 => Transaction ( DML일 때 필요 ) 

 

commit 하지 않으면 DB에는 적용되지 않고 session에만 살아있다가 꺼짐. 

commit 하면 이전의 내용까지 commit되고, 다음부터 transaction 시작 

잘못되었을 경우 rollback해서 transaction 복구 

 

컬럼명 = 컬럼명 + 값 

=> 기존값에 연산해서 새로운 값을 더해서 수정해라 

 

subquery를 이용한 수정문 

 

subquery의 결과가 

다중 행일때는 in 사용 

하나 일때는 = 사용

create table a_enrol
as select * 
from enrol
where stu_no < 20150000;

update a_enrol
set enr_grade = enr_grade +10 
where sub_no = (select sub_no
				from subject
                where sub_name='시스템분석설계');

 

delete

delete from 테이블명 

where 조건절 ; 

-> 데이터 삭제 

 

drop table 테이블명 ; 

-> 테이블 삭제, 트랜잭션의 영향을 받지 않음, auto commit 

 

merge

create table a_student
	as select * 
    from student
    where 1 = 0 ;

merge into a_student a
	using student s 
on (s.stu_no = a.stu_no)
when matched then
	update set a.stu_weight = s.stu_weight
when not matched then
	insert values(s.stu_no, s.stu_name, s.stu_dept, null, null, null, null, s.stu_weight) ;

 

TCL

특징 : all or nothing 

insert   |     rollback update | delete  | ------------------------------ commit | 완료되면서 새로운 트랜잭션이 시작된다

 

transaction -- 신뢰성 

무결성 : 입력되는 데이터는 결점을 가지면 안됨 

 

문제가 하나도 없으면 완료하고, 

하나라도 문제가 있으면 처음으로 돌아간다. 

( 금융거래 시, 반드시 들어감 ) 

 

commit 되면 돌아갈 수 없음 

 

예시 ) 

delete  create ( auto commit 되는 문장 ) rollback <-- 위에있는 delete까지 auto commit 되어서 rollback 할 수 없음

 

create table A 

as select * from B

where 1 = 0 ;     // data없이 테이블 구조만 복제하는 경우 

 

select문은 lock의 영향을 받지 않음. // select문 자체가 lock일 발생시키지는 않는다 

commit 되지 않았으면 lock이 걸린 상태

 

상호간에 lock을 걸면 -> deadlock ( 상호교착 ) 

oracle에서는 deadlock을 스스로 해제