Use One Statement to Insert with a Sequence Value

David Clement

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.

            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> begin
              2  select seq_tt.nextval into :a from dual;
              3  end;
            PL/SQL procedure successfully completed.
            SQL> insert into tt (a, b) values (:a, 'A');
            1 row created.
            SQL> select * from tt;
                     A B
            ---------- -
                     1 A
            SQL> exec dbms_output.put_line (to_char (:a));
            1

The problem with this process is that it takes one select and one insert to do the job.

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));
            1

Now 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.