mysql游标嵌套循环例子

CREATE PROCEDURE finance_reciveDetail(customer_id varchar(20))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE id_a bigint;
  DECLARE pre_recv_remain_a decimal(12,2);
	DECLARE storageId_a varchar(20);
  DECLARE pre_recv_remain_b decimal(12,2);
  DECLARE storageId_b varchar(20);
  DECLARE cur1 CURSOR FOR SELECT id,pre_recv_remain,storage_id
			 FROM `t_fin_pre_recive` WHERE 
			 pre_recv_customer_id = customer_id
			 AND pre_recv_remain>0  ORDER BY create_time ASC;

DECLARE cur2 CURSOR FOR SELECT ABS(pre_recv_remain),storage_id FROM 
				`t_fin_pre_recive` WHERE  pre_recv_remain<0 AND pre_recv_customer_id=customer_id;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  -- OPEN cur1;
  OPEN cur2;
	WHILE done = 0 DO -- 要有不然循环一次
		FETCH cur2 INTO pre_recv_remain_b,storageId_b; -- 退款的
    IF NOT done THEN  -- 要有不然循环一次
      OPEN cur1; 
			WHILE done = 0 DO  -- 要有不然循环一次
			FETCH cur1 INTO id_a, pre_recv_remain_a,storageId_a;
			IF NOT done THEN  -- 要有不然循环一次
			IF (pre_recv_remain_a < pre_recv_remain_b AND storageId_b=storageId_a)  THEN
          INSERT INTO `t_fin_pre_recive_order` (
		            pre_recv_id,
		            order_id,
		            order_amount,
		            order_type,
		            create_time
		            ) VALUES  (id_a,'',pre_recv_remain_a,2,NOW());
				UPDATE `t_fin_pre_recive` set pre_recv_remain ='0' WHERE id=id_a;
       ELSEIF pre_recv_remain_a = pre_recv_remain_b AND storageId_b=storageId_a THEN 
          INSERT INTO `t_fin_pre_recive_order`(
		            pre_recv_id,
		            order_id,
		            order_amount,
		            order_type,
		            create_time
		            ) VALUES  (id_a,'',pre_recv_remain_a,2,NOW());
				UPDATE `t_fin_pre_recive` set pre_recv_remain ='0' WHERE id=id_a;
			ELSEIF pre_recv_remain_a > pre_recv_remain_b AND storageId_b=storageId_a THEN
				INSERT INTO `t_fin_pre_recive_order` (
		            pre_recv_id,
		            order_id,
		            order_amount,
		            order_type,
		            create_time
		            ) VALUES  (id_a,'',pre_recv_remain_b,2,NOW());
				UPDATE `t_fin_pre_recive` set pre_recv_remain =(pre_recv_remain_a-pre_recv_remain_b) WHERE  id= id_a;
       END IF;
    END IF;
		END WHILE ;
		CLOSE cur1;
		SET done = 0 ; -- 重点设置为0 继续循环不然done=1时直接不循环了，外层的也不循环了
  END IF; 
END WHILE;
  CLOSE cur2;
END