存储过程——mysql

CALL pr_build_kline_day ('t_quote_quote_NYMEX', 't_quote_kline_day_NYMEX');

drop procedure if exists pr_build_kline_min;

create procedure pr_build_kline_min
(
  in in_quote_table varchar(1024),
  in in_kline_table varchar(1024)
)
lablel:begin


#min,max
SET @sqlcmd = CONCAT('INSERT into ',in_kline_table,'(instrumentid, quotetime, lowestprice, highestprice) 
select instrumentid, substr(updatetime, 1, 16) as newtime, MIN(lastprice) as low, MAX(lastprice) as high 
from ',in_quote_table,' GROUP BY instrumentid, substr(updatetime, 1, 16)');
PREPARE temp FROM @sqlcmd;
EXECUTE temp;
DEALLOCATE PREPARE temp;


#open
SET @sqlcmd = CONCAT('UPDATE ',in_kline_table,' a INNER JOIN 
(select instrumentid, substr(updatetime, 1, 16) as newtime, lastprice from ',in_quote_table,'
where id in (select distinct min(id) as id from ',in_quote_table,' group by instrumentid, substr(updatetime, 1, 16))
) b 
on a.instrumentid = b.instrumentid and a.quotetime = b.newtime
set a.openprice = b.lastprice');
PREPARE temp FROM @sqlcmd;
EXECUTE temp;
DEALLOCATE PREPARE temp;


#close
SET @sqlcmd = CONCAT('UPDATE ',in_kline_table,' a INNER JOIN 
(select instrumentid, substr(updatetime, 1, 16) as newtime, lastprice from ',in_quote_table,'
where id in (select distinct max(id) as id from ',in_quote_table,' group by instrumentid, substr(updatetime, 1, 16))
) b 
on a.instrumentid = b.instrumentid and a.quotetime = b.newtime
set a.closeprice = b.lastprice');
PREPARE temp FROM @sqlcmd;
EXECUTE temp;
DEALLOCATE PREPARE temp;


#quotetime
SET @sqlcmd = CONCAT('UPDATE ',in_kline_table,' set quotetime = concat(quotetime, ":00")');
PREPARE temp FROM @sqlcmd;
EXECUTE temp;
DEALLOCATE PREPARE temp;


end;