CODEWITHSUNDEEP
sqlplsqloracle11g
Pankaj Kumar
Pankaj Kumar

Reputation: 685

how to store resultset in type and fetch in Oracle

I am new for Oracle.I just want to capture the result of the selection in variable and then I want to use that variable for multiple operation on that data like we do in the Sql server using table variable.

I tried as below:

BEGIN
   DECLARE
      TYPE FullRecord IS RECORD (RNumber NUMBER);
      rec    FullRecord;
   BEGIN
      SELECT RNumber 
       INTO rec 
      FROM tableData;
   END;

END

;

Upvotes: 1

Views: 66

Answers (1)

XING
XING

Reputation: 9886

You need BULK COLLECT. See below:

DECLARE
      TYPE FullRecord1 IS RECORD (RNumber NUMBER);          
      TYPE FullRecord is table of FullRecord1 index by pls_integer;          
      rec    FullRecord;
BEGIN
      SELECT RNumber 
       BULK COLLECT INTO rec 
      FROM tableData;

   for i in 1..rec.count
   loop

    dbms_output.put_line(rec(i).RNumber);
   end loop;

END;

Read a much better explaination Here

Upvotes: 2

Related Questions