oracle 存储过程事务使用断点回滚

oracle 存储过程事务使用断点回滚

create or replace procedure delete_exceed_bound(playtype   varchar2 , end07   varchar2 , end08   varchar2 )
   is

   begin
     delete lotterydate where lotterydate.playtype = playtype and    lotterydate.lotterydate_name > end07 and lotterydate.lotterydate_name like ' 07% ' ;
     delete lotterydate where lotterydate.playtype = playtype and    lotterydate.lotterydate_name > end08 and lotterydate.lotterydate_name like ' 08% ' ;
     savepoint p1;
     delete province_sell_amounts where province_sell_amounts.play_no = playtype and province_sell_amounts.term > end07 and province_sell_amounts.term like ' 07% ' ;
     delete province_sell_amounts where province_sell_amounts.play_no = playtype and province_sell_amounts.term > end08 and province_sell_amounts.term like ' 08% ' ;

     delete province_winning_prize where province_winning_prize.play_no = playtype and province_winning_prize.term > end07 and province_winning_prize.term like ' 07% ' ;
     delete province_winning_prize where province_winning_prize.play_no = playtype and province_winning_prize.term > end08 and province_winning_prize.term like ' 08% ' ;
     savepoint p2;
     delete condition_winning_prize where condition_winning_prize.play_no = playtype and condition_winning_prize.term > end07 and condition_winning_prize.term like ' 07% ' ;
     delete condition_winning_prize where condition_winning_prize.play_no = playtype and condition_winning_prize.term > end08 and condition_winning_prize.term like ' 08% ' ;
     savepoint p3;
     delete open_result where open_result.play_no = playtype and open_result.term > end07 and open_result.term like ' 07% ' ;
     delete open_result where open_result.play_no = playtype and open_result.term > end08 and open_result.term like ' 08% ' ;


   exception
       when others then
         dbms_output.put_line(sqlerrm);
         rollback to savepoint p1;
   end delete_exceed_bound;

保存点(SAVEPOINT) 是事务 处理过程中的一个标志，与回滚命令 (ROLLBACK) 结合使用，主要的用途是允许用户将某一段处理回滚而不必回滚整个事务 。

 

如果定义了多个 savepoint ，当指定回滚到某个 savepoint 时，那么回滚操作将回滚这个 savepoint 后面的所有操作（即使后面可能标记了 N 个 savepoint ）。

在一段处理中定义了3个 savepoint ，从第2个 savepoint 回滚，后面的第3个标记的操作都将被回滚，如果不使用 ROLLBACK TO savepoint_name 而使用 ROLLBACK ，将会滚整个事务 处理。