Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page