有以下过程,我想每次触发异常时都回滚事务,应该怎么改呢?
不会在每个异常处理后面都加上
if j_StartWork = 1 then
rollback work;
end if;
吧
-------------------------------------------------------
Create or Replace procedure testproc(
in_SheetID in char,
in_Checker in char,
in_OrderNo in int,
out_Result out int) is
STATUS_IS_VALID exception;
SHEET_NOT_FOUND exception;
UNKNOW_SHEET_STATUS exception;
QTY_ERROR exception;
begin
j_Result := 0;
j_StartWork := 0;
j_startwork := 1;
j_SheetType := 2503;
j_BreakPoint := 2502115;
commit work;
out_Result := j_Result;
return;
exception
when SHEET_NOT_FOUND then
sMsg := '单据没有找到';
raise_application_error(-20000,'SQLCODE='||SQLCODE||',Breakpoint='||j_Breakpoint||',MSG='||sMsg);
when STATUS_IS_VALID then
sMsg := '单据状态错误';
raise_application_error(-20000,'SQLCODE='||SQLCODE||',Breakpoint='||j_Breakpoint||',MSG='||sMsg);
when QTY_ERROR then
sMsg := '验收数据验证错误';
raise_application_error(-20000,'SQLCODE='||SQLCODE||',Breakpoint='||j_Breakpoint||',MSG='||sMsg);
when UNKNOW_SHEET_STATUS then
sMsg := '未定义状态标志';
raise_application_error(-20000,'SQLCODE='||SQLCODE||',Breakpoint='||j_Breakpoint||',MSG='||sMsg);
when others then
if j_StartWork = 1 then
rollback work;
end if;
raise_application_error(-20000,'SQLCODE='||SQLCODE||','|| 'Breakpoint='||j_Breakpoint||',MSG='||SQLERRM);
end testproc;
不会在每个异常处理后面都加上
if j_StartWork = 1 then
rollback work;
end if;
吧
-------------------------------------------------------
Create or Replace procedure testproc(
in_SheetID in char,
in_Checker in char,
in_OrderNo in int,
out_Result out int) is
STATUS_IS_VALID exception;
SHEET_NOT_FOUND exception;
UNKNOW_SHEET_STATUS exception;
QTY_ERROR exception;
begin
j_Result := 0;
j_StartWork := 0;
j_startwork := 1;
j_SheetType := 2503;
j_BreakPoint := 2502115;
commit work;
out_Result := j_Result;
return;
exception
when SHEET_NOT_FOUND then
sMsg := '单据没有找到';
raise_application_error(-20000,'SQLCODE='||SQLCODE||',Breakpoint='||j_Breakpoint||',MSG='||sMsg);
when STATUS_IS_VALID then
sMsg := '单据状态错误';
raise_application_error(-20000,'SQLCODE='||SQLCODE||',Breakpoint='||j_Breakpoint||',MSG='||sMsg);
when QTY_ERROR then
sMsg := '验收数据验证错误';
raise_application_error(-20000,'SQLCODE='||SQLCODE||',Breakpoint='||j_Breakpoint||',MSG='||sMsg);
when UNKNOW_SHEET_STATUS then
sMsg := '未定义状态标志';
raise_application_error(-20000,'SQLCODE='||SQLCODE||',Breakpoint='||j_Breakpoint||',MSG='||sMsg);
when others then
if j_StartWork = 1 then
rollback work;
end if;
raise_application_error(-20000,'SQLCODE='||SQLCODE||','|| 'Breakpoint='||j_Breakpoint||',MSG='||SQLERRM);
end testproc;
解决方案 »
- 几个问题
- 如何在oracle中利用存储过程实现分组排序功能
- 机器没有正常关机,出现ORA-01033错误,怎么办?请高手们帮帮忙忙,本人非常急。
- 关于查询当前月的记录~~~在线求解
- 如何将oracle的数据库移植到mysql5.0上?
- 为何我的java线程会引起oracle的TNSLSNR.EXE进程占用100%的CPU
- 关于调整字段顺序的问题
- 谁知道按用户数买和按PROCESSER买LICENSE差异到底如何,谁能给我解饰一下
- 请问怎么在Oracle数据库中修改sid,我十分十分急用,多少分都可以!!!!!!!!!
- 能回答的一率给1000
- 创建logmnr出现的问题。。。
- imp导入时遇到IMP-00017错误,求救
if j_StartWork = 1 then
rollback work;
end if; 本来就只有OTHERS有呀,
用一个就是了,在raise之前,设置sMsg,然后到Exception块,不就好了吗?
只需要写你自己的这个exception和others.我看你的错误处理,基本上都是一样的。
Create or Replace procedure testproc(
in_SheetID in char,
in_Checker in char,
in_OrderNo in int,
out_Result out int) is
STATUS_IS_VALID exception;
SHEET_NOT_FOUND exception;
UNKNOW_SHEET_STATUS exception;
QTY_ERROR exception;
begin
j_Result := 0;
j_StartWork := 0;
j_startwork := 1;
j_SheetType := 2503;
...
if ... then
sMag := 'error occurred';
raise MY_DEFINE_ERROR;
end if;
...
j_BreakPoint := 2502115;
commit work;
out_Result := j_Result;
return;
exception
when MY_DEFINE_ERROR then
if j_StartWork = 1 then
rollback work;
end if;
raise_application_error(-20000, 'SQLCODE= ' | |SQLCODE | | ',Breakpoint= ' | |j_Breakpoint | | ',MSG= ' | |sMsg);
when others then
if j_StartWork = 1 then
rollback work;
end if;
raise_application_error(-20000, 'SQLCODE= ' | |SQLCODE | | ', ' | | 'Breakpoint= ' | |j_Breakpoint | | ',MSG= ' | |SQLERRM);
end testproc;