正文
SQLSERVER之高灵活的业务单据流水号生成
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
SQLSERVER之高灵活的业务单据流水号生成最近的工作中要用到流水号,而且业务单据流水号生成的规则分好几种,并非以前那种千篇一律的前缀+日期+流水号的简单形式,经过对业务的分析,以及参考网上程序员的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),isnull(part_year_fmt,'')+isnull(part_month_fmt,'')+isnull(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 = isnull(part_year_fmt,0)
117 ,@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(getdate()) as varchar(50)) else right(cast(year(getdate()) as varchar(50)),2) end
135 + case @part_month_fmt
when 'mm' then right('0'+ cast(month(getdate()) as varchar(50)),2) else cast(month(getdate()) as varchar(50)) end
136 + case @part_day_fmt
when 'dd' then right('0'+ cast(day(getdate()) as varchar(50)),2) else cast(day(getdate()) 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
最近的工作中要用到流水号,而且业务单据流水号生成的规则分好几种,并非以前那种千篇一律的前缀+日期+流水号的简单形式,经过对业务的分析,以及参考网上程序员的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),isnull(part_year_fmt,'')+isnull(part_month_fmt,'')+isnull(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 = isnull(part_year_fmt,0)
117 ,@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(getdate()) as varchar(50)) else right(cast(year(getdate()) as varchar(50)),2) end
135 + case @part_month_fmt
when 'mm' then right('0'+ cast(month(getdate()) as varchar(50)),2) else cast(month(getdate()) as varchar(50)) end
136 + case @part_day_fmt
when 'dd' then right('0'+ cast(day(getdate()) as varchar(50)),2) else cast(day(getdate()) 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
