Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Red face Unanswered: Oracle-Store Procedure advice

    Hi,
    I'm trying to create an oracle store procedure that has 2 cursors. 1st one will get certain column from table A and the 2nd cursor will get another certain column of data based on the cursor1 output. eg.:

    CURSOR cursor1 IS
    SELECT DISTINCT a.id AS CUST_ID, a.passport
    FROM tableA a;

    then from cursor1, I want to use it as in the where clause to get the data from tableB.

    CURSOR cursor2 IS
    SELECT b.trans_id, b,b.trans_item
    FROM trans b WHERE
    b.id = cursor1.CUSTOMER_ID

    At last, I'll return those 2 cursor output to the caller program. Can anyone show me the syntax to passed in the cursor1 info into the second SQL statement. The one shown here is just an example. All I want is to use the first cursor1 info from 1 SQL to another and not joining this 2 table since I want to separate out this 2 query.

  2. #2
    Join Date
    Oct 2004
    Posts
    145

    Oracle-Store Procedure advice

    1. Retrieve the value from cursor 1 into a variable (within your pl/sql block): using fetch command.
    2. Put the variable retrieved from cursor 1 into where clause in cursor 2.

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    To expand on Jim's idea. What you do is create cursor2 as follows
    CURSOR cursor2(cust_no) IS
    SELECT b.trans_id, b,b.trans_item
    FROM trans b WHERE
    b.id = cust_no.
    I.E a parametric cursor.
    Now in your PL/SQL program body. Fetch the cust_id value into a variable (lets say var_cust)from your cursor1. And then open cusror 2 by OPEN cursor2(var_cust). This should solve your problem.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    why not just make one cursor??
    PHP Code:
    CURSOR c1 IS
       SELECT b
    .trans_idb.trans_itemb.id
       FROM trans b
       WHERE EXISTS 
          
    (SELECT null FROM tableA a where b.id a.id); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Aug 2002
    Posts
    21
    The Duck,
    I was just giving an example. What I intend to do is let say:
    [1] select from 1 table into cursor1 > e.g. 10 records
    [2] declared 2nd cursor to select from 2nd table (e.g. tableB) with where clause is based on the cursor1's output. And then maybe other manipulation methods like SUM or blah blah...

  6. #6
    Join Date
    Aug 2002
    Posts
    21
    da_coolestofall ,
    What if I selected more than 1 column, let say I (select * from tableA) and the statement you provide to me <CURSOR cursor2(cust_no) IS > have to be modified, right? Can you show me?

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    [2] declared 2nd cursor to select from 2nd table (e.g. tableB) with where clause is based on the cursor1's output. And then maybe other manipulation methods like SUM or blah blah...
    So just join the two tables in SQL. Alternatively you can define cursor and collection expressions within SQL.

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    why do feel the need to return your data as cursors? do mean ref cursors? why not just return the data as arrays? And, if the first cursor returns 10 rows, would the 2nd cursor return 10 sets of data - assuming that each query on it would return 3 rows, then cursor 2 would return 30 rows - so how do plan to connect the data between the two cursors?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    declare

    vID varchar2(100);

    cursor c1 is
      select id from tableA
    ;

    begin

      
    for v_id in c1 loop
        
        
    for vData in (select abc from tableB where id v_id.idloop

          
    /* do your manipulation */

        
    end loop;
      
    end loop;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    declare

      
    cursor c1 is
        select id from tableA
    ;

    begin

      
    for v_id in c1 loop
        
        
    for vData in (select abc from tableB where id v_id.idloop

          
    /* do your manipulation */

        
    end loop;
      
    end loop;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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