Oracle SQL：如何读取和递增字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2029277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL: How to read-and-increment a field
提问by markus_b
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.
我正在重构企业应用程序的数据导入过程，并遇到了一个我想找到更好解决方案的片段。导入数据时，我们必须为每个数据集创建一个唯一的实体，并且字段中有一个计数器用于按顺序分配此 id。您阅读该字段以获取下一个空闲 ID，然后将其递增以准备下一次。
At the moment this is done in two steps in the original app, written in 'C':
目前，这是在原始应用程序中分两步完成的，用“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.
显然，这里存在竞争条件，如果多个进程做同样的事情。
Edit: Important corequisite: I can not touch the database/field definition, this rules out a sequence.
编辑：重要的共存条件：我不能触及数据库/字段定义，这排除了一个序列。
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 :-)
我们正在用 perl 重写，我想做同样的事情，但更好。原子解决方案会很好。不幸的是，我的 SQL 技能有限，所以我转向集体智慧:-)
回答by Dan
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 RETURNINGclause:
在这种特殊情况下，如上所述，序列是正确的解决方案。但是，如果在将来的某些情况下，您需要在同一语句中更新某些内容并返回值，则可以使用以下RETURNING子句：
UPDATE atable SET foo = do_something_with(foo) RETURNING foo INTO ?
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.
如果调用代码是 PL/SQL，替换 ? 使用本地 PL/SQL 变量；否则，您可以将其绑定为程序中的输出参数。
Edit: Since you mentioned Perl, something like this ought to work (untested):
编辑：既然你提到了 Perl，这样的事情应该可以工作（未经测试）：
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
See DBI.
请参阅DBI。
回答by wweicker
Why not use a sequence?
为什么不使用序列？
Create the sequence one time, using whatever START WITH value you want:
使用您想要的任何 START WITH 值创建序列一次：
CREATE SEQUENCE mysequence
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
Then in your application code at runtime you can use this statement to get the next value:
然后在运行时的应用程序代码中，您可以使用此语句来获取下一个值：
SELECT mysequence.NEXTVAL
INTO idnext
FROM DUAL;
Update:Using a sequence would be the preferred method, but since you can't change the database then I agree that using RETURNING should work for your situation:
更新：使用序列将是首选方法，但由于您无法更改数据库，因此我同意使用 RETURNING 应该适用于您的情况：
UPDATE mytable
SET idnext = idnext + 1
RETURNING idnext
INTO mylocalvariable;
回答by Ender
Use SELECT FOR UPDATE statement. It guarantees mutually exclusive rights to the record :
使用 SELECT FOR UPDATE 语句。它保证对记录的互斥权利：
回答by Steve De Caux
A sequencewill do the job, have a look at e.g. Oracle sequences
一个序列将完成这项工作，看看例如Oracle 序列
