How can I find out how many rows were affected by an SQL statement after I have executed it ?

The Oracle (tm) Users' Co-Operative FAQ

How can I find how many rows were affected by an SQL Statement after I have executed it.

In order to determine how many rows were affected by an SQL statement, the SQL%ROWCOUNT function is used.  " The %ROWCOUNT  attribute returns the number of records fetched from a cursor at the time the attribute is queried."-Oracle PL/SQL Programming.  Since all SQL uses cursors, be they explicit or implicit, the SQL%ROWCOUNT function can be used. Before I came across   SQL%ROWCOUNT, I used to have code which issued an update, performed a count, and then inserted a row if the count was zero as indicated below:    The procedure entitled Sample, shows  a more efficient way to achieve the same effect, using SQL%ROWCOUNT.

        UPDATE sample 
 	   SET testno = 1
         WHERE test   = 'Pl/SQL';

         SELECT count(*)
           INTO v_count 
           FROM SAMPLE
          WHERE test = 'PL/SQL';            
         IF v_count := 0
         THEN
              /* Insert statement */
         END IF;

        CREATE OR REPLACE
        PROCEDURE sample IS
 	    v_rows_processed integer := 0;
        BEGIN
            UPDATE sample
               SET testno = 1; 
             WHERE test   = 'PL/SQL';
            v_rows_processed := SQL%ROWCOUNT;
            dbms_output.enable;
            dbms_output.put_line('There were '||v_rows_processed||' rows updated');
            IF v_rows_processed := 0
            THEN 
                /* Insert Statement */
            END IF;
         END sample;.

.

Further reading: Oracle PL/SQL Programming O'Reilly Steven Feuerstein with Bill Pribyl    ISBN: 1-56592-335-9