SQL 检索 Oracle 最后插入的 IDENTITY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34811283/
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
Retrieve Oracle last inserted IDENTITY
提问by bubi
Since Oracle 12c we can use IDENTITY fields.
从 Oracle 12c 开始，我们可以使用 IDENTITY 字段。
Is there a way to retrieve the last inserted identity (i.e. select @@identityor select LAST_INSERTED_ID()and so on)?
有没有一种方法来检索最后插入的标识（即select @@identity或select LAST_INSERTED_ID()等等）？
回答by Atilla Ozgur
Well. Oracle uses sequences and default values for IDENTITY functionality in 12c. Therefore you need to know about sequences for your question.
好。Oracle 对 12c 中的 IDENTITY 功能使用序列和默认值。因此，您需要了解问题的序列。
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';
for me this value is "ISEQ$$_193606"
对我来说，这个值是“ISEQ$$_193606”
insert some values.
插入一些值。
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;
Last ID is my identity column name.
最后一个 ID 是我的身份列名称。
回答by Jawad Siddiqui
Please check
请检查
INSERT INTO yourtable (....)
VALUES (...)
RETURNING pk_id INTO yourtable;
It will help you to retrieve last inserted row
它将帮助您检索最后插入的行
回答by Mikhailov Valentine
IDENTITYcolumn uses a SEQUENCE“under the hood” - creating and dropping sequence automatically with the table it uses.
Also, you can specify start with and increment parameters using
start with 1000 and increment by 2. It's really very convenient to use IDENTITYwhen you don't want to operate it's values directly.
IDENTITY列使用SEQUENCE“幕后” - 使用它使用的表自动创建和删除序列。此外，您可以使用 start with 1000 and increment by 2 来指定 start with 和 increment 参数。IDENTITY当您不想直接操作它的值时，使用它真的非常方便。
But if you need to somehow operate sequence directly you should use
another option available in Oracle 12c - column default values. Sutch default
values could be generated from sequence nextvalor currval. To allow you to have a comprehensible sequence name and use it as "identity" without a trigger.
但是，如果您需要以某种方式直接操作序列，则应使用 Oracle 12c 中提供的另一个选项 - 列默认值。Sutch 默认值可以从序列nextval或currval. 让您拥有一个易于理解的序列名称，并在没有触发器的情况下将其用作“身份”。
create table my_new_table
(id number default my_new_table_seq.nextval not null)
You will be always able to call: my_new_table_seq.currval.
您将始终能够调用：my_new_table_seq.currval。
It is possible to get ID generated from SEQUENCEon insert statement using RETURNINGclause.
可以SEQUENCE使用RETURNING子句从插入语句中获取生成的 ID 。
For example, create a temporary table:
例如，创建一个临时表：
create global temporary table local_identity_storage ("id" number) on commit delete rows
Make some insert saving this value in the temporary table:
在临时表中进行一些插入保存此值：
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;
Now you have "local" inserted id.
现在您已经插入了“本地”ID。
select "id" from local_identity_storage
回答by bubi
It seems that Oracle implemented IDENTITYjust to say that they support identities. Everything is still implemented using SEQUENCESand sometimes you need to access the SEQUENCEto make some of the work (i.e. retrieve the latest inserted IDENTITY).
似乎 Oracle 实施IDENTITY只是为了说他们支持身份。一切仍然使用实现SEQUENCES，有时您需要访问SEQUENCE以进行一些工作（即检索最新插入的IDENTITY）。
There is not a way to retrieve the IDENTITYsimilar to MySQL, SQL Server, DB2, and so on, you have to retrieve it using the SEQUENCE.
没有一种方法可以检索IDENTITY类似于 MySQL、SQL Server、DB2 等的内容，您必须使用SEQUENCE.
回答by Krish
What is your scope, global or last user inserted? If global just use
你的作用域是什么，全局的还是最后插入的用户？如果全局只是使用
SELECT mytable_seq.nextval MyTableID FROM DUAL
https://www.sitepoint.com/community/t/oracle-last-insert-id-question/1402
https://www.sitepoint.com/community/t/oracle-last-insert-id-question/1402
If specific encapsulate your inserts & query within a transaction.
如果特定将您的插入和查询封装在事务中。
回答by Lukas Eder
As I've written in this blog post, you could fetch all the current identity values of your schema with a single query:
正如我在这篇博文中所写的那样，您可以通过单个查询获取架构的所有当前标识值：
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;
/
