如何更换游标？ [英] How to replace cursors?

问题描述

大家好，

 我正在使用游标循环遍历每条记录并为每行调用SP，如下所示，并且需要更多时间来执行。你可以帮我调整吗，

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 '

结束¥b $ b 如果离开（@ plan_no，1）='R'

BEGIN

SET @app_type ='MAPD'

结束

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



结束

ELSE IF @status ='DISENR'

BEGIN

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



UP DATE
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

T. hanks。

解决方案

https://www.sqlshack.com/understanding-cursors-replacing-joins-sql-server/

Hi All,

  I'm using cursor to loop thru each record and calling SPs for each row as shown below and it is taking more time to execute. Can you please help me to tune it,

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

Thanks.

解决方案

https://www.sqlshack.com/understanding-cursors-replacing-joins-sql-server/

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