Oracle SQL：如何读取和递增字段 [英] Oracle SQL: How to read-and-increment a field

问题描述

我重构了企业应用程序的数据导入过程，并遇到了一个代码段，我想找到一个更好的解决方案。当导入数据时，我们必须为每个数据集创建一个唯一的实体，并且在字段中有一个计数器，用于按顺序分配此ID。您读取该字段以获取下一个自由ID，然后增加以备下次准备。

I'm refactoring the data import procedure for an enterprise application and came across a snippet I'd like to find a better solution. When importing data we have to create a unique entity for each data set and there is a counter in a field to be used to assign this id sequentially. You read the field to get the next free id and increment it afterwards to prepare for the next time.

此时，这是在原始应用程序中完成两个步骤，写为'C'：

At the moment this is done in two steps in the original app, written in 'C':

   SELECT idnext FROM mytable;
   UPDATE mytable SET idnext = idnext + 1;

这里显然有一个竞争条件，如果多个进程做同样的事情。

Obviously there is a race condition here, if multiple processes do the same thing.

编辑：重要的核心：我不能接触数据库/字段定义，这排除了一个序列。

Important corequisite: I can not touch the database/field definition, this rules out a sequence.

perl，我想做同样的事情，但更好。原子解决方案会很好。不幸的是，我的SQL技能有限，所以我转向集体智慧： - ）

We are rewriting in perl, and I'd like to do the same thing, but better. An atomic solution would be nice. Unfortunately my SQL skills are limited, so I'm turning to collective wisdom :-)

推荐答案

是所提到的正确的解决方案。但是如果在某些未来的情况下你需要同时更新一些东西并在同一语句中返回一个值，你可以使用 RETURNING 子句：

In this particular case, a sequence is the right solution as mentioned. But if in some future situation you need to both update something and return a value in the same statement, you can use the RETURNING clause:

UPDATE atable SET foo = do_something_with(foo) RETURNING foo INTO ?

如果调用代码是PL / SQL，与本地PL / SQL变量;

If the calling code is PL/SQL, replace the ? with a local PL/SQL variable; otherwise you can bind it as an output parameter in your program.

编辑：因为你提到了Perl，这样的东西应该工作（未测试）：

Since you mentioned Perl, something like this ought to work (untested):

my $sth = $dbh->prepare('UPDATE mytable SET idnext = idnext + 1 returning idnext into ?');
my $idnext;
$sth->bind_param_inout(1, \$idnext, 8);
$sth->execute; # now $idnext should contain the value

请参阅 DBI 。

这篇关于Oracle SQL：如何读取和递增字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！