oracle中块commit遇异常减少工作丢失的方法-savepoint的用法示例

.文中想说明一种使用块 commit，遇到某条数据异常时，能够 commit 异常出现前所有工作 的方法。 一 初始情况 iiam_bookingreg 这张表存在主键地区号zoneno ，存在如下记录 二 pl/sql 验证过程 验证过程描述： 任务：向iiam_booingreg表中插入地区号为00102 到00300 的这么一些记录，每 10条 commit一次，其中插入过程包含两步，第一步向表中插入4位的地区号，第二步将刚刚插 入的记录地区号字段左补0 ，扩位为5 位地区号。 由于数据库中已有00203，00300地区两条记录，所以在遇到这两条记录时，第一步插入 4位地区号语句不会发生异常，而第二步将地区号左补0 时会发生主键冲突异常，我们的考察 目标 1 这两步能否作为一个事物来处理，同进退。 2 虽然是每10条commit一次，但是能否做到当其中某一条出现数据库异常时，仍能够 commit上一个提交点到出现这个异常前的所有记录，而尽可能少的丢失已有工作，同时又不 commit当前的异常。 我们使用了回滚点savepoint来实现这些目标。 最后一个commit，这里暂时把它注释掉，用以考察第291到第299条数据在遇到第300 条 语句错误时。能否通过rollback to savepoint后的commit提交。 DECLARE v_booking iiam_bookingreg%ROWTYPE; -插入数据结构体 v_loop integer := 1; -循环变量 BEGIN -循环变量范围 102-300， for v_loop in 102 300 loop v_booking := null; v_booking.zoneno := '0' | v_loop; -地区号拼接 v_booking.bookdate := '20100429' v_booking.flag := '1' SAVEPOINT savpoint1; -定义回滚点，注意该回滚点处于循环体内， v_booking.workdate := '20100429' begin insert into iiam_bookingreg values v_booking; exception when others then rollback to SAVEPOINT savpoint1; -插入异常时回滚当前一次循环 commit; -commit回滚点之前的所有工作 end; begin update iiam_bookingreg set zoneno ='0'|v_booking.zoneno -地区号扩位，会遇到主键冲突 where zoneno= v_booking.zoneno; exception when others then rollback to SAVEPOINT savpoint1; -插入异常时回滚当前一次循环 commit; -commit回滚点之前的所有工作 end; if mod(v_loop, 10) = 0 then -一般情况下每 10条 commit一次 commit; end if; end loop; -commit; END; 三 验证结果： 1 00203 地区号之前的记录被 commit，00203 地区记录插入时无异常，更新时主键冲突， 但是插入语句也被回滚了，系统中没有02034 位地区号的记录，第一步插入和第二步 更新扩位被作为一个事物来处理了。 2 00300 地区号之前的记录被 commit， 其中 00290 之前的记录通过每 10 条 commit 一次的语句 commit， 00291 到 00299 的记录通过 rollback to savepoint 后的 commit 提交。 00300 地区记录插入时无异常，更新时主键冲突，但是插入语句也被回滚了，系统中没有 02034 位地区号的记录。 四 总结 每多少条 commit 一次数据库是提高移行过程性能的有效方法，但是块分大了，往往 异常时丢失的工作也多了，如果能够保留出异常的那条语句之前的所有工作，那应该是一 种不错的结果，当然这是要求前后多条记录之间是不具有相关性的前提下才能使用的，如 果记录前后相关，那么就应该是作为一个事物来处理的，整片 commit，整片 rollback。 虽然有时候即使出现异常了而又不希望丢失之前的工作，不回滚当前这条异常，直接 commit 应该也不会有多大的问题，但是能够将当前的异常回滚一下还是比较规范的做法。 而且忽略异常直接 commit 是有前提的，就是整个事物仅包含这么一条 sql 语句，如本例中 就不适合这样的做法，如果在第二步将地区号扩位时遇到异常时不回滚，直接 commit，那 就会把前面的第一步插入也 commit 进去了，系统中就会出现 4 位地区号的中间结果了。