CODEWITHSUNDEEP
oracle-databasefor-loopstored-proceduresplsqldatabase-cursor
MasterC
MasterC

Reputation: 193

For loop to select over a list

I'm trying to do the following using PL/SQL:

  1. Use a query to get a list of strings
  2. Use a for loop to query using the list as an input.

I have this so far:

DECLARE 
sub2 varchar2(12);

cursor sub is Select ID_SUBLIN from TABLE 1 group by ID_SUBLIN;

BEGIN
for sub2 in sub LOOP
for inner in (select * from TABLE2 where PARAMETER=sub2.ID_SUBLIN )
loop
DBMS_OUTPUT.PUT_LINE( sub2 );
end loop;
end loop;
END;

However it doesn't work. I'm only iterating over a 11 items list

Upvotes: 4

Views: 41694

Answers (2)

kanagaraj
kanagaraj

Reputation: 442

Alternate way with 2 queries combined in cursor and then looping.

DECLARE
    sub2   VARCHAR2(12);
cursor sub is 
SELECT id_sublin
FROM TABLE1
JOIN table2 
ON table2.parameter = TABLE1.id_sublin 
group    by    id_sublin;

BEGIN
    FOR sub2 IN sub 
    LOOP
        dbms_output.put_line(sub2.id_sublin);
        EXIT WHEN sub%notfound;
    END LOOP;
END;

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You have some structural problems and typoes. Try the following instead :

DECLARE 
-- sub2 varchar2(12);  
--> record_index "sub2" should exist in "for sub2 in sub", not as variable.
  cursor sub is Select ID_SUBLIN from TABLE1 group by ID_SUBLIN;
                                    --TABLE^1 [table name can't contain a space]   
BEGIN
for sub2 in sub 
loop
  for inner in (select * from TABLE2 where PARAMETER=sub2.ID_SUBLIN )
  loop
   dbms_output.put_line( sub2.ID_SUBLIN ); -- must be as <record_index>.<aColumn>
   dbms_output.put_line( inner.parameter );
-- by the way, both of the record_indexes with columns of cursors may be used here.
  end loop;
end loop;
END;
/

Upvotes: 5

Related Questions