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

yzckvree  于 2022-12-11  发布在  Oracle
关注(0)|答案(5)|浏览(210)

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

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:

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.
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.
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.

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:

omhiaaxx

omhiaaxx1#

语法select . . . for update锁定表中的记录以准备更新。执行聚合时,结果集不再引用原始行。
换句话说,数据库中没有要更新的记录,只有一个临时结果集。

jdg4fx2g

jdg4fx2g2#

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.

lnvxswe2

lnvxswe23#

为此,可以使用WITH命令。
示例:

WITH result AS (
  -- your select
) SELECT * FROM result GROUP BY material_id;
6ju8rftf

6ju8rftf4#

For example, there is product table with id, name and stock as shown below.

product table:

idnamestock
1Apple3
2Orange5
3Lemon8

Then, both 2 queries below can run sum() and SELECT FOR UPDATE together:

SELECT sum(stock) FROM (SELECT * FROM product FOR UPDATE) AS result;
WITH result AS (SELECT * FROM product FOR UPDATE) SELECT sum(stock) FROM result;

Output:

sum
-----
  16
(1 row)
von4xj4u

von4xj4u5#

您的问题是“但是,理论上,有人可能会在两次查询之间更新物料表中的成本列,在这种情况下,计算的百分比将出现偏差。"
在这种情况下,您可以简单地使用内部查询:

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;

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

相关问题