插入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?

如果必须是存储的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标识标签分开)更容易管理.存储的proc需要我做更多的工作，但是如果这是唯一可行的方法，我可以采用这种方式.

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作为out参数返回.

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屋！