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

Not sure where you are doing your committing, but based on the code as it stands all you should need to do is SELECT ... FROM ITEM WHERE STATUS='started'

If it is small numbers, you could keep a collection of ROWIDs. if it is larger, then I'd do an

INSERT into a global temporary table SELECT id FROM item .. AND ROWNUM < n;
UPDATE item SET status = .. WHERE id in (SELECT id FROM global_temp_table);

Then return a cursor of

SELECT ... FROM item  WHERE id in (SELECT id FROM global_temp_table);

Answer 2

Maybe this can help you to do what you want?

http://it.toolbox.com/blogs/database-solutions/returning-rows-through-a-table-function-in-oracle-7802

Answer 3

A possible solution:

create type nt_number as table of number;

PROCEDURE getrows(box IN VARCHAR2,   
                  row_no IN NUMBER,   
                  work_dtls_out OUT dtls_cursor) AS    
  v_item_rows nt_number;
  indx number;    
  cursor cur_work_dtls_cursor is
     SELECT id
     FROM item 
     WHERE status IS NULL
     AND rownum <= row_no 
     FOR UPDATE;    
BEGIN    
  open cur_work_dtls_cursor;
  fetch cur_work_dtls_cursor bulk collect into nt_number;

  for indx in 1 .. item_rows.count loop
    UPDATE item
    SET status = 'started'
    WHERE id=v_item_rows(indx);
  END LOOP;
  close cur_work_dtls_cursor;

  open work_dtls_out for select id, workname, status 
       from item i, table(v_item_rows) t
       where i.id = t.column_value;
END getrows;

If the number of rows is particularly large, the global temporary solution may be better.

Answer 4

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.