SQL Snippets ™: Tutorials for Oracle Developers

This tutorial discusses how to use the Serial Number Generator created earlier in this section with an UPDATE command. This approach is useful when multiple users need to insert rows at the same time and serial number generation can be deferred to an off-peak batch approval process or batch posting process.

Before proceeding however, let's clean up old test data from prior tutorials.

Test Data Setup

delete from serial_number_controls ;

insert into serial_number_controls
  ( key, last_serial_number, increment_size )
values
  ( 'T.SERIAL_NUMBER' , 0 , 1 )
;

delete from t;

commit;
 

NEXT_SERIAL_NUMBER() With UPDATE

Here is how it works.

----------------------------------------------------------------------
-- Transaction 1
----------------------------------------------------------------------

insert into t ( key, name, serial_number ) values ( 1, 'a', null );
insert into t ( key, name, serial_number ) values ( 2, 'b', null );
insert into t ( key, name, serial_number ) values ( 3, 'c', null );
insert into t ( key, name, serial_number ) values ( 4, 'd', null );
insert into t ( key, name, serial_number ) values ( 5, 'e', null );

commit;
 

select * from t order by key ;
 

       KEY NAME  SERIAL_NUMBER
---------- ----- -------------
         1 a
         2 b
         3 c
         4 d
         5 e
 

select * from serial_number_controls where key = 'T.SERIAL_NUMBER' ;
 

KEY                            LAST_SERIAL_NUMBER INCREMENT_SIZE
------------------------------ ------------------ --------------
T.SERIAL_NUMBER                                 0              1
 

... some time goes by ...

----------------------------------------------------------------------
-- Transaction 2
----------------------------------------------------------------------

declare

  v_next_serial_number number ;
  v_row_count          binary_integer ;

begin

  v_next_serial_number := next_serial_number( 'T.SERIAL_NUMBER' ) ;

  update t
  set    serial_number =  v_next_serial_number + rownum - 1
  where  serial_number is null ;

  v_row_count := sql%rowcount ;

  update SERIAL_NUMBER_CONTROLS
  set    last_serial_number = v_next_serial_number + v_row_count - 1
  where  key = 'T.SERIAL_NUMBER' ;

  commit;

end;
/
 

select * from t order by key ;
 

       KEY NAME  SERIAL_NUMBER
---------- ----- -------------
         1 a                 1
         2 b                 2
         3 c                 3
         4 d                 4
         5 e                 5
 

select * from serial_number_controls where key = 'T.SERIAL_NUMBER' ;
 

KEY                            LAST_SERIAL_NUMBER INCREMENT_SIZE
------------------------------ ------------------ --------------
T.SERIAL_NUMBER                                 5              1
 

Note that, unlike the Generator With INSERT Commands approach, the control row in SERIAL_NUMBER_CONTROLS is only locked during Transaction 2, not Transaction 1.