oracle 为什么我不能将 SELECT ... FOR UPDATE 与聚合函数一起使用？

Why can't I use SELECT ... FOR UPDATE with aggregate functions?

提问by BimmerM3

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

回答by Gordon Linoff

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

该语法select . . . for update锁定表中的记录以准备更新。进行聚合时，结果集不再引用原始行。

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

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

回答by David Aldridge

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.

我不知道这是否被允许，请注意——听听是否允许很有趣。

回答by A Nice Guy

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?

为什么要锁表？如果其他应用程序在此期间尝试更新该表，它们可能会失败，对吗？