Home » SQL & PL/SQL » SQL & PL/SQL » BULK DELETE
BULK DELETE [message #9934] Mon, 15 December 2003 05:57 Go to next message
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 Go to previous message
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
Previous Topic: SQL Loader
Next Topic: size of a table
Goto Forum:
  


Current Time: Wed Apr 24 18:14:57 CDT 2024