MySQL——循环（双重循环）

– 函数

– 设置好时区
set time_zone = ‘+8:00’;
– 开启事件调度器
set GLOBAL event_scheduler = 1;

drop event if exists commission_ms_ever_month;

设置分隔符为 ‘$$’

DELIMITER $$

create event commission_ms_ever_month
on schedule every 1 MONTH starts ‘2018-07-01 03:00:00’
do
begin
CALL proc_commission_ms();
end $$

将语句分割符设置回 ‘;’

DELIMITER ;

DROP PROCEDURE IF EXISTS proc_commission_ms;
CREATE PROCEDURE proc_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 DEFAULT FALSE;
DECLARE edone INT DEFAULT FALSE; 
-- 定义游标01 --- 没有3个月限制的数据 以及结束标识
DECLARE _outerForEach CURSOR FOR  
        SELECT
            MIN(tx.finish_time) AS txTime,
            tx.order_code AS orderCode,
            tx.receive_id AS receiveId,
            tx.receive_name AS receiveName,
            MIN(sh.finish_time) AS xhTime,
            tx.submit_role AS submitRole
        FROM
            v_mytask_txmsxx tx,
            v_mytask_zgsh sh
        WHERE
            tx.order_code = sh.order_code
        AND tx.order_code in(SELECT order_code FROM v_mytask_zs)
        AND 
            tx.order_code IN (
                SELECT order_code FROM v_order_lastmonth_reject_loaned -- 上个月产生的新拒单+放款
            )
        GROUP BY
            tx.order_code;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 结束标识

    # 打开游标1 
OPEN _outerForEach;  
     read_loop: LOOP  -- 循环游标开始,
        FETCH _outerForEach INTO txTime1, orderCode1, receiveId1, receiveName1, xhTime1, submitRole1;
                    IF done THEN
                        LEAVE read_loop;#跳出循环
                    END IF;
                     BEGIN
                         # 定义游标02 --- 已放款的和拒单的单子 --并 排除已经进入面审提成库的单子
                        DECLARE _innerForEach CURSOR FOR  
                                 SELECT order_code as orderCode,finish_time AS doneTime FROM v_order_reject 
                                WHERE order_code not in(SELECT order_code FROM commission_inquiry_collection WHERE type = 1)
                                 UNION ALL 
                                 SELECT orderCode as orderCode,finishTime AS doneTime FROM v_task_loaned
                                WHERE orderCode not in(SELECT order_code FROM commission_inquiry_collection WHERE type = 1);  
                         DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone = 1;#结束标识
                         # 打开游标2 
                         OPEN _innerForEach;
                             inner_loop: LOOP
                                 FETCH _innerForEach INTO orderCode2,doneTime2;
                                     IF edone THEN
                                         LEAVE 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;
                             END LOOP;
                         CLOSE _innerForEach;-- 关闭内层游标
                        SET edone = FALSE;-- 内循环复位 以便再次循环
                     END;
        END LOOP; -- 结束循环
CLOSE _outerForEach;-- 关闭游标
 COMMIT;

END;