SQL 插入 Oracle 并检索生成的序列 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5558979/
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
Inserting into Oracle and retrieving the generated sequence ID
提问by Allbite
I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution…
我有一些针对 SQL Server 的原始 SQL 查询，它们使用 SCOPE_IDENTITY 来检索特定 INSERT 的生成 ID，在该 INSERT 全部在一次执行中发生之后......
INSERT into Batch(
BatchName,
BatchType,
Source,
Area
) Values (
@strBatchName,
@strType,
@strSource,
@intArea
);
SELECT SCOPE_IDENTITY() BatchID;
The question is:
问题是：
What's the best way to do that for an Oracle database?
对 Oracle 数据库执行此操作的最佳方法是什么？
Can this be done on Oracle through standard SQL or do I have to switch this to use a stored procedure and place something similar in the body of the stored proc?
这可以通过标准 SQL 在 Oracle 上完成，还是我必须切换它以使用存储过程并在存储过程的主体中放置类似的东西？
If it must be a stored proc, then what is the de-facto standard way for retrieving the last generated sequence number, taking care to consider there will likely be overlapping executions on multiple threads so this mechanism will need to retrieve the right generated ID and not necessarily the absolute last generated ID.
如果它必须是一个存储过程，那么检索最后生成的序列号的事实上的标准方法是什么，注意考虑可能会在多个线程上重叠执行，因此该机制需要检索正确的生成 ID 和不一定是最后生成的绝对 ID。
If two execute simultaneously then each must return the correct generated ID from each respective call. Notice I'm not using SQL Server's “@@IDENTITY” because of that multithreaded nature of the calls.
如果两个同时执行，那么每个都必须从每个相应的调用中返回正确生成的 ID。请注意，由于调用的多线程特性，我没有使用 SQL Server 的“@@IDENTITY”。
I would rather keep it as raw SQL if possible since that's much easier for me to manage across platforms (single file containing each platform's SQL block separated by DBMS identifying tags). Stored procs are a bit more work for me to manage, but I can go that way if it's the only way possible.
如果可能的话，我宁愿将它保留为原始 SQL，因为这对我来说更容易跨平台管理（包含每个平台的 SQL 块的单个文件，由 DBMS 标识标记分隔）。存储过程对我来说要管理更多的工作，但如果这是唯一可能的方法，我可以这样做。
回答by Alex Poole
Expanding a bit on the answers from @Guru and @Ronnis, you can hide the sequence and make it look more like an auto-increment using a trigger, and have a procedure that does the insert for you and returns the generated ID as an out parameter.
对@Guru 和@Ronnis 的答案进行一点扩展，您可以隐藏序列并使其看起来更像是使用触发器的自动增量，并有一个过程为您执行插入操作并将生成的 ID 作为输出返回范围。
create table batch(batchid number,
batchname varchar2(30),
batchtype char(1),
source char(1),
intarea number)
/
create sequence batch_seq start with 1
/
create trigger batch_bi
before insert on batch
for each row
begin
select batch_seq.nextval into :new.batchid from dual;
end;
/
create procedure insert_batch(v_batchname batch.batchname%TYPE,
v_batchtype batch.batchtype%TYPE,
v_source batch.source%TYPE,
v_intarea batch.intarea%TYPE,
v_batchid out batch.batchid%TYPE)
as
begin
insert into batch(batchname, batchtype, source, intarea)
values(v_batchname, v_batchtype, v_source, v_intarea)
returning batchid into v_batchid;
end;
/
You can then call the procedure instead of doing a plain insert, e.g. from an anoymous block:
然后您可以调用该过程而不是进行简单的插入，例如从一个匿名块：
declare
l_batchid batch.batchid%TYPE;
begin
insert_batch(v_batchname => 'Batch 1',
v_batchtype => 'A',
v_source => 'Z',
v_intarea => 1,
v_batchid => l_batchid);
dbms_output.put_line('Generated id: ' || l_batchid);
insert_batch(v_batchname => 'Batch 99',
v_batchtype => 'B',
v_source => 'Y',
v_intarea => 9,
v_batchid => l_batchid);
dbms_output.put_line('Generated id: ' || l_batchid);
end;
/
Generated id: 1
Generated id: 2
You can make the call without an explicit anonymous block, e.g. from SQL*Plus:
您可以在没有显式匿名块的情况下进行调用，例如来自 SQL*Plus：
variable l_batchid number;
exec insert_batch('Batch 21', 'C', 'X', 7, :l_batchid);
... and use the bind variable :l_batchidto refer to the generated value afterwards:
...然后使用绑定变量:l_batchid来引用生成的值：
print l_batchid;
insert into some_table values(:l_batch_id, ...);
回答by Ronnis
There are no auto incrementing features in Oracle for a column. You need to create a SEQUENCE object. You can use the sequence like:
Oracle 中没有列的自动递增功能。您需要创建一个 SEQUENCE 对象。您可以使用如下序列：
insert into table(batch_id, ...) values(my_sequence.nextval, ...)
...to return the next number. To find out the last created sequence nr (in your session), you would use:
...返回下一个数字。要找出最后创建的序列 nr（在您的会话中），您可以使用：
my_sequence.currval
This sitehas several complete examples on how to use sequences.
这个站点有几个关于如何使用序列的完整示例。
回答by Guru
Doing it as a stored procedure does have lot of advantages. You can get the sequence that is inserted into the table using syntax insert into table_name values returning.
将其作为存储过程进行确实有很多优点。您可以使用 syntax 获取插入表中的序列insert into table_name values returning。
Like:
喜欢：
declare
some_seq_val number;
lv_seq number;
begin
some_seq_val := your_seq.nextval;
insert into your_tab (col1, col2, col3)
values (some_seq_val, val2, val3) returning some_seq_val into lv_seq;
dbms_output.put_line('The inserted sequence is: '||to_char(lv_seq));
end;
/
Or just return some_seq_val. In case you are not making use of SEQUENCE, and arriving the sequence on some calculation, you can make use of returning intoeffectively.
或者只是返回some_seq_val。如果您不使用 SEQUENCE，而是在某些计算中得出序列，您可以returning into有效地使用。
回答by Sarath Avanavu
You can use the below statement to get the inserted Id to a variable-like thing.
您可以使用以下语句将插入的 Id 获取到类似变量的事物中。
INSERT INTO YOUR_TABLE(ID) VALUES ('10') returning ID into :Inserted_Value;
Now you can retrieve the value using the below statement
现在您可以使用以下语句检索值
SELECT :Inserted_Value FROM DUAL;
回答by Franjo Markovic
You can do this with a single statement - assuming you are calling it from a JDBC-like connector with in/out parameters functionality:
您可以使用单个语句执行此操作 - 假设您是从具有输入/输出参数功能的类 JDBC 连接器调用它：
insert into batch(batchid, batchname)
values (batch_seq.nextval, 'new batch')
returning batchid into :l_batchid;
or, as a pl-sql script:
或者，作为 pl-sql 脚本：
variable l_batchid number;
insert into batch(batchid, batchname)
values (batch_seq.nextval, 'new batch')
returning batchid into :l_batchid;
select :l_batchid from dual;
