存储过程

use CallingBS
go
if (exists (select * from sys.objects where name = 'proc_UpdateInsertUpdate'))
drop proc proc_UpdateInsertUpdate
go

create proc proc_UpdateInsertUpdate
as
DECLARE
@ncode AS NVARCHAR(16),@bedEtn AS NVARCHAR(16);
DECLARE C_Employees
CURSOR FAST_FORWARD FOR --声明游标
select a.NURSE_UNIT_CODE as ncode,a.BED_ETN as bedEtn from bed_layout a join patient_in b on a.NURSE_UNIT_CODE=b.NURSE_UNIT_CODE and a.BED_NO= b.BED_NO
open C_Employees
--开始取记录
-- 取第一条记录
FETCH NEXT FROM C_Employees INTO @ncode,@bedEtn
WHILE @@FETCH_STATUS=0
begin
--发更新指令
insert into interface_update (COMMAND_TYPE,NURSE_UNIT_CODE,insertdate,rawParas) values (50,@ncode,GETDATE(),@bedEtn)
insert into interface_update (COMMAND_TYPE,NURSE_UNIT_CODE,insertdate,rawParas) values (51,@ncode,GETDATE(),@bedEtn)
-- 取下一条记录
FETCH NEXT FROM C_Employees INTO @ncode,@bedEtn
end
-- 关闭游标
close C_Employees;
-- 释放游标
deallocate C_Employees;

 

While循环

将数据放在临时表中，然后操作临时表，最后更新回总表。耗时16s。

--遍历50素材
DECLARE @nursencode AS NVARCHAR(16);
DECLARE C_Employeess
CURSOR FAST_FORWARD FOR --声明游标
select NURSE_UNIT_CODE as nursencode from nurse_unit_dict
open C_Employeess
--开始取记录
-- 取第一条记录
FETCH NEXT FROM C_Employeess INTO @nursencode
WHILE @@FETCH_STATUS=0
begin
--发更新指令
DECLARE @RowID int
-- 获取待处理的数据记录到临时表
-- 字段说明：RowID：记录行号 / DealFlg：行处理标识
SELECT DealFlg=0,show_id,RowID = IDENTITY(INT , 1, 1)
INTO #Tmp
FROM SPECIAL_CLUES_DICT where show_id<10
SELECT @RowID = MIN(show_id) FROM #Tmp WHERE DealFlg = 0
-- 若最小行号不为空（有需要处理的数据）
WHILE @RowID IS NOT NULL
BEGIN
UPDATE #Tmp SET DealFlg = 1 WHERE show_id = @RowID
insert into interface_update (COMMAND_TYPE,NURSE_UNIT_CODE,insertdate,rawParas) values (50,@nursencode,GETDATE(),@RowID)

SELECT @RowID = MIN(show_id) FROM #Tmp WHERE DealFlg = 0
END
drop table #tmp

-- 取下一条记录
FETCH NEXT FROM C_Employeess INTO @nursencode
end
-- 关闭游标
close C_Employeess;
-- 释放游标
deallocate C_Employeess;