oracle 存储过程 - 选择、更新并返回一组随机行

Question

oracle 我希望从表中随机选择几行，更新这些行中的列并使用存储过程返回它们

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;

请帮助

Answer 1

一种可能的解决方案：

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;

如果行数特别多，全局临时方案可能会更好。

Answer 2

跟进 Sjuul Janssen 的出色建议：

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;
/

一些注释：

1. 未经测试。

2. 您需要将类型声明中的括号部分替换为适合您的架构的类型。

3. 您需要在 SELECT 语句的 SAMPLE 子句中给出适当的值；可能可以将其作为参数传递，但这可能需要使用动态 SQL。但是，如果您的要求是从表中获取随机行（仅按 ROWNUM 进行过滤无法实现），您将需要执行类似的操作。

4. 由于您正在选择更新，因此一个会话可能会阻止另一个会话。如果您使用的是 11g，您可能希望检查 SELECT 语句的 SKIP LOCKED 子句，这将使多个并发会话能够运行这样的代码。

Answer 3

不确定您在哪里进行提交，但根据代码，您需要做的就是 SELECT ... FROM ITEM WHERE STATUS='started'

如果数字很小，您可以保留 ROWID 的集合。
如果它更大，那么我会执行“

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);

然后返回一个游标”

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

Answer 4

也许这可以帮助你做你想做的事？

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