结贴结快了。发现还是有问题[SQL] create procedure x()
begin
DECLARE done INT DEFAULT 0;
DECLARE a,b INT;
DECLARE c varchar(10);
DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post order by pid limit 24000;
DECLARE cur2 CURSOR FOR SELECT pid,subject FROM pra_forum_post order by pid limit 24000,24000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1;
OPEN cur2; FETCH cur1 INTO a;
FETCH cur2 INTO b, c; WHILE done = 0 DO
update post set subject=c where pid=a; FETCH cur1 INTO a;
FETCH cur2 INTO b, c;
END WHILE;
CLOSE cur1;
CLOSE cur2;end;
delimiter ;
call x();[Err] 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 'x()
begin
DECLARE done INT DEFAULT 0;
DECLARE a,b INT;
DECLARE c varchar(' at line 1
begin
DECLARE done INT DEFAULT 0;
DECLARE a,b INT;
DECLARE c varchar(10);
DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post order by pid limit 24000;
DECLARE cur2 CURSOR FOR SELECT pid,subject FROM pra_forum_post order by pid limit 24000,24000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1;
OPEN cur2; FETCH cur1 INTO a;
FETCH cur2 INTO b, c; WHILE done = 0 DO
update post set subject=c where pid=a; FETCH cur1 INTO a;
FETCH cur2 INTO b, c;
END WHILE;
CLOSE cur1;
CLOSE cur2;end;
delimiter ;
call x();[Err] 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 'x()
begin
DECLARE done INT DEFAULT 0;
DECLARE a,b INT;
DECLARE c varchar(' at line 1
CREATE PROCEDURE X()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a INT;
DECLARE b INT;
DECLARE c VARCHAR(10);
DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post ORDER BY pid LIMIT 24000;
DECLARE cur2 CURSOR FOR SELECT pid,SUBJECT FROM pra_forum_post ORDER BY pid LIMIT 24000,24000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
FETCH cur1 INTO a;
FETCH cur2 INTO b,c;
WHILE done = 0 DO
UPDATE post SET `subject`=c WHERE pid=a;
FETCH cur1 INTO a;
FETCH cur2 INTO b,c;
END WHILE;
CLOSE cur1;
CLOSE cur2;
END;$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE X()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a,b INT;
DECLARE c VARCHAR(10);
DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post ORDER BY pid LIMIT 24000;
DECLARE cur2 CURSOR FOR SELECT pid,SUBJECT FROM pra_forum_post ORDER BY pid LIMIT 24000,24000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
FETCH cur1 INTO a;
FETCH cur2 INTO b,c;
WHILE done = 0 DO
UPDATE post SET `subject`=c WHERE pid=a;
FETCH cur1 INTO a;
FETCH cur2 INTO b,c;
END WHILE;
CLOSE cur1;
CLOSE cur2;
END;$$
DELIMITER ;
-> begin
-> DECLARE done INT DEFAULT 0;
-> DECLARE a,b INT;
-> DECLARE c varchar(10);
-> DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post order by pid lim
it 24000;
-> DECLARE cur2 CURSOR FOR SELECT pid,subject FROM pra_forum_post order by
pid limit 24000,24000;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
-> OPEN cur2;
->
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b, c;
->
-> WHILE done = 0 DO
-> update post set subject=c where pid=a;
->
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b, c;
-> END WHILE;
-> CLOSE cur1;
-> CLOSE cur2;
->
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
-> begin
-> DECLARE done INT DEFAULT 0;
-> DECLARE a,b INT;
-> DECLARE c varchar(10);
-> DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post order by pid lim
it 24000;
-> DECLARE cur2 CURSOR FOR SELECT pid,subject FROM pra_forum_post order by
pid limit 24000,24000;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
-> OPEN cur2;
->
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b, c;
-> WHILE done = 0 DO
-> update post set subject=c where pid=a;
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b, c;
-> END WHILE;
-> CLOSE cur1;
-> CLOSE cur2;
->
-> end;
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 'x()beginDECLARE done INT DEFAULT 0;DECLARE a,b INT;DECLARE c' at line 1
mysql>
mysql> DELIMITER $$
mysql> CREATE PROCEDURE X()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE a INT;
-> DECLARE b INT;
-> DECLARE c VARCHAR(10);
-> DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post ORDER BY pid LIMIT 24000;
-> DECLARE cur2 CURSOR FOR SELECT pid,SUBJECT FROM pra_forum_post ORDER BY pid LIMIT 24000,24000;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-> OPEN cur1;
-> OPEN cur2;
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b,c;
-> WHILE done = 0 DO
-> UPDATE post SET `subject`=c WHERE pid=a;
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b,c;
-> END WHILE;
-> CLOSE cur1;
-> CLOSE cur2;
-> END;$$
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 'X()BEGINDECLARE done INT DEFAULT 0;DECLARE a INT; DECLARE b ' at line 1
mysql>
+------------------+
| version() |
+------------------+
| 5.1.47-community |
+------------------+
1 row in set (0.00 sec)mysql>另外再试下
create procedure x1()
select 1;
mysql> create procedure x1()
-> select 1;
Query OK, 0 rows affected (0.00 sec)mysql> call x1();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>
+------------+
| version() |
+------------+
| 5.0.91-log |
+------------+
1 row in setmysql> create procedure x1()
select 1;
Query OK, 0 rows affectedmysql> call x1();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in setQuery OK, 0 rows affectedmysql>
mysql> CREATE PROCEDURE X()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE a INT;
-> DECLARE b INT;
-> DECLARE c VARCHAR(10);
-> DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post ORDER BY pid LIMIT 24000;
-> DECLARE cur2 CURSOR FOR SELECT pid,SUBJECT FROM pra_forum_post ORDER BY pid LIMIT 24000,24000;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-> OPEN cur1;
-> OPEN cur2;
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b,c;
-> WHILE done = 0 DO
-> UPDATE post SET `subject`=c WHERE pid=a;
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b,c;
-> END WHILE;
-> CLOSE cur1;
-> CLOSE cur2;
-> END;$$
-> DELIMITER ;
-> call X();
-> 这次居然什么反应都没有?
mysql> CREATE PROCEDURE X()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE a INT;
-> DECLARE b INT;
-> DECLARE c VARCHAR(80);
-> DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post ORDER BY pid LIMIT 24000;
-> DECLARE cur2 CURSOR FOR SELECT pid,SUBJECT FROM pra_forum_post ORDER BY pid LIMIT 24000,24000;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-> OPEN cur1;
-> OPEN cur2;
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b,c;
-> WHILE done = 0 DO
-> UPDATE post SET `subject`=c WHERE pid=a;
-> FETCH cur1 INTO a;
-> FETCH cur2 INTO b,c;
-> END WHILE;
-> CLOSE cur1;
-> CLOSE cur2;
-> END;$$
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 'X()BEGINDECLARE done INT DEFAULT 0;DECLARE a INT;DECLARE b I' at line 1
mysql>
drop PROCEDURE X$$
CREATE PROCEDURE X()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a INT;
DECLARE b INT;
DECLARE c VARCHAR(80);
DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post ORDER BY pid LIMIT 24000;
DECLARE cur2 CURSOR FOR SELECT pid,SUBJECT FROM pra_forum_post ORDER BY pid LIMIT 24000,24000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
FETCH cur1 INTO a;
FETCH cur2 INTO b,c;
WHILE done = 0 DO
UPDATE post SET `subject`=c WHERE pid=a;
FETCH cur1 INTO a;
FETCH cur2 INTO b,c;
END WHILE;
CLOSE cur1;
CLOSE cur2;
END;$$
CREATE PROCEDURE xxx()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a INT;
DECLARE b INT;
DECLARE c VARCHAR(80);
DECLARE cur1 CURSOR FOR SELECT pid FROM pra_forum_post ORDER BY pid LIMIT 24000;
DECLARE cur2 CURSOR FOR SELECT pid,SUBJECT FROM pra_forum_post ORDER BY pid LIMIT 24000,24000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
FETCH cur1 INTO a;
FETCH cur2 INTO b,c;
WHILE done = 0 DO
UPDATE pra_forum_post SET `subject`=c WHERE pid=a;
FETCH cur1 INTO a;
FETCH cur2 INTO b,c;
END WHILE;
CLOSE cur1;
CLOSE cur2;
END;$$
DELIMITER ;