Complex Cursors for returning multiple rows

Each row in a cursor should be joined with another table and the whole result should开发者_如何学Python be returned a one cursor

Say cursor1 return 5 rows. Each of these 5 rows should be joined with table1. and the final result for all the 5 rows should be returned a 1 row.

Plz help

It is rather tricky to combine the fetched records from a ref cursor with the result set of another query. A much better idea would be to open just the one ref cursor with a SELECT which joins all the relevant tables.

The question isn't clear, but it sounds like what you need is something like this

a) Define an object type with the shape of your result row b) Define a collection of that object type c) Create a function with a return type of the collection type - this could take in cursor A as a parameter (SYS_REFCURSOR), join each row in cursor A to table B, and then use PIPE ROW for each result row. d) If the final result is needed as a cursor, then another function along the lines of

 FUNCTION complex_query(in_cursor SYS_REFCURSOR) 
 RETURN SYS_REFCURSOR
 IS
     lreturn SYS_REFCURSOR;
 BEGIN
     OPEN lreturn FOR
        (SELECT * FROM TABLE(convert_to_collection(in_cursor)));
     RETURN lreturn;
 END;

Alternatively, you could just do the SELECT * FROM TABLE(convert_to_collection(in_cursor)) directly.

What I don't understand is the requirement that everything is returned as 1 row.