Serial Numbers

Generator With UPDATE Commands

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.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11895.html]SQL Snippets: Serial Numbers - Generator With UPDATE Commands[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11895.html">SQL Snippets: Serial Numbers - Generator With UPDATE Commands</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Serial Numbers - Generator With UPDATE Commands
  • URL (href): http://www.sqlsnippets.com/en/topic-11895.html