为什么我不能使用SELECT ... FOR UPDATE with aggregate functions？

I have an application where I find a sum() of a database column for a set of records and later use that sum in a separate query, similar to the following (made up tables, but the idea is the same):

我有一个应用程序，我在其中找到一组记录的数据库列的sum（），然后在单独的查询中使用该总和，类似于以下（组成表，但想法是相同的）：

SELECT Sum(cost)
INTO v_cost_total
FROM materials
WHERE material_id >=0
AND material_id <= 10; 

[a little bit of interim work]

SELECT material_id, cost/v_cost_total
INTO v_material_id_collection, v_pct_collection
FROM materials
WHERE material_id >=0
AND material_id <= 10
FOR UPDATE;

However, in theory someone could update the cost column on the materials table between the two queries, in which case the calculated percents will be off.

但是，理论上有人可以在两个查询之间更新材料表上的成本列，在这种情况下，计算的百分比将关闭。

Ideally, I would just use a FOR UPDATE clause on the first query, but when I try that, I get an error:

理想情况下，我只会在第一个查询中使用FOR UPDATE子句，但是当我尝试这个时，我收到一个错误：

ORA-01786: FOR UPDATE of this query expression is not allowed

Now, the work-around isn't the problem - just do an extra query to lock the rows before finding the Sum(), but that query would serve no other purpose than locking the tables. While this particular example is not time consuming, the extra query could cause a performance hit in certain situations, and it's not as clean, so I'd like to avoid having to do that.

现在，解决方法不是问题 - 在查找Sum（）之前只需执行额外的查询来锁定行，但该查询除了锁定表之外没有任何其他用途。虽然这个特定的例子并不耗时，但额外的查询可能会在某些情况下导致性能下降，而且它不是那么干净，所以我想避免这样做。

Does anyone know of a particular reason why this is not allowed? In my head, the FOR UPDATE clause should just lock the rows that match the WHERE clause - I don't see why it matters what we are doing with those rows.

有谁知道为什么不允许这样做的特殊原因？在我的脑海中，FOR UPDATE子句应该只锁定与WHERE子句匹配的行 - 我不明白为什么我们正在对这些行做什么很重要。

EDIT: It looks like SELECT ... FOR UPDATE can be used with analytic functions, as suggested by David Aldridge below. Here's the test script I used to prove this works.

编辑：看起来像SELECT ... FOR UPDATE可以与分析函数一起使用，如David Aldridge所示。这是我用来证明这个工作的测试脚本。

SET serveroutput ON;

CREATE TABLE materials (
    material_id NUMBER(10,0),
    cost        NUMBER(10,2)
);
ALTER TABLE materials ADD PRIMARY KEY (material_id);
INSERT INTO materials VALUES (1,10);
INSERT INTO materials VALUES (2,30);
INSERT INTO materials VALUES (3,90);

<<LOCAL>>
DECLARE
    l_material_id materials.material_id%TYPE;
    l_cost        materials.cost%TYPE;
    l_total_cost  materials.cost%TYPE;

    CURSOR test IS
        SELECT material_id,
            cost,
            Sum(cost) OVER () total_cost
        FROM   materials
        WHERE  material_id BETWEEN 1 AND 3
        FOR UPDATE OF cost;
BEGIN
    OPEN test;
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
END LOCAL;
/

Which gives the output:

这给出了输出：

1 10 130
2 30 130
3 90 130

3 个解决方案

#1

The syntax select . . . for update locks records in a table to prepare for an update. When you do an aggregation, the result set no longer refers to the original rows.

语法选择。。。用于更新锁定表中的记录以准备更新。进行聚合时，结果集不再引用原始行。

In other words, there are no records in the database to update. There is just a temporary result set.

换句话说，数据库中没有要更新的记录。只有一个临时结果集。

#2

You might try something like:

您可以尝试以下方法：

<<LOCAL>>
declare
  material_id materials.material_id%Type;
  cost        materials.cost%Type;
  total_cost  materials.cost%Type;
begin
  select material_id,
         cost,
         sum(cost) over () total_cost
  into   local.material_id,
         local.cost,
         local.total_cost 
  from   materials
  where  material_id between 1 and 3
  for update of cost;

  ...

end local;

The first row gives you the total cost, but it selects all the rows and in theory they could be locked.

第一行为您提供总成本，但它会选择所有行，理论上它们可以被锁定。

I don't know if this is allowed, mind you -- be interesting to hear whether it is.

我不知道这是否允许，请注意 - 听听它是否有趣。

#3

Is your problem "However, in theory someone could update the cost column on the materials table between the two queries, in which case the calculated percents will be off."?

是你的问题“但是，理论上有人可以在两个查询之间更新材料表上的成本列，在这种情况下，计算出的百分比将会关闭。”？

In that case , probably you can simply use a inner query as:

在这种情况下，您可以简单地使用内部查询：

SELECT material_id, cost/(SELECT Sum(cost)
  FROM materials
  WHERE material_id >=0
  AND material_id <= 10)
INTO v_material_id_collection, v_pct_collection
FROM materials
WHERE material_id >=0
AND material_id <= 10;

Why do you want to lock a table? Other applications might fail if they try to update that table during that time right?

你为什么要锁桌子？如果他们在那段时间内尝试更新该表，其他应用程序可能会失败吗？

#1