trigger in oracle stored procedure

Question

Hi

I have a requirement like this: I have to select a certain number of records from an Oracle table using a stored procedure. I will capture the entire resultset in ref cursors, but at the same time I have to update a flag in the selected records ( the ones stored in ref cursors).

So I want to know whether this is possible in stored procedure. If yes, then what do I have to use e.g. triggers?

Thanks in advance

Answer 1

A cursor doesn't "capture" or "store" anything. To store something, use a PL/SQL table. Probably the closest thing to what you want to do:

DECLARE
  type t_foobar is table of foobar;
  v_foobar t_foobar;
BEGIN
  UPDATE foobar SET flag=1 WHERE foo=bar AND flag=0 AND rowum<=10;
  SELECT * FROM foobar BULK SELECT INTO v_foobar WHERE flag=1;
  UPDATE foobar SET flag=2 WHERE flag=1;
  COMMIT;
  /* process v_foobar here */
END;

I'm doing two UPDATEs here so that in a concurrent environment, every session selects und updates different rows. (E.g. for a airplane seat reservation system)

Answer 2

It sounds like you are trying to ensure multiple processes do not select the same rows?

SELECT FOR UPDATE NO WAIT might be your best option.
http://download.oracle.com/docs/cd/E11882%5F01/appdev.112/e10472/static.htm#CIHHIIID

While that's 11.2 it will work for earlier versions of Oracle, like 10g.

Answer 3

Ref cursors are non-modifiable, as they exist only in memory. They are a reference, as indicated by the name. You will have to perform updates separately.

Answer 4

Firstly you will not "capture the entire resultset in ref cursors", a ref cursor is a pointer to a result set. In effect, what you are returning is a query that will (or technically MAY) by executed by whatever receives it.

I'd forget the ref cursor and go with a pipelined table function. That way the procedure can process the records (ie flag them as updated) as it returns them to the caller.