mysql中遊標的使用

標籤：style   blog   color   os   ar   使用   for   sp   資料   

BEGIN#遍曆佔領野地表還原軍隊#update armforce set number=num where troopsid=(select id from troops where isfrist=1 and cityid=cityid)    DECLARE tId DECIMAL(21,0) default -1;#occupyWild表的TroopsId          DECLARE cid DECIMAL(21,0) default -1;#occupyWild表的CityId    DECLARE temp_tId DECIMAL(21,0) default -1;#occupyWild表的TroopsId          DECLARE temp_cid DECIMAL(21,0) default -1;#occupyWild表的CityId        DECLARE tId2 DECIMAL(21,0) default -1;#troops表的TroopsId         DECLARE num0 int default 0;        DECLARE num1 int default 0;        DECLARE num2 int default 0;        DECLARE num3 int default 0;        DECLARE curl CURSOR FOR SELECT TroopsId,CityId FROM occupyWild order by TroopsId ASC;          DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET tId = -1;        OPEN curl;      FETCH curl INTO tId,cid;      WHILE(tId<>-1) DO              #說明：遊標變數使用一次以後就會被清除          #************************ 邏輯 START ************************                set tId2=-1,temp_tId=-1,temp_cid=-1,num0=0,num1=0,num2=0,num3=0;                set temp_tId=tId;                set temp_cid=cid;                select id into tId2 from Troops where isFrist=1 and CityId=temp_cid limit 1;#正常情況下只有一條資料                select number into num0 from Armforce where TroopsId=temp_tId and ArmType=0;                select number into num1 from Armforce where TroopsId=temp_tId and ArmType=1;                select number into num2 from Armforce where TroopsId=temp_tId and ArmType=2;                select number into num3 from Armforce where TroopsId=temp_tId and ArmType=3;                update Armforce set Number=Number+num0 where TroopsId=tId2 and ArmType=0;#退回軍隊                delete from Armforce where TroopsId=temp_tId and ArmType=0;#刪除軍隊                update Armforce set Number=Number+num1 where TroopsId=tId2 and ArmType=1;                delete from Armforce where TroopsId=temp_tId and ArmType=1;                update Armforce set Number=Number+num2 where TroopsId=tId2 and ArmType=2;                delete from Armforce where TroopsId=temp_tId and ArmType=2;                update Armforce set Number=Number+num3 where TroopsId=tId2 and ArmType=3;                delete from Armforce where TroopsId=temp_tId and ArmType=3;                            #************************ 邏輯 END ************************      FETCH curl into tId,cid;      END WHILE;      CLOSE curl;  END

 

mysql中遊標的使用