BULK DELETE [message #9934] |
Mon, 15 December 2003 05:57 |
Sunil
Messages: 132 Registered: September 1999
|
Senior Member |
|
|
Can anyone pls let me know, how can I delete from a table using rowid.
I need to do a BULK COLLECT and DELETE the records fetched inside a cursor.
Thanks in advance
Sunil
|
|
|
Re: BULK DELETE [message #9952 is a reply to message #9934] |
Tue, 16 December 2003 20:57 |
|
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Allow me to quote from Tom Kyte:
----------------------------------------------------------------------
You would always want to use the "regular" delete.
Less code, runs in less (generally) time.
the rule to decide when to use a bulk delete: "use it when you cannot do the
delete in a single SQL statement".
----------------------------------------------------------------------
But, to answer your question, here are some possibilities:Declare
cursor c
is
select rowid
from your_table
where ...
for update;
Begin
For rec in c
Loop
Delete from your_table where current of c;
End Loop;
Commit;
End;
/
Declare
cursor c
is
select rowid
from your_table
where ...
for update;
Begin
For rec in c
Loop
Delete from your_table where rowid = rec.rowid;
End Loop;
Commit;
End;
/ With a bulk collect:Declare
cursor cur_del
is
select rowid
from your_table
where ...
for update;
type tab_type is table of ROWID index by binary_integer;
temp_tab tab_type;
Begin
open cur_del;
fetch cur_del bulk collect into temp_tab;
close cur_del;
forall i in temp_tab.first..temp_tab.last
delete from your_table where rowid = temp_tab(i);
Commit;
end;
/ MHE
|
|
|