Rollback in PLSQL Exception

Question

Would like to know whether rollback is required when a SQL exception (exception when others) is detected:

declare
 cursor c_test is
    select    *
    from  tesing;
    begin
       for rec in c_test loop
       begin
           update test1 set test1.name=rec.name where test1.id=rec.id;
           IF sql%rowcount = 1 THEN
                            commit;
           ELSIF   sql%rowcount =0 THEN
                   dbms_output.put_line('No Rows Updated');
           else
                  dbms_output.put_line('More than 1 row exists');
                  rollback;
           END IF;
       exception when others then
                  dbms_output.put_line(Exception');
                  rollback;
      end;      

end;

Answer 1

First, I'm assuming we can ignore the syntax errors (for example, there is no END LOOP, the dbms_output.put_line call is missing the first single quote, etc.)

As to whether it is necessary to roll back changes, it depends.

In general, you would not have interim commits in a loop. That is generally a poor architecture both because it is much more costly in terms of I/O and elapsed time. It also makes it much harder to write restartable code. What happens, for example, if your SELECT statement selects 10 rows, you issue (and commit) 5 updates, and then the 6th update fails? The only way to be able to restart with the 6th row after you've fixed the exception would be to have a separate table where you stored (and updates) your code's progress. It also creates problems for any code that calls this block which has to then handle the case that half the work was done (and committed) and the other half was not.

In general, you would only put transaction control statements in the outermost blocks of your code. Since a COMMIT or a ROLLBACK in a procedure commits or rolls back any work done in the session whether or not it was done by the procedure, you want to be very cautious about adding transaction control statements. You generally want to let the caller make the determination about whether to commit or roll back. Of course, that only goes so far-- eventually, you're going to be in the outer-most block that will never be called from some other routine and you need to have appropriate transaction control-- but it's something to be very wary about if you're writing code that might be reused.

In this case, since you have interim commits, the only effect of your ROLLBACK would be that if the first update statement failed, the work that had been done in your session prior to calling this block would be rolled back. The interim commit would commit those previous changes if the first update statement was successful. That's the sort of side effect that people worry about when they talk about why interim commits and transaction control in reusable blocks are problematic.