我的存储过程如果是:
drop procedure if exists proc_test;
DELIMITER $$
CREATE PROCEDURE proc_test (
)
BEGIN DECLARE done INT DEFAULT 0;
declare myName varchar(20);
declare redoDate char(10); set redoDate = '2007-11-20';
DECLARE myCursor CURSOR FOR
select vc_name from table_mythings where vc_name like 'test%';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN myCursor;
REPEAT
FETCH myCursor INTO myName;
UNTIL done END REPEAT;
CLOSE myCursor;END $$
DELIMITER ;就会出现错误: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 'DECLARE myCursor CURSOR FOR
select vc_name from table_mythings where vc_name ' at line 8但是,仅仅把粗体的语句放在最下面,就可以通过。
这是怎么回事啊?????下面的存储过程可以通过:drop procedure if exists proc_test;DELIMITER $$
CREATE PROCEDURE proc_test (
)
BEGIN
DECLARE done INT DEFAULT 0;
declare myName varchar(20);
declare redoDate char(10); DECLARE myCursor CURSOR FOR
select vc_name from table_mythings where vc_name like 'test%';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN myCursor;
REPEAT
FETCH myCursor INTO myName;
UNTIL done END REPEAT;
CLOSE myCursor; set redoDate = '2007-11-20';
END $$
DELIMITER ;
drop procedure if exists proc_test;
DELIMITER $$
CREATE PROCEDURE proc_test (
)
BEGIN DECLARE done INT DEFAULT 0;
declare myName varchar(20);
declare redoDate char(10); set redoDate = '2007-11-20';
DECLARE myCursor CURSOR FOR
select vc_name from table_mythings where vc_name like 'test%';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN myCursor;
REPEAT
FETCH myCursor INTO myName;
UNTIL done END REPEAT;
CLOSE myCursor;END $$
DELIMITER ;就会出现错误: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 'DECLARE myCursor CURSOR FOR
select vc_name from table_mythings where vc_name ' at line 8但是,仅仅把粗体的语句放在最下面,就可以通过。
这是怎么回事啊?????下面的存储过程可以通过:drop procedure if exists proc_test;DELIMITER $$
CREATE PROCEDURE proc_test (
)
BEGIN
DECLARE done INT DEFAULT 0;
declare myName varchar(20);
declare redoDate char(10); DECLARE myCursor CURSOR FOR
select vc_name from table_mythings where vc_name like 'test%';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN myCursor;
REPEAT
FETCH myCursor INTO myName;
UNTIL done END REPEAT;
CLOSE myCursor; set redoDate = '2007-11-20';
END $$
DELIMITER ;
原来是 declare 语句一定是在最前面定义,而且中间不能有其他语句。解决办法:再加多一层 begin ... end 上去。这分可啥办啊?????
我遇到游标中必须使用游标的问题。一直找不到解决办法。
加多一层 begin...end 是指,把 begin...end 看成一个语句,在这语句里再写游标的定义,例如...
BEGIN
declare t int;
set t = 0; BEGIN
declare done int default 0;
DECLARE myCursor CURSOR FOR
......
CLOSE myCursor; END;END $$