mysql 存储过程

DELIMITER $$

USE `elephant`$$

DROP PROCEDURE IF EXISTS `procs_cal_order_overdue`$$

CREATE DEFINER=`root`@`%` PROCEDURE `procs_cal_order_overdue`()
BEGIN
      DECLARE order_code VARCHAR(50);
      DECLARE overdue_status INT;
      DECLARE max_overdue_days INT;
      DECLARE user_id INT;
      DECLARE current_overdue_status INT;
      DECLARE overdue_amount DOUBLE;
      DECLARE current_max_overdue_days INT;
      
      DECLARE done INT DEFAULT FALSE;
      DECLARE i INT DEFAULT 0;
      
      DECLARE cur CURSOR FOR 
      (
        SELECT 
        t1.order_code order_code, 
        CASE WHEN SUM(t1.everoverdue)>0 THEN 1 ELSE 0 END overdue_status,
        MAX(t1.overdays) max_overdue_days,
        t1.user_id user_id,
        CASE WHEN SUM(t1.cover)>0 THEN 1 ELSE 0 END current_overdue_status,
        ROUND(SUM(t1.overamount),2) overdue_amount,
        MAX(t1.coverdays) current_max_overdue_days
        FROM 
        (
        SELECT 
        a.order_code,
        CASE WHEN b.repayment_status='2' THEN d.overdue_days WHEN b.repayment_status='3' THEN c.overdue_days ELSE 0 END overdays,
        CASE WHEN b.repayment_status IN ('2','3') THEN 1 ELSE 0 END everoverdue,
        a.user_id,
        CASE WHEN b.repayment_status='2' THEN 1 ELSE 0 END cover,
        CASE WHEN b.repayment_status IN ('2','5') THEN d.overdue_amount ELSE 0 END overamount,
        CASE WHEN b.repayment_status='2' THEN d.overdue_days ELSE 0 END coverdays
        FROM t_am_bystages_order a
        LEFT JOIN t_am_repayment_plan b ON a.order_id=b.order_id
        LEFT JOIN t_am_repayment_detail c ON b.id=c.repayment_plan_id
        LEFT JOIN t_report_plan_overdue_status d ON b.id=d.plan_id
        WHERE a.status IN ('1','3')
        ) t1
        GROUP BY t1.order_code
      );
      
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
      
      SET @exesql = "";      
      SET @exedata = "";      
      
      OPEN cur;
      myloop :LOOP
        FETCH cur INTO order_code,overdue_status,max_overdue_days,user_id,current_overdue_status,overdue_amount,current_max_overdue_days;
        IF i!=0 AND i%1000=0 THEN
           SET @exedata = SUBSTRING(@exedata, 2);
           SET @exesql = CONCAT("REPLACE into t_report_order_overdue_status(order_code,overdue_status,max_overdue_days,user_id,create_time,current_overdue_status,overdue_amount,current_max_overdue_days) values ", @exedata);   
           PREPARE stmt FROM @exesql;
           EXECUTE stmt;
           DEALLOCATE PREPARE stmt;
           SET @exedata = "";
        END IF;        
        IF done THEN 
             IF i!=0 THEN 
             SET @exedata = SUBSTRING(@exedata, 2);
             SET @exesql = CONCAT("REPLACE into t_report_order_overdue_status(order_code,overdue_status,max_overdue_days,user_id,create_time,current_overdue_status,overdue_amount,current_max_overdue_days) values ", @exedata);          
             PREPARE stmt FROM @exesql;
             EXECUTE stmt;
             DEALLOCATE PREPARE stmt;
             END IF;
           LEAVE myloop;
        END IF;
        SET @exedata = CONCAT(@exedata, ",('",order_code,"',",overdue_status,",",max_overdue_days,",",user_id,",'",NOW(),"',",current_overdue_status,",",overdue_amount,",",current_max_overdue_days,")");
        SET i=i+1;       
        /*REPLACE into t_report_plan_overdue_status (plan_id,overdue_days,overdue_amount,create_time,user_id,order_code,order_id,bs_code) values 
        (planId,overdueDays,overdueAmount,now(),userId,orderCode,orderId,bsCode);*/
      END LOOP;
      CLOSE cur;      
      
    END$$

DELIMITER ;

主要就是从多张表汇总数据到一张表，涉及到了sql拼接，游标遍历，分批提交