count the row before a delete

francy77

Hi all,

In a store procedure, I need to delete some row, and I want to know how many rows I'm going to delete before to really delete them,

for example I can write

select count(k_id) into count from tab1 where id_s=12;

and after

delete from tab1 where id_s=12;

But I'm asking if there is a different way to accomplish this.

thanks

Alex Nuijten

Why not do it the other way around?

delete from tab1 where id_s = 12;

and using sql%rowcount immediately after the DELETE, you know how many were removed.

BEGIN
   delete from tab1 where id_s = 12;
   dbms_output.put_line (to_char (sql%rowcount)||' removed');
end;

Alex Nuijten

Why not do it the other way around?

delete from tab1 where id_s = 12;

and using sql%rowcount immediately after the DELETE, you know how many were removed.

BEGIN
   delete from tab1 where id_s = 12;
   dbms_output.put_line (to_char (sql%rowcount)||' removed');
end;

Frank Kulash

Hi,

Why do you want to count the rows?

Are you going to do something special before deleting them, or maybe not delete them at all, depending on how many rows are involved? If so, what you posted is the best way.

Do you just need to know how many rows were deleted? In PL/SQL, you can do that after the DELETE, like this:

DELETE ...;
rows_deleted := SQL%ROWCOUNT;

Make sure you save the value immediately after the DELETE. Other statements may automatically change SQL%ROWCOUNT (just like DELETE did).