oracle：对于更新选择前 10 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6337126/
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: For update select first 10 rows
提问by Vinoth
I have an ITEMtable with one of the column as CREATED_DATE. In a clustered enviroment, many copies of services will pick items from this table and process it. Each service should pick the oldest 10 items from the ITEM table.
我有一张ITEM表，其中一列为CREATED_DATE. 在集群环境中，许多服务副本将从该表中挑选项目并对其进行处理。每个服务都应该从 ITEM 表中选择最旧的 10 个项目。
I am able to select top 10 rows using this in a Stored Procedure:
我可以在存储过程中使用它选择前 10 行：
select * from (
select item_id, row_number() over (order by CREATED_DATE) rownumber
FROM item )
where rownumber < 11
Since many service should use this, I am using select ... for updateto update the rows as "processing". But the below FOR UPDATEstatement, fails for the above select statement with error "ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."
由于许多服务应该使用它，我使用select ... for update将行更新为“处理”。但是下面的FOR UPDATE语句对于上面的选择语句失败，错误为“ORA-02014：无法从带有 DISTINCT、GROUP BY 等的视图中选择 FOR UPDATE”。
OPEN items_cursor FOR
**select Statement**
FOR UPDATE;
Please help me with a solution.
请帮我解决。
采纳答案by marcink
DCookie's answer doesn't solve multisession processing (it's just FOR UPDATE syntax fix). If you won't manipulate rownumber range, every instance of service if going to select for update the same rows. If you execute that_for_update_select in two sessions, the second one is going to wait until first finishes the transaction. Parallel processing will be an illusion.
DCookie 的答案不能解决多会话处理（它只是 FOR UPDATE 语法修复）。如果您不操作 rownumber 范围，则每个服务实例都将选择更新相同的行。如果您在两个会话中执行 that_for_update_select，则第二个将等到第一个完成事务。并行处理将是一种错觉。
I would consider efficient bulk processing together with for update skip lockedapproach. My answer below:
我会考虑有效的批量处理和for update skip locked方法。我的回答如下：
declare
con_limit constant number default 10;
cursor cItems is
select i.item_id, i.created_date
from item i
order by i.created_date
for update skip locked;
type t_cItems is table of cItems%rowtype;
tItems t_cItems;
begin
open cItems;
while true loop
fetch cItems bulk collect into tItems limit con_limit;
-- processing tItems
exit when tItems.count < con_limit;
end loop;
end;
Possible long transaction could be a disadvantage. Consider using Oracle Streams Advanced Queuing (DBMS_AQ) as an alternative to this solution.
可能的长事务可能是一个缺点。考虑使用 Oracle Streams Advanced Queuing (DBMS_AQ) 作为此解决方案的替代方案。
回答by DCookie
Does this work for your situation?
这对你的情况有用吗？
SELECT *
FROM item
WHERE (item_id,created_date) IN
(SELECT item_id,created_date
FROM (SELECT item_id, created_date
, ROW_NUMBER() OVER (ORDER BY created_date) rownumber
FROM item)
WHERE rownumber < 11)
回答by Alex Poole
You can use skip lockedand a counter to achieve this, as long as you don't necessarily need each session to get contiguous rows. For example:
您可以使用skip locked和计数器来实现这一点，只要您不一定需要每个会话来获取连续的行。例如：
declare
l_cursor sys_refcursor;
l_name all_objects.object_name%type;
l_found pls_integer := 0;
begin
open l_cursor for
select object_name
from all_objects
order by created
for update skip locked;
loop
fetch l_cursor into l_name;
dbms_output.put_line(l_fetches || ':' || l_name);
if l_cursor%found then
l_found := l_found + 1;
-- dbms_lock.sleep(1);
end if;
exit when l_cursor%notfound or l_found = 10;
end loop;
end;
/
If you run this simultaneously from two sessions they'll get different objects (though you may need to enable the call to dbms_lock.sleepinside the foundblock to make it slow enough to be visible).
如果您从两个会话中同时运行它，它们将获得不同的对象（尽管您可能需要启用dbms_lock.sleep对found块内部的调用以使其足够慢以使其可见）。
According to this post, when using skip lockedthe selected rows aren't locked until they're fetched, and any rows locked by another session after the cursor is opened are just ignored.
根据这篇文章，当使用skip locked选定的行时，直到它们被提取时才会被锁定，并且在打开游标后被另一个会话锁定的任何行都将被忽略。
