CODEWITHSUNDEEP
databaseoracle-databaseplsqlcursor
fuzzy
fuzzy

Reputation: 229

Append multiple records from each loop iteration in PL/SQL

I am trying to write a procedure in Oracle PL/SQL that looks like below snippet:

    > LOOP:   
         >READ INPUT FROM CURSOR
         >GET OUTPUTS USING SELECT QUERY(OUTPUT RECORD COUNT MAY VARY ON EACH ITERATION)
         >APPEND OUTPUT TO SOME DATA STRUCTURE 
    > END LOOP; 
    >RETURN DATA STRUCTURE

Actually I am stuck with this appending task. In each iteration different number of output will be available using a select query. I want to append all of the records in a single data structure.

Suppose inner select query return 3 columns ColA,ColB,ColC and the loop iterates 2 times. Then in 1st iteration it returns:

1,2,3
3,4,5

In the next iteration it returns 4,5,7.

The procedure will return some data structure containing below:

1,2,3
3,4,5
4,5,7

Is there any way?

Upvotes: 0

Views: 1562

Answers (1)

PKey
PKey

Reputation: 3841

I guess this can be solved with pipelined functions. So it will look something like this:

declare a type 

 TYPE my_record is RECORD(
       cola      number,
       colb      number,
       colc       number);

TYPE my_table IS TABLE OF my_record;

a function 

function  get_results()  RETURN my_table PIPELINED is 
    my_rec   my_record:=null;
    cursor myCursor(data number) is
    --some select expression
    ;

    begin 

    --loop through outputs 

     FOR item IN myCursor(data)  LOOP
      my_rec:=null; 
      select item.value1,item.value2,item.value2 into rec from dual;
      PIPE ROW(my_rec);
     end loop;
    return;

    end;

and finally use it 

SELECT * FROM TABLE(get_results);

Upvotes: 1

Related Questions