var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Query on cursor
I have written a procedure which defined like this:
It will return a ref cursor storing a set of IDs. Now I would like to make use of this cursor to join against another table to get appropriate rows. Like below:
TYPE t_refcursor IS REF CURSOR;
get_id(p_name IN VARCHAR2, p_resultset OUT t_refcusor);
select id, salary from employee_salay where id in (<p_resultset>);
How to do this task? I have no idea how to use the result from a cursor in another query.
Thanks in advance.
Better to write a new proc called get_idsalary which does a single query combining the query in your get_id and your select ... from employee_salary.
The other alternative is to iterate through your list of ids and plug them into your select one id at a time but it will be much less efficient.
Actually, I have many procedures that are going to use the result from get_id, and the logic in get_id is quite complicated. Seems not a good idea to combine the query inside get_id with other queries in each of the procedures.
Looping through the result from get_id to do select at each time will be very inefficient for my case too.
Any other idea?
If you create the get id query as a view on all ID's, your other procedures can select from that?
If Bill's suggestion of using view doesnt work either and you have 9i take a look at table functions in the PLSQL manual as then you will be able to join to the resultset from your cursor.
Thanks again for the reply.
Bill, more information on my question, I need to dynamically construct the select statement based on the input parameter from the stored procedure. So it may not be able to make up a pre-defined view.
Alan, will table function be able to cast a cursor to a table/view? I will take a look in the Oracle manual.
Take a look at
http://download-west.oracle.com/docs...subs.htm#19677 under table functions/ pipelined table functions. Using these your ref cursor can appear as a table which you can then use to join to.