I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL.
Best Answer
You use the sql%rowcount variable.
You need to call it straight after the statement which you need to find the affected row count for.
For example:
set serveroutput ON; DECLARE i NUMBER; BEGIN UPDATE employees SET status = 'fired' WHERE name LIKE '%Bloggs'; i := SQL%rowcount; --note that assignment has to precede COMMITCOMMIT; dbms_output.Put_line(i); END; For those who want the results from a plain command, the solution could be:
beginDBMS_OUTPUT.PUT_LINE(TO_Char(SQL%ROWCOUNT)||' rows affected.');end;The basic problem is that SQL%ROWCOUNT is a PL/SQL variable (or function), and cannot be directly accessed from an SQL command. By using a noname PL/SQL block, this can be achieved.
... If anyone has a solution to use it in a SELECT Command, I would be interested.
alternatively, SQL%ROWCOUNTyou can use this within the procedure without any need to declare a variable
SQL%ROWCOUNT can also be used without being assigned (at least from Oracle 11g).
As long as no operation (updates, deletes or inserts) has been performed within the current block, SQL%ROWCOUNT is set to null. Then it stays with the number of line affected by the last DML operation:
say we have table CLIENT
create table client (val_cli integer,status varchar2(10))/We would test it this way:
begindbms_output.put_line('Value when entering the block:'||sql%rowcount);insert into client select 1, 'void' from dualunion all select 4, 'void' from dualunion all select 1, 'void' from dualunion all select 6, 'void' from dualunion all select 10, 'void' from dual; dbms_output.put_line('Number of lines affected by previous DML operation:'||sql%rowcount);for val in 1..10loopupdate client set status = 'updated' where val_cli = val;if sql%rowcount = 0 thendbms_output.put_line('no client with '||val||' val_cli.');elsif sql%rowcount = 1 thendbms_output.put_line(sql%rowcount||' client updated for '||val);else -- >1dbms_output.put_line(sql%rowcount||' clients updated for '||val);end if;end loop; end;Resulting in:
Value when entering the block:Number of lines affected by previous DML operation:52 clients updated for 1no client with 2 val_cli.no client with 3 val_cli.1 client updated for 4no client with 5 val_cli.1 client updated for 6no client with 7 val_cli.no client with 8 val_cli.no client with 9 val_cli.1 client updated for 10Please try this one..
create table client (val_cli integer,status varchar2(10));---------------------begininsert into clientselect 1, 'void' from dualunion allselect 4, 'void' from dualunion allselect 1, 'void' from dualunion allselect 6, 'void' from dualunion allselect 10, 'void' from dual;end;---------------------select * from client;---------------------declarecounter integer := 0;beginfor val in 1..10loopupdate client set status = 'updated' where val_cli = val;if sql%rowcount = 0 thendbms_output.put_line('no client with '||val||' val_cli.');elsedbms_output.put_line(sql%rowcount||' client updated for '||val);counter := counter + sql%rowcount;end if;end loop;dbms_output.put_line('Number of total lines affected update operation: '||counter);end;---------------------select * from client;--------------------------------------------------------Result will be like below:
2 client updated for 1
no client with 2 val_cli.
no client with 3 val_cli.
1 client updated for 4
no client with 5 val_cli.
1 client updated for 6
no client with 7 val_cli.
no client with 8 val_cli.
no client with 9 val_cli.
1 client updated for 10
Number of total lines affected update operation: 5
Use the Count(*) analytic function OVER PARTITION BY NULL This will count the total # of rows