存储过程-遍历更新20181227

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`()
BEGIN
DECLARE _word INT;
DECLARE tmpstr varchar(64);
DECLARE mes varchar(64);
DECLARE paytm INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur_pay CURSOR FOR select order_mes,updatetime from pay_order;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_pay;
read_loop: LOOP
FETCH NEXT from cur_pay INTO mes,paytm;
IF done THEN
LEAVE read_loop;
END IF;

SET _word = LOCATE(',',mes);

WHILE _word > 0
DO
SET tmpstr = SUBSTR(mes,1,_word-1);
update order_orderlist set pay_tm = paytm where id = tmpstr;
SELECT * FROM order_orderlist where id = tmpstr;
SET mes = SUBSTR(mes FROM _word+1);
SET _word = LOCATE(',',mes);
END WHILE;
update order_orderlist set pay_tm = paytm where id = mes;
END LOOP;
CLOSE cur_pay;
END

转载于:https://www.cnblogs.com/daochong/p/10185847.html