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，將會滾整個事務處理。
posted on 2008-06-24 16:41
有貓相伴的日子 閱讀(4744)
評論(3) 編輯 收藏 所屬分類:
pl/sql