Hi,
I have a procedure which will call 3 functions.
First function will update a record.
Second function will delete a record.
Third function will insert a record.
Each function will return a zero if successful, otherwise return 1.
My questions are
1) If one or more of the function fail, and I will call a rollback in
the procudure, would it rollback all 3 functions?
2) Or I should check the return code for each function before I call
the next function?
Feel free to tell me your suggestion!
Thank you!
Anders
Procedure my_proc ()
ret1 number;
ret2 number;
ret3 number;
total := number;
Begin
ret1 := function_one();
ret2 := function_two();
ret3 := function_three();
total := ret1 + ret2 +ret3;
if total > 0 then
rollback;
else
commit;
end if;
End my_proc;
function_one()
return number is
ret_one number := 0;
begin
update dummy1 set dummy_col ='dummy';
return ret_one;
EXCEPTION
WHEN OTHERS
THEN
ret_one:=1;
RETURN ret_one;
end function_one();
function_two()
return number is
ret_two number := 0;
begin
delete from dummy2 where dummy_col ='dummy';
return ret_two;
EXCEPTION
WHEN OTHERS
THEN
ret_two:=1;
RETURN ret_two;
end function_two();
function_three()
return number is
ret_three number := 0;
begin
insert into dummy3 (dummy_col) values ('dummy');
return ret_three;
EXCEPTION
WHEN OTHERS
THEN
ret_three:=1;
RETURN ret_three;
end function_three();