Home » SQL & PL/SQL » SQL & PL/SQL » Using an array as a select condition (10)
Using an array as a select condition [message #325286] Thu, 05 June 2008 09:41 Go to next message
Messages: 3
Registered: March 2008
Junior Member
Does anyone know how if it's possible to use an array without having to loop through it one by one?

Lets' say that I want to delete a set of comments. I get a set of comment id's as an array parameter comment_ids.

Right now, I am doing

FOR i in 1..comment_ids.last LOOP
    delete from comments where comment_id = comment_ids(i)

Obviously, it forces context switching and I would like to avoid that.
What I would like to do instead is something like

delete from comments where comment_id in (select * from comment_ids)

Is that possible? I've googled and googled and can't seem to find a solution.

Thanks for any help.
Re: Using an array as a select condition [message #325296 is a reply to message #325286] Thu, 05 June 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is a permanent array type (not a PL/SQL one), you can use:
delete from comments where comment_id in (select * from table(comment_ids))

Re: Using an array as a select condition [message #325467 is a reply to message #325286] Fri, 06 June 2008 04:58 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If it's a Pl/Sql array, you can use FORALL:
SQL> create table del_test (col_1 number);

Table created.

SQL> insert into del_test (select level from dual connect by level <= 100);

100 rows created.

SQL> declare
  2    type ty_del_Tab is table of number index by binary_integer;
  3    del_tab  ty_del_tab;
  4  begin
  5  -- populate table holding records to delete
  6    for i in 1..60 loop
  7      del_tab(i) := i;
  8    end loop;
 10  -- delete records
 12    forall i in 1..del_tab.last
 13      delete del_test where col_1 = del_Tab(i);
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL> select count(*) from del_test;


Previous Topic: Test-data insert execution time
Next Topic: Is it possible?
Goto Forum:

Current Time: Thu Aug 17 14:30:19 CDT 2017

Total time taken to generate the page: 0.16474 seconds