Nested cursor in a cursor

Question

I have a cursor which is

CURSOR B_CUR IS select DISTINCT big_id from TEMP_TABLE;

This would return multiple values. Earlier it was being used as

FOR b_id IN B_CUR LOOP
    select s.col1, s.col2 INTO var1, var2 from sometable s where s.col3 = b_id.col1;
END LOOP;

Earlier it was certain that the inner select query would always return 1 row. Now this query can return multiple rows. How can I change this logic?

I was thinking to create a nested cursor which will fetch into an array of record type (which i will declare) but I have no idea how nested cursor would work here.

My main concern is efficiency. Since it would be working on millions of records per execution. Could you guys suggest what would be the best approach here?

Answer 1

Normally, you would just join the two tables.

FOR some_cursor IN (SELECT s.col1,
                           s.col2
                      FROM sometable s
                           JOIN temp_table t ON (s.col3 = t.col1))
LOOP
  <<do something>>
END LOOP

Since you are concerned about efficiency, however

• Is TEMP_TABLE really a temporary table? If so, why? It is exceedingly rare that Oracle actually needs to use temporary tables so that leads me to suspect that you're probably doing something inefficient to populate the temporary table in the first place.
• Why do you have a cursor FOR loop to process the data from TEMP_TABLE? Row-by-row processing is the slowest way to do anything in PL/SQL so it would generally be avoided if you're concerned about efficiency. From a performance standpoint, you want to maximize SQL so that rather than doing a loop that did a series of single-row INSERT or UPDATE operations, you'd do a single INSERT or UPDATE that modified an entire set of rows. If you really need to process data in chunks, that's where PL/SQL collections and bulk processing would come in to play but that will not be as efficient as straight SQL.
• Why do you have the DISTINCT in your query against TEMP_TABLE? Do you really expect that there will be duplicate big_id values that are not erroneous? Most of the time, people use DISTINCT incorrectly either to cover up problems where data has been joined incorrectly or where you're forcing Oracle to do an expensive sort just in case incorrect data gets created in the future when a constraint would be the more appropriate way to protect yourself.

Answer 2

FOR b_id IN B_CUR LOOP 
  for c_id in  (select s.col1, s.col2 INTO var1, var2 from sometable s where s.col3 = b_id.col1)loop
    ......
  end loop;
END LOOP;