declare list varchar(200); declare cur_manu cursor for select number ,id where number in list 我定义了个游标报错,但是我单独把select语句拿出来执行就没有问题,list的值是('1','2').请问各位高手如何解决啊?我找了好多资料都不可以。谢谢
没办法,declare cursor 中后面一定是一句SQL语句,你的这个 list 是个字符串变量,无法直接加入。相同的功能你可以用下面这种方法。declare cur_manu cursor for select number ,id where FIND_IN_SET(number,list);
给一些测试数据,否则无法模拟你的错误和问题。1。 表结构 show create table 2。 测试数据 select * from .. 3。 你的 create procedure 代码你可以做适当的简化,但要能保持错误的再现。
我的表结构如下:CREATE TABLE `manu` ( `id` int(11) NOT NULL auto_increment, `manu_number` varchar(20) default NULL, `name` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk; 数据为: insert into `manu` (`id`,`manu_number`,`name`) values (1,'200901','name1'); 存储过程为:drop procedure if exists `aa`;DELIMITER $$CREATE PROCEDURE `aa`( glist varchar(8000)) BEGIN declare done int default 0; declare gid int ; declare manu cursor for select manu_number,name from manu where FIND_IN_SET(manu_number,glist); DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done=1; select(glist); open manu; select('0'); FETCH NEXT FROM manu INTO gid; select('1'); while(done!=1) do select('123'); FETCH NEXT FROM manu INTO gid; end while; END$$DELIMITER ;调用语句为:call aa('(''200901'',''200901'')'); 为什么执行语句不进入while循环语句呢啊?
mysql> select * from manu; +----+-------------+-------+ | id | manu_number | name | +----+-------------+-------+ | 1 | 200901 | name1 | +----+-------------+-------+ 1 row in set (0.00 sec)mysql> DELIMITER $$ mysql> mysql> CREATE PROCEDURE `aa`( -> glist varchar(8000)) -> BEGIN -> declare done int default 0; -> declare gid int ; -> declare manu1 cursor for -> select manu_number -> from manu -> where FIND_IN_SET(manu_number,glist); -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done=1; -> select(glist); -> open manu1; -> select 0; -> FETCH NEXT FROM manu1 INTO gid; -> select 1,gid; -> while(done!=1) do -> select('123'); -> FETCH NEXT FROM manu1 INTO gid; -> end while; -> END$$ Query OK, 0 rows affected (0.00 sec)mysql> mysql> DELIMITER ; mysql> call aa('200901,200901'); +---------------+ | (glist) | +---------------+ | 200901,200901 | +---------------+ 1 row in set (0.00 sec)+---+ | 0 | +---+ | 0 | +---+ 1 row in set (0.98 sec)+---+--------+ | 1 | gid | +---+--------+ | 1 | 200901 | +---+--------+ 1 row in set (0.98 sec)+-----+ | 123 | +-----+ | 123 | +-----+ 1 row in set (0.98 sec)Query OK, 0 rows affected (1.00 sec)mysql>
2。 测试数据 select * from ..
3。 你的 create procedure 代码你可以做适当的简化,但要能保持错误的再现。
`id` int(11) NOT NULL auto_increment,
`manu_number` varchar(20) default NULL,
`name` varchar(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
数据为:
insert into `manu` (`id`,`manu_number`,`name`) values (1,'200901','name1');
存储过程为:drop procedure if exists `aa`;DELIMITER $$CREATE PROCEDURE `aa`(
glist varchar(8000))
BEGIN
declare done int default 0;
declare gid int ;
declare manu cursor for
select manu_number,name
from manu
where FIND_IN_SET(manu_number,glist);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done=1;
select(glist);
open manu;
select('0');
FETCH NEXT FROM manu INTO gid;
select('1');
while(done!=1) do
select('123');
FETCH NEXT FROM manu INTO gid;
end while;
END$$DELIMITER ;调用语句为:call aa('(''200901'',''200901'')');
为什么执行语句不进入while循环语句呢啊?
+----+-------------+-------+
| id | manu_number | name |
+----+-------------+-------+
| 1 | 200901 | name1 |
+----+-------------+-------+
1 row in set (0.00 sec)mysql> DELIMITER $$
mysql>
mysql> CREATE PROCEDURE `aa`(
-> glist varchar(8000))
-> BEGIN
-> declare done int default 0;
-> declare gid int ;
-> declare manu1 cursor for
-> select manu_number
-> from manu
-> where FIND_IN_SET(manu_number,glist);
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done=1;
-> select(glist);
-> open manu1;
-> select 0;
-> FETCH NEXT FROM manu1 INTO gid;
-> select 1,gid;
-> while(done!=1) do
-> select('123');
-> FETCH NEXT FROM manu1 INTO gid;
-> end while;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql> call aa('200901,200901');
+---------------+
| (glist) |
+---------------+
| 200901,200901 |
+---------------+
1 row in set (0.00 sec)+---+
| 0 |
+---+
| 0 |
+---+
1 row in set (0.98 sec)+---+--------+
| 1 | gid |
+---+--------+
| 1 | 200901 |
+---+--------+
1 row in set (0.98 sec)+-----+
| 123 |
+-----+
| 123 |
+-----+
1 row in set (0.98 sec)Query OK, 0 rows affected (1.00 sec)mysql>