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.


Author's name: Keith Jamieson

Author's Email: Keith_Jamieson@hotmail.com

Date written: 24 July 2001

Oracle version(s): 7.3.4, 8.0.6,8.1.7

Reduce the number of SQL statements in PL/SQL by using the very useful and not often enough used SQL%ROWCOUNT statement. 

Back to index of questions


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


Back to top

Back to index of questions