231024 Oracle SQL SAVEPOINT

■ SAVEPOINT
    A. DML 작업시 ROLLBACK 을 도와주는 표시자 
    B. SAVEPOINT 표시자; '표시자' 이름만 중복되지 않게 주의할 것
    C. ROLLBACK TO 표시자;

 

# DATA DML 작업 및Transaction 

     - ROLLBACK 전체취소. Transaction 시작시점까지 전부 취소

INSERT INTO employees (employee_id, last_name, department_id) VALUES (9999, ‘Smith’, 10);
DELETE employees WHERE employee_id = 100;
UPDATE employees SET last_name = ‘Clark’;
INSERT INTO employees (employee_id, last_name, department_id) VALUES (8888, ‘Thomas’, 30);
DELETE employees WHERE department_id = 20;
ROLLBACK;

# SAVEPOINT 위치에 따라 ROLLBACK 시점이 달라진다.

 

1) 
INSERT INTO employees (employee_id, last_name, department_id) VALUES (9999, ‘Smith’, 10);
2) 
DELETE employees WHERE employee_id = 100;

SAVEPOINT A;
3) 
UPDATE employees SET last_name = ‘Clark’;
4) 
INSERT INTO employees (employee_id, last_name, department_id) VALUES (8888, ‘Thomas’, 30);

SAVEPOINT B;
5) 
DELETE employees WHERE department_id = 20;

ROLLBACK TO B; -- 5번 transaction 취소
COMMIT; -- 1,2번 transaction 영구히 저장

 

1) 
INSERT INTO employees (employee_id, last_name, department_id) VALUES (9999, ‘Smith’, 10);
2) 
DELETE employees WHERE employee_id = 100;

SAVEPOINT A;
3) 
UPDATE employees SET last_name = ‘Clark’;
4) 
INSERT INTO employees (employee_id, last_name, department_id) VALUES (8888, ‘Thomas’, 30);

SAVEPOINT B;
5) 
DELETE employees WHERE department_id = 20;
ROLLBACK TO A; -- 3,4,5 번 transaction 취소
COMMIT; -- 1,2번 transaction 영구히 저장