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.
精彩评论