Oracle PL/SQL for DBAs

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.

Table 1-8. 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.

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 ...