oracle 获取同一语句中插入的id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1004128/
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
Obtain id of an insert in the same statement
提问by dfa
Is there any way of insert a row in a table and get the new generated ID, in only one statement? I want to use JDBC, and the ID will be generated by a sequence or will be an autoincrement field.
有什么方法可以在表中插入一行并仅在一个语句中获取新生成的 ID？我想用JDBC，ID会由序列生成或者是自增字段。
Thanks for your help.
谢谢你的帮助。
John Pollancre
约翰·波兰克
回答by dfa
using getGeneratedKeys():
使用 getGeneratedKeys()：
resultSet = pstmt.getGeneratedKeys();
if (resultSet != null && resultSet.next()) {
lastId = resultSet.getInt(1);
}
回答by Vincent Malgrat
You can use the RETURNINGclause to get the value of any column you have updated or inserted into. It works with trigger (i-e: you get the values actually inserted after the execution of triggers). Consider:
您可以使用该RETURNING子句获取已更新或插入的任何列的值。它适用于触发器（即：您在触发器执行后获得实际插入的值）。考虑：
SQL> CREATE TABLE a (ID NUMBER PRIMARY KEY);
Table created
SQL> CREATE SEQUENCE a_seq;
Sequence created
SQL> VARIABLE x NUMBER;
SQL> BEGIN
2 INSERT INTO a VALUES (a_seq.nextval) RETURNING ID INTO :x;
3 END;
4 /
PL/SQL procedure successfully completed
x
---------
1
SQL> /
PL/SQL procedure successfully completed
x
---------
2
回答by Mihai Tudor
Actually, I think nextval followed by currval does work. Here's a bit of code that simulates this behaviour with two threads, one that first does a nextval, then a currval, while a second thread does a nextval in between.
实际上，我认为 nextval 后跟 currval 确实有效。这里有一些代码用两个线程模拟这种行为，一个线程首先执行 nextval，然后执行 currval，而第二个线程则在两者之间执行 nextval。
public void checkSequencePerSession() throws Exception {
final Object semaphore = new Object();
Runnable thread1 = new Runnable() {
public void run() {
try {
Connection con = getConnection();
Statement s = con.createStatement();
ResultSet r = s.executeQuery("SELECT SEQ_INV_BATCH_DWNLD.nextval AS val FROM DUAL ");
r.next();
System.out.println("Session1 nextval is: " + r.getLong("val"));
synchronized(semaphore){
semaphore.notify();
}
synchronized(semaphore){
semaphore.wait();
}
r = s.executeQuery("SELECT SEQ_INV_BATCH_DWNLD.currval AS val FROM DUAL ");
r.next();
System.out.println("Session1 currval is: " + r.getLong("val"));
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
};
Runnable thread2 = new Runnable(){
public void run(){
try{
synchronized(semaphore){
semaphore.wait();
}
Connection con = getConnection();
Statement s = con.createStatement();
ResultSet r = s.executeQuery("SELECT SEQ_INV_BATCH_DWNLD.nextval AS val FROM DUAL ");
r.next();
System.out.println("Session2 nextval is: " + r.getLong("val"));
con.commit();
synchronized(semaphore){
semaphore.notify();
}
}catch(Exception e){
e.printStackTrace();
}
}
};
Thread t1 = new Thread(thread1);
Thread t2 = new Thread(thread2);
t1.start();
t2.start();
t1.join();
t2.join();
}
The result is as follows: Session1 nextval is: 47 Session2 nextval is: 48 Session1 currval is: 47
结果如下： Session1 nextval 为：47 Session2 nextval 为：48 Session1 currval 为：47
回答by Rob van Laarhoven
I couldn't comment otherwise I would have added to Vinko Vrsalovic's post:
我无法发表评论，否则我会添加到 Vinko Vrsalovic 的帖子中：
The id generated by a sequence can be obtained via
insert into table values (sequence.NextVal, otherval)
select sequence.CurrVal
ran in the same transaction as to get a consistent view.
Updating de sequence after getting a nextval from it is an autonomous transaction. Otherwise another session would get the same value from the sequence. So getting currval will not get the inserted id if anothers sesssion has selected from the sequence in between the insert and select.
从序列中获取 nextval 后更新序列是一个自主事务。否则另一个会话将从序列中获得相同的值。因此，如果另一个会话已从插入和选择之间的序列中选择，则获取 currval 将不会获取插入的 id。
Regards, Rob
问候， 罗布
回答by Jeffrey Kemp
I think you'll find this helpful:
我想你会发现这很有帮助：
I have a table with a auto-incrementing id. From time to time I want to insert rows to this table, but want to be able to know what the pk of the newly inserted row is.
我有一个带有自动递增 ID 的表。有时我想向这个表插入行，但希望能够知道新插入的行的 pk 是什么。
String query = "BEGIN INSERT INTO movement (doc_number) VALUES ('abc') RETURNING id INTO ?; END;";
OracleCallableStatement cs = (OracleCallableStatement) conn.prepareCall(query);
cs.registerOutParameter(1, OracleTypes.NUMBER );
cs.execute();
System.out.println(cs.getInt(1));
Source: Thread: Oracle / JDBC Error when Returning values from an Insert
回答by jnt30
I couldn't comment, otherwise I would have just added to dfa's post, but the following is an example of this functionality with straight JDBC.
我无法发表评论，否则我会直接添加到 dfa 的帖子中，但以下是使用直接 JDBC 进行此功能的示例。
http://www.ibm.com/developerworks/java/library/j-jdbcnew/
http://www.ibm.com/developerworks/java/library/j-jdbcnew/
However, if you are using something such as Spring, they will mask a lot of the gory details for you. If that can be of any assistance, just good Spring Chapter 11, which is the JDBC details. Using it has saved me a lot of headaches.
但是，如果您使用诸如 Spring 之类的东西，它们会为您掩盖许多血腥细节。如果这有任何帮助，那就是 Spring 第 11 章，这是 JDBC 的详细信息。使用它让我省了很多麻烦。
回答by Bill Karwin
The value of the auto-generated ID is not known until after the INSERT is executed, because other statements could be executing concurrently and the RDBMS gets to decide how to schedule which one goes first.
自动生成的 ID 的值直到执行 INSERT 之后才知道，因为其他语句可能会同时执行，并且 RDBMS 可以决定如何调度哪个先执行。
Any function you call in an expression in the INSERT statement would have to be evaluated before the new row is inserted, and therefore it can't know what ID value is generated.
在插入新行之前，您在 INSERT 语句中的表达式中调用的任何函数都必须进行评估，因此它无法知道生成什么 ID 值。
I can think of two options that are close to what you're asking:
我可以想到两个与您要求的很接近的选项：
Write a trigger that runs AFTER INSERT, so you have access to the generated ID key value.
Write a procedure to wrap the insert, so you can execute other code in the procedure and query the last generated ID.
编写一个在 INSERT 后运行的触发器，以便您可以访问生成的 ID 键值。
编写一个过程来包装插入，这样你就可以在过程中执行其他代码并查询最后生成的ID。
However, I suspect what you're really asking is whether you can query for the last generated ID value by your current sessioneven if other sessions are also inserting rows and generating their own ID values. You can be assured that every RDBMS that offers an auto-increment facility offers a way to query this value, and it tells you the last ID generated in your current session scope. This is not affected by inserts done in other sessions.
但是，我怀疑您真正要问的是，即使其他会话也在插入行并生成自己的 ID 值，您是否可以查询当前会话最后生成的 ID 值。您可以放心，每个提供自动增量功能的 RDBMS 都提供了一种查询此值的方法，它会告诉您在当前会话范围内生成的最后一个 ID。这不受在其他会话中完成的插入的影响。
回答by Vinko Vrsalovic
The id generated by a sequence can be obtained via
序列生成的id可以通过
insert into table values (sequence.NextVal, otherval)
select sequence.CurrVal
ran in the same transaction as to get a consistent view.
在同一个事务中运行以获得一致的视图。
