mysql 存储过程嵌套循环

存储过程嵌套循环

1、while方式

drop procedure  IF EXISTS member_service;
CREATE   PROCEDURE `member_service`(IN order_id_in BIGINT,IN start_date_in VARCHAR(10),IN end_date_in VARCHAR(10),OUT msg_out VARCHAR(100))
BEGIN
 
      DECLARE done INT DEFAULT 0;
      DECLARE l_order_id                          BIGINT;-- 订单id 
	  DECLARE l_order_stored_amount	              BIGINT;-- 订单储值卡支付金额
      DECLARE l_status                            INT;-- 订单状态



      -- 查询订单信息
      DECLARE orders    CURSOR FOR	
			select so.id,IFNULL(so.stored_value_amount,0) stored_value_amount,so.status
			from   shop_order so
			where   so.type = 2
			AND   so.id = order_id_in
			AND   date_format(so.created_at,'%Y%m%d') >= start_date_in
			AND   date_format(so.created_at,'%Y%m%d') <= end_date_in 
			ORDER BY so.id;

      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET done = -1; -- 在执行过程中出任何异常设置done为-1
	  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

       START TRANSACTION; -- 开始事务

           -- 打开订单游标
           OPEN orders;
 							FETCH orders INTO l_order_id,l_order_stored_amount,l_status;											
							while done<>1 DO

							BEGIN

								DECLARE done2 int DEFAULT 0; 
								DECLARE l_member_service_id         BIGINT;-- 会员卡项id
								DECLARE l_order_detail_id           BIGINT;
								DECLARE l_order_service_product_id  BIGINT;
													
							
								-- 查询会员卡项
								DECLARE   memberServices  CURSOR FOR
									SELECT  sms.id,sms.order_detail_id,sms.order_service_product_id
									from  shop_member_service  sms 
									where sms.order_id = l_order_id 
									and sms.is_deleted = 0;

									DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;


									-- 开启会员卡项游标					
									 OPEN memberServices;
									 
											FETCH memberServices INTO l_member_service_id,l_order_detail_id,l_order_service_product_id;
										  WHILE done2 <> 1 DO
												-- 业务代码
										
									 FETCH memberServices INTO l_member_service_id,l_order_detail_id,l_order_service_product_id;	
									 END WHILE;
									 CLOSE memberServices;
					        END;

					 FETCH orders INTO l_order_id,l_order_stored_amount,l_status;	
           END WHILE;
					 CLOSE orders;
					-- 关闭订单游标


        IF done = -1 THEN 
 					ROLLBACK;
 					set  msg_out = '存储过程执行失败!';
        ELSE 
           COMMIT; 
 					 set  msg_out = '存储过程执行成功!';
        END IF;
      
	   select msg_out;
    END

2、Loop方式

DROP PROCEDURE IF EXISTS month_partners_report；
CREATE   PROCEDURE `month_partners_report`(IN month_str_in VARCHAR(10),OUT msg_out VARCHAR(100))
BEGIN
 
      DECLARE done            INT DEFAULT 0;
      DECLARE l_shop_id       BIGINT;
      DECLARE l_shop_name     VARCHAR(200);
      DECLARE l_partners_id   BIGINT;
      DECLARE l_partners_name VARCHAR(255); 
      

      -- 查询门店信息
      DECLARE shops    CURSOR FOR
			select bs.id,bs.name
			from shop bs
			where  bs.type =2 and  bs.is_deleted = 0;
																

     -- 查询合作方信息
     DECLARE partners  CURSOR FOR
		SELECT id,name from partners bp where bp.is_deleted = 0;

     


      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET done = -1; -- 在执行过程中出任何异常设置done为-1
	  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

      START TRANSACTION; -- 开始事务

      

	  -- 打开合作方游标
      OPEN partners;
 					 partners_loop: LOOP
 							FETCH partners INTO l_partners_id, l_partners_name;
 							IF done = 1
 										THEN LEAVE partners_loop;
							ELSE
							
                            -- 开启门店游标
							 OPEN shops;
							 shops_Loop: Loop
							 	FETCH shops INTO l_shop_id, l_shop_name;
							 	IF done = 1
								 		THEN LEAVE shops_Loop;
							 	ELSE

					                  -- 业务代码start
					
									  -- 业务代码 end
       
                 
							 END IF;
							 END LOOP shops_Loop;
							 CLOSE shops;-- 关闭门店游标
	 			 

					 SET done = 0;
 					-- 关闭合作方游标
         
         
        END IF;
        END LOOP partners_loop;
		CLOSE partners;


		


       IF done = -1 THEN 
					ROLLBACK;
					set  msg_out = '存储过程执行失败!';
       ELSE 
          COMMIT; 
					set  msg_out = '存储过程执行成功!';
       END IF;
      
	  select msg_out;
    END