Results 1 to 7 of 7

Thread: Query on cursor

  1. #1
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Arrow Unanswered: Query on cursor

    Hi all,

    I have written a procedure which defined like this:

    Code:
    TYPE t_refcursor IS REF CURSOR;
    
    get_id(p_name IN VARCHAR2, p_resultset OUT t_refcusor);
    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:

    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.
    Cheers,
    Shev

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

  3. #3
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Thanks Alan.
    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?
    Thanks.
    Cheers,
    Shev

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    If you create the get id query as a view on all ID's, your other procedures can select from that?

    Hth
    Bill

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

  6. #6
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    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.

    Thanks.
    Cheers,
    Shev

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •