Cursor Attributes for DML Operations
Oracle allows you to access information about the most recently executed implicit cursor by referencing one of the following special implicit cursor attributes:
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN
Implicit cursor attributes return information about the execution of an INSERT, UPDATE, DELETE, or SELECT INTO statement. Cursor attributes for SELECT INTOs are covered in Chapter 14. In this section, we'll discuss how to take advantage of the SQL% attributes for DML statements.
First of all, remember that the values of implicit cursor attributes always refer to the most recently executed SQL statement, regardless of the block in which the implicit cursor is executed. And before Oracle opens the first SQL cursor in the session, all the implicit cursor attributes yield NULL. (The exception is %ISOPEN, which returns FALSE.)
Table 13-1 summarizes the significance of the values returned by these attributes for implicit cursors.
| Table 13-1. Implicit SQL cursor attributes for DML statements | |
| Name | Description |
| SQL%FOUND | Returns TRUE if one or more rows were modified (created, changed, removed) successfully. |
| SQL%NOTFOUND | Returns TRUE if no rows were modified by the DML statement. |
| SQL%ROWCOUNT | Returns number of rows modified by the DML statement. |
| SQL%ISOPEN | Always returns FALSE for implicit cursors, because Oracle opens and closes implicit cursors automatically. |
Now let's see how we can use cursor attributes with implicit cursors.
· Use SQL%FOUND to determine if your DML statement affected any rows. For example, from time to time an author will change his name and want the new name used for all of his books. So I create a small procedure to update the name and then report back via a Boolean variable the number of book entries affected:
· CREATE OR REPLACE PROCEDURE change_author_name (· old_name_in IN books.author%TYPE,· new_name_in IN books.author%TYPE,· changes_made_out OUT BOOLEAN)· IS· BEGIN· UPDATE books· SET author = new_name_in· WHERE author = old_name_in;· · changes_made_out := SQL%FOUND;END;· Use SQL%NOTFOUND to confirm that your DML statement did not affect any rows. This is the inverse of SQL%FOUND.
· Use SQL%ROWCOUNT when you need to know exactly how many rows were affected by your DML statement. Here is a reworking of the above name-change procedure that returns a bit more information:
· CREATE OR REPLACE PROCEDURE change_author_name (· old_name_in IN books.author%TYPE,· new_name_in IN books.author%TYPE,· rename_count_out OUT PLS_INTEGER)· IS· BEGIN· UPDATE books· SET author = new_name_in· WHERE author = old_name_in;· · rename_count_out := SQL%ROWCOUNT;END;