Cursor Attributes for DML Operations
Oracle allows you to access information about the most recently executed native DML statement by referencing one of the implicit SQL cursor attributes (these are identical to those listed in Table 1-6). Table 1-8 describes the values returned by these 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. |
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 a 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 whether any rows were modified:
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%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 ...