oracle 事物例子

我寫的。

 儲存點(SAVEPOINT)是交易處理過程中的一個標誌，與復原命令(ROLLBACK)結合使用，主要的用途是允許使用者將某一段處理復原而不必復原整個事務。

如果定義了多個savepoint，當指定復原到某個savepoint時，那麼復原操作將復原這個savepoint後面的所有操作（即使後面可能標記了N個savepoint）。

例如，在一段處理中定義了五個savepoint，從第三個savepoint復原，後面的第四、第五個標記的操作都將被復原，如果不使用ROLLBACK TO savepoint_name而使用ROLLBACK，將會滾整個交易處理。

create or replace procedure TL_Parameters_Delete(projectid integer, schemeid integer)isstrsql varchar(500);beginsavepoint point1;  --===1系統設定表=== strsql:= 'delete  from TL_XTSZPRO where id in (select id from  TL_XTSZPRO where evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point2;  --===2交叉表=== strsql:='delete from tl_jckypro where id in( select id from tl_jckypro where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point3;  ---===3導地線===TL_DDXPROstrsql:='delete from tl_ddxpro where id in( select id from tl_ddxpro where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point4; ---==4氣象區===氣象區工程關聯表TL_ QXQPROstrsql:='delete from TL_QXQPRO where id in( select id from TL_QXQPRO where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point5;----===5杆塔工程關聯表===TL_TOWERPROstrsql:='delete from TL_TOWERPRO where id in( select id from TL_TOWERPRO where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point6;--====6分段設定表===TL_FDSZPROstrsql:='delete from TL_FDSZPRO where id in( select id from TL_FDSZPRO where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point7;---===絕緣子工程關聯表TL_INSULATORPRO===strsql:='delete from TL_INSULATORPRO where id in( select id from TL_INSULATORPRO where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point8;--===接地裝置參數表 TL_GROUNDDEVICEPRO===strsql:='delete from TL_GROUNDDEVICEPRO where id in( select id from TL_GROUNDDEVICEPRO where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point9;--===防震錘參數表TL_FZCCSPRO===strsql:='delete from TL_FZCCSPRO where id in( select id from TL_FZCCSPRO where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point10;--===設計和最佳化計算表TL_SJHYHJSPRO===strsql:='delete from TL_SJHYHJSPRO where id in( select id from TL_SJHYHJSPRO where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point11;---===價格及損耗表TL_JGJSHPRO===strsql:='delete from TL_JGJSHPRO where id in( select id from TL_JGJSHPRO where  evprojectid='||projectid||' and evschemeid='||schemeid||')';execute immediate strsql;--執行savepoint point12;exceptionwhen others thenrollback to savepoint point1;return; dbms_output.put_line(strsql);end TL_Parameters_Delete;