游标修改数据

--使用游标补充报审信息

DECLARE

CURSOR c_my IS select id,SERIAL_NO from pfwxt_stc.T_SH_PROD_TRUST_ACCT WHERE PROD_AUDIT_STATUS ='AUDIT';

my_rec c_my%ROWTYPE;

BEGIN

OPEN c_my;

LOOP

FETCH c_my INTO my_rec;

EXIT WHEN c_my%NOTFOUND;

UPDATE STD_MERCHANT_REGISTER_PROCESS t set t."DATA" =regexp_replace(t."DATA",'000093',my_rec.SERIAL_NO) where t.NODE ='200' AND TRUST_NUMBER =my_rec.SERIAL_NO;

UPDATE STD_MERCHANT_REGISTER_PROCESS t set t."DATA" =regexp_replace(t."DATA",'510',my_rec.id) where t.NODE ='200' AND TRUST_NUMBER =my_rec.SERIAL_NO;

END LOOP;

CLOSE c_my;

END;

 

--使用游标补充tcmpid

DECLARE

-- CURSOR c_my IS select rownum as l_accoid,id from STD_TRUST_BANKCARD_INFO WHERE ACCT_ID IS NULL; --使用行号补充tcmpid

CURSOR c_my IS select b.l_accoid ,a.id from STD_TRUST_BANKCARD_INFO a,tcmp.taccount_bankinfo@DBLINK_TCMP b WHERE a.BANK_ACCT =b.C_BANKACCO AND a.ACCT_ID IS NULL; --使用dblink补充tcmpid

my_rec c_my%ROWTYPE;

BEGIN

OPEN c_my;

LOOP

FETCH c_my INTO my_rec;

EXIT WHEN c_my%NOTFOUND;

UPDATE STD_TRUST_BANKCARD_INFO SET ACCT_ID =my_rec.l_accoid WHERE ID =my_rec.id;

END LOOP;

CLOSE c_my;

END;

 

--使用游标补充支付单号

DECLARE

CURSOR c_my IS select rownum,trans_no from STD_TRANS_APPLY_ORDER WHERE BIZ_TYPE IN('CONSUME','RECHARGE')AND substr(ORDER_BEGIN_TIME,1,10)='2023-06-25';

my_rec c_my%ROWTYPE;

BEGIN

OPEN c_my;

LOOP

FETCH c_my INTO my_rec;

EXIT WHEN c_my%NOTFOUND;

UPDATE STD_TRANS_APPLY_ORDER SET OUT_PAY_NO =my_rec.rownum WHERE trans_no =my_rec.trans_no;

END LOOP;

CLOSE c_my;

END;