而若要指定恢復到哪個交易點前的資料, 可以設定 Savepoint.
參考下面的範例 :
範例 1 : 沒有指定 rollback 到哪裡
begin
insert into tom1 values( '1' );
insert into tom1 values( '2' );
insert into tom1 values( '3' );
savepoint a;
insert into tom1 values( '4' );
insert into tom1 values( '5' );
insert into tom1 values( '6' );
rollback;
insert into tom1 values( '7' );
insert into tom1 values( '8' );
insert into tom1 values( '9' );
commit;
end;
-- 結果
-- 7, 8, 9
範例 2 : 指定 rollback 到哪裡
begin
insert into tom1 values( '1' );
insert into tom1 values( '2' );
insert into tom1 values( '3' );
savepoint a;
insert into tom1 values( '4' );
insert into tom1 values( '5' );
insert into tom1 values( '6' );
rollback to savepoint a;
insert into tom1 values( '7' );
insert into tom1 values( '8' );
insert into tom1 values( '9' );
commit;
end;
-- 結果
-- 1, 2, 3, 7, 8, 9
範例 3 : rollback 之前先做 commit, 且沒有指定 rollback 到哪裡
begin
insert into tom1 values( '1' );
insert into tom1 values( '2' );
insert into tom1 values( '3' );
savepoint a;
insert into tom1 values( '4' );
insert into tom1 values( '5' );
insert into tom1 values( '6' );
commit;
rollback;
insert into tom1 values( '7' );
insert into tom1 values( '8' );
insert into tom1 values( '9' );
commit;
end;
-- 結果
-- 1, 2, 3, 4, 5, 6, 7, 8, 9
範例 4 : rollback 之前先做 commit, 且有指定 rollback 到哪裡
begin
insert into tom1 values( '1' );
insert into tom1 values( '2' );
insert into tom1 values( '3' );
savepoint a;
insert into tom1 values( '4' );
insert into tom1 values( '5' );
insert into tom1 values( '6' );
commit;
rollback to savepoint a;
insert into tom1 values( '7' );
insert into tom1 values( '8' );
insert into tom1 values( '9' );
commit;
end;
-- 結果
-- 執行失敗, 因為 commit 之後, Savepoint 已不存在
以上, 希望對各位有所幫助.