Use One Statement to Insert with a Sequence Value
29 March 2006
Oracle version 10.1.0.2.0
A coding technique that is unfortunately common is to use two SQL statements for an insert, when a sequence is involved. There is no need for two statements now that the returning clause exists.
The coder first selects the next sequence value into a local variable or bind variable and then uses the variable in a separate insert statement. In SQL*Plus the statements might appear like this.
It is simpler to do the same work by means of a single insert.
SQL> create sequence seq_tt; Sequence created. SQL> create table tt 2 (a number (1), 3 b char (1)); Table created. SQL> variable a number; SQL> insert into tt (a, b) 2 values (seq_tt.nextval, 'A') 3 returning a into :a; 1 row created. SQL> select * from tt; A B ---------- - 1 A SQL> exec dbms_output.put_line (to_char (:a)); 1Now both the table and the variable have been populated in a single statement.
It is even simpler, of course, if the coder did not actually need the local variable to begin with:
SQL> insert into tt (a, b) values (seq_tt.nextval, 'A'); 1 row inserted.