SqlServer——游标

create proc LCN0019999.NK_FXSLTJ
as
IF OBJECT_ID(N‘tempdb.dbo.#SLTJ‘) IS NOT NULL --删除临时表
drop table #SLTJ
create table #SLTJ
(
LCKMC varchar(70),
GFX INT,
ZFX INT,
DFX INT
)
declare @LCMC varchar(60), @num numeric
insert into #SLTJ select distinct NKLCK_MC,0,0,0 from NKLCK JOIN NKJZZZMX ON NKLCK_NM =NKJZZZMX_LCNM --获取流程名称

declare My_Cursor2 cursor
for (select NKFXJZ_FXZ,NKLCK_MC from NKFXJZ join NKFXJZMX on NKFXJZMX_JZNM=NKFXJZ_NM join
NKJZZZMX on NKJZZZMX_FXJZMX =NKFXJZMX_NM JOIN NKLCK ON NKLCK_NM =NKJZZZMX_LCNM )
open My_Cursor2;
fetch next from My_Cursor2 into @num,@LCMC;
while @@FETCH_STATUS =0
begin
if @num>=3.5 and @num<=5
update #SLTJ set GFX=GFX+1 where LCKMC=@LCMC
else if @num>2 and @num<3.5
update #SLTJ set ZFX=ZFX+1 where LCKMC=@LCMC
else if @num>=0 and @num<=2
update #SLTJ set DFX=DFX+1 where LCKMC=@LCMC
fetch next from My_Cursor2 into @num,@LCMC;
end
close My_Cursor2;
deallocate My_Cursor2;

select LCKMC ,GFX ,ZFX ,DFX from #SLTJ