循环更新数据库记录吗? [英] Updating database records in a loop?

问题描述

declare
begin
  for i in (select * from emp)
  loop
    if i.sal=1300 then
      update emp
      set sal=13000;
    end if;
  end loop;
end;

此代码将更新薪水为13000的所有记录.
相反，我想将薪水1300为1300的记录更新为值13000.
您能告诉我我在哪里弄错了吗?
我正在使用隐式游标访问记录.
对于每条记录，我正在检查该记录的sal值.
如果特定记录中的薪水值为1500，我想将其更新为15000.

This code is updating all the records with salary 13000.
Instead i want to update records having salary 1300 to the value 13000.
Can you tell where I made a mistake?
I am accesing records using implicit cursor..
for every record i am checking the sal value of that record..
if salary value in a particular record is 1500 i want to update it to 15000..

推荐答案

只要您可以使用一条语句进行更新，就应该这样做，而不要使用循环.这样，您将获得非常巨大的性能提升.或者反之，循环更新会花费很多性能.

Whenever you can do the update with one single statement, you should do that instead of using a loop. You'll get a very huge performance gain that way; or, the other way round, updates in a loop cost you a lot of performance.

如果确实需要使用循环，那么当然需要一个where条件，以确保仅更新您真正想要更新的记录.始终有效的一种可能方法(即使没有可用的唯一键)是使用rowid伪列:

If you really really have to use a loop, of course you need a where condition to make sure you are only updating the record you really want to update. A possible way that always works (even if there is no unique key available) is to use the rowid pseudocolumn:

begin
  for i in (select rowid, emp.* from emp)
  loop
    if i.sal=1300 then
      update emp
      set sal=13000
      where rowid=i.rowid;
    end if;
  end loop;
end;

另一种可能性是使用显式游标和更新...此处为 cursorname 的最新版本"语法.

Another possibility would be using an explicit cursor and the "update ... where current of cursorname" syntax.

这篇关于循环更新数据库记录吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！