游标循环查询和更新是很慢的一件事情

最新推荐文章于 2021-11-14 11:53:54 发布

iteye_2060

最新推荐文章于 2021-11-14 11:53:54 发布

阅读量99

点赞数

最近赶个项目，里面要统计末末的属性






 

for
 LASTLOGIN_cur 
in
 ( 
select
 username,last_landtime 
from
 a 
where
 landtime > trunc(
sysdate
) -
7
) 

loop


 

update
 a 

 

set

(last_landtime,last_account,update_time)= 
( 

select
 
LASTTIME,USERMONEY,
sysdate
 

from
 b
 

where

f_username = LASTLOGIN_cur.f_username

 

and
 
LASTTIME > ASTLOGIN_cur.f_last_landtime 
) 
where

a.f_username = LASTLOGIN_cur.f_username ;



for cur in(....)

loop


for cur in(....)


 



像上面第一条把所有的查询写到IN里面去获得运算后的结果，然后循环更新。



下面语句也是把所有的写到IN 中采用LEFT JOIN 来排除不存在的数据再来运算





for n_fill_curA in 
( 
select p. username,p. optime 
from 
( 
select * 
from 
( 
select username, optime,ROW_NUMBER() OVER (PARTITION BY username ORDER BY optime asc) rowrid 
from user_pay_his 
where busisort = 20 and optime > trunc(sysdate)-7 
) where rowrid =1 
)p inner join 
( 
select username 
from user_attr 
where filltime is null 
)b on p. username=b. username 
) 
loop 
update t_behavior_attribute a --用户首次充值时间 
set filltime = n_fill_curA. optime, 
update_time = sysdate 
where a. username = n_fill_curA. username 
and a. filltime is null; 
v_count :=v_count+1; 
v_rownum :=v_rownum+sql%rowcount; 
if mod(v_count,100)=0 then 
v_count:=0; 
commit; 
end if; 
end loop; 
commit;