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.
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 ',@myname,'select * from quote where symbol=',@myname,order by date desc');
set @myname=icode;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
until 0 end repeat;
close cur_sCode;
end;
$$
编译无法通过
ERROR 1064 (42000): 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 'order by date desc');
set @myname=icode;
PREPARE stmt1 FROM @sql;
EXECUT' at line 9
mysql>
请问,究竟应该如何处理呢?请问,如何处理?
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.
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 ',@myname,'select * from quote where symbol=',@myname,order by date desc');
set @myname=icode;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
until 0 end repeat;
close cur_sCode;
end;
$$
编译无法通过
ERROR 1064 (42000): 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 'order by date desc');
set @myname=icode;
PREPARE stmt1 FROM @sql;
EXECUT' at line 9
mysql>
请问,究竟应该如何处理呢?请问,如何处理?
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);');
select @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
until 0 end repeat;
close cur_sCode;
end;
$$
ERROR 1064 (42000): 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 'CALL( select * from quote where symbol='CALL'order by date desc)' at line 1
我执行语句
mysql> select * from quote where symbol='CALL' order by date desc ;
+--------+------------+-------+------+-------+-------+-------+------+---------+
| symbol | date | open | high | low | close | vol | adj | id |
+--------+------------+-------+------+-------+-------+-------+------+---------+
| CALL | 2010-07-21 | 14.66 | 16.4 | 13.65 | 16.07 | 97000 | NULL | 8044255 |
+--------+------------+-------+------+-------+-------+-------+------+---------+
1 row in set (8.98 sec)
有结果,为何程序出错呢,检查半天,没有发现原因
set @sql = concat('create table ',icode,'( select * from quote where symbol=\'',icode,'\'order by date desc);');
\'',icode,'\'可否解释一下?
我自己归纳,`是用来表达转义的,,用来框住变量的
2.\'',icode,'\'
这里我就有点晕了。
总之,存储过程好样的,比使用连接好多了,我重新设计数据库后,用存储过程来做以前那个分组取最大n个值的操作,将5个半小时,缩短到(1 min 2.67 sec),吓人吧。
我爱死存储过程了。