存储过程变量赋值不灵了？

[fnzh001]

我的存储过程如下：
delimiter $$
create procedure sCode ()
begin
declare icode varchar(10);
declare cur_sCode cursor for (select symbol from quote group by symbol);
declare exit HANDLER for not found close cur_sCode;
open cur_sCode ;
repeat
fetch cur_sCode into icode;
create table icode (select * from quote where symbol=icode order by date desc);
until 0 end repeat;
close cur_sCode;
end;
$$
注意， create table icode (select * from quote where symbol=icode order by date desc);
这句话的意图是创建表，表名是变化的，icode取不同的值，创建不同的表，为何这个存储过程执行后，出现这样的问题
仅仅创建了一个表，表名就是icode?但是select * from quote where symbol=icode order by date desc这句话，执行的时候，确将icode的值正确赋值给了symbol,没有将icode这个字符串赋值给symbol,为何 create table icode没有将icode对应的值付给table名称，而是将icode赋值给table??

[fnzh001]

有问题，问题很大，打开所有的表发现都是空的。

[fnzh001]

估计问题出现在这里，已经重新改写，还是不行
1. set @sql = concat('create table ',icode,'( select * from quote where symbol=',''',icode,''',' order by date desc);');
可以运行，产生了新的表名，但是每个表都是空的
2.set @sql = concat('create table ',icode,'( select * from quote where symbol='',icode,''order by date desc);');
可以运行，产生了新的表名，但是每个表都是空的
请问，如何处理？

[fnzh001]

mysql> call sCode();
ERROR 1054 (42S22): Unknown column 'AACC' in 'where clause'
这个小毛病如何解决？

[ACMAIN_CHM]

delimiter $$
create procedure sCode ()
begin  
   declare icode varchar(10);
   declare cur_sCode cursor for (select symbol from quote group by symbol);
   declare exit HANDLER for not found close cur_sCode;
   open cur_sCode ;
       repeat
       fetch cur_sCode into icode;
       set @sql = concat('create table ',icode,' select * from quote where symbol=',icode,' order by date  desc');
       PREPARE stmt1 FROM @sql;
       EXECUTE stmt1 ;
       DEALLOCATE PREPARE stmt1;
    until 0 end repeat;
   close cur_sCode;
end;
$$

[ACMAIN_CHM]

MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

[fnzh001]

我自己通过测试解决了，
delimiter $$
create procedure sCode ()
begin
declare icode varchar(10);
declare cur_sCode cursor for (select symbol from quote group by symbol);
declare exit HANDLER for not found close cur_sCode;
open cur_sCode ;
repeat
fetch cur_sCode into icode;
set @sql = concat('create table ',icode,'( select * from quote where symbol=',''',icode,''',' order by date desc);');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
until 0 end repeat;
close cur_sCode;
end;
$$

[feixianxxx]

这个需要动态执行

否则的话 它不会吧icode当真变量的

[feixianxxx]

[Quote=引用 4 楼 fnzh001 的回复:]
我自己通过测试解决了，
delimiter $$
create procedure sCode ()
begin
declare icode varchar(10);
declare cur_sCode cursor for (select symbol from quote group by symbol);
declare exit HANDLER for not ……
[/Quote]

拼接的时候处理单引号

delimiter $$
create procedure sCode ()
begin
declare icode varchar(10);
declare cur_sCode cursor for (select symbol from quote group by symbol);
declare exit HANDLER for not found close cur_sCode;
open cur_sCode ;
repeat
fetch cur_sCode into icode;
set @sql = concat('create table ',icode,'( select * from quote where symbol=''',icode,''' order by date desc);');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
until 0 end repeat;
close cur_sCode;
end;
$$