Number of rows affected by an UPDATE in PL/SQL

Question

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.

Answer 1

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:

declare
  i number;
begin
  update employees set status = 'fired' where name like '%Bloggs';
  i := sql%rowcount;
end;