我写了一个存储过程,
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;
select * from quote where symbol=icode order by close limit 5;
until 0 end repeat;
close cur_sCode;
end;
$$
输出如下:+--------+------------+------+------+------+-------+---------+------+---------+
| symbol | date | open | high | low | close | vol | adj | id |
+--------+------------+------+------+------+-------+---------+------+---------+
| DIVX | 2009-03-09 | 4.4 | 4.6 | 4.09 | 4.15 | 100300 | 4.15 | 2237676 |
| DIVX | 2008-11-21 | 4.09 | 4.5 | 3.87 | 4.2 | 573400 | 4.2 | 2237747 |
| DIVX | 2008-11-24 | 4.28 | 4.64 | 4.15 | 4.22 | 607300 | 4.22 | 2237746 |
| DIVX | 2008-12-04 | 4.5 | 4.61 | 4.14 | 4.23 | 276700 | 4.23 | 2237739 |
| DIVX | 2008-11-18 | 4.67 | 4.88 | 4.3 | 4.31 | 1560300 | 4.31 | 2237750 |
+--------+------------+------+------+------+-------+---------+------+---------++--------+------------+------+------+------+-------+------+------+---------+
| symbol | date | open | high | low | close | vol | adj | id |
+--------+------------+------+------+------+-------+------+------+---------+
| DJCO | 1992-03-03 | 8.25 | 8.25 | 8.25 | 8.25 | 100 | 8.25 | 2089063 |
| DJCO | 1992-05-21 | 8.75 | 8.75 | 8.75 | 8.75 | 200 | 8.75 | 2089046 |
| DJCO | 1992-05-18 | 8.75 | 8.75 | 8.75 | 8.75 | 100 | 8.75 | 2089047 |
| DJCO | 1992-05-14 | 8.75 | 8.75 | 8.75 | 8.75 | 100 | 8.75 | 2089049 |
| DJCO | 1992-04-22 | 8.75 | 8.75 | 8.75 | 8.75 | 200 | 8.75 | 2089054 |
+--------+------------+------+------+------+-------+------+------+---------+如何将这些输出结果拼成一个表呢?
我知道可以
select * from quote where symbol=' DIVX' order by close limit 5 union select * from quote where symbol='DJCO' order by close limit 5
但是,我的这个存储过程里面如何实现呢?
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;
select * from quote where symbol=icode order by close limit 5;
until 0 end repeat;
close cur_sCode;
end;
$$
输出如下:+--------+------------+------+------+------+-------+---------+------+---------+
| symbol | date | open | high | low | close | vol | adj | id |
+--------+------------+------+------+------+-------+---------+------+---------+
| DIVX | 2009-03-09 | 4.4 | 4.6 | 4.09 | 4.15 | 100300 | 4.15 | 2237676 |
| DIVX | 2008-11-21 | 4.09 | 4.5 | 3.87 | 4.2 | 573400 | 4.2 | 2237747 |
| DIVX | 2008-11-24 | 4.28 | 4.64 | 4.15 | 4.22 | 607300 | 4.22 | 2237746 |
| DIVX | 2008-12-04 | 4.5 | 4.61 | 4.14 | 4.23 | 276700 | 4.23 | 2237739 |
| DIVX | 2008-11-18 | 4.67 | 4.88 | 4.3 | 4.31 | 1560300 | 4.31 | 2237750 |
+--------+------------+------+------+------+-------+---------+------+---------++--------+------------+------+------+------+-------+------+------+---------+
| symbol | date | open | high | low | close | vol | adj | id |
+--------+------------+------+------+------+-------+------+------+---------+
| DJCO | 1992-03-03 | 8.25 | 8.25 | 8.25 | 8.25 | 100 | 8.25 | 2089063 |
| DJCO | 1992-05-21 | 8.75 | 8.75 | 8.75 | 8.75 | 200 | 8.75 | 2089046 |
| DJCO | 1992-05-18 | 8.75 | 8.75 | 8.75 | 8.75 | 100 | 8.75 | 2089047 |
| DJCO | 1992-05-14 | 8.75 | 8.75 | 8.75 | 8.75 | 100 | 8.75 | 2089049 |
| DJCO | 1992-04-22 | 8.75 | 8.75 | 8.75 | 8.75 | 200 | 8.75 | 2089054 |
+--------+------------+------+------+------+-------+------+------+---------+如何将这些输出结果拼成一个表呢?
我知道可以
select * from quote where symbol=' DIVX' order by close limit 5 union select * from quote where symbol='DJCO' order by close limit 5
但是,我的这个存储过程里面如何实现呢?
那只能通过输出后倒入一个表中实现 看下面--先建立一个表存储你的结果
create table xxx();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;
--这里先插入
insert xxx
select * from quote where symbol=icode order by close limit 5;
--然后显示
select * from quote where symbol=icode order by close limit 5;
until 0 end repeat;
close cur_sCode;
--这里再显示全部的记录
select * from xxx;
end;
$$