René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Oracle SQL: Insert into y returning x

The following example is based on autonumbers in Oracle?.
set feedback off
set serveroutput on


create table auto_increment (
  id                number not null constraint pk_ai primary key,
  something         number
); 


create sequence seq_auto_increment start with 1 increment by 1;


create or replace trigger ai 
  before insert on auto_increment
  for each row
  begin
    if :new.id is null then
      select seq_auto_increment.nextval into :new.id from dual;
    end if;
  end;
/



declare
  i number;
begin
  insert into auto_increment (something) values (9) returning id into i;
  dbms_output.put_line('id was: '||i);


  insert into auto_increment (something) values (8) returning id into i;
  dbms_output.put_line('id was: '||i);


  insert into auto_increment (something) values (4) returning id into i;
  dbms_output.put_line('id was: '||i);


  insert into auto_increment (something) values (5) returning id into i;
  dbms_output.put_line('id was: '||i);
end;
/

drop table auto_increment;
drop sequence seq_auto_increment;