SQL> begin 2 savepoint p1; 3 insert into t_test_transaction values(1); 4 savepoint p2; 5 insert into t_test_transaction values(2); 6 commit; 7 savepoint p3; 8 insert into t_test_transaction values(3); 9 rollback to p2; 10 commit; 11 end; 12 /
begin savepoint p1; insert into t_test_transaction values(1); savepoint p2; insert into t_test_transaction values(2); commit; savepoint p3; insert into t_test_transaction values(3); rollback to p2; commit; end;
savepoint sp;
op;
savepoint sp2;
op;
savepoint sp3;
op;
commit;--新添
rollback;
是否恢复到sp3的状态?
Table truncated
SQL>
SQL> begin
2 savepoint p1;
3 insert into t_test_transaction values(1);
4 savepoint p2;
5 insert into t_test_transaction values(2);
6 savepoint p3;
7 insert into t_test_transaction values(3);
8 rollback ;
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed
SQL> select * from t_test_transaction;
ID
----------
SQL>
SQL> begin
2 savepoint p1;
3 insert into t_test_transaction values(1);
4 savepoint p2;
5 insert into t_test_transaction values(2);
6 savepoint p3;
7 insert into t_test_transaction values(3);
8 rollback to p2;
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed
SQL> select * from t_test_transaction;
ID
----------
1
SQL>
op;
savepoint sp2;
op;
savepoint sp3;
op;
commit;--新添
savepoint sp4;
op;
rollback;
回滚到sp4。
已经commit的sp,无法回滚了。
2 savepoint p1;
3 insert into t_test_transaction values(1);
4 savepoint p2;
5 insert into t_test_transaction values(2);
6 commit;
7 savepoint p3;
8 insert into t_test_transaction values(3);
9 rollback to p2;
10 commit;
11 end;
12 /
begin
savepoint p1;
insert into t_test_transaction values(1);
savepoint p2;
insert into t_test_transaction values(2);
commit;
savepoint p3;
insert into t_test_transaction values(3);
rollback to p2;
commit;
end;
ORA-01086: 从未创建保存点 'P2'
ORA-06512: 在 line 10
SQL>