一个关于游标的问题
sql数据准备:
-
SQL code
DROP TABLE IF EXISTS T_User; CREATE TABLE T_User( id INT PRIMARY KEY, age INT, tname VARCHAR(20) ); INSERT INTO T_User VALUE(1,11,'abc1'); INSERT INTO T_User VALUE(2,12,'abc2'); INSERT INTO T_User VALUE(3,13,'abc3'); INSERT INTO T_User VALUE(4,14,'abc4'); INSERT INTO T_User VALUE(5,15,'abc5'); INSERT INTO T_User VALUE(6,16,'abc6');
游标代码:
-
SQL code
CREATE PROCEDURE curdemo() BEGIN DECLARE age1 INT; DECLARE id1 INT; DECLARE Cur CURSOR FOR SELECT id,age FROM T_User; OPEN Cur; FETCH Cur INTO id1,age1; WHILE @@fetch_status=0 BEGIN UPDATE T_User SET age=age1+1 WHERE id=id1; FETCH Cur INTO id1,age1; END CLOSE Cur; DEALLOCATE Cur; END ############################################################################ CREATE PROCEDURE curdemo1() BEGIN DECLARE age1 INT; DECLARE id1 INT; DECLARE Cur CURSOR FOR SELECT id,age FROM T_User; DECLARE EXIT HANDLER FOR NOT FOUND CLOSE Cur; OPEN Cur; REPEAT FETCH Cur INTO id1,age1; UPDATE T_User SET age=age1+1 WHERE id=id1; UNTIL done END REPEAT; CLOSE Cur; DEALLOCATE; END;
这两个游标都不对,我用的是Mysql5.1的数据库,老是报语句错误。
错误提示:
Query : CREATE PROCEDURE curdemo() BEGIN DECLARE age1 INT
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 3
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
….
很多,都是这类的错误。
谁能帮我解决一下,谢谢!!!!
delimiter //
CREATE PROCEDURE curdemo()
BEGIN
DECLARE age1 INT;
DECLARE id1 INT;
DECLARE Cur CURSOR FOR SELECT id,age FROM T_User;
OPEN Cur;
FETCH Cur INTO id1,age1;
WHILE @@fetch_status=0
BEGIN
UPDATE T_User SET age=age1+1 WHERE id=id1;
FETCH Cur INTO id1,age1;
END
CLOSE Cur;
DEALLOCATE Cur;
END//
delimiter ;
DELIMITER $$
CREATE PROCEDURE curdemo()
BEGIN
DECLARE age1 INT;
DECLARE id1 INT;
DECLARE done INT DEFAULT 0;
DECLARE Cur CURSOR FOR SELECT id,age FROM T_User;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN Cur;
FETCH Cur INTO id1,age1;
WHILE done=0 DO
UPDATE T_User SET age=age1+1 WHERE id=id1;
FETCH Cur INTO id1,age1;
END WHILE ;
CLOSE Cur;
END$$
DELIMITER ;
其它的自行修改
-
SQL code
delimiter // CREATE PROCEDURE curdemo() BEGIN DECLARE age1 INT; DECLARE id1 INT; DECLARE Cur CURSOR FOR SELECT id,age FROM T_User; OPEN Cur; FETCH Cur INTO id1,age1; WHILE @@fetch_status=0 BEGIN UPDATE T_User SET age=age1+1 WHERE id=id1; FETCH Cur INTO id1,age1; END CLOSE Cur; DEALLOCATE Cur; END// delimiter ;