存储过程-遍历更新20181227

2018/12/27 16:41
阅读数 18

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

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部