时间戳

首页 > 解决方案 > 不允许更新此查询表达式（光标）

oracle - 不允许更新此查询表达式（光标）

问题描述

我在查询下方运行

Select location, sum(units) as Units
from
( Select a.from_loc as location, sum(units) as units
from emp a, dept b
where a.id=b.id
Union all
Select a.to_loc as location, sum(units) as units
feom emp a, dept b
where a.id=b.id)
group by location;

上面的查询给了我以下格式的数据。

Location | sum(Units)
--------------------
100      |  350
200      |  450

现在我需要使用上述查询给出的单位更新另一个表类。类也有 Location 作为主键列和单位列。

我试图创建一个游标，但它的抛出错误，因为无法使用更新

这是光标代码的片段

Declare
V_location number(20);
V_units (20),
Cursor c1 is
Select location, sum(units) as Units
from
 ( Select a.from_loc as location, sum(units) as units
   from emp a, dept b
    where a.id=b.id
    Union all
    Select a.to_loc as location, sum(units) as units
     from emp a, dept b
     where a.id=b.id)
      group by location -----above select query
 for update;
Begin
 Open c1;
 Loop
 Fetch c1 into v_location, v_units;
 Exit when c1%notfound;

 Update class set units=v_units
 where location=v_location;
  End loop;
  Close c1;
 End;

它的抛出 不允许更新此查询表达式 有人可以让我知道我在这里做错了什么吗？或其他一些更新类表的方法

标签： oracleplsqlcursor

解决方案

这有什么好处吗？

  • 我认为该FOR UPDATE子句会导致问题
  • 我删除了整个DECLARE部分并SELECT在游标循环中使用了您的语句，FOR因为它更易于维护（没有打开、关闭、退出......）
BEGIN
   FOR cur_r IN (  SELECT location, SUM (units) AS units
                     FROM (SELECT a.from_loc AS location, SUM (units) AS units
                             FROM emp a, dept b
                            WHERE a.id = b.id
                           UNION ALL
                           SELECT a.to_loc AS location, SUM (units) AS units
                             FROM emp a, dept b
                            WHERE a.id = b.id)
                 GROUP BY location)
   LOOP
      UPDATE class
         SET units = cur_r.units
       WHERE location = cur_r.location;
   END LOOP;
END;

[编辑，阅读评论后]

IF-THEN-ELSE将使用CASE（或DECODE）完成；例如：

update class set
  units = case when location between 1 and 100 then cur_r.units / 10
               else cur_r.units / 20
          end
where location = cur_r.location

推荐阅读