Oracle Pl/sql return one cursor from multiple queries

I have a stored procedure, which returns a cursor.

The application passes a parameter to the procedure which determines how many ID`s should be fetched, so the procedure doesnt know a head of time that number.

foreach ID i need to fetch the top 3 records with that ID. what I have tried is using:

select * from table_name where id in (List of ID`s);

That query works, but i cant get the top 3 of each ID. If i limit the result count, i will get the TOP results of the first ID.

I thought using For Loop, executing the query for each 开发者_如何学运维ID and append the results to the cursor, but as I understand it`s impossible.

Any Ideas ?

More details Lets say I have 5 IDs and each of them have inner Ids so Id 1 has (1,2,3,4,5) Id 2 (1,2,3,4,5) Id 3 (12,14,15,16,22) Id 4 (2,3,5,7,9) Id 5 (4,7,8,9,10) In this case, which is the case I am dealing with, I dont see how row number will help me. I need the top 3 for each ID, in this case the cursor should have 15 results.

10x alot and have a good weekend ;)

Presumably you have some criterion for determining the top three?

Anyway, the way to achieve this end is with an analytic function. Oracle offers three distinct functions: ROW_NUMBER(), RANK() and DENSE_RANK(). These offer three slightly different interpretations of TOP 3. Find out more.

Here is the basic idea, using ROW_NUMBER(), which will return exactly three rows for each ID.

open rc for 
    select * from (
        select t.*
               , row_number() over (partition by id order by whatever ) rn 
        from table_name t 
        where t.id in (List of ID`s)
     )
     where rn <= 3;

The whatever in the ROW_NUMBER() clause is the column you use to determine TOP-ness.

Another idea to go with would be to define an Oracle temporary table

create global temporary table temp_table_name

Link to more information

Then in a for loop you can insert rows into the temp table for all of the needed ids. The cursor returned would then be the content of the temp table. Of course this solution only makes sense when it is not possible to get the result back from a single sql query