什么是savepoint?
Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.
例如：
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> UPDATE SCOTT.DEPT SET loc ='a' WHERE loc='NEW YORK';
1 row updated
SQL> SAVEPOINT a;
Savepoint created
SQL> UPDATE SCOTT.DEPT SET loc ='b' WHERE loc='DALLAS';
1 row updated
SQL> SAVEPOINT b;
Savepoint created
SQL> ROLLBACK TO SAVEPOINT a;
Rollback complete
SQL> COMMIT;
Commit complete
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING a
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
事務中的Savepoints
你可以在事務上下文中聲明稱為savepoint的中間標記。Savepoint將一個長事務分隔為較小的部分。
使用savepoint，你可以在長事務中任何點任意標記你的操作。然后你可以選擇回滾在事務中當前點之前、聲明的savepoint之后執行的操作。比如，你可以在一長段復雜的更新中使用savepoint，如果犯了個錯，你不需要重新提交所有語句。
Savepoints在應用程序中同樣有用。如果一個過程包含幾個函數，那可以在每個函數前創建一個savepoint。如果一個函數失敗，返回數據到函數開始前的狀態并在修改參數或執行一個恢復操作后重新運行函數就非常容易。
在回滾到一個savepoint后，Oracle釋放由被回滾的語句持有的鎖。其他等待之前被鎖資源的事務可以進行了。其他要更新之前被鎖行的事務也可以執行。
當一個事務回滾到一個savepoint，發生下列事件：
1. Oracle僅回滾savepoint之后的語句。
2. Oracle保留這一savepoint，但所有建立于此后的savepoints丟失。
3. Oracle釋放在該savepoint后獲得的所有表、行鎖，但保留之前獲得的所有鎖。
事務保持活動并可繼續。
無論何時一個會話在等待事務，到savepoint的回滾不會釋放行鎖。為了確保事務如果無法獲得鎖也不會懸掛（hang），在執行UPDATE或DELETE前使用FOR UPDATE ... NOWAIT。（這里指回滾的savepoint之前獲得的鎖。該savepoint后獲得的行鎖會被釋放，之后執行的語句也會被徹底回滾。）
注意：
1.savepoint 名字保持唯一
2.如果后面新設置的一個savepoint的名字和前面的一個savepoint名字重復，前一個savepoint將被取消
3.設置savepoint后，事務可以繼續commit,全部回退或者回退到具體一個savepoints
(Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.)
4.撤銷的處理必須是在沒有發出commit命令的前提下才能有效。
如下：在commit;后執行rollback to savepoint失敗
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> UPDATE SCOTT.DEPT SET loc ='a' WHERE loc='NEW YORK';
1 row updated
SQL> SAVEPOINT a;
Savepoint created
SQL> UPDATE SCOTT.DEPT SET loc ='b' WHERE loc='DALLAS';
1 row updated
SQL> SAVEPOINT b;
Savepoint created
SQL> COMMIT;
Commit complete
SQL> ROLLBACK TO SAVEPOINT a;
ROLLBACK TO SAVEPOINT a
ORA-01086: 從未創建保留點 'A'
SQL> SELECT * FROM SCOTT.DEPT ;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING a
20 RESEARCH b
30 SALES CHICAGO
40 OPERATIONS BOSTON