oracle 存储过程——选择、更新和返回一组随机的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3262164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle stored procedure - select, update and return a random set of rows
提问by user503647
oracle i wish to select few rows at random from a table, update a column in those rows and return them using stored procedure
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;
PLEASE HELP
请帮忙
回答by Adam Musch
Following up on Sjuul Janssen's excellent recommendation:
跟进 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;
/
A few notes:
一些注意事项：
This is untested.
You'll need to replace the bracketed section in the type declarations with appropriate types for your schema.
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.
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.
这是未经测试的。
您需要将类型声明中的括号部分替换为适合您的架构的类型。
您需要在 SELECT 语句的 SAMPLE 子句中提出适当的值；可以将其作为参数传递，但这可能需要使用动态 SQL。但是，如果您的要求是从表中获取随机行——仅通过 ROWNUM 过滤无法实现——您将需要执行此类操作。
因为您正在选择 FOR UPDATE，一个会话可能会阻止另一个会话。如果您使用的是 11g，您可能希望检查 SELECT 语句的 SKIP LOCKED 子句，这将使多个并发会话能够运行这样的代码。
回答by Gary Myers
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'
不确定你在哪里提交，但根据代码，你需要做的就是 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
如果是小数字，您可以保留一组 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);
Then return a cursor of
然后返回一个游标
SELECT ... FROM item WHERE id in (SELECT id FROM global_temp_table);
回答by Sjuul Janssen
Maybe this can help you to do what you want?
也许这可以帮助你做你想做的事？
回答by Allan
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.
如果行数特别多，全局临时解决方案可能会更好。
