思路
把数据保存为带分隔符长字符串，如"aaa,bbb,ccc,ddd,..."，利用字符串方法遍历每个元素
知识点
- 查询结果拼接成字符串
- 查询字符数组元素个数
- 获取字符数组指定元素
- sql try...catch
- 事务
-- 删除fun
If Exists (Select * from sys.objects where name ='Get_StrArrayLength' and type =N'FN')
BEGIN
drop function [dbo].[Get_StrArrayLength];
END
If Exists (Select * from sys.objects where name ='Get_StrArrayStrOfIndex' and type =N'FN')
BEGIN
drop function [dbo].[Get_StrArrayStrOfIndex];
END
GO
-- 新建fun
CREATE function Get_StrArrayLength
(
@str varchar(8000), --要分割的字符串，注意大小，否则超范围只能取最后一个值
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end;
GO
--
CREATE function Get_StrArrayStrOfIndex
(
@str varchar(8000), --要分割的字符串 注意大小，否则超范围只能取最后一个值
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(8000) -- 注意大小，否则超范围只能取最后一个值
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
--这儿存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location-@start)
end
GO
--select dbo.Get_StrArrayLength('78,1,2,3',',');
--select dbo.Get_StrArrayStrOfIndex('8,9,4','-',2)
-- 拼接全部卡号
DECLARE @cards VARCHAR(8000)
DECLARE @empnos VARCHAR(8000)
DECLARE @empids VARCHAR(8000)
SET @cards = STUFF((SELECT ',' + LTRIM(cardNumber) FROM employee for xml path('')),1,1,'');
SET @empnos = STUFF((SELECT ',' + LTRIM(empNo) FROM employee for xml path('')),1,1,'');
SET @empids = STUFF((SELECT ',' + LTRIM(id) FROM employee for xml path('')),1,1,'');
-- SELECT dbo.Get_StrArrayStrOfIndex(@cards, ',', 169)
-- SELECT dbo.Get_StrArrayStrOfIndex(@cards, ',', 170)
-- SELECT dbo.Get_StrArrayStrOfIndex(@cards, ',', 171)
-- RETURN
-- 数组遍历
declare @next int
declare @oldcard int
declare @empno int
declare @empid int
declare @addtime datetime
set @next=1
while @next<=dbo.Get_StrArrayLength(@cards,',')
begin
set @oldcard = CAST(dbo.Get_StrArrayStrOfIndex(@cards,',',@next) as INT)
set @empno = CAST(dbo.Get_StrArrayStrOfIndex(@empnos,',',@next) as INT)
set @empid = CAST(dbo.Get_StrArrayStrOfIndex(@empids,',',@next) as INT)
print @next
print @oldcard
BEGIN TRY
BEGIN TRANSACTION T
-- 更新卡号
UPDATE kjtx.dbo.employee SET cardNumber = @oldcard + 100000 WHERE cardNumber = @oldcard
-- 更新报警
update sosMsg set IsRead=1 where cardnum= @oldcard and isread=0
Update employeeException set isRead=1,ReadId=-1 WHERE employeeId= @empid and isread=0
update electricMsg set IsRead=1,ReadId=-1 where cardnum= @oldcard and isread=0
update areaAlarm set IsRead=1 where cardnum= @oldcard and isread=0
update WorkAlarm set IsRead=1 where EmployeeId= @empid and isread=0
update CardAccelAlarm set IsRead=1 where cardNum= @oldcard and isread=0
select top 1 @addtime=addtime from ChangeCardList where lastCardNum = @oldcard order by addtime
-- 插入换卡记录 employeeId == employee.id
insert into ChangeCardList(currentCardNum,lastCardNum,cardType,addtime,changeId,uniqueFlag,guid) values(@oldcard , @oldcard + 100000 , 1, @addtime, @empid , @empno, NEWID() )
COMMIT TRANSACTION T
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
ROLLBACK TRANSACTION T
END CATCH
set @next=@next+1
end;
-- 更新换卡时间
update settings set itemValue= CONVERT(VARCHAR(20), GETDATE(), 120) where item='ChangeCardTime'
Bug
Get_StrArrayStrOfIndex 必须注意数组范围，此处均取最大长度8000
参考文章
sql try...catch 处理事务
SQLServer将查询内容拼接成字符串输出
在SQL SErver中实现数组功能
