Re: Executing Stored Procedures
Marisa Moretto wrote:
>
> ----------
> De: Bill Ennis[SMTP:ennis@ssax.com]
> Enviado: Viernes, Mayo 23, 1997 12:43
> Para: Mike Gallagher
> Cc: informix-list@rmy.emory.edu
> Asunto: Re: Executing Stored Procedures
>
> }
> } I am currently using Sybase/db-lib and I routinely execute
> } stored procs that return multiple (different) result sets. I know
> } Informix is limited to one result set per stored procedure. Does anyone
> } know if this is also true in Oracle. An example of a stored procedure is
> } as follows:
> }
> } create procedure test1
> } @arg1 int,
> } @arg2 float
> } as
> } select
> } col1,
> } col2
> } from
> } table1
> } where
> } col3 = @arg1
> }
> } select
> } col4,
> } col5
> } from
> } table2
> } where
> } col6 = @arg2
> } go
> }
> } NOTE: the datatypes of the results do not need to be defined and can be
> } different within the 2 result sets.
> }
> } Michael Gallagher
> } Open Link Financial
> } Email: mgallagh@olf.com
> } Phone: (516) 227-6600 x212
> }
>
> Mr.Bill:
>
> The next procedure is one example that return more than one row:
>
> create procedure xx() returning integer;>
> define ver_ret integer;
>
> foreach
> select order_num
> into var_ret
> from orders
> return var_set with resume;> end foreach;
> return -- this sentence return status = 100 (not found) at the aplication
> end procedure;
>
> And the aplication, must be have:
>
> prepare prep_stmt from "execute procedure xx()"
> declare traer cursor for prep_stmt
> foreach traer into var_order_num
> ....
> ....
> end foreach
>
> I hope help you.
>
> Saludos Gabriel.
>
>
My question was not about multiple rows, it was about different types of
rows.
For example, (In the same stored procedure)
select
price
from
x
select
name, address
from
y
--
Michael Gallagher
Open Link Financial
Email: mgallagh@olf.com
Phone: (516) 227-6600 x212