Re: Does Oracle support multiple result sets

The short answer is to combine them all into one result. Assuming that the account number passed dentifies the customer, the customer identifies the order, and the order identifies the product use something like this:

  SELECT customer.name,

                 order.number,
                 product.description
      FROM customer, order, product
      WHERE customer.account = input_account
            AND order.account = customer.account
            AND product.product_id = order.product_id;

The other option, since you are in a procedure is to create three separate cursors and try to match things up yourself. However, is what you are after are all of the orders, and the products on those orders, for the customer account, the single result set above is MUCH easier to deal with.

• Ron Olcott, President
• Interlude Enterprises
• 808-638-7775

In article <6gii1p$22a$1_at_usenet57.supernews.com>,   "Jeff" <jsouthworth_at_dsitopdog.com> wrote:
>
> So what happens when you issue the 2 selects from within a stored proc? Say
> you want to pass an account number to a stored procedure that pulls info
> from a customer, orders and product table. How would you do this?
>
> Billy Verreynne wrote in message <6gi9se$om7$1_at_hermes.is.co.za>...
> >Jeff wrote in message <6ggef6$s0m$1_at_usenet54.supernews.com>...
> >>Was wondering if Oracle supports multiple result sets? Can I pass an
> >>account number to a stored procedure, then select * from a customer table,
> >>then select * from an orders table. What I'm trying to do is pass an
> >>account number and pull the info from the customer and orders table.
> >
> >
> >Not sure what you mean with "multiple result sets". The SQL language does
> >not support "multiple data sets" resulting from a single SQL statement.
> >
> >Multiple data sets (or tables) can be combined with a JOIN. In this case
> >every invoice row will also contain all the columns from the customer
> table.
> >
> >Another way to combine more than one data set into a single result set via
> >SQL is to UNION the SELECTs. This will however only work if the number of
> >columns and datatypes correspond between the SELECTs sets.
> >
> >What is wrong using two seperate SQL statements, one to supply info from
> the
> >customer table and one to supply the invoices? I fail to see any real
> >technical requirement for trying to do it as a single result set via a
> >stored procedure.
> >
> >regards,
> >Billy
> >
> >
> >
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Apr 09 1998 - 12:29:41 CDT