Oracle中savepoint的使用

create table TABLE5
(
  id   INTEGER,
  name VARCHAR2(10)
);




prompt Importing table table5...
set feedback off
set define off
insert into table5 (ID, NAME)
values (1, '赵1');

insert into table5 (ID, NAME)
values (2, '赵2');

insert into table5 (ID, NAME)
values (3, '赵3');

insert into table5 (ID, NAME)
values (4, '赵4');

insert into table5 (ID, NAME)
values (5, '赵5');

insert into table5 (ID, NAME)
values (6, '赵6');

insert into table5 (ID, NAME)
values (7, '赵7');

insert into table5 (ID, NAME)
values (8, '赵8');

insert into table5 (ID, NAME)
values (9, '赵9');

insert into table5 (ID, NAME)
values (10, '赵10');

prompt Done.

 

 

 

 

savepoint sp1;--完整正确
update table5 SEt name='赵15' where id=5;
select * from table5;--查出来变更了一处
savepoint sp2;--变更了赵15
update table5 SEt name='赵16' where id=6;
savepoint sp3;--变更了赵16
select * from table5;
rollback to sp2;
select * from table5;--查了下，回退到了预期的地方
commit;
-------------------------------
SELECT * FROM table5;
savepoint sp0;
update table5 SEt name='赵5' where id=6;
savepoint sp1;--id还是顺序的，赵6变更成了赵5
update table5 set id=100 where name='赵5';
savepoint sp2;--id100的有两个，赵5有两个
rollback to sp1;
rollback to sp2;--出错
rollback to sp0;
rollback to sp1;--出错
rollback;