Re: Rollback and Commit
Date: Mon, 7 Jun 2004 11:14:08 -0400
Message-ID: <H_ednWhtjacwG1nd4p2dnA_at_comcast.com>
did not look in detail at your code, but here's a quick bunch of suggestions:
- use standard oracle pl/sql exception handling to indicate success or failure instead of returning 0 or 1 -- also realize that if you handle an exception in your pl/sql code, work done in that procedure up to the exception is NOT rolled back, it's only rolled back if you do not handle the exception
- rollback in linear -- it will rollback all work performed since the prior rollback or commit
- look into rollback to savepoint for more control or the transaction
- should you check the return code? depends on what your functionality requires
read up on transaction processing in the oracle concepts manual and exception handling in the PL/SQL manual
- mcs
<anders_tung_at_hotmail.com> wrote in message
news: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:14:08 CEST