CREATE DEFINER=`root`@`localhost` PROCEDURE `xxxx_SYNC`(OUT count INT, OUT EX INT) BEGIN DECLARE MODEL VARCHAR(32); DECLARE PART_NR VARCHAR(32); DECLARE icount INT DEFAULT 0; DECLARE _Done INT DEFAULT 0; DECLARE _Cur CURSOR FOR select MODEL, KP_PN from xxxx where xxxx_site=17 and KP_PN <>'None' and MODEL is not null and ct_date>now()-interval 10 day group by MODEL, KP_PN;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _Done = 1; set _Done=0; OPEN _Cur; REPEAT FETCH _Cur INTO MODEL, PART_NR; set EX=_Done; IF NOT _Done THEN set EX=-1; insert into yyyy(model, part_nr, usage_dt) values(MODEL, PART_NR, now()) on DUPLICATE KEY UPDATE USAGE_DT=NOW(); END IF; set icount=icount+1; set _Done=EX; UNTIL _Done END REPEAT; set count=icount; CLOSE _Cur; END
BEGIN
DECLARE MODEL VARCHAR(32);
DECLARE PART_NR VARCHAR(32);
DECLARE icount INT DEFAULT 0;
DECLARE _Done INT DEFAULT 0;
DECLARE _Cur CURSOR FOR
select MODEL, KP_PN from xxxx
where xxxx_site=17 and KP_PN <>'None' and MODEL is not null and ct_date>now()-interval 10 day
group by MODEL, KP_PN;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _Done = 1;
set _Done=0;
OPEN _Cur;
REPEAT
FETCH _Cur INTO MODEL, PART_NR;
set EX=_Done;
IF NOT _Done THEN
set EX=-1;
insert into yyyy(model, part_nr, usage_dt) values(MODEL, PART_NR, now())
on DUPLICATE KEY
UPDATE USAGE_DT=NOW();
END IF;
set icount=icount+1;
set _Done=EX;
UNTIL _Done END REPEAT;
set count=icount;
CLOSE _Cur;
END
設置_Done=EX? 這個EX參數只是作為調式用的, 以便觀察_Done的具體數值.
EX的數值有兩種情況: 1, -1. 分別表示: 從未執行過Insert, 有執行過insert.運行你的代碼:
call BENCH_USAGE_SYNC(@count, @ex);
select @count,@ex;
執行后得到結果: 1, 1
結果是一樣的啊, 因為FETCH后_Done的值就是1, 就算後面set _Done=EX, 還是1, 就退出循環了.
自己找到答案了, 是本地變量的名稱不能與列名等關鍵字重複, anyway, 設置複雜點就是了~~~
搞定, 收工~