BEGIN
--声明变量
DECLAREv_addtime_beginvarchar(13);
DECLAREv_addtime_endvarchar(13);
DECLAREv_borrow_idint;
DECLAREv_countint;
DECLAREs1int;
/**声明游标，并将查询结果存到游标中**/
DECLAREc_borrowCURSORFOR
SELECTIDfromrocky_borrowWHEREBORROWTYPE=2ANDPUBLISH_TIME>=UNIX_TIMESTAMP('2014-05-27')ANDPUBLISH_TIME<=UNIX_TIMESTAMP('2014-07-30')ORDERbyIDASC;
/**获取查询数量**/
SELECTcount(ID)INTOv_countfromrocky_borrowWHEREBORROWTYPE=2ANDPUBLISH_TIME>=UNIX_TIMESTAMP('2014-05-27')ANDPUBLISH_TIME<=UNIX_TIMESTAMP('2014-07-30')ORDERbyIDASC;
SETs1=1;
--开始事务
STARTTRANSACTION;
--打开游标
OPENc_borrow;
--循环游标
WHILEs1<v_count+1DO
--遍历游标
FETCHc_borrowINTOv_borrow_id;
SELECTt1.addtimeINTOv_addtime_beginFROM(SELECT*FROMrocky_b_tenderrecordbtWHEREBORROW_ID=v_borrow_idANDtender_type=1ORDERBYIDASC)t1GROUPBYt1.borrow_id;
SELECTt1.addtimeINTOv_addtime_endFROM(SELECT*FROMrocky_b_tenderrecordbtWHEREBORROW_ID=v_borrow_idANDtender_type=1ORDERBYIDDESC)t1GROUPBYt1.borrow_id;
IF(v_addtime_beginISNOTNULL)&&(v_addtime_endISNOTNULL)THEN
--嵌套使用游标
BEGIN
DECLAREv_idint;
DECLAREv_user_idint;
DECLAREv_typevarchar(20);
DECLAREv_totaldecimal(20,8)DEFAULT0;
DECLAREv_moneydecimal(20,8)DEFAULT0;
DECLAREv_use_moneydecimal(20,8)DEFAULT0;
DECLAREv_no_use_moneydecimal(20,8)DEFAULT0;
DECLAREv_collectiondecimal(20,8)DEFAULT0;
DECLAREv_to_userint(11);
DECLAREv_remarkVARCHAR(1000);
DECLAREv_addtimevarchar(13);
DECLAREv_addipvarchar(64);
DECLAREv_first_borrow_use_moneydecimal(20,8)DEFAULT0;
DECLAREdoneVARCHAR(45)DEFAULT'';
DECLAREt_errorintDEFAULT0;
DECLAREc_accountlogCURSORFOR
SELECTID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEYFROM(
SELECTID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEYFROMrocky_accountlog
WHEREADDTIME>=v_addtime_beginANDADDTIME<=v_addtime_endAND(type='tender_cold'ortype='repayment_deduct')
)tGROUPBYt.user_idHAVINGcount(t.user_id)>1;
DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=NULL;
OPENc_accountlog;
FETCHc_accountlogINTOv_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money;
WHILE(doneISNOTNULL)DO
INSERTINTOrocky_accountlog_test2(ACCOUNTLOG_ID,USER_ID,TYPE,TOTAL,MONEY,USE_MONEY,NO_USE_MONEY,COLLECTION,TO_USER,REMARK,ADDTIME,ADDIP,FIRST_BORROW_USE_MONEY,BORROW_ID)
VALUES(v_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money,v_borrow_id);
FETCHc_accountlogINTOv_id,v_user_id,v_type,v_total,v_money,v_use_money,v_no_use_money,v_collection,v_to_user,v_remark,v_addtime,v_addip,v_first_borrow_use_money;
ENDWHILE;
CLOSEc_accountlog;
END;
ENDIF;
SETs1=s1+1;
ENDWHILE;
CLOSEc_borrow;
COMMIT;--事务提交
END
