MySQL 游标不能循环了,只能执行第一条,后面执行不了CREATE PROCEDURE sp_xxx()
BEGIN DECLARE _done INT DEFAULT 0;
DECLARE _id INT DEFAULT 0;
/*光标*/
DECLARE _cur1 CURSOR FOR SELECT id FROM ppp;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
OPEN _cur1;
REPEAT
FETCH _cur1 INTO _id;
IF NOT _done THEN
select _id; --操作
END IF;
UNTIL _done END REPEAT;
CLOSE _cur1;
END;
BEGIN DECLARE _done INT DEFAULT 0;
DECLARE _id INT DEFAULT 0;
/*光标*/
DECLARE _cur1 CURSOR FOR SELECT id FROM ppp;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
OPEN _cur1;
REPEAT
FETCH _cur1 INTO _id;
IF NOT _done THEN
select _id; --操作
END IF;
UNTIL _done END REPEAT;
CLOSE _cur1;
END;
这一句有问题吧?
DECLARE CONTINUE HANDLER FOR not found SET _done = 1;
-> BEGIN
->
-> DECLARE _done INT DEFAULT 0;
-> DECLARE _id INT DEFAULT 0;
->
->
-> /*光标*/
-> DECLARE _cur1 CURSOR FOR SELECT id FROM ppp;
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
->
-> OPEN _cur1;
->
-> REPEAT
-> FETCH _cur1 INTO _id;
-> IF NOT _done THEN
-> select _id;
-> END IF;
-> UNTIL _done END REPEAT;
->
-> CLOSE _cur1;
-> END//
Query OK, 0 rows affected (0.25 sec)mysql> DELIMITER ;
mysql> call sp_xxx();
+------+
| _id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)+------+
| _id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>