检索Oracle最后插入的IDENTITY [英] Retrieve Oracle last inserted IDENTITY

问题描述

从Oracle 12c开始，我们可以使用IDENTITY字段.

Since Oracle 12c we can use IDENTITY fields.

有没有办法检索最后插入的身份(即select @@identity或select LAST_INSERTED_ID()等等)?

Is there a way to retrieve the last inserted identity (i.e. select @@identity or select LAST_INSERTED_ID() and so on)?

推荐答案

好. Oracle在12c中使用IDENTITY功能的序列和默认值.因此，您需要了解问题的顺序.

Well. Oracle uses sequences and default values for IDENTITY functionality in 12c. Therefore you need to know about sequences for your question.

首先创建一个测试身份表.

First create a test identity table.

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

首先，让我们找到使用此标识列创建的序列名称.此序列名称是表中的默认值.

First, lets find your sequence name that is created with this identity column. This sequence name is a default value in your table.

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

对我来说，该值为"ISEQ $$ _ 193606"

for me this value is "ISEQ$$_193606"

插入一些值.

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

然后插入值并查找标识.

then insert value and find identity.

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

您应该看到您的身份值.如果要在一个步骤中进行操作，请使用

you should see your identity value. If you want to do in one block use

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

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

Last ID is my identity column name.

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