对于员工工号或者其他编码有特定生成规则,存在多处地方同时生成时,为了解决并的问题,在获取最新编码时,锁定行
注意:在使用for update锁行的时候,判断字段需为索引字段(例如主键)
CREATE DEFINER=`root`@`%` FUNCTION `EF_CODEDO`(V_XTYPE INT,V_DATE varchar(10)) RETURNS varchar(500) CHARSET utf8mb4
BEGIN
DECLARE P_SQL VARCHAR(500);
declare p_htlsh VARCHAR(255);
declare p_NewLsh VARCHAR(255);
declare p_ID int;
-- V_XTYPE=1、获取工号最新值(xtype=1)
if V_XTYPE = 1 then
-- 初始化数据
if not exists(select 1 from Emp_CodeDo where xtype= 1 ) then
insert into Emp_CodeDo(xtype,NewCode)
select 1,'100000';
end if;
-- 获取主键(或者索引字段)
select id into p_ID from Emp_CodeDo where xtype= 1;
-- 根据主键或者索引字段锁行 获取最准确数据(解决并发)
select (IFNULL(NewCode,'100000')+1) into P_SQL from Emp_CodeDo where id = p_ID for update;
end if;
-- V_XTYPE=2、获取合同编号最新值(xtype=2)
if V_XTYPE = 2 then
-- **合同编码工号生成规则:签订日期-3位流水号
-- 1、编码表中存在相同日期数据,取最大值
if exists(select 1 from Emp_CodeDo a,EMP_CONTRACT b
where a.xtype = 2
and a.NewCode = b.CONNO
and a.Dodate = date_format(b.SIGNDATE,'%Y-%m-%d')
and a.Dodate = V_DATE) then
-- 1>获取最新流水号
select (right(NewCode,3)+1) into p_htlsh from Emp_CodeDo where xtype= 2 and Dodate = V_DATE;
-- 2>流水号判断
set p_NewLsh = case when p_htlsh > 0 and p_htlsh < 10 then concat('00',p_htlsh)
when p_htlsh >= 10 and p_htlsh <= 99 then concat('0',p_htlsh)
else p_htlsh END;
select id into p_ID from Emp_CodeDo where xtype= 2 and Dodate = V_DATE ;
select concat(left(NewCode,8),'-',p_NewLsh) into P_SQL from Emp_CodeDo where id = p_ID for update;
end if;
-- 2、编码中不存在符合条件,新增一条并初始化
if not exists(select 1 from Emp_CodeDo a,EMP_CONTRACT b
where a.xtype = 2
and a.NewCode = b.CONNO
and a.Dodate = date_format(b.SIGNDATE,'%Y-%m-%d')
and a.Dodate = V_DATE) then
delete from Emp_CodeDo where xtype = 2 and Dodate = V_DATE;
-- 新增初始化数据
insert into Emp_CodeDo(xtype,NewCode,Dodate)
select 2,concat(date_format(V_DATE,'%Y%m%d'),'-001'),V_DATE;
select id into p_ID from Emp_CodeDo where xtype = 2 and Dodate = V_DATE ;
select NewCode into P_SQL from Emp_CodeDo where id = p_ID for update;
end if;
end if;
RETURN P_SQL;
END