-- 函数
-- 设置好时区
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;
MySQL——循环(双重循环)
最新推荐文章于 2024-07-22 03:19:09 发布