T-SQL 中的灵活编号序列

• 下载源文件 - 1.15 KB

引言

通常，应用程序需要一种可靠的方式来生成唯一编号。例如发票编号、订单编号和捐款收据编号。这些编号序列通常包含非数字部分，例如在线捐款收据编号“WD-00000121”。

背景

在“过去”（当我们使用纸张时），有预印有数字序列的纸张，例如银行账户支票。当你撕下最上面的一张（或支票）时，你实际上就声明了该序列号——你将其从纸张上取下，其他人就无法使用它。在这个灵活的 SQL 解决方案中，当我们获取序列中的下一个编号时，我们本质上也在做同样的事情。

在应用程序中有几种方法可以解决这个问题。我不知道所有的方法，但我见过一些好的方法。在这篇文章中，我将展示一种简单的方法和一种更灵活的方法。

一个注意事项

虽然本文的代码是为 Microsoft SQL Server TSQL 编写的，但此技术可应用于大多数现代关系数据库。例如，Oracle 有一个名为“Sequence”的功能，它很好但本文未涵盖（我曾使用过 Oracle 数据库产品，并非常尊重它，但最近，我所有的工作都在 MS SQL Server 中）。

简单解决方案

一个简单的解决方案是插入到一个具有 `IDENTITY` 列定义的表中，SQL Server 将自动为你的新记录分配序列中的下一个编号，例如：

--This is partial code, do not try to execute this snippet of code it is just for 
illustration purposes and is not part of the 'flexible solution':

CREATE TABLE WebDonation (
       DonationNumber INT NOT NULL IDENTITY(1,1),
       DonationDate DATETIME NOT NULL DEFAULT(GETDATE()),
  …
)

此序列的优点是它是自动的、易于实现的，并且由 SQL Server 保证唯一。我本身并不反对使用 `IDENTITY` 列；虽然我在很多有意义的地方都使用过它们，但我更倾向于在可能的情况下使用有意义的、人类可读的、与人类相关的键。

使用 `IDENTITY` 列的缺点是，应用程序的管理员无法控制编号，而且这是一个对用户没有意义的内部编号。

另外，例如，如果应用程序要求在线捐款收据编号每月从 00001 开始，那么 `identity` 列将难以使用；例如，2009 年 2 月的捐款将从“W-200902-00001”开始，2009 年 3 月的捐款将从“W-200903-00001”开始。

灵活的解决方案

一个更灵活的解决方案是将实际序列保存在其使用所在的表之外。还需要在某处存储序列的一些元数据。一些应用程序的另一个要求是它们需要多个序列或类型的序列。

为此，创建一个如下表：

CREATE TABLE SequenceControl (
        SequenceKey      VARCHAR(25) NOT NULL PRIMARY KEY CLUSTERED,
        LastSequence     INT NOT NULL 
                         CHECK ( LastSequence<2000000000 AND LastSequence > -1),
        SequenceFormat   VARCHAR(20) NOT NULL DEFAULT('[#]')
                         CHECK ( CHARINDEX( '[#]', SequenceFormat)>0),
        ZeroPadToDigits  INT NOT NULL DEFAULT(0) 
                         CHECK ( ZeroPadToDigits>-1 AND ZeroPadToDigits<11),
        IncrementBy      INT NOT NULL DEFAULT(1) CHECK ( IncrementBy>0),
        LongDescription  VARCHAR(200) NULL
)

首先，我想指出该表（上方）的 DDL 包含我应用的一些业务规则，例如：

• 序列号不能小于零
• 序列号不能大于二十亿，并且
• 最终输出长度最多为 20-3+10=27 个字符（`[#]`，存储过程将其 `CAST` 为 `VARCHAR(30)`）。

你的需求可能不同，因此你需要对表的 DDL 和存储过程进行相应的调整。

SequenceControl 表说明

第一个列“SequenceKey”是序列的唯一标识符；此表可以存储任何数量序列的定义。

列“LastSequence”保存已使用的最后一个序列号。在存储过程中，它会在计算并声明下一个编号时递增。要将序列从 1 开始（以 1 递增），初始值将是零。

列“SequenceFormat”包含我们希望此序列最终看起来的模式。它可以包含多个可替换的标签。必须包含 `[#]` 标签，因为这将放置递增的整数。其他可能的标签可以添加日期和时间部分或 SQL 用户。

列“ZeroPadToDigits”告诉存储过程要使递增数字达到多少位数，并在左侧用零填充；例如，一个八位零填充序列将是“00000001”、“00000002”、“00000003”等。

以在线捐款收据为例，我们将如下开始我们的序列：

INSERT INTO SequenceControl ( SequenceKey, LastSequence, SequenceFormat, ZeroPadToDigits)
VALUES ( 'online donation', 0, 'WD-[#]', 8)

