DECLARE _IDA INT;需要放到一开始所有语句之前。CREATE PROCEDURE p8 () BEGIN DECLARE _IDA INT;create table tmp1 (id int,pid int,subject varchar(80)); create table tmp2 (id int,pid int,subject varchar(80)); 但你的语句中还有其它错误。
subject in tmp1:是什么意思 ?
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 ; call x();
不好,还是有问题哦。[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
select _IDA:=_IDA+1 as ID,pid,subject into tmp1 from pra_forum_post limit 23999; 错误的地方还很多呢。
+-----+---------+
| pid | subject |
+-----+---------+
| 1 | A01 |
| 3 | A02 |
| 31 | A16 |
| 33 | A17 |
| 9 | A05 |
| 11 | A06 |
| 13 | A07 |
| 15 | A08 |
| 7 | A04 |
| 19 | A10 |
| 21 | A11 |
| 23 | A12 |
| 39 | A20 |
| 41 | A21 |
| 25 | A13 |
| 27 | A14 |
| 29 | A15 |
| 35 | A18 |
| 17 | A09 |
| 5 | A03 |
| 37 | A19 |
| 43 | A22 |
| 45 | A23 |
| 47 | A24 |
+-----+---------+
24 rows in set (0.00 sec)
mysql> delimiter //
mysql> create procedure x()
-> begin
-> DECLARE done INT DEFAULT 0;
-> DECLARE a,b INT;
-> DECLARE c varchar(10);
-> DECLARE cur1 CURSOR FOR SELECT pid FROM post order by pid limit 10;
-> DECLARE cur2 CURSOR FOR SELECT pid,subject FROM post order by pid limit
10,10;
-> 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.09 sec)mysql> delimiter ;
mysql> call x();
Query OK, 1 row affected (0.05 sec)mysql> select * from post order by pid;
+-----+---------+
| pid | subject |
+-----+---------+
| 1 | A11 |
| 3 | A12 |
| 5 | A13 |
| 7 | A14 |
| 9 | A15 |
| 11 | A16 |
| 13 | A17 |
| 15 | A18 |
| 17 | A19 |
| 19 | A20 |
| 21 | A11 |
| 23 | A12 |
| 25 | A13 |
| 27 | A14 |
| 29 | A15 |
| 31 | A16 |
| 33 | A17 |
| 35 | A18 |
| 37 | A19 |
| 39 | A20 |
| 41 | A21 |
| 43 | A22 |
| 45 | A23 |
| 47 | A24 |
+-----+---------+
24 rows in set (0.00 sec)mysql>
BEGIN
DECLARE _IDA INT;create table tmp1 (id int,pid int,subject varchar(80));
create table tmp2 (id int,pid int,subject varchar(80));
但你的语句中还有其它错误。
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 ;
call 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
错误的地方还很多呢。