mysql 二重循环_MySQL——循环（双重循环）

--函数

--设置好时区

set time_zone = '+8:00';--开启事件调度器

set GLOBAL event_scheduler = 1;drop event if existscommission_ms_ever_month;

# 设置分隔符为'$$'DELIMITER $$createevent commission_ms_ever_monthon schedule every 1 MONTH starts '2018-07-01 03:00:00'dobeginCALL proc_commission_ms();end$$

# 将语句分割符设置回';'DELIMITER ;DROP PROCEDURE IF EXISTSproc_commission_ms;CREATE PROCEDUREproc_commission_ms()BEGIN

--1、下户就有提成，但订单状态为拒单或者放款时才加入计算--2、以填写面审信息流程的第一次提交账号为提成归属人--3、以主管审核(风控助理操作)流程的第一次提交时间作为下户时间；(所以要有终审节点)--4、下户时间与拒单时间，下户时间与放款时间不能跨3个月；--！！！ v_select_mytask_txmsxx 填写面试信息 v_select_mytask_zgsh主管审核 v_select_mytask_zs终审 v_order_reject拒单 v_task_loaned已放款

--定义变量 (变量名称不能和select 接收别名一样)

DECLARE txTime1 datetime;DECLARE orderCode1 CHAR(100);DECLARE receiveId1 int(11);DECLARE receiveName1 CHAR(100);DECLARE xhTime1 datetime;DECLARE submitRole1 CHAR(255);DECLARE orderCode2 CHAR(100);DECLARE doneTime2 CHAR(100);--定义结束标识 并绑定游标

DECLARE done INT DEFAULTFALSE;DECLARE edone INT DEFAULTFALSE;--定义游标01 --- 没有3个月限制的数据 以及结束标识

DECLARE _outerForEach CURSOR FOR

SELECT

MIN(tx.finish_time) AStxTime,

tx.order_codeASorderCode,

tx.receive_idASreceiveId,

tx.receive_nameASreceiveName,MIN(sh.finish_time) ASxhTime,

tx.submit_roleASsubmitRoleFROMv_mytask_txmsxx tx,

v_mytask_zgsh shWHEREtx.order_code=sh.order_codeAND tx.order_code in(SELECT order_code FROMv_mytask_zs)ANDtx.order_codeIN(SELECT order_code FROM v_order_lastmonth_reject_loaned --上个月产生的新拒单+放款

)GROUP BYtx.order_code;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;--结束标识

# 打开游标1OPEN_outerForEach;

read_loop: LOOP--循环游标开始,

FETCH _outerForEach INTOtxTime1, orderCode1, receiveId1, receiveName1, xhTime1, submitRole1;IF done THENLEAVE read_loop;#跳出循环END IF;BEGIN# 定义游标02--- 已放款的和拒单的单子 --并 排除已经进入面审提成库的单子

DECLARE _innerForEach CURSOR FOR

SELECT order_code as orderCode,finish_time AS doneTime FROMv_order_rejectWHERE order_code not in(SELECT order_code FROM commission_inquiry_collection WHERE type = 1)UNION ALL

SELECT orderCode as orderCode,finishTime AS doneTime FROMv_task_loanedWHERE orderCode not in(SELECT order_code FROM commission_inquiry_collection WHERE type = 1);DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;#结束标识

# 打开游标2OPEN_innerForEach;

inner_loop: LOOPFETCH _innerForEach INTOorderCode2,doneTime2;IF edone THENLEAVE inner_loop;ELSE#(处理业务)订单号一致，而且时间差3个月以内，插入统计表IF (orderCode2 = orderCode1 AND xhTime1 IS NOT NULL AND doneTime2 IS NOT NULL AND TIMESTAMPDIFF(MONTH,xhTime1,doneTime2) <= 3) THEN

INSERT INTO`commission_inquiry_collection` (

`user_id`,

`order_code`,

`execute_time`,

`task_code`,

`count_time`,

`commission_time`,

`type`,

`submit_role`,

`city`,

`ext3`

)VALUES (receiveId1,orderCode1,xhTime1,"T_TXMSXX_0002",NOW(),DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'1',submitRole1,NULL,NULL);END IF;END IF;ENDLOOP;CLOSE _innerForEach;--关闭内层游标

SET edone = FALSE;--内循环复位 以便再次循环

END;END LOOP; --结束循环

CLOSE _outerForEach;--关闭游标

COMMIT;END;