一个关于游标的有关问题

一个关于游标的问题
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 ; 

 

查看更多 下一篇