oracle stored procedure - select, update and return a random set of rows

Question

oracle i wish to select few rows at random from a table, update a column in those rows and return them using stored procedure

PROCEDURE getrows(box IN VARCHAR2,   row_no IN NUMBER,   work_dtls_out OUT dtls_cursor) AS

  v_id VARCHAR2(20);
  v_workname VARCHAR2(20);
  v_status VARCHAR2(20);

  v_work_dtls_cursor dtls_cursor;

BEGIN

  OPEN v_work_dtls_cursor FOR
    SELECT id, workname, status
    FROM item 
    WHERE status IS NULL
    AND rownum <= row_no 
  FOR UPDATE;

  LOOP
    FETCH v_work_dtls_cursor
    INTO  v_id ,v_workname,v_status;

    UPDATE item
    SET status = 'started'
    WHERE id=v_id;

    EXIT
     WHEN v_work_dtls_cursor % NOTFOUND;
  END LOOP;

  close v_work_dtls_cursor ;

  /* I HAVE TO RETURN THE SAME ROWS WHICH I UPDATED NOW. 
     SINCE CURSOR IS LOOPED THRU, I CANT DO IT.  */

END getrows;

PLEASE HELP

Answer 1

Following up on Sjuul Janssen's excellent recommendation:

create type get_rows_row_type as object
  (id          [item.id%type],
   workname    [item.workname%type],
   status      [item.status%type]
  )
/

create type get_rows_tab_type as table of get_rows_row_type
/

create function get_rows (box in varchar2, row_no in number)
  return get_rows_tab_type pipelined
as
  v_work_dtls_cursor dtls_cursor; 
  l_out_rec get_rows_row_type;

BEGIN 

  OPEN v_work_dtls_cursor FOR 
    SELECT id, workname, status 
    FROM item  sample ([ROW SAMPLE PERCENTAGE])
    WHERE status IS NULL 
    AND rownum <= row_no  
  FOR UPDATE; 

  LOOP 
    FETCH v_work_dtls_cursor 
    INTO  l_out_rec.id, l_out_rec.workname, l_outrec.status;
    EXIT WHEN v_work_dtls_cursor%NOTFOUND;  

    UPDATE item 
       SET status = 'started' 
     WHERE id=l_out_rec.id; 
    l_out_rec.id.status := 'started';

    PIPE ROW (l_out_rec);
  END LOOP; 
  close v_work_dtls_cursor ; 
END;
/

A few notes:

1. This is untested.

2. You'll need to replace the bracketed section in the type declarations with appropriate types for your schema.

3. You'll need to come up with an appropriate value in the SAMPLE clause of the SELECT statement; it might be possible to pass that in as an argument, but that may require using dynamic SQL. However, if your requirement is to get random rows from the table -- which just filtering by ROWNUM will not accomplish -- you'll want to do something like this.

4. Because you're SELECTing FOR UPDATE, one session can block another. If you're in 11g, you may wish to examine the SKIP LOCKED clause of the SELECT statement, which will enable multiple concurrent sessions to run code like this.