plsql语句块 for 遍历游标

来源：互联网发布：php jquery ajax登录编辑：程序博客网时间：2024/05/01 03:21

declare
cursor cur_quick_info is
select * from ewallet.t_quick_pay_info where status='S' and pay_channel='01001906' and outer_merchant_id='1217015601' and quick_pay_id='201501010010KJ072895'; --微信010商户号老签约快捷信息

v_row_count integer :=0; --计数
v_total integer :=0;
v_quick_temp cur_quick_info%rowtype;
v_pre_value varchar2(1000); --快捷签约信息update前的值

begin

for v_quick_temp in cur_quick_info
loop
v_pre_value:='{"accountId":"' || v_quick_temp.account_id || '","status":"' || v_quick_temp.status || '","quickPaySn":"'
|| v_quick_temp.quick_pay_sn || '","quickPayId":"' || v_quick_temp.quick_pay_id || '","payChannel":"' || v_quick_temp.pay_channel
|| '","outerM商户号
updaterchantId":"' || v_quick_temp.outer_merchant_id || '","outerSignSn":"' || v_quick_temp.outer_sign_sn || '"}';

--更改快捷签约的走04新渠道与新e ewallet.t_quick_pay_info set pay_channel='02401906',outer_merchant_id='1301414501', modify_time=sysdate where
quick_pay_id=v_quick_temp.quick_pay_id;
--插入一条更改记录
insert into ewallet.t_quick_pay_info_log(id,quick_pay_id,status,create_ip,description,pre_value,create_time)
values(ewallet.T_QUICK_PAY_LOG_SEQ.nextval,v_quick_temp.quick_pay_id,v_quick_temp.status,'','010改为024走新接口扣款',v_pre_value,sysdate);

v_row_count:=v_row_count+1;
v_total:=v_total+1;

if v_row_count mod 10000 = 0 then --每1w条commit一次
commit;
v_row_count:=0;
end if;

if v_total mod 100000 =0 then
-- dbms_lock.sleep(3); --每10w条记录sleep 3秒
v_total:=0;
end if;

end loop;

commit; --commit不足1w条的事务

end;
/

0 0