最近的工作中要用到流水号,而且业务单据流水号生成的规则分好几种,并非以前那种千篇一律的前缀+日期+流水号的简单形式,经过对业务的分析,以及参考网上程序员的N种方法,整理出了一个表结构和存储过程
思路分析:
1.大体上,流水号都遵循近似这样的一般规则:流水号 = 前缀+动态内容+日期+中缀+流水号+后缀
这里的动态内容是通过参数传入流水号生成存储过程的,比如动态内容为科室+组别,这个科室和组别是动态的,每一张单可能都不同,是根据登录人组织信息得到的
2.流水号生成要控制并发
并发的话,网上资料大多都是加锁提示,比如holdlock,xlock,rowlock等等,但其它人又说最好不要加提示,锁提示,索引提示能不加就不加,因为这个数据库可以自动判断,比如是锁定整张表,还是锁定某几行等等,所以我直接设定了事务级别,我还是没怎么搞清楚到底设置为repeatable read 还是 serializable,经过分析,个人认为还是设定为serializable,即串行化比较保险,相当于所有事务都按队列进行,总是有排序的,但缺点是性能很差,当然也要看情况,一天几张单,几十张单,几百张单就根本不用考虑性能问题了.如果是大型系统,估计也不是这样搞法,至于大型系统是如何搞的,我真不知道(当然很想知道,比如淘宝,当当的订单号是如何生成的???)
其它的还暂未想到,到时候碰到再更改,请各位同行们指点,以求改进
脚本代码如下:
1 --流水号表 2 --流水号一般规则:流水号 = 前缀+动态内容+日期+中缀+流水号+后缀。 3 --动态内容一般是执行存储过程取流水号的时候动态传入的 4 if object_id('t_sequence_number') is not null 5 drop table t_sequence_number 6 go 7 8 create table t_sequence_number( 9 id int identity 10 ,sequence_type varchar(50) not null --哪种类型的流水号,为求方便,默认是表名 11 ,sequencenumber_len int not null --流水号长度 如长度为4,表示'0000',长度为7,表示'0000000'等等 12 ,reset_type varchar(50) not null --归一类型,'year'表示按年,'month'表示按月,'day'表示按日,归一类型与日期模板三个字段是有约束的,
如果是day,则年,月,日都不能为空,如果为month,则年月不能为空,如果为'',表示不要日期
13 ,separator varchar(50) not null --流水号各部分分隔符 14 ,prefix varchar(50) not null --前缀 15 ,part_year_fmt varchar(50) not null --日期模板年份 可以是:yyyy,yy, 16 ,part_month_fmt varchar(50) not null --日期模板月份 可以是:mm,m等等 17 ,part_day_fmt varchar(50) not null --日期模板日期 可以是:dd,d等等 18 ,midfix varchar(50) not null --中缀 19 ,cur_max_sequence_number int not null --当前最大流水号值 20 ,cur_max_date datetime null --当前最大日期 21 ,suffix varchar(50) not null --后缀 22 23 ,constraint uq_t_sequence_number_sequence_type unique (sequence_type) 24 ,constraint pk_t_sequence_number_id primary key (id) 25 ,constraint chk_t_sequence_number_reset_type check (reset_type in ('year','month','day','')) 26 ,constraint chk_t_sequence_number_part_year_fmt check (part_year_fmt in ('yyyy','yy','')) 27 ,constraint chk_t_sequence_number_part_month_fmt check (part_month_fmt in ('mm','m','')) 28 ,constraint chk_t_sequence_number_part_day_fmt check (part_day_fmt in ('dd','d','')) 29 ,constraint chk_t_sequence_number_reset_type_map_fmt
check ( reset_type = ''
or charindex(left(reset_type,1),part_year_fmt+part_month_fmt+part_day_fmt)>0)
30 ,constraint chk_t_sequence_number_cur_max_date check ( reset_type = ''
or (reset_type<>'' and cur_max_date is not null))
32 )
33 alter table t_sequence_number 34 add constraint def_t_sequence_number_sequencenumber_len default 4 for sequencenumber_len 35 36 alter table t_sequence_number 37 add constraint def_t_sequence_number_reset_type default ('day') for reset_type 38 39 alter table t_sequence_number 40 add constraint def_t_sequence_number_separator default ('') for separator 41 42 alter table t_sequence_number 43 add constraint def_t_sequence_number_prefix default ('') for prefix 44 45 alter table t_sequence_number 46 add constraint def_t_sequence_number_part_year_fmt default ('yyyy') for part_year_fmt 47 48 alter table t_sequence_number 49 add constraint def_t_sequence_number_part_month_fmt default ('mm') for part_month_fmt 50 51 alter table t_sequence_number 52 add constraint def_t_sequence_number_part_day_fmt default ('dd') for part_day_fmt 53 54 alter table t_sequence_number 55 add constraint def_t_sequence_number_midfix default ('') for midfix 56 57 alter table t_sequence_number 58 add constraint def_t_sequence_number_cur_max_sequence_number default (0) for cur_max_sequence_number 59 60 alter table t_sequence_number 61 add constraint def_t_sequence_number_cur_max_date default (getdate()) for cur_max_date 62 63 alter table t_sequence_number 64 add constraint def_t_sequence_number_suffix default ('') for suffix 65 66 go 67 68 if object_id('sp_Get_Sequence_Number') is not null 69 drop proc sp_Get_Sequence_Number 70 go 71 72 create procedure sp_Get_Sequence_Number 73 @sequence_type varchar(50) --流水号类别 74 ,@count int = 1 --获取几个序列号 75 ,@dynamic_content varchar(50) = '' --动态内容 76 as 77 begin 78 /* 79 --流水号一般规则:流水号 = 前缀+动态内容+日期+中缀+流水号+后缀。 80 --动态内容一般是执行存储过程取流水号的时候动态传入的 81 插入内容示例: 82 insert into t_sequence_number(sequence_type,sequencenumber_len,reset_type,separator,prefix,part_year_fmt,
part_month_fmt,part_day_fmt,midfix,cur_max_sequence_number,cur_max_date,suffix) 83 values('CarApply',4,'day','','LA','yy','mm','dd','',0,getdate(),'') 84 85 执行示例:exec sp_Get_Sequence_Number 'CarApply' 86 87 */ 88 set nocount on; 89 set transaction isolation level serializable; 90 91 declare @currentdate datetime 92 ,@sequencenumber_len int 93 ,@reset_type varchar(50) 94 ,@separator varchar(50) 95 ,@prefix varchar(50) 96 ,@part_year_fmt varchar(50) 97 ,@part_month_fmt varchar(50) 98 ,@part_day_fmt varchar(50) 99 ,@midfix varchar(50) 100 ,@cur_max_sequence_number int 101 ,@cur_max_sequence_number_firt_bak int 102 ,@cur_max_date datetime 103 ,@suffix varchar(50) 104 ,@datestr varchar(50); 105 106 declare @seqtable table(seqnum varchar(50),orderno int); 107 108 begin tran 109 110 --读取配置信息 111 select @currentdate = getdate() 112 ,@sequencenumber_len = sequencenumber_len 113 ,@reset_type = reset_type 114 ,@separator = separator 115 ,@prefix = prefix 116 ,@part_year_fmt = part_year_fmt117 ,@part_month_fmt = part_month_fmt 118 ,@part_day_fmt = part_day_fmt 119 ,@midfix = midfix 120 ,@cur_max_sequence_number = cur_max_sequence_number 121 ,@cur_max_sequence_number_firt_bak = cur_max_sequence_number 122 ,@cur_max_date=cur_max_date 123 ,@suffix = suffix 124 ,@datestr = '' 125 from t_sequence_number where sequence_type=@sequence_type; 126 127 if @@rowcount = 0 128 begin 129 raiserror('无相应的流水号类别,请确认@sequence_type参数值是否正确!',16,1); 130 end 131 132 if @reset_type<>'' 133 begin 134 set @datestr = case @part_year_fmt
when 'yyyy' then cast(year(@currentdate) as varchar(50)) else right(cast(year(@currentdate) as varchar(50)),2) end 135 + case @part_month_fmt
when 'mm' then right('0'+ cast(month(@currentdate) as varchar(50)),2) else cast(month(@currentdate) as varchar(50)) end 136 + case @part_day_fmt
when 'dd' then right('0'+ cast(day(@currentdate) as varchar(50)),2) else cast(day(@currentdate) as varchar(50)) end 137 138 end 139 140 if convert(varchar(8), @currentdate, 112) = convert(varchar(8), @cur_max_date, 112) 141 set @cur_max_sequence_number = @cur_max_sequence_number + @count; --累加 142 else 143 begin 144 set @cur_max_sequence_number = 1; --归1 145 set @cur_max_sequence_number_firt_bak = 0; 146 end 147 148 while @count >=1 149 begin 150 insert into @seqtable values( 151 @prefix 152 + @separator 153 + @dynamic_content 154 + case @dynamic_content when '' then '' else @separator end 155 + @datestr 156 + case @datestr when '' then '' else @separator end 157 + @midfix 158 + case @midfix when '' then '' else @separator end 159 + right( replicate('0',@sequencenumber_len)
+cast(@cur_max_sequence_number_firt_bak+@count as varchar(50)),@sequencenumber_len) 160 + case @suffix when '' then '' else @separator end 161 + @suffix 162 ,@count 163 ) 164 165 set @count = @count - 1; 166 167 end 168 169 update t_sequence_number 170 set cur_max_date = @currentdate 171 ,cur_max_sequence_number=@cur_max_sequence_number 172 where sequence_type=@sequence_type; 173 174 commit tran 175 176 --获取流水号 177 select seqnum from @seqtable order by orderno asc 178 end