借助数据库生成订单号

借助数据库生成订单号

平时在项目开发中，遇到自己生成订单号的情况很常见，下面就介绍一种借助数据库生成订单号的方法。

在数据库中创建IDPool表

USE [数据库名称]
GO

/****** Object:  Table [dbo].[IDPool]    Script Date: 2018/3/13 16:48:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[IDPool](
    [OID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [LeaseholderID] [int] NOT NULL,
    [DbName] [varchar](64) NOT NULL,
    [TableName] [varchar](64) NOT NULL,
    [MaxValue] [int] NULL,
    [Prefix] [varchar](64) NULL,
    [IDType] [smallint] NOT NULL,
    [InDate] [datetime] NOT NULL,
 CONSTRAINT [PK_IDPool] PRIMARY KEY CLUSTERED 
(
    [OID] 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 OFF
GO

ALTER TABLE [dbo].[IDPool] ADD  CONSTRAINT [DF_IDPool_LeaseholderID]  DEFAULT ((1)) FOR [LeaseholderID]
GO

ALTER TABLE [dbo].[IDPool] ADD  CONSTRAINT [DF_IDPool_IDType]  DEFAULT ((0)) FOR [IDType]
GO

ALTER TABLE [dbo].[IDPool] ADD  CONSTRAINT [DF_IDPool_InDate]  DEFAULT (getdate()) FOR [InDate]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IDPool', @level2type=N'COLUMN',@level2name=N'OID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'库名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IDPool', @level2type=N'COLUMN',@level2name=N'DbName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'表名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IDPool', @level2type=N'COLUMN',@level2name=N'TableName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IDPool', @level2type=N'COLUMN',@level2name=N'MaxValue'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自递增ID的池子' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IDPool'
GO

新建存储过程

USE [数据库名称]
GO

/****** Object:  StoredProcedure [dbo].[UP_GetID]    Script Date: 2018/3/13 16:53:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[UP_GetID] --1,'数据库名称','订单表名称','F',4,1,4,1
@LeaseholderID INT,
@DbName varchar(64),--数据库名
@TableName varchar(64),--表名
@Prefix varchar(10)='',--前缀
@Type smallint=0,      --类型 -0:前缀+ID, 1:前缀+年（两位）月日+ID,2:前缀+年（两位）月+ID,3:前缀+年（两位）+ID
@BeginNum int=1,          --开始
@NumLen int=0,          --补零 0\1不补0,
@ID varchar(100) output
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MaxValue int,@i int
SET @ID=''


DECLARE @MaxTab table(MaxValue int);

Merge dbo.IDPool as a
Using(
select
    TableName=@TableName ,
    DbName=@DbName,
    Prefix=@Prefix,
    Indate_Start = CASE @type
                  WHEN 0 THEN 0
                  WHEN 1 THEN DATEDIFF(DAY, 0, GETDATE())
                  WHEN 2 THEN DATEDIFF(DAY, DAY(GETDATE()) - 1, GETDATE())
                  WHEN 3 THEN DATEADD(YEAR, YEAR(GETDATE()) - 1900, 0)
  WHEN 4 THEN CONVERT(VARCHAR(16),  DATEADD(MINUTE, 0, GETDATE()), 120 )
              END,
    Indate_Stop = CASE @type
                  WHEN 0 THEN CONVERT(datetime, '9999-12-31')
                  WHEN 1 THEN DATEDIFF(DAY, -1, GETDATE())
                  WHEN 2 THEN DATEADD(Month, 1, DATEDIFF(DAY, DAY(GETDATE()) - 1, GETDATE()))
                  WHEN 3 THEN DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)
  WHEN 4 THEN CONVERT(VARCHAR(16),  DATEADD(MINUTE, 1, GETDATE()), 120 )

              END
) as b 
on a.TableName = b.TableName and a.DbName =b.DbName and a.Prefix=b.Prefix
and a.indate >= b.Indate_Start and a.indate < b.Indate_Stop 
WHEN MATCHED THEN
UPdate 
SET MaxValue=MaxValue+1
WHEN NOT MATCHED THEN
INSERT ([DbName],[TableName],[Prefix],idtype,[InDate],[MaxValue])

VALUES(@DbName,@TableName,@Prefix,@type,CONVERT(VARCHAR(16), getdate(), 120 ),@BeginNum) 
OUTPUT inserted.MaxValue into @MaxTab
; 

select @MaxValue=MaxValue from @MaxTab
;

--PRINT @MaxValue
IF @NumLen>0 AND LEN(CAST(@MaxValue AS varchar))<=@NumLen
BEGIN
    SET @i=0
    WHILE @i<@NumLen
    BEGIN
        SET @ID=@ID+'0'
        set @i=@i+1
    END
    print(@id)
    SET @ID=RIGHT(@ID+lTRIM(STR(@MaxValue)),@NumLen)
END
ELSE
    SET @ID=lTRIM(STR(@MaxValue))
--print(@id)
SET @ID=@Prefix+ CASE 
WHEN @type=1
THEN SUBSTRING(CONVERT(VARCHAR(100),GETDATE(),112),3,6)
WHEN @type=2
THEN SUBSTRING(CONVERT(VARCHAR(100),GETDATE(),112),3,4)
WHEN @type=3
THEN SUBSTRING(CONVERT(VARCHAR(100),GETDATE(),112),3,2)
WHEN @type=4
THEN  SUBSTRING(replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''),3,12) 
ELSE
''
                END
            +@ID
select @ID
RETURN @MaxValue
END
GO