检索Oracle最后插入的IDENTITY ID

Question

自从 Oracle 12c 版本开始，我们可以使用 IDENTITY 字段。是否有一种方法可以检索最后插入的 identity 值（例如，select @@identity 或 select LAST_INSERTED_ID() 等）？

- bubi

---

“identity”列仍然在后台使用序列。您应该能够使用通常的sequence.currval来获取最后生成的值。 - user330315

问题是我必须知道序列的名称。实际上，很难知道表的名称和插入记录的模式的名称（序列的名称与它们相关）。 - bubi

你怎么可能不知道你要插入的表的名称？ - user330315

根据您的需求，您还可以使用返回子句 - 如果您想要插入的ID，那么这可能适合您，而无需知道序列名称；如果您想要任何会话中任何人最后插入的ID，则可能需要查询表。更多上下文将有助于理解。 - Alex Poole

@a_horse_with_no_name 我正在开发一个库。用户（使用该库）只需操作表格，当他需要知道ID时，只需请求ID即可。 - bubi

Answer 1

好的。在12c中，Oracle使用序列和默认值来实现IDENTITY功能。因此，您需要了解序列才能回答您的问题。

首先创建一个测试IDENTITY表。

CREATE TABLE IDENTITY_TEST_TABLE
(
  ID NUMBER GENERATED ALWAYS AS IDENTITY 
, NAME VARCHAR2(30 BYTE) 
);

首先，让我们找到使用该标识列创建的序列名称。这个序列名称是您表中的默认值。

Select TABLE_NAME, COLUMN_NAME, DATA_DEFAULT from USER_TAB_COLUMNS
where TABLE_NAME = 'IDENTITY_TEST_TABLE';

对我来说，这个值是"ISEQ$$_193606"

插入一些值。

INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla');
INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('aydın');

然后插入数值并查找身份。

INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla');
 SELECT "ISEQ$$_193606".currval from dual; 

您应该看到您的身份值。如果想在一个块中执行，请使用

declare
   s2 number;
 begin
   INSERT INTO IDENTITY_TEST_TABLE (name) VALUES ('atilla') returning ID into s2;
   dbms_output.put_line(s2);
 end;

最后一个ID是我的身份列名称。

- Atilla Ozgur

---

这是唯一正确的答案。你只需要确保在插入数据时，在同一个会话中评估<sequence_name>.currval。 - Ben

启发性的答案。在阅读这个答案之前，我从未想过IDENTITY列缺乏currval功能 - 尚未在实际生活中使用它们:(。 - APC

这个解决方案对抗竞态条件是否安全：在我的插入和我的序列值选择之间，是否会有另一行插入到表中的其他线程？是否可以确保我的序列值选择将返回在我的插入期间生成的值，而不是在其他线程中生成的值？ - undefined

返回ID解决方案应该安全防止竞态条件。 - undefined

Answer 2

IDENTITY列在“幕后”使用SEQUENCE - 自动创建和删除与其使用的表相应的序列。此外，您可以使用start with 1000 and increment by 2指定起始值和增量参数。当您不想直接操作其值时，使用IDENTITY非常方便。

但是，如果您需要直接操作序列，则应使用Oracle 12c中提供的另一种选项 - 列默认值。这些默认值可以从序列nextval或currval生成。这使您可以拥有一个可理解的序列名称，并将其用作“标识”，而无需触发器。

create table my_new_table
(id number default my_new_table_seq.nextval not null)

你将始终能够调用：my_new_table_seq.currval。

可以使用RETURNING子句在插入语句中获取从SEQUENCE生成的ID。

例如，创建一个临时表：

create global temporary table local_identity_storage ("id" number) on commit delete rows

在临时表中插入保存这个值：

CREATE TABLE identity_test_table (
  id_ident          NUMBER GENERATED BY DEFAULT AS IDENTITY,
  same_value VARCHAR2(100)
);

declare
  v_id number(10, 0);
begin  
  INSERT INTO identity_test_table
    (same_value)
  VALUES
    ('Test value')
  RETURNING id_ident INTO v_id;

  insert into local_identity_storage ("id") values (v_id);
  commit;
end;

现在您有了“local”插入的ID。

select "id" from local_identity_storage

- Mikhailov Valentin

Answer 3

请检查。

INSERT INTO yourtable (....)
  VALUES (...)
  RETURNING pk_id INTO yourtable;

这将帮助您检索最后插入的行

- Jawad Siddiqui

Answer 4

似乎Oracle只是为了表明他们支持身份标识而实现了IDENTITY。一切仍然使用SEQUENCES来实现，有时需要访问SEQUENCE来完成一些工作（例如检索最新插入的IDENTITY）。

与MySQL、SQL Server、DB2等不同，没有类似于检索IDENTITY的方法，必须使用SEQUENCE来检索它。

- bubi

---

是的，它是对SEQUENCE的抽象，但它将改变INSERT子句的语法，并且这种改变也会反映在性能上。看看这个测试（在Oracle 12c中插入），使用三种不同的技术（结果来自于此[https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1]）： 1）TRIGGER_IDENTITY：时间=217 hsecs CPU时间=204 hsecs; 2）直接使用SEQUENCE：时间=26 hsecs CPU时间=22 hsecs; 3）REAL_IDENTITY：时间=28 hsecs CPU时间=26 hsecs。 - Mikhailov Valentin

这是为了使迁移到Oracle数据库更加容易而完成的。现在无需手动创建序列和触发器来在插入时触发它们 - 数据库现在会为您执行此操作。我不代表Oracle，但如果您对我们在12c中为协助迁移所做的其他工作感兴趣，我有一份白皮书。 - thatjeffsmith

@thatjeffsmith 我正在进行一次迁移，但是由于没有其他数据库管理系统类似的行为，所以工作很难。 - bubi

Answer 5

如我在这篇博客文章中所写，你可以通过一次查询获取模式的所有当前标识值：

with
  function current_value(p_table_name varchar2) return number is
    v_current number;
  begin
    for rec in (
      select sequence_name
      from user_tab_identity_cols
      where table_name = p_table_name
    )
    loop
      execute immediate 'select ' || rec.sequence_name || '.currval from dual'
      into v_current;
      return v_current;
    end loop;

    return null;
  end;
select *
from (
  select table_name, current_value(table_name) current_value
  from user_tables
)
where current_value is not null
order by table_name;
/

- Lukas Eder

Answer 6

你的作用域是什么，全局的还是最后一个用户插入的？ 如果是全局的，就使用

。

SELECT mytable_seq.nextval MyTableID FROM DUAL

https://www.sitepoint.com/community/t/oracle-last-insert-id-question/1402

如果需要，可以在事务中封装您的插入和查询操作。

- Krish

---

谢谢你的回答！作用域是最后一个用户。我认为你的建议只适用于MySQL。在Oracle中不起作用（语法错误），即使添加了FROM Dual子句。 - bubi

那是全局的（我也可以使用当前，但仍然是全局的） - bubi

Answer 7

最后插入的将会是该列的最高值。 所以我认为最简单的方法是使用max()函数。 像这样

select max(id) from table_name

- yogev levi