"return with resume" in Oracle8?

From: <adil_at_msil.sps.mot.com>
Date: Tue, 14 Sep 1999 08:43:31 GMT
Message-ID: <7rl1rh$5pm$1@nnrp1.deja.com>

Hi there,

We're migrating our database from an old Informix database to an Oracle 8 one. However, I could not find out how Oracle stored procedures implement what Informix calls "return with resume". The return with resume feature allows a procedure to return a set of values of the same type, similar to several rows returned by a select statement.

For example, if I have a table called user_groups, which holds two columns, user id and group id, and I wanted to write a procedure which given a group id returns a list of user ids associated with it, in Informix, I would write something like,

Procedure getUsers(proc_group_id int)
begin
  define proc_user_id int;
.
.

  foreach select user_id into proc_user_id

          from user_groups
          where group_id=proc_group_id
          return proc_user_id with resume;

  end foreach;
end procedure;

Or something like that. Obviously, this is just an example to demonstrate what I mean; I don't need a procedure for the above case (I could just use a select). How is this sort of thing done in Oracle stored procedures (Oracle 8)?

Thanks,

Adi.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Sep 14 1999 - 03:43:31 CDT