【无标题】Sql Server下实现类似Orcal的序号功能

目的：需要在2个流程上使用相同的流水号作为编号，且2个流程的编号不能重复。

所以使用了一下方法实现序号功能。

注：GUID只能实现不重复，没办法用于编号规则(如：INT0001,INT0002,...)

        

1. 执行下面脚本，创建cm_sequence表和Proc_NextNum存储过程。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cm_sequence](
	[c_name] [varchar](50) NOT NULL,
	[current_value] [bigint] NOT NULL,
	[increment_value] [int] NOT NULL,
 CONSTRAINT [PK_cm_sequence] PRIMARY KEY CLUSTERED 
(
	[c_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_cm_sequence] ON [dbo].[cm_sequence]
(
	[c_name] ASC,
	[current_value] ASC,
	[increment_value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cm_sequence] ADD  CONSTRAINT [DF_cm_sequence_increment_value]  DEFAULT ((1)) FOR [increment_value]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Proc_NextNum] 
	@seq_name	VARCHAR(50)
AS
BEGIN
	DECLARE @c_value INTEGER 
	SET @c_value=0
	SELECT @c_value=current_value FROM cm_sequence WHERE c_name = @seq_name
	UPDATE dbo.cm_sequence SET current_value=@c_value+increment_value WHERE c_name = @seq_name
	SELECT @c_value=current_value FROM cm_sequence WHERE c_name = @seq_name
	RETURN @c_value 
END
GO

2.在cm_sequence表中增加一条记录

 3. 执行存储过程：

DECLARE        @return_value int
EXEC @return_value = [dbo].[Proc_NextNum] @seq_name ='HRMSerialNum'
select @return_value