应用,自动生成编号,如何解决并发问题。
外贸的订单信息,每添加一条自动生成一个编号 如 S14100066。
//设计表,没加什么约束之类的东西。
CREATE TABLE [dbo].[Order](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderNum] [varchar](16) NOT NULL,
[OrderPO] [varchar](16) NOT NULL,
[OrderType] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[CreTime] [date] NOT NULL,
[ModTime] [date] NOT NULL,
[PriceType] [tinyint] NOT NULL,
[Season] [varchar](12) NULL,
[ShipDate] [date] NULL,
[OrderSta] [int] NOT NULL,
[OrderShipSta] [int] NOT NULL,
[DeptID] [int] NOT NULL,
[LogID] [int] NOT NULL,
[DetailCount] [int] NOT NULL,
[InitPrice] [decimal](16, 4) NOT NULL,
[Cost] [decimal](16, 4) NOT NULL,
[RePrice] [decimal](16, 4) NOT NULL,
[ReCost] [decimal](16, 4) NOT NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
自己随便写了个存储过程,如下。没加防并发的设置,和一些专业设置。以前没用过锁表,事物那些操作。
ALTER PROCEDURE [dbo].[Order_Insert]
@OrderPO varchar(16),
@OrderType int,
@CustomerID int,
@PriceType tinyint,
@ShipDate date,
@DeptID int,
@LogID int,
@Season varchar(12),
@Custm varchar(2)
AS
--编号
declare @OrderNumber varchar(14)
declare @Number varchar(4)
declare @OrType varchar(1)
--先取日期 + 组名(如 10)
set @OrderNumber = convert(varchar(2),getdate(),2) + @Custm
--取最后一个序号,然后 +1 生成序号
set @Number = (select top 1 right(convert(varchar(5),Convert(int,SubString(OrderNum,6,4))+10001),4) as newSS
from [Order] where SubString(OrderNum,4,2) = @Custm
and SubString(OrderNum,2,2) = convert(varchar(2),getdate(),2)
order by OrderID desc)
if (@Number is null)
begin
set @Number='0001'
end
set @OrderNumber = 'S' + @OrderNumber + @Number -- + @OrType
INSERT INTO [Order](
[OrderNum],[OrderPO],[OrderType],[CustomerID],[CreTime],[ModTime],[PriceType],[ShipDate],[DeptID],[LogID],[Season]
)VALUES(
@OrderNumber,@OrderPO,@OrderType,@CustomerID,GETDATE(),GETDATE(),@PriceType,@ShipDate,@DeptID,@LogID,@Season
)
Select Top 1 OrderID,OrderNum From [Order] where OrderID = @@IDENTITY
高人能否帮忙写一个防止并发防止重复订单号的存储过程。谢谢指点。