Using an array as a select condition [message #325286] |
Thu, 05 June 2008 09:41  |
radalapsker@yahoo.com
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)
END LOOP;
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 #325467 is a reply to message #325286] |
Fri, 06 June 2008 04:58  |
JRowbottom
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>
SQL> insert into del_test (select level from dual connect by level <= 100);
100 rows created.
SQL>
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;
9
10 -- delete records
11
12 forall i in 1..del_tab.last
13 delete del_test where col_1 = del_Tab(i);
14
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from del_test;
COUNT(*)
----------
40
|
|
|