CODEWITHSUNDEEP
sqloracle-databaseplsql
Paul
Paul

Reputation: 3954

Adding to a table within a cursor

I'm new to PL/SQL and I'm working on writing a bulk insert. I have a nested cursor and inside the inner cursor I want to add records to a collection that I will then perform the bulk insert with. I create the collection as:

TYPE mydata_tab IS TABLE OF mydata%ROWTYPE INDEX BY BINARY_INTEGER;
v_mydata_tab mydata_tab;

And then my cursors look like:

FOR rec_one IN cursor_one LOOP

  strKey := rec_one.key;

  FOR rec_two IN cursor_two LOOP

    -- here is where I want to add a record to v_mydata_tab, that uses properties of both rec_one and rec_two

    -- something like SELECT rec_one.key, rec_one.a, rec_two.b INTO v_mydata_tab; 

  END LOOP;
END LOOP;

I've played around with SELECT INTO and INSERT INTO, but can't seem to figure out how to get this to work.

Upvotes: 2

Views: 97

Answers (2)

Dave Costa
Dave Costa

Reputation: 48131

You just use assignment. rec_one and rec_two are local variables of a record type. 

FOR rec_one IN cursor_one LOOP

  strKey := rec_one.key;

  FOR rec_two IN cursor_two LOOP

    i := v_mydata_tab.COUNT+1;  -- declare i above as local var

    v_mydata_tab( i ).key := rec_one.key;
    v_mydata_tab( i ).a := rec_one.a;
    v_mydata_tab( i ).b := rec_two.b;

  END LOOP;
END LOOP;

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132730

You can do it like this:

n := 0; -- n is integer variable declared above

FOR rec_one IN cursor_one LOOP

  strKey := rec_one.key;

  FOR rec_two IN cursor_two LOOP

    n := n + 1;
    v_mydata(n).key := rec_one.key;
    v_mydata(n).a := rec_two.a;
    v_mydata(n).b := rec_two.b;

  END LOOP;
END LOOP;

Of course, if you can combine the SQL of the 2 cursors into one select statement, you could do this instead:

select x.key, y.a, y.b
bulk collect into v_mydata
from ...;

Or if too much data:

open cur;
loop
   fetch cur bulk collect into v_mydata limit 1000;
   ...
   exit when cur%notfound;
end loop;
close cur;

Upvotes: 1

Related Questions