Menu
Woocommerce Menu

当然使用GUID就另当别论了3522vip靠谱吗:

0 Comment


 1 Create procedure [dbo].[GetSerialNo]   2 (   3  @sCode varchar(50)   4 )   5  6  as  7  8 --exec GetSerialNo   9 10 begin 11 12  Declare @sValue varchar(16),  13 14      @dToday  datetime,      15 16      @sQZ varchar(50) --这个代表前缀 17 18  Begin Tran   19 20  Begin Try  21 22   -- 锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了 23   --在同一个事物中,执行了update语句之后就会启动锁 24   Update SerialNo set sValue=sValue where sCode=@sCode  25 26   Select @sValue = sValue From SerialNo where sCode=@sCode  27 28   Select @sQZ = sQZ From SerialNo where sCode=@sCode  29 30   -- 因子表中没有记录,插入初始值--基数据必须手动去创建  31 32   If @sValue is null  33 34   Begin 35 36    Select @sValue = convert(bigint, convert(varchar(6), getdate(), 12) + '000001')  37 38    Update SerialNo set sValue=@sValue where sCode=@sCode  39 40   end41   42  else  43 44   Begin        --因子表有记录  45 46    Select @dToday = substring(@sValue,1,6)  47 48    --如果日期相等,则加1  49 50    If @dToday = convert(varchar(6), getdate(), 12)  51 52     Select @sValue = convert(varchar(16), (convert(bigint, @sValue) + 1))  53 54    else       --如果日期不相等,则先赋值日期,流水号从1开始  55 56     Select @sValue = convert(bigint, convert(varchar(6), getdate(), 12) +'000001')  57 58      59 60    Update SerialNo set sValue =@sValue where sCode=@sCode  61 62   End 63 64   Select result = @sQZ+@sValue   65 66   Commit Tran  67 68  End Try  69 70  Begin Catch  71 72   Rollback Tran  73 74   Select result = 'Error' 75 76  End Catch  77 78 end 

USE[shouyinxitong]GO/******Object:Table[dbo].[t_user]ScriptDate:07/08/201614:40:36******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[t_user]([yonghuming][varchar](10)NOTNULL,[mima][varchar](10)NOTNULL,[quanxian][int]NOTNULL,CONSTRAINT[PK_user]PRIMARYKEYCLUSTERED([yonghuming]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******Object:Table[dbo].[shopping]ScriptDate:07/08/201614:40:36******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[shopping]([bianhao][varchar](6)NOTNULL,[danhao][int]NOTNULL,[huiyuanhao][varchar](6)NULL,[shuliang][int]NULL,[danjia][float]NULL,[riqi][date]NULL,[yonghuming][varchar](10)NULL,[leibie][varchar](10)NULL,[shifu][float]NULL,PRIMARYKEYCLUSTERED([bianhao]ASC,[danhao]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******Object:Table[dbo].[necess]ScriptDate:07/08/201614:40:36******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGOFFGOCREATETABLE[dbo].[necess]([bianhao][varchar](6)NOTNULL,[mingzi][varchar](20)NOTNULL,[danjia][float]NOTNULL,[shuliang][int]NOTNULL,[jcrq][date]NULL,[scrq][date]NOTNULL,[baoziqi][int]NULL,CONSTRAINT[PK_necess]PRIMARYKEYCLUSTERED([bianhao]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******Object:Table[dbo].[member]ScriptDate:07/08/201614:40:36******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[member]([huiyuanhao][varchar](6)NOTNULL,[xingming][varchar](8)NOTNULL,[jifen][int]NULL,[dianhua][varchar](11)NULL,[zhuzi][varchar](20)NULL,CONSTRAINT[PK_member]PRIMARYKEYCLUSTERED([huiyuanhao]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******Object:Table[dbo].[food]ScriptDate:07/08/201614:40:36******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLE[dbo].[food]([bianhao][varchar](6)NOTNULL,[mingzi][varchar](20)NOTNULL,[danjia][float]NOTNULL,[shuliang][int]NOTNULL,[jcrq][date]NULL,[scrq][date]NOTNULL,[baoziqi][int]NULL,CONSTRAINT[PK_food]PRIMARYKEYCLUSTERED([bianhao]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******Object:Table[dbo].[drink]ScriptDate:07/08/201614:40:36******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGOFFGOCREATETABLE[dbo].[drink]([bianhao][varchar](6)NOTNULL,[mingzi][varchar](20)NOTNULL,[danjia][float]NOTNULL,[shuliang][int]NOTNULL,[jcrq][date]NULL,[scrq][date]NOTNULL,[baoziqi][int]NULL,CONSTRAINT[PK_drink]PRIMARYKEYCLUSTERED([bianhao]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO/******Object:Table[dbo].[cosmetic]ScriptDate:07/08/201614:40:36******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGOFFGOCREATETABLE[dbo].[cosmetic]([bianhao][varchar](6)NOTNULL,[mingzi][varchar](20)NOTNULL,[danjia][float]NOTNULL,[shuliang][int]NOTNULL,[jcrq][date]NULL,[scrq][date]NOTNULL,[baoziqi][int]NULL,CONSTRAINT[PK_odd]PRIMARYKEYCLUSTERED([bianhao]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOSETANSI_PADDINGOFFGO

结果:CUS150413000001

此方法可用作生成流水号,使用update运营数据库锁,并发不会再次,可读性相比较好,当然使用GUID就另当别论了。

CREATE TABLE [dbo].[SerialNo](
[sCode] [varchar](50) NOT NULL,
[sName] [varchar](100) NULL,
[sQZ] [varchar](50) NULL,
[sValue] [varchar](80) NULL,
CONSTRAINT [PK_SerialNo] PRIMARY KEY CLUSTERED
(
[sCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

3522vip靠谱吗 1

基数表-用来存款和储蓄编号前缀和花色

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图