流水号 Ver2
以前曾写了一个流水号的实现功能,它是在SQL中处理的。可以参考:http://www.cnblogs.com/insus/articles/1702583.html
这段时间开发,又涉及到使用流水号功能。不同的条件,使用二种前缀。后面的流水号还是要流水的功能,要求再加上位数已经有所变化了。为了解决此问题,只有添加另外一个自定义函数在sql中。改写如下,可参考:
SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ dbo ] . [ udf_SeriesNumber ]
(
@Number NVARCHAR ( 12 ), -- 原来的值
@Digit INT -- 流水号的位数
)
RETURNS NVARCHAR ( 100 )
AS
BEGIN
DECLARE @ReturnValue NVARCHAR ( 100 )
IF ( @Number IS NULL )
SET @ReturnValue = REPLICATE ( ' 0 ' , @Digit - 1 ) + ' 1 '
ELSE
BEGIN
DECLARE @nbr NVARCHAR ( 6 )
SET @nbr = CONVERT ( NVARCHAR ,( CONVERT ( INT , RIGHT ( @Number , @Digit )) + 1 ))
SET @nbr = REPLICATE ( ' 0 ' , @Digit - LEN ( @nbr )) + @nbr
SET @ReturnValue = @nbr
END
RETURN @ReturnValue
END
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ dbo ] . [ udf_SeriesNumber ]
(
@Number NVARCHAR ( 12 ), -- 原来的值
@Digit INT -- 流水号的位数
)
RETURNS NVARCHAR ( 100 )
AS
BEGIN
DECLARE @ReturnValue NVARCHAR ( 100 )
IF ( @Number IS NULL )
SET @ReturnValue = REPLICATE ( ' 0 ' , @Digit - 1 ) + ' 1 '
ELSE
BEGIN
DECLARE @nbr NVARCHAR ( 6 )
SET @nbr = CONVERT ( NVARCHAR ,( CONVERT ( INT , RIGHT ( @Number , @Digit )) + 1 ))
SET @nbr = REPLICATE ( ' 0 ' , @Digit - LEN ( @nbr )) + @nbr
SET @ReturnValue = @nbr
END
RETURN @ReturnValue
END
接下来,就可以实现会议室预的的会议流水号功能,也许以后又来一个流水号的,还是使用上面的自定义函数。
有好几个会议室,而第一个会议室是一个虚拟会议室,只能作为测试使用,其余的会议室全作为正式的会议室供预定。这些预定的出来的流水号需要区分,虚拟的会议流水号如下:#000001,#000002,#000003,...,#999999。而正式的会议流水号如:M000001,M000002,M000003,...,M999999。
下面的程序是产生流水号的实现功能:
SET
ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ dbo ] . [ udf_GetMeetingNumber ]
(
@MeetingRoomId SMALLINT
)
RETURNS NVARCHAR ( 20 )
AS
BEGIN
-- 宣告两个变量,一个是流水号的前缀,初始化其值。另个是将保存最新的会议号码。
DECLARE @Prefixion NVARCHAR ( 2 ) = ' M ' , @LastMeetingNumber NVARCHAR ( 20 )
IF ( @MeetingRoomId = 1 )
BEGIN
-- 如果是虚拟的会议室,去会议室预定的记录表,找到最新的会议号码
SELECT TOP 1 @LastMeetingNumber = [ Meeting_Number ] FROM [ dbo ] . [ MeetingRoomBooking ] WHERE [ MeetingRoomId ] = @MeetingRoomId ORDER BY [ MeetingRoomBookingId ] DESC
-- 设定前缀
SET @Prefixion = ' # '
END
ELSE
-- 如果是其它的会议室,找到最新的会议号码
SELECT TOP 1 @LastMeetingNumber = [ Meeting_Number ] FROM [ dbo ] . [ MeetingRoomBooking ] WHERE [ MeetingRoomId ] <> 1 ORDER BY [ MeetingRoomBookingId ] DESC
-- 结合上面的流水号产生的自定义函数,与前缀组合,得到流水号。产生6位的流水号
RETURN @Prefixion + [ dbo ] . [ udf_SeriesNumber ] ( @LastMeetingNumber , 6 )
END
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [ dbo ] . [ udf_GetMeetingNumber ]
(
@MeetingRoomId SMALLINT
)
RETURNS NVARCHAR ( 20 )
AS
BEGIN
-- 宣告两个变量,一个是流水号的前缀,初始化其值。另个是将保存最新的会议号码。
DECLARE @Prefixion NVARCHAR ( 2 ) = ' M ' , @LastMeetingNumber NVARCHAR ( 20 )
IF ( @MeetingRoomId = 1 )
BEGIN
-- 如果是虚拟的会议室,去会议室预定的记录表,找到最新的会议号码
SELECT TOP 1 @LastMeetingNumber = [ Meeting_Number ] FROM [ dbo ] . [ MeetingRoomBooking ] WHERE [ MeetingRoomId ] = @MeetingRoomId ORDER BY [ MeetingRoomBookingId ] DESC
-- 设定前缀
SET @Prefixion = ' # '
END
ELSE
-- 如果是其它的会议室,找到最新的会议号码
SELECT TOP 1 @LastMeetingNumber = [ Meeting_Number ] FROM [ dbo ] . [ MeetingRoomBooking ] WHERE [ MeetingRoomId ] <> 1 ORDER BY [ MeetingRoomBookingId ] DESC
-- 结合上面的流水号产生的自定义函数,与前缀组合,得到流水号。产生6位的流水号
RETURN @Prefixion + [ dbo ] . [ udf_SeriesNumber ] ( @LastMeetingNumber , 6 )
END