begin
declare @a int,@error int
declare @temp varchar(50)
declare @corp_code varchar(200)
declare @corp_name varchar(200)
declare @short_name varchar(200)
set @a=1
set @error=0
begin tran --申明事务
--申明游标为OD_Id
declare order_cursor cursor
for (SELECT [OD_Id] FROM [LZMISFrame].[dbo].[SF_Org_Department]
where OD_Id like '%OD%' and OD_Id !='OD191226101101')
--打开游标--
open order_cursor
--开始循环游标变量--
fetch next from order_cursor into @temp
while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
begin
if not exists (select pk_corp from [ZJK].[dbo].[ZJ_OUTSIDE_CORP] where pk_corp=@temp)
begin
insert into [ZJK].[dbo].[ZJ_OUTSIDE_CORP] ([pk_corp]
,[corp_code]
,[corp_name]
,[short_name])
SELECT [OD_Id],[OD_Code],[OD_Name],[OD_ShortName]
FROM [LZMISFrame].[dbo].[SF_Org_Department]
where OD_Id =@temp
end
else
begin
SELECT @corp_code=[OD_Code],@corp_name=[OD_Name],@short_name=[OD_ShortName]
FROM [LZMISFrame].[dbo].[SF_Org_Department]
where OD_Id =@temp
update [ZJK].[dbo].[ZJ_OUTSIDE_CORP] set corp_code=@corp_code,corp_name=@corp_name,short_name=@short_name
where pk_corp =@temp
end
set @a=@a+1
set @error= @error + @@ERROR --记录每次运行sql后是否正确,0正确
fetch next from order_cursor into @temp --转到下一个游标
end
if @error=0
begin
commit tran --提交事务
end
else
begin
rollback tran --回滚事务
end
close order_cursor --关闭游标
deallocate order_cursor --释放游标
end
go
游标循环—事务
最新推荐文章于 2022-05-08 18:31:03 发布