MySQL——雙重迴圈

標籤：comm   code   begin   流程   int   schedule   span   助理   cal   

-- 函數-- 設定好時區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——雙重迴圈