SELECT IDENT_CURRENT(‘tablename’) in Oracle

I'm new with PL/SQL and I need last inserted id in data table after insert statement.

Like on MS SQL SELECT IDENT_开发者_如何学JAVACURRENT(‘tablename’)

---

You can use the RETURNING clause:

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.

---

Update Oracle 12c has introduced identity columns (search "identity_clause", first few hits will be schemes and then text description). See example in Lukas' answer.

There were no built-in autoincrement fields in Oracle (prior to 12c), you created them using sequences:

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

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:

SELECT some_tab_seq.CURRVAL
  INTO your_var
  FROM DUAL;

---

Oracle does not implement identity columns. It uses Sequences which generate unique numbers which can be used as PK values in any table.

So the expression IDENT_CURRENT('my_table') is best translated into MySequence.CURRVAL of the sequence feeding the table's PK.

Typically, you'd retrieve the inserted PK value in MS SQL by:

INSERT INTO MyTable ...
SELECT @PK = SCOPE_IDENTITY()

In Oracle, use the INSERT RETURNING clause to achieve similar functionality

DECLARE PK ...;
INSERT INTO MyTable
RETURNING TableID INTO PK;

---

If you're using Oracle 12c IDENTITY columns, as I've mentioned in this blog post or on this other Stack Overflow answer, 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;
/

This query can obviously be filtered for table_name, or you just reused the content of the current_value() function above directly.