我的存储过程如下:
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??
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??
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;
$$
ERROR 1054 (42S22): Unknown column 'AACC' in 'where clause'
这个小毛病如何解决?
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;
$$
拼接的时候处理单引号 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;
$$
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);');
可以运行,产生了新的表名,但是每个表都是空的
请问,如何处理?