Oracle 中的 SELECT IDENT_CURRENT('tablename')
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3657565/
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
SELECT IDENT_CURRENT(‘tablename’) in Oracle
提问by Goran
I'm new with PL/SQL and I need last inserted id in data table after insert statement.
我是 PL/SQL 的新手，我需要在插入语句后在数据表中最后插入 id。
Like on MS SQL SELECT IDENT_CURRENT(‘tablename')
就像 MS SQL SELECT IDENT_CURRENT('tablename')
回答by Erich Kitzmueller
You can use the RETURNING clause:
您可以使用 RETURNING 子句：
insert into mytable(x,y) values (1,2) returning id into v_id;
This works well when there is a trigger that fills an id column, so you don't need the "select seq.currval from dual" thing.
当存在填充 id 列的触发器时，这很有效，因此您不需要“select seq.currval from dual”。
回答by Alexander Malakhov
UpdateOracle 12c has introduced identity columns(search "identity_clause", first few hits will be schemes and then text description). See example in Lukas' answer.
更新Oracle 12c 引入了身份列（搜索“identity_clause”，前几个命中将是方案，然后是文本描述）。请参阅Lukas 的回答中的示例。
There were no built-in autoincrement fields in Oracle (prior to 12c), you created them using sequences:
Oracle 中没有内置的自动增量字段（12c 之前），您使用序列创建它们：
CREATE TABLE some_tab(
rec_id INTEGER,
some_data VARCHAR2(300)
);
CREATE SEQUENCE some_tab_seq;
CREATE OR REPLACE TRIGGER trig_BI
BEFORE INSERT
ON some_tab
FOR EACH ROW
BEGIN
IF :NEW.rec_id IS NULL
THEN
:NEW.rec_id := some_tab_seq.NEXTVAL ;
END IF;
END;
Then in PL/SQL you can fetch current value of the sequence by
然后在 PL/SQL 中，您可以通过以下方式获取序列的当前值
your_var := some_tab_seq.CURRVAL
your_var := some_tab_seq.CURRVAL
Also in older version of Oracle you can't directly read NEXTVAL / CURRVAL into var and have to do:
同样在旧版本的 Oracle 中，您不能直接将 NEXTVAL / CURRVAL 读入 var 并且必须执行以下操作：
SELECT some_tab_seq.CURRVAL
INTO your_var
FROM DUAL;
回答by devio
Oracle does not implement identity columns. It uses Sequences which generate unique numbers which can be used as PK values in any table.
Oracle 不实现标识列。它使用生成唯一数字的序列，这些数字可用作任何表中的 PK 值。
So the expression IDENT_CURRENT('my_table') is best translated into MySequence.CURRVAL of the sequence feeding the table's PK.
所以表达式 IDENT_CURRENT('my_table') 最好翻译成 MySequence.CURRVAL 提供表的 PK 的序列。
Typically, you'd retrieve the inserted PK value in MS SQL by:
通常，您可以通过以下方式在 MS SQL 中检索插入的 PK 值：
INSERT INTO MyTable ...
SELECT @PK = SCOPE_IDENTITY()
In Oracle, use the INSERT RETURNING clause to achieve similar functionality
在 Oracle 中，使用 INSERT RETURNING 子句实现类似功能
DECLARE PK ...;
INSERT INTO MyTable
RETURNING TableID INTO PK;
回答by Lukas Eder
If you're using Oracle 12c IDENTITYcolumns, as I've mentioned in this blog postor on this other Stack Overflow answer, you could fetch all the current identity values of your schema with a single query:
如果您使用 Oracle 12cIDENTITY列，正如我在这篇博文或其他Stack Overflow 答案中提到的，您可以使用单个查询获取架构的所有当前标识值：
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;
/
This query can obviously be filtered for table_name, or you just reused the content of the current_value()function above directly.
这个查询显然可以过滤为table_name，或者你只是直接重用current_value()上面函数的内容。
