(转)oracle存储过程的事务处理及事务使用断点回滚
createorreplaceprocedure stu_proc
(
v_id innumber,
v_name invarchar2,
v_age innumber,
v_msg out varchar2
) as
begin
insertinto student(id, sname, age) values (v_id, v_name, v_age);
commit;
v_msg:='添加成功';
exception
when others then
rollback;
v_msg:='失败成功';
RAISE_APPLICATION_ERROR(-20010, 'ERROR：违反唯一索引约束！');
end;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，将会滚整个事务处理。
