存储过程里的事务操作:
create or replace procedure pr_mypro2(p_a in varchar2,p_b in varchar2,p_count out number)
![](/Images/OutliningIndicators/None.gif)
temp varchar2(1000); /*定义临时变量*/
is
begin
![](/Images/OutliningIndicators/None.gif)
select code into p_count from table1 where a=p_a; /*查询并返回值*/
temp := p_count; /*将返回值赋给临时变量*/
savepoint point1; /*保存点*/
![](/Images/OutliningIndicators/None.gif)
insert into table2(a,b)values(temp,p_b); /*将临时变量值添加到新表的字段*/
![](/Images/OutliningIndicators/None.gif)
savepoint point2;
![](/Images/OutliningIndicators/None.gif)
insert into ![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
exception
when others then
rollback to savepoint point1; /*异常处理,保存点下面的操作都不会被执行*/
return;
![](/Images/OutliningIndicators/None.gif)
end;
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
保存点(SAVEPOINT)是事务处理过程中的一个标志,与回滚命令(ROLLBACK)结合使用,主要的用途是允许用户将某一段处理回滚而不必回滚整个事务。
如果定义了多个savepoint,当指定回滚到某个savepoint时,那么回滚操作将回滚这个savepoint后面的所有操作(即使后面可能标记了N个savepoint)。
例如,在一段处理中定义了五个savepoint,从第三个savepoint回滚,后面的第四、第五个标记的操作都将被回滚,如果不使用ROLLBACK TO savepoint_name而使用ROLLBACK,将会滚整个事务处理。