Rollback and Commit

From: anders_tung_at_hotmail.com <(anders_tung_at_hotmail.com)>
Date: 7 Jun 2004 08:00:13 -0700
Message-ID: <da2e9ee1.0406070700.40d26356_at_posting.google.com>



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(); Received on Mon Jun 07 2004 - 17:00:13 CEST

Original text of this message