SqlServer实现数组遍历

思路

把数据保存为带分隔符长字符串，如"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中实现数组功能