熟悉仓库管理系统的人会比较清楚,系统中统有各种各样的单据,如补货单 、进货单、出库单等等。在这里,我们先介绍一种单据编码定义规则:
[仓库编码][单据类型][YYMMDD][4位流水号]
如:0101JH1202030001:
仓库编码:0101
单据类型:JH(进货)
生成日期:120203(12年02月03日)
四位流水:0001
上述规则是一种比较简单的编码方式,要注意的是上述的流水号在仓库、单据和日期之间不共享,即是说不同的仓库、不同的单据每天的流水号都是从1开始。
首先,我们采用一个表NoType,用来记录系统中的各种单据类型。
序号 | 列名 | 数据类型 | 长度 | 主键 | 允许空 | 默认值 | 说明 |
1 | NoType | varchar | 10 | 是 | 否 |
| 单据类型 |
2 | NoTypeName | nvarchar | 50 |
| 否 |
| 单据名称 |
然后,我们使用另一个表NoGenerate来记录每个仓库每种单据当日的最后生成的流水号。
序号 | 列名 | 数据类型 | 长度 | 主键 | 允许空 | 默认值 | 说明 |
1 | Date | nchar | 10 | 是 | 否 |
| 日期 |
2 | NoType | varchar | 10 | 是 | 否 |
| 类型 |
3 | HouseCode | varchar | 20 | 是 | 否 |
| 库房编号 |
4 | LatestNo | int | 4 | 是 | 否 | ((1)) | 最后流水号 |
使用Update更新方式
@NoType VARCHAR( 10),
@HouseCode VARCHAR( 20),
@length INT,
@code VARCHAR( 200) OUTPUT
AS
-- 判断号码类型是否合法
IF NOT EXISTS ( SELECT 0
FROM NoType(NOLOCK)
WHERE NoType = @NoType )
BEGIN
SET @code = ' 0 '
RETURN
END
DECLARE @LatestNo INT
DECLARE @date NCHAR( 10)
SET @date = CONVERT( NCHAR( 10), GETDATE(), 120)
UPDATE NoGenerate
SET @LatestNo =LatestNo =LatestNo + 1
WHERE Date = @date
AND NoType = @NoType
AND HouseCode = @HouseCode
IF ( @@rowcount = 0)
BEGIN
INSERT INTO NoGenerate (Date,NoType,HouseCode)
VALUES ( @Date, @NoType, @HouseCode)
SET @latestNo = 1
END
SET @code =dbo.NumberAddZeroPre( @LatestNo, @length)
SET @code = @HouseCode + @NoType + SUBSTRING( REPLACE( @date, ' - ', ''), 3, 6) + @code
其中,函数NumberAddZeroPre用来生成指定长度的流水号。
RETURNS VARCHAR( 20)
AS
BEGIN
DECLARE @str VARCHAR( 20)
SET @str = CAST( @num AS VARCHAR)
DECLARE @curLen INT
SET @curLen = LEN( @str)
WHILE ( @curLen < @len)
BEGIN
SET @str = ' 0 ' + @str
SET @curLen = @curLen + 1
END
RETURN @str
END
使用Update方式进行单据号获取,足以满足大部情况的需求,但是在数据量和并发量大到一定程序的时候,会产生大量的数据锁,严重影响到系统性能,这时候,我们可以另一种解决方案,使用Insert方式实现流水号获取。
使用Insert插入方式
新建一表,定义主键时加上with(ignore_dup_key=on),忽略重复的记录。
2 (
3 Date NCHAR( 10) NOT NULL,
4 NoType VARCHAR( 10) NOT NULL,
5 HouseCode VARCHAR( 20) NOT NULL,
6 LatestNo INT NOT NULL DEFAULT 1,
7 CONSTRAINT PK_NOGENERATE PRIMARY KEY (Date,NoType,HouseCode,LatestNo)
8 )
9 Go
新建一存储过程,采用插入的方式得到递增的流水号。
@NoType varchar( 10),
@HouseCode varchar( 20),
@length int,
@code varchar( 200) output
AS
-- 判断号码类型是否合法
if not exists( select 0 from NoType(nolock) where NoType = @NoType)
begin
set @code = ' 0 '
return
end
-- 当前日期、最大流水号、是否采号成功(:成功,:未成功)
declare @date int = cast( Convert( nchar( 8), GetDate(), 112) as int)
declare @dateS nchar( 10) = Convert( nchar( 10), GetDate(), 120)
declare @LatestNo int
declare @LatestNoOld int
declare @LatestDateForDel int = 0
declare @LatestNoForDel int = 0
-- 删除前一天的数据
if (( select count( 1) from NoGenerateEx(nolock) where NoType = @NoType and HouseCode = @HouseCode) > 1500)
begin
select @LatestDateForDel = T2.intDate, @LatestNoForDel = T2.LatestNo from (
select top 1 T1. * from (
select top 1500 nge.intDate,nge.LatestNo from NoGenerateEx nge(nolock)
where NoType = @NoType and HouseCode = @HouseCode
order by nge.intDate,nge.LatestNo
) T1 order by t1.intDate desc, T1.LatestNo desc
) T2
delete from NoGenerateEx with(rowlock,xlock) where [ intDate ] < @LatestDateForDel AND NoType = @NoType and HouseCode = @HouseCode
delete from NoGenerateEx with(rowlock,xlock) where [ intDate ] = @LatestDateForDel and LatestNo < @LatestNoForDel AND NoType = @NoType and HouseCode = @HouseCode
end
declare @insertRe smallint = 0
while( @insertRe = 0)
BEGIN
set @LatestNo = isnull(( select max(LatestNo) from NoGenerateEx(nolock)
where [ intDate ] = @date and NoType = @NoType and HouseCode = @HouseCode), 0) + 1
-- 插入新号
insert into NoGenerateEx( [ intDate ],NoType,HouseCode,LatestNo) values( @date, @NoType, @HouseCode, @LatestNo)
set @insertRe = @@ROWCOUNT
end
-- 返回值
set @code = @HouseCode + @NoType +right( CAST( @date as varchar( 8)), 6) +dbo.NumberAddZeroPre( @LatestNo, @length)
采用此方式生成单据编码,与Update方式相比,会产生大量的数据,所以在存储过程中,需要不断的进行数据的清除,以保证性能,但是在并发比较高的情况下,优点也很明显,由于采用了Insert操作,所以不会出现锁表的现象。
指定长度流水号生成参考:http://www.cnblogs.com/wych/archive/2009/08/06/1540302.html