【SQL-自动生成编号】按规则自动生成单据编号 以及并发问题_使用触发器、函数 等
描述：每种单据新建时，自动生成它的单据编号。
规则如：固定码+日期+流水号 （ABC1603180001）
方法一：触发器
触发器的缺点是,执行了sql之后才看到编码。
测试：流水号不能超过最大数，否则后面的号码全是0
--有两张表，客户表和项目表，要求：新建项目时自动生成项目编号，每个不同的客户的项目的编号从1开始
--项目编号格式为PJ+"-"+"客户编号"+"-"+"日期"+"-"+"流水号"
create table TestAccount --创建测试客户表
(
ClientNum nvarchar(32), --客户编号
ClientName varchar(100) --客户姓名
)
create table TestProject --创建测试项目表
(
ProID int primary key identity(1,1),
ProNum nvarchar(32), --项目编号
ProName nvarchar(100), --项目名称
ClientNum nvarchar(100), --客户编号
)
create trigger T_AutoNumber
on TestProject after insert
as
begin
declare @one nvarchar(8), --PJ
@two nvarchar(32), --客户编号
@three nvarchar(8), --日期
@four int, --流水号
@id int --ID
set @one='PJ'
set @three=CONVERT(varchar(8),GETDATE(),112)
--从inserted副本表里获取当前数据插入
select @two=ClientNum,@id=ProID from inserted
--获取编号最后4位
select @four=MAX(CAST(right(ProNum,4) as int)) from TestProject where ClientNum=@two
--对每个新客户的流水号都是从1开始，已存在客户为最大流水号加1
if @four is null
set @four=0
else
set @four=CAST(@four as int)
set @four=@four+1
update TestProject set ProNum=@one+'-'+@two+'-'+@three+'-'+RIGHT('0000'+CAST(@four as varchar),4) where ProID=@id
end
go
--生成测试表数据
insert into TestAccount values('小小鸭','XXY')
insert into TestAccount values('丑小鸭','CXY')
insert into TestProject(ProName,ClientNum) values('小小鸭游泳项目','XXY')
insert into TestProject(ProName,ClientNum) values('小小鸭成长项目','XXY')
insert into TestProject(ProName,ClientNum) values('丑小鸭造型项目','CXY')
insert into TestProject(ProName,ClientNum) values('小小鸭游泳项目','XXY')
insert into TestProject(ProName,ClientNum) values('小小鸭成长项目','XXY')
insert into TestProject(ProName,ClientNum) values('丑小鸭造型项目','CXY')
--订单编号自动编号:固定码+日期+流水号
create table salesOrder
(
id int primary key identity(1,1),
orderno varchar(50),
goods varchar(50),
qty int
)
CREATE trigger [dbo].[T_AutoNumByOrder]
on [dbo].[salesOrder] after insert
as
begin
declare @one varchar(10), --固定编号
@two varchar(32), --日期
@three int, --流水号
@id int --id
set @one='ABCD'
set @two=right(CONVERT(varchar(8),GETDATE(),112),6)
--当天没有记录的从1开始
select @three=MAX(CAST(right(orderno,4) as int)) from salesOrder where substring(orderno,5,6)=right(CONVERT(varchar(8),GETDATE(),112),6)
if not exists (select top 1 id from salesOrder where substring(orderno,5,6)=right(CONVERT(varchar(8),GETDATE(),112),6))
set @three=0
else
set @three=CAST(@three as int)
set @three=@three+1
--从inserted表获得主键的自动编号
select @id=id from inserted
update salesOrder set orderno=@one+@two++RIGHT('0000'+CAST(@three as varchar),4) where id=@id
end
方法二：函数
在数据上写个函数做，在一个表里记录一个最后的值，这个函数被调用了就把最后的这个值+1
测试：此方法插入数据慢，在批量插入数据表现更明显
--创建表
create table CostBill
(
BillID int primary key identity(1,1),
CostBillNo nvarchar(50),
Client nvarchar(50),
Goods nvarchar(50),
QTY int
)
--创建函数
create function GetCostBillNo(
@headStr nvarchar(10), --固定码
@date datetime) --日期
returns nvarchar(50)
begin
declare @oid2 nvarchar(50), --单据编号,临时值
@oid nvarchar(50), --单据编号,返回值
@month nvarchar(2), --月
@year nvarchar(2), --年
@ym nvarchar(4) --年月
set @month=MONTH(@date) --月
if LEN(@month)=1 --如果月只有一位
set @month='0'+@month --使月为2位长
set @year=RIGHT(CONVERT(nvarchar,year(@date)),2) --取年的后两位
set @ym=@year+@month --组成年月字符
--格式CB16030001
if exists(select * from CostBill) --如果CostBill有数据
begin
select top 1 @oid2=CostBillNo from CostBill order by BillID desc
end
else
begin
set @oid2=@headStr+@ym+'0000' --没有数据,单号流水号归0
end
if CONVERT(nvarchar,LEFT(@oid2,6))<>@headStr+@ym
begin
set @oid2=@headStr+@ym+'0000'
end
declare @str nvarchar(50)
set @str=convert(nvarchar,(convert(int,right(@oid2,4))+1)) --订单号加一
while (4-len(@str)>0)
begin
set @str='0'+@str
end
set @oid2=@headStr+@ym+@str
while exists(select * from CostBill where CostBillNO=@oid2) --如果存在该订单号
begin
set @str=convert(nvarchar,(convert(int,right(@oid2,4))+1)) --订单号加一
while (4-len(@str)>0)
begin
set @str='0'+@str
end
set @oid2=@headStr+@ym+@str
--print @oid2
end
set @oid=convert(nvarchar,@oid2)
--print 'HP'+convert(nvarchar,year(getdate()))+convert(nvarchar,month(getdate()))+@str
RETURN @oid
END
--测试数据
insert into CostBill(CostBillNo,Client,Goods,QTY) values (dbo.GetCostBillNo('VISO','2016-04-01'),'C001','G001',100)
方法三：存储过程
方法四：程序
并发处理
当两个并发用户同时创建或保存一张同样的业务单据时，系统会返回两个相同的单据编码，产生了并发问题。
A 方案
打开业务功能时，立即为当前单据创建单据编码，比如产生单据编码SO15080004，在单据保存时，发现这张单据编码被其它的用户使用过，则重新产生一个新的业务单据编码SO15080005，如有发现此编码仍然被占用，依此向下搜寻，直到找到可以保存的单据编码。
这种方案的优点是总是可以保存单据，缺点是界面中看到的单据编码，不一定是最终保存的单据编码。
B 方案
打开业务功能时，不产生单据编码，只有在单据保存时才产生单据编码。避免了单据并发冲突。
这种方案优点是没有并发冲突，缺点是只有单据保存之后才可以看到单据编码。
C
在产生编码的同时，绑定了业务数据的ID，这样可以保证不重号不跳号
D
并发的时候不管你是什么时机生成编码都可能存在重复编码的机遇，这个的解决办法必须使用代码锁定，同时只允许一个线程允许就可以了
E
生成编码的时候，要用个lock 来保证同一时刻只执行一次。
销售单是有主表和子表的，程序添加完主表以后就可以解锁了。速度是非常非常快的。
F
在数据库中把单号做成唯一的键，在程序里面再做判断。
G
1、每个帐号生成自己属性的编号
2、每个帐号分配不同的号段，号段用完需要再向管理员申请新的可用号段。
3、编号不在窗口上显示，保存的时候再由触发器生产编号。
4、设置一个编号表，就一个字段记录当前最大编号，新增记录时从此表中取号并加1，然后更新这个表。如果用户又取消新增，则这个编号就会被跳过，形成跳号。
H
当然把生成的新的编号放入一个只有一个字段且该字段为主键的表中也可以防止同时生成2个相同的编号，不过在保存数据时候最好将该编号从该表中删除。
建立一个种子表，这个种子表用来存放你各种单据的编号
生成一个编号就将生成的最大编号放入。
如
调拨单 PO 0001
配送单 PS 0100
其实有个最简单的办法就是在保存时产生单据编号就可以了
最好不要有查询最大的记录号这个动作。容易产生重复。
设置一个编号表，就一个字段记录当前最大编号，新增记录时从此表中取号并加1，然后更新这个表。如果用户又取消新增，则这个编号就会被跳过，形成跳号。
或者保存后显示单据号
1) 获取自增的字段是可以的.
set @id=SCOPE_IDENTITY();
2) 直接采用max(id)这种是不行的,并发有重复号码.
3) 自己处理,update 这种可以,但是高并发容易跳号
create table Tb_NO(
name char(20) primary key,--待产生编号的表名
head nvarchar(10)not null default'',--编号的前缀，默认值为空
currentNo int not null default 0,--当前的编号数据
BHlen int not null default 6, --编号数字部分的长度
descript nvarchar(40))--对编号的描述
--向编号表Tb_NO中添加记录，记录客户投诉表中的编号信息
insert into tb_no select ' tbl Cus_com’,'TS',0,4,'客户投诉表编号'
--创建存储过程，产生新编号
create proc pro_nextNO
@Name char(20),--待产生编号的表名
@NO nvarchar(20) output --返回新编号
as
begin tran
update tb_no with(rowlock)
set @NO=head+right(power(10,BHlen)+currentNo+1,BHlen),
currentno=currentno+1
where name=@Name
commit tran
go
====
