Looping through multiple results in PLSQL

Question

I have a select query which returns 50 results. The select query returns id .

The next part i need to run a select query which value for all the 50 ids.

Please can some one tell me what is the best way to get it done. is using a for loop a good idea e.g.

begin
declare  @count= select id from table1 
 for 1 to @count ..loop
   select value from table2 where id =1
end loop

Can i use cursors what is the best method.

In general the best option is to use plain SQL. Your problem description sounds like SQL solution is possible but as you provide no details about your issue it's hard to give a precise answer. — user272735, Sep 04 '13 at 18:21

Baljeet · Accepted Answer · 2013-09-04T16:27:24.783

4

Please elaborate on this statement "The next part i need to run a select query which value for all the 50 ids." However, if you want to fetch those 50 records in a pl/sql block and manipulate them in some way, you would require a cursor. Records from cursor can be fetched both with and without FOR loop.

Cursor example:

declare
 cursor c1 is select col1 from table1;
begin
 for a in c1
  loop
   dbms_output.put_line(a.col1);
  end loop;
end;

The above piece of code would display the values from col1 when server output is on.

edited Sep 04 '13 at 16:27

answered Sep 04 '13 at 14:04

Baljeet

428
2
9

I have not worked with cursors can you give an example to accomplish the task using a cursor – Jayant Rao Sep 04 '13 at 14:47
I have edited my answer, please check. – Baljeet Sep 04 '13 at 16:49

score 3 · Answer 2 · answered Sep 05 '13 at 01:47

In a simple case such as you suggest I think it's a bad idea to build up a collection of values and then iterate through them to fetch data from a second table. It's a better idea to use a JOIN to, well, join together the data from the two tables based on whatever criteria you've got. In this case it appears you're select an ID value from TABLE1 which is also on TABLE2. Thus, you might consider something like the following:

FOR myRow IN (SELECT t1.ID, t2.VALUE
                FROM TABLE1 t1
                INNER JOIN TABLE2 t2
                  ON (t2.ID = t1.ID))
LOOP
  -- Do something useful with the data

  DBMS_OUTPUT.PUT_LINE('t1.ID=' || myRow.ID || '  t2.VALUE=' || t2.VALUE);
END LOOP;

Share and enjoy.

score 0 · Answer 3 · answered Sep 04 '13 at 17:02

0

Given below can be onw good waye

declare
cursor c1 is select value from table2 where id in (select id from table1)
begin
for i in c1
loop
...
...
end loop;
end;

answered Sep 04 '13 at 17:02

Harshit

560
1
5
15

Looping through multiple results in PLSQL

3 Answers3