Oracle PL/SQL Best Practices

Name

SQL-17: Check SQL%ROWCOUNT when updating or removing data that “should” be there.

Synopsis

The SQL%ROWCOUNT cursor attribute returns the numbers of rows affected by the most recent INSERT, UPDATE, or DELETE statement executed in your session. Check this value to verify that the action completed properly. (Note that updates and deletes don’traise an exception if no rows are affected.)

Example

Let’s suppose that the local library spelled my name incorrectly when they entered my books into their system. Now they need to fix it and they want to make sure they got them all (eight, including this text):

BEGIN
   UPDATE book
      SET author = 'FEUERSTEIN, STEVEN'
    WHERE author = 'FEVERSTEIN, STEPHEN';

   IF SQL%ROWCOUNT < 8
   THEN
      ROLLBACK;
      Pl (
        'Find the rest of his books, rapido!');
   END IF;
END;

Benefits

Your programs will check for and be able to handle problems more effectively.