RE: Executing Stored Procedures
----------
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.