oracle-databasefor-loopstored-proceduresplsqldatabase-cursor

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


Solution

  • 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;
    /