CODEWITHSUNDEEP
sqloracle-databaseplsql
user2848242
user2848242

Reputation: 177

Insert and return value in oracle

I need to insert and return the primary key in one query , is it possible ? the reason is that primary key is autoincrement so i don't know what it is.

insert into Person(name) values ('Radouane')

Select PersonId : the current id that i inserted.

Thanks,

Upvotes: 0

Views: 7427

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18745

The RETURNING INTOclause is what you need:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  l_id person.PersonId%type;
BEGIN
  INSERT INTO person ( name ) VALUES ( 'Radouane' ) 
    RETURNING id INTO l_id;
  DBMS_OUTPUT.PUT_LINE( 'Returning ID value is : '||l_id );
END;
/

Replace "id" with your primary key column and "l_id" with the variable you want to put the value in.

There are plenty of examples available on the internet. Note that is is limited to PL/SQL, it cannot be used in SQL.

Upvotes: 1

Soumendra Mishra
Soumendra Mishra

Reputation: 3663

You can try this example:

DECLARE
   strlen   NUMBER;
BEGIN
   INSERT INTO myemp(ename) values('emp-name')
   RETURNING LENGTH(ename) INTO strlen;
   DBMS_OUTPUT.PUT_LINE(strlen);
END;
/

Upvotes: 0

Related Questions