Home » SQL & PL/SQL » SQL & PL/SQL » Returning multiple records in a sp
Returning multiple records in a sp [message #2868] Mon, 19 August 2002 04:52 Go to next message
Mark Grimshaw
Messages: 73
Registered: June 2002
Member
Hello,

I wanted to know what the best way is of returning multiple records from a table into a TABLE type whose element type was that of a user defined RECORD. I knwo I can't use the BULK COLLECT statement but am I just restricted to a LOOP with a FETCH on a Cursor variable ?

Thanks
Re: Returning multiple records in a sp [message #2880 is a reply to message #2868] Mon, 19 August 2002 09:48 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
A CURSOR FOR loop will be the best (less code, highest performing) way to handle this:

for r in (select ...) loop
  i := i + 1;
  v_tab(i).slot_1 := r.col1;
  v_tab(i).slot_2 := r.col2;
end loop;


You can also predefine a cursor if you want to and:

for r in my_cursor loop
Previous Topic: SQLCODE, SQLERM
Next Topic: Conditional return values
Goto Forum:
  


Current Time: Thu Mar 28 21:06:07 CDT 2024