Re: Cursors within Cursors - is it possible?

By combining the two sql statements!
Just join those two tables and you are there. How to join tables is described in any sql course. You can't have 'cursors within cursors'
Do you have access to the Oracle manuals? If you don't they are available at http://technet.oracle.com

Hth,

Sybrand Bakker, Oracle DBA

"Patrick Casey" <patrick_casey_at_unc.edu> wrote in message news:95bv3j$2jq$1_at_news2.isis.unc.edu...
> Here is my code as it currently stands:
>
> PROCEDURE multipleCursors
>
> (outCursor OUT pjcCursor)
>
> IS
>
> CURSOR itemCursor (rec_bndl_id NUMBER) IS
>
> SELECT
>
> cat_itm_id
>
> FROM ITP_CATALOG_BUNDLE_ITEM
>
> WHERE bndl_id = rec_bndl_id;
>
> CURSOR orderCursor IS
>
> SELECT
>
> b.bndl_id,
>
> b.bndl_nm
>
> FROM ITP_CATALOG_BUNDLE b
>
> ORDER BY b.bndl_id;
>
> BEGIN
>
> DBMS_OUTPUT.PUT_LINE ('see me? ');
>
> FOR orderRecord in orderCursor
>
> LOOP
>
> DBMS_OUTPUT.PUT_LINE ('bundle ' || orderRecord.bndl_id);
>
> FOR itemRecord in itemCursor (orderRecord.bndl_id)
>
> LOOP
>
> DBMS_OUTPUT.PUT_LINE ('item ' || itemRecord.cat_itm_id);
>
> END LOOP;
>
> END LOOP;
>
> END;
>
>
> It prints out a list of bundles (orders) and the items within. How can I
> combine these to come out of the procedure as 1 REF CURSOR?
>
> Thanks!
>
>
> "Patrick Casey" <patrick_casey_at_unc.edu> wrote in message
> news:95bp5q$ci$1_at_news2.isis.unc.edu...
> > I have two tables (Order and OrderItems). I would like to create a
 procedure
> > that returns back all Orders and for each Order, all of its items.
> >
> > Is it possible to create a procedure that returns a REF CURSOR that has
 a
> > cursor within itself?
> >
> > So, it would be something like:
> >
> > ...
> > TYPE testCursor is REF CURSOR;
> > ...
> > BEGIN
> > OPEN testCursor FOR
> > SELECT order_id, order_date
> > FROM ORDER;
> > END;
> > ...
> >
> > How can I also get a cursor or items within that order for each order? I
> > have tried many different guesses, but none will compile.
> >
> > Any help is greatly appreciated.
> >
> > --
> >
> > Thanks.
> >
> > Patrick Casey
> >
> >
> >
>
>
Received on Thu Feb 01 2001 - 18:45:54 CET