如何将光标转换为while循环？ [英] How to convert cursor to while loop?

问题描述

大家好，

 请帮我将以下光标转换为while循环，是否会提高性能？如果不是除了基于集合的quires之外还能做什么呢？

  Please help me to convert the following cursor into while loop, whether it will improve the performance? if not what else I can do other than set-based quires?

  DECLARE @plan_no varchar（5），@ user_id varchar（7），@ group_type varchar（1） ，

@ status varchar（6），@ received_dt varchar（10），@ app_type varchar（15），@ member_id int



DECLARE
  batch_apps_cursor_nagarpr CURSOR FAST_FORWARD FOR

SELECT
  plan_no，user_id，group_type，status，app_received_dt，member_id

FROM
stat_his..stg_eats_batch_tab WITH（NOLOCK）  

WHERE
processed_status ='-1'



OPEN
batch_apps_cursor_nagarpr


FETCH NEXT FROM batch_apps_cursor_nagarpr INTO

@ plan_no，@ user_id，@ group_type，@ status，@ received_dt，@ member_id

WHILE（@@ FETCH_STATUS  = 0）

BEGIN

SET @app_type ='PDP'

IF if left（@ plan_no，1）='H'

BEGIN

SET @app_type ='MAPD'

END

IF if left（@ plan_no，1）='R'

BEGIN

SET @app_type ='MAPD'

END

IF @status IN（'APPRCV'，'PENDNG'，'ACTIVE'，'RESRCH'，'INCMPL'， 

        'INCMED'，'PNDMED'，'INCSNP '）

BEGIN

EXECUTE stat_his..upd_eats_enrbatch_apps @ plan_no，@ user_id，@ group_type，@ status，@ received_dt，@ app_type



更新
  stat_his..stg_eats_batch_tab

SET
processed_status ='5'

WHERE
member_id = @member_id



END

ELSE IF @status ='DISENR'

BEGIN

EXECUTE stat_his..upd_eats_disenrbatch_apps @ plan_no，@ user_id，@ group_type，@ status，@ received_dt，@ app_type



更新
  stat_his..stg_eats_batch_tab

SET
processed_status ='5'

WHERE
member_id = @member_id

END




FETCH NEXT FROM batch_apps_cursor_nagarpr INTO

@ plan_no，@ user_id，@ group_type，@ status，@ received_dt，@ member_id


END



关闭
batch_apps_cursor_nagarpr

DEALLOCATE
  batch_apps_cursor_nagarpr

 DECLARE @plan_no varchar(5),@user_id varchar(7),@group_type varchar(1),
@status varchar(6),@received_dt varchar(10),@app_type varchar(15),@member_id int

DECLARE  batch_apps_cursor_nagarpr CURSOR FAST_FORWARD FOR
SELECT  plan_no,user_id,group_type,status,app_received_dt,member_id
FROM stat_his..stg_eats_batch_tab WITH (NOLOCK)  
WHERE processed_status = '-1'

OPEN batch_apps_cursor_nagarpr
FETCH NEXT FROM batch_apps_cursor_nagarpr INTO
@plan_no ,@user_id ,@group_type ,@status ,@received_dt,@member_id
WHILE ( @@FETCH_STATUS  = 0 )
BEGIN
SET @app_type = 'PDP'
IF left(@plan_no,1) ='H'
BEGIN
SET @app_type = 'MAPD'
END
IF left(@plan_no,1) ='R'
BEGIN
SET @app_type = 'MAPD'
END
IF @status IN ('APPRCV','PENDNG','ACTIVE','RESRCH','INCMPL', 
               'INCMED', 'PNDMED','INCSNP')
BEGIN
EXECUTE stat_his..upd_eats_enrbatch_apps @plan_no,@user_id,@group_type,@status,@received_dt,@app_type

UPDATE  stat_his..stg_eats_batch_tab
SET processed_status = '5'
WHERE member_id = @member_id

END
ELSE IF @status = 'DISENR'
BEGIN
EXECUTE stat_his..upd_eats_disenrbatch_apps @plan_no,@user_id,@group_type,@status,@received_dt,@app_type

UPDATE  stat_his..stg_eats_batch_tab
SET processed_status = '5'
WHERE member_id = @member_id
END

FETCH NEXT FROM batch_apps_cursor_nagarpr INTO
@plan_no ,@user_id ,@group_type ,@status ,@received_dt ,@member_id
END

CLOSE batch_apps_cursor_nagarpr
DEALLOCATE  batch_apps_cursor_nagarpr

谢谢..

推荐答案

为什么？它们完全相同。停止使用NOLOCK直到你知道它做了什么，你准备好处理该提示带来的问题。 

Why? They are EXACTLY the same thing. And stop using NOLOCK until you KNOW what it does and you are PREPARED to handle the problems that come with that hint. 

这篇关于如何将光标转换为while循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！