插入 Oracle 并检索生成的序列 ID [英] Inserting into Oracle and retrieving the generated sequence ID

问题描述

我有一些针对 SQL Server 的原始 SQL 查询，它们使用 SCOPE_IDENTITY 来在一次执行中立即检索特定 INSERT 的生成 ID……

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…

INSERT into Batch(
BatchName,
BatchType,
Source,
Area
) Values (
@strBatchName,
@strType,
@strSource,
@intArea
);

SELECT SCOPE_IDENTITY() BatchID;

问题是:

对 Oracle 数据库执行此操作的最佳方法是什么?

What’s the best way to do that for an Oracle database?

这可以通过标准 SQL 在 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?

如果它必须是存储过程，那么检索最后生成的序列号的事实上的标准方法是什么，注意考虑可能会在多个线程上重叠执行，因此该机制需要检索正确的生成的 ID，不一定是最后生成的绝对 ID.

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.请注意，由于调用的多线程特性，我没有使用 SQL Server 的@@IDENTITY".

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.

如果可能的话，我宁愿将其保留为原始 SQL，因为这对我来说更容易跨平台管理(单个文件包含每个平台的 SQL 块，由 DBMS 标识标记分隔).存储过程对我来说需要更多的工作来管理，但如果这是唯一可能的方法，我可以这样做.

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.

推荐答案

对@Guru 和@Ronnis 的答案进行一点扩展，您可以隐藏序列并使其看起来更像是使用触发器的自动增量，并且有一个过程为您执行插入操作并将生成的 ID 作为输出参数返回.

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.

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

您可以在没有显式匿名块的情况下进行调用，例如来自 SQL*Plus:

You can make the call without an explicit anonymous block, e.g. from SQL*Plus:

variable l_batchid number;
exec insert_batch('Batch 21', 'C', 'X', 7, :l_batchid);

... 并使用绑定变量 :l_batchid 引用之后生成的值:

... and use the bind variable :l_batchid to refer to the generated value afterwards:

print l_batchid;
insert into some_table values(:l_batch_id, ...);

这篇关于插入 Oracle 并检索生成的序列 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！