-- 函数-- 设置好时区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;