mysql存储过程循环插入

0 0

mysql存储过程循环插入0

BEGIN

declare numareaId int(10);
declare currentareaId int(10);
declare typesIdx int(10);

SELECT COUNT(areaId),MIN(areaId) INTO @a,@b FROM option_area_info;
select max(idx) INTO @iTypesIdx from types;

SET typesIdx=@iTypesIdx;
SET numareaId=@a;
SET currentareaId=@b;

loop1:WHILE numareaId>0 DO

SET @AID = currentareaId;


select date_format(current_date(),'%y-%m-%d %23:%55:%00') INTO @cTime;

select current_date() INTO @curTime;

      loop2:WHILE typesIdx>0 DO
            select types,subType INTO @iTypes,@iSubType from types where idx=typesIdx;
            insert into game_con (areaId,mainChannel,channel,types,subType,useCount,useInputCount,useUnInputCount,conGold,conGmoney,conMilitary,conPubScore,conPst,createTime) values (
            currentareaId,
            1000,
            1000,
            @iTypes,
            @iSubType,
            (select count(playerId) from log_consumption where createTime>=@curTime and createTime<=@cTime and areaId=@AID and types=@iTypes and subType=@iSubType),
            (select count(playerId) from log_consumption where createTime>=@curTime and createTime<=@cTime and areaId=@AID and types=@iTypes and subType=@iSubType and vip>0),
            (select count(playerId) from log_consumption where createTime>=@curTime and createTime<=@cTime and areaId=@AID and types=@iTypes and subType=@iSubType and vip=0),
            (select sum(ggold) from log_consumption where createTime>=@curTime and createTime<=@cTime and areaId=@AID and types=@iTypes and subType=@iSubType),
            (select sum(gmoney) from log_consumption where createTime>=@curTime and createTime<=@cTime and areaId=@AID and types=@iTypes and subType=@iSubType),
            (select sum(military) from log_consumption where createTime>=@curTime and createTime<=@cTime and areaId=@AID and types=@iTypes and subType=@iSubType),
            (select sum(military) from log_consumption where createTime>=@curTime and createTime<=@cTime and areaId=@AID and types=@iTypes and subType=@iSubType),
            (select sum(pubScore) from log_consumption where createTime>=@curTime and createTime<=@cTime and areaId=@AID and types=@iTypes and subType=@iSubType),
            (NOW()));
    SET typesIdx=typesIdx-1;
    end WHILE loop2;

SET numareaId=numareaId-1;
SET currentareaId=currentareaId+1;

END WHILE loop1;

END

option_area_info 表里边有连个区，1区和2区。但是我写的这个存储过程为什么直插入了1区，却不能插入2区？是因为我嵌套的问题么？