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.
Get Oracle PL/SQL Best Practices now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.