oracle 如何在Oracle中获取添加行的id
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3468482/
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
How to get the id of the added row in Oracle
提问by Boris
I need to translate a script from tsql to plsql, something like:
我需要将脚本从 tsql 转换为 plsql，例如：
DECLARE @temp_id int
INSERT INTO Table (col1, col2) VALUES (1, 2)
SET @temp_id = @@identity
DECLARE @temp_id int
INSERT INTO Table (col1, col2) VALUES (1, 2)
SET @temp_id = @@identity
but, I am having trouble to find something similar to global variable @@identity
但是，我很难找到类似于全局变量@@identity 的东西
Oracle expert anyone?
Oracle 专家有人吗？
回答by APC
Presuming you have some kind of trigger to populate the primary key column with a sequence, and you want to get the assigned value...
假设您有某种触发器来使用序列填充主键列，并且您想要获得分配的值...
INSERT INTO Table (col1, col2) VALUES (1, 2)
RETURNING pk_col INTO temp_id
/
Note that the RETURNING syntax only works with single row inserts.
请注意， RETURNING 语法仅适用于单行插入。
回答by Vicky Ronnen
The answer of Michael Pakhantsov is only usable in a single user single tasking environment. The insert and select statements are separate statements! What happens in a multi user multi process environment?
Michael Pakhantsov 的回答仅适用于单用户单任务环境。insert 和 select 语句是单独的语句！在多用户多进程环境中会发生什么？
Process 1 insert
Process 2 insert
Process 2 select returns the is the id by process 2 insert
Process 1 select returns the is the id by process 2 insert NOT the process 1 insert
Don't ever program this way, don't even think about it. You need an atomic operation, which means it will not be affected by task switching.
永远不要以这种方式编程，甚至不要考虑它。你需要一个原子操作，这意味着它不会受到任务切换的影响。
The answer of APC would be:
APC的答案是：
create table FOO (
id number primary key,
name varchar2(100)
);
create sequence FOO_seq;
create or replace trigger FOO_trg
before insert on FOO
for each row
begin
select FOO_seq.nextval into :new.id from dual;
dbms_output.put_line('inside trigger '||:new.id||' '||:new.name);
end;
/
declare
temp_id number:=10;
begin
INSERT INTO FOO (id, name) VALUES (null, 'Vicky') RETURNING id INTO temp_id;
dbms_output.put_line(temp_id);
rollback;
INSERT INTO FOO (id, name) VALUES (null, 'Jo?l') RETURNING id INTO temp_id;
dbms_output.put_line(temp_id);
commit;
end;
/
select * from FOO;
drop table FOO;
drop sequence FOO_seq;
It would output:
它会输出：
table FOO created.
sequence FOO_SEQ created.
TRIGGER FOO_TRG compiled
anonymous block completed
ID NAME
------ --------
2 jo?l
table FOO dropped.
sequence FOO_SEQ dropped.
The dbms_output would be:
dbms_output 将是：
inside trigger 1 Vicky
1
inside trigger 2 Jo?l
2
Remember you can only use this for inserting one row at a time:
请记住，您一次只能使用它来插入一行：
insert all
into foo(id,name) values(null,'Vicky')
into foo(id,name) values(null,'Jo?l')
SELECT null,'none' FROM dual RETURNING id INTO temp_id;
Gives a PL/SQL: ORA-00933: SQL command not properly ended error, omit the RETURNING id INTO temp_id.
给出 PL/SQL：ORA-00933：SQL 命令未正确结束错误，省略 RETURNING id INTO temp_id。
In Oracle 12 you can use the identity column and get something similar to SQLServer and MySql.
在 Oracle 12 中，您可以使用标识列并获得类似于 SQLServer 和 MySql 的内容。
CREATE TABLE foo (
id NUMBER GENERATED ALWAYS AS IDENTITY,
name VARCHAR2(30)
);
/
declare
temp_id varchar2(100);
begin
INSERT INTO foo(name) VALUES ('Vicky') RETURNING id||' '||name INTO temp_id;
dbms_output.put_line(temp_id);
INSERT INTO foo(name) VALUES ('Jo?l') RETURNING id||' '||name INTO temp_id;
dbms_output.put_line(temp_id);
end;
/
drop table foo;
purge recyclebin;
dbms_output would be:
dbms_output 将是：
1 Vicky
2 Jo?l
One note added: When you create a table using identity, a system-generated sequence will be generated. This sequence will continue to exists even after dropping the table! Even the sysdba cannot drop this sequence! After the drop table statement you need the purge recyclebin to remove them.
添加了一个注释：当您使用身份创建表时，将生成系统生成的序列。即使删除表后，此序列仍将继续存在！即使是 sysdba 也不能删除这个序列！在 drop table 语句之后，您需要清除回收站来删除它们。
回答by Michael Pakhantsov
You need use sequences. (http://psoug.org/reference/sequences.html)
您需要使用序列。( http://psoug.org/reference/sequences.html)
SequenceName.NEXTVAL next value, sequenceName.CURRVAL - latest used value (like @@Identity)
SequenceName.NEXTVAL 下一个值，sequenceName.CURRVAL - 最近使用的值（如@@Identity）
INSERT INTO Table (Id, col1, col2) VALUES (Sequence.NEXTVAL, 1, 2);
插入表 (Id, col1, col2) VALUES (Sequence.NEXTVAL, 1, 2);
SELECT sequence.CURRVAL INTO Temp_ID from dual;
SELECT sequence.CURRVAL INTO Temp_ID from dual;
