<bdo id='5Qmdy'></bdo><ul id='5Qmdy'></ul>
    1. <tfoot id='5Qmdy'></tfoot>
      <i id='5Qmdy'><tr id='5Qmdy'><dt id='5Qmdy'><q id='5Qmdy'><span id='5Qmdy'><b id='5Qmdy'><form id='5Qmdy'><ins id='5Qmdy'></ins><ul id='5Qmdy'></ul><sub id='5Qmdy'></sub></form><legend id='5Qmdy'></legend><bdo id='5Qmdy'><pre id='5Qmdy'><center id='5Qmdy'></center></pre></bdo></b><th id='5Qmdy'></th></span></q></dt></tr></i><div id='5Qmdy'><tfoot id='5Qmdy'></tfoot><dl id='5Qmdy'><fieldset id='5Qmdy'></fieldset></dl></div>
    2. <legend id='5Qmdy'><style id='5Qmdy'><dir id='5Qmdy'><q id='5Qmdy'></q></dir></style></legend>

      1. <small id='5Qmdy'></small><noframes id='5Qmdy'>

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

        时间:2023-11-28

        <legend id='TCj5q'><style id='TCj5q'><dir id='TCj5q'><q id='TCj5q'></q></dir></style></legend>

      3. <tfoot id='TCj5q'></tfoot>
        • <small id='TCj5q'></small><noframes id='TCj5q'>

          <i id='TCj5q'><tr id='TCj5q'><dt id='TCj5q'><q id='TCj5q'><span id='TCj5q'><b id='TCj5q'><form id='TCj5q'><ins id='TCj5q'></ins><ul id='TCj5q'></ul><sub id='TCj5q'></sub></form><legend id='TCj5q'></legend><bdo id='TCj5q'><pre id='TCj5q'><center id='TCj5q'></center></pre></bdo></b><th id='TCj5q'></th></span></q></dt></tr></i><div id='TCj5q'><tfoot id='TCj5q'></tfoot><dl id='TCj5q'><fieldset id='TCj5q'></fieldset></dl></div>
            <bdo id='TCj5q'></bdo><ul id='TCj5q'></ul>
              <tbody id='TCj5q'></tbody>

                1. 本文介绍了为什么我不能将 SELECT ... FOR UPDATE 与聚合函数一起使用?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

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

                  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.

                  理想情况下,我只会在第一个查询中使用 FOR UPDATE 子句,但是当我尝试这样做时,出现错误:

                  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
                  

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

                  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.

                  有没有人知道不允许这样做的特定原因?在我看来,FOR UPDATE 子句应该只锁定与 WHERE 子句匹配的行 - 我不明白为什么我们对这些行所做的事情很重要.

                  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.

                  看起来 SELECT ... FOR UPDATE 可以与分析函数一起使用,如下面的 David Aldridge 所建议的.这是我用来证明它有效的测试脚本.

                  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;
                  /
                  

                  给出输出:

                  1 10 130
                  2 30 130
                  3 90 130
                  

                  推荐答案

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

                  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.

                  这篇关于为什么我不能将 SELECT ... FOR UPDATE 与聚合函数一起使用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:如何从 Oracle 中的声明/开始/结束块返回行? 下一篇:ORA-01779: 无法修改映射到非键保留表的列

                  相关文章

                      <bdo id='59xqP'></bdo><ul id='59xqP'></ul>
                  1. <tfoot id='59xqP'></tfoot>

                    <small id='59xqP'></small><noframes id='59xqP'>

                  2. <legend id='59xqP'><style id='59xqP'><dir id='59xqP'><q id='59xqP'></q></dir></style></legend>
                    <i id='59xqP'><tr id='59xqP'><dt id='59xqP'><q id='59xqP'><span id='59xqP'><b id='59xqP'><form id='59xqP'><ins id='59xqP'></ins><ul id='59xqP'></ul><sub id='59xqP'></sub></form><legend id='59xqP'></legend><bdo id='59xqP'><pre id='59xqP'><center id='59xqP'></center></pre></bdo></b><th id='59xqP'></th></span></q></dt></tr></i><div id='59xqP'><tfoot id='59xqP'></tfoot><dl id='59xqP'><fieldset id='59xqP'></fieldset></dl></div>