这将生成序列中的第一个捐款收据：“WD-00000001”。

存储过程代码是：

SET QUOTED_IDENTIFIER OFF

GO 
SET ANSI_NULLS OFF
GO 

CREATE PROCEDURE DBO.p_GetNextInSequence 
    @SequenceKey VARCHAR(20) -- e.g. 'online donation'
AS 
BEGIN 
SET NOCOUNT ON 
DECLARE @LASTNUMBER INT
DECLARE @NEXTNUMBER INT
DECLARE @FORMATTEDNUMBER VARCHAR(40)
DECLARE @MYKEY VARCHAR(30)
--if the sequence does not exist, we return a '' and zero
IF NOT EXISTS(SELECT 'X' 
              FROM SequenceControl 
              WHERE SequenceControl.SequenceKey = @SequenceKey )
BEGIN 
    SELECT    @SequenceKey AS SequenceKey, 
              CAST('' AS VARCHAR(30)) AS NextSequenceFormatted, 
              CAST(0 AS INT) AS NextSequenceInt 
    RETURN 
END 

DECLARE @SEQFORMAT VARCHAR(30), @IncrementBy INT, 
                   @IncrementStep INT, @zeropadtodigits INT

SELECT @SEQFORMAT = RTRIM(LTRIM(SequenceFormat)),
       @IncrementBy = IncrementBy, 
       @zeropadtodigits = ZeroPadToDigits 
FROM    SequenceControl 
WHERE    SequenceControl.SequenceKey = @SequenceKey 

DECLARE @THEROWCOUNT INT 
SET @THEROWCOUNT    = 0 

BEGIN TRANSACTION T1
    WHILE ( @THEROWCOUNT =0 ) 
    BEGIN 
        SELECT   @LASTNUMBER= LastSequence 
        FROM     SequenceControl
        WHERE    SequenceControl.SequenceKey= @SequenceKey 

        UPDATE     SequenceControl 
        SET        LastSequence = @LASTNUMBER + @IncrementBy 
        WHERE      SequenceKey = @SequenceKey AND    
                   LastSequence = @LASTNUMBER --this guarantees that no one has
                                              --updated it in the meantime 

        SELECT @THEROWCOUNT = @@ROWCOUNT
        --if its zero, then we need to get the next number after that and try again 
    END 
COMMIT TRANSACTION T1 

--here, we format the number according to the pattern for this sequence 
DECLARE @FMTNUM VARCHAR(20) 
SET @NEXTNUMBER = @LASTNUMBER + @IncrementBy 
IF ( @zeropadtodigits>0) 
    SET @FMTNUM = RIGHT( REPLICATE('0', @zeropadtodigits) + 
                    CAST(@NEXTNUMBER AS VARCHAR(20)), @zeropadtodigits) 
ELSE 
    SET @FMTNUM = CAST(@NEXTNUMBER AS VARCHAR(10)) 


SET @FORMATTEDNUMBER = REPLACE(@SEQFORMAT,'[#]', @FMTNUM ) 

SELECT    @SequenceKey AS SequenceKey, 
          CAST(@FORMATTEDNUMBER AS VARCHAR(30)) AS NextSequenceFormatted, 
          @NEXTNUMBER AS NextSequenceInt 

END 

GO 
SET QUOTED_IDENTIFIER OFF
GO 
SET ANSI_NULLS ON    
GO

如果我们每달需要生成一个新的序列，我们将对其进行编码，尝试将该月（例如，2009 年 2 月）的序列插入到 `SequenceControl` 表中，然后继续使用它。

INSERT INTO SequenceControl ( SequenceKey, LastSequence, SequenceFormat, ZeroPadToDigits)
VALUES ( 'donations 2009 02', 0, 'WD-200902-[#]', 8)

对于此示例（每月，一个从 `00001` 开始的序列），由于主键冲突而导致 `INSERT` 失败与否无关紧要；无论哪种情况，该键都会在表中，我们将继续使用它。

EXEC DBO.P_GetNextInSequence 'donations 2009 02';

关注点

现在很容易创建一个 UI 来管理序列；一个 UI 可以用于维护应用程序中的所有序列。管理人员可以选择从哪个数字开始。

存储过程也可以重构为一个数据库函数（UDF），该函数仅返回最终格式化的 `varchar` 序列号。如果你需要在 SQL 代码中声明下一个编号（例如，如果你有一个存储过程来添加新的捐款，而不是从 [你最喜欢的编程语言] 代码中添加），这将非常有用。

在 UDF 内部，不能对数据库进行更改，因此此存储过程不能用作数据库用户定义的函数。显然，有解决此问题的方法，但正如解决方法一样，为了在 SQL Server 中绕过此规则，出于多种原因，这很可能是一个坏主意。感谢 **drbarriesmith** 提出一个好问题。