Dealing with identity gaps2

 存储过程sp_fix_idgap.sql

/*
* This script contains everything you need to run a demo of how
* to use a two-table database design technique that allows
* identity gaps to be fixed quickly using a stored procedure.
* This script set up the following objects:
*
*    - a table 'invoices', holding application data
*    - a table 'invoices_keytable', for generating invoice numbers
*    - a procedure 'sp_fix_idgap_invoices' to repair identity gaps
*      in the 'invoices' table
*    - a procedure 'sp_insert_invoice' to simulate the application
*      inserting invoices
*
/
if not exists(select * from master.dbo.sysdatabases
             where name = "my_db")
begin
print "***************************************************"
print "*** You should edit this script first !         ***"
print "*** Change 'my_db' to the name of your database ***"
print "*** in which you want to run this script.       ***"
print "***************************************************"
end
go

use my_db
go

/*
* create the application table holding invoice data
*/
if object_id("invoices") !=  NULL
begin
   drop table invoices
end
go

create table invoices
      (invoice_nr   numeric(10,0),
       customer_nr  int,
       amount       money)
go
create unique index ix1 on invoices(invoice_nr)
go
grant all on invoices to public
go

/*
* Create a separate procedure for creating the keytable.
* This is required because it's not allowed to drop
* and recreate an object with the same name in one procedure
*/
if object_id("invoices_keytable") !=  NULL
begin
   drop table invoices_keytable
end
go

if object_id("sp_create_invoices_keytable") !=  NULL
begin
   drop procedure sp_create_invoices_keytable
end
go

create procedure sp_create_invoices_keytable
/* Copyright (c) 1998 Rob Verschoor/Sypron B.V. */
as
begin
  create table invoices_keytable
         (dummy_key numeric(10,0) identity)

  /*
   * Partitioning only works in system 11.0 and later
   * The number of partitions can be increased if there
   * will be many concurrent inserts.
   */
  alter table invoices_keytable partition 10
end
go
grant execute on sp_create_invoices_keytable to public
go

/*
* create the keytable
*/
exec sp_create_invoices_keytable
go

/*
* create a procedure to simulate application activity:
* first generate a new invoice number, then insert a new invoice
*/
if object_id("sp_insert_invoice") !=  NULL
begin
   drop procedure sp_insert_invoice
end
go

create procedure sp_insert_invoice
/* Copyright (c) 1998 Rob Verschoor/Sypron B.V. */
 @p_customer_nr int,
 @p_amount money
as
begin
  insert invoices_keytable values ()
  insert invoices values (@@identity, @p_customer_nr, @p_amount)
end
go
grant execute on sp_insert_invoice to public
go

/*
* Create the procedure to fix an identity gap.
* It takes one parameter, and can be called in two ways:
*
*    1. If "-1" is specified as a parameter, the keytable
*       will be dropped and recreated according to the highest
*       existing invoice number. The best use of this form
*       is to always run this procedure directly after server
*       startup, so that a possible identity gap is be corrected
*       before new invoices are inserted which could cause problems.
*
*    2. If another value is specified, it must be invoice number
*       generated just before the identity gap occurred. All invoices
*       having a higher invoice numbers than this will be corrected
*       downwards to create a consecutive range. The keytable will
*       be recreated and adjusted accordingly.
*       Example: suppose the following invoice numbers exist:
*                    (...)
*                   10028
*                   10029
*                   10030
*                   10031  <--\
*                 5000002  <--- identity gap here
*                 5000003
*
*       Now run "exec sp_fix_idgap_invoices 10031", and the last two
*       invoices will be corrected. Also, the next invoice will 
*       receive number 10034.
*/
if object_id("sp_fix_idgap_invoices") !=  NULL
begin
   drop procedure sp_fix_idgap_invoices
end
go

create procedure sp_fix_idgap_invoices
/* Copyright (c) 1998 Rob Verschoor/Sypron B.V. */
  @p_gap_low numeric(10,0)
as
begin
declare @gap_low  numeric(10,0)
declare @gap_low_plus_1  numeric(10,0)
declare @gap_high numeric(10,0)
declare @gap_size numeric(10,0)
declare @gap_max  numeric(10,0)
declare @set_new_idval  numeric(10,0)
declare @rows  int
declare @error  int

  if (@p_gap_low != -1) and (@p_gap_low < 1)
  begin
     print "Invalid invoice number specified."
     print "You must specify the last correct invoice number before the identity gap."
     return (-1)
  end

  /*
   * First check identity_insert is not active for any
   * other table: there can be only one at a time
   * if another table is already in this mode, the
   * procedure will be aborted here and user must switch off
   * identity_insert on that other table before continuing.
   */
  set identity_insert invoices_keytable on 
  set identity_insert invoices_keytable off
 
  if @p_gap_low > 0
  begin
      /*
       * check the row at lower end of gap exists
       */
     select @gap_low = invoice_nr
     from invoices 
     where invoice_nr = @p_gap_low     
 
     select @error = @@error, @rows = @@rowcount     

     if @error != 0
     begin
        print "Error occurred"
        return (-1)
     end
 
     if @rows = 0
     begin
        print "Could not find invoice number specified."
        print "You must specify the last correct invoice number before the identity gap."
        return (-1)
     end
  
     select @gap_low_plus_1 = @gap_low + 1
 
     /*
      * determine the size of the identity gap
      */
     select @gap_high = min(invoice_nr)
     from invoices
     where invoice_nr > @gap_low
 
     if @@rowcount = 0 or @@error != 0
     begin
        print "Row at upper end not found or error occurred"
        return (-1)
     end
 
     /*
      * calculate size of gap; must be > 99 to be
      * considered a real identity gap(this is
      * an arbitrary limit). Small 'gaplets' of
      * non-existing numbers can always exists due to
      * rolled-back transactions
      */
     select @gap_size = @gap_high - @gap_low
    
     if @gap_size < 100
     begin
       

[1] [2]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18