create procedure mysp
(
starttime datetime,
endtime datetime,
str varchar(255)
)
beginDECLARE h INT DEFAULT 0;declare tname varchar(20);while h<TIMESTAMPDIFF(hour,endtime,starttime) doset tname=concat('vq'',''starttime'',''0000');SET @x = concat('SELECT * FROM `',tname,str);prepare s1 from @x; execute s1; deallocate prepare s1; set h=h+1;
set starttime:=DATE_ADD(starttime,Interval 1 hour);
select tname;
end while;end
需求:
1.传入3个参数,starttime为开始时间,endtime为结束时间,str为sql条件
2.根据开始时间和结束时间生成表名,表名为1小时生成1张;
3.在循环中执行sql,查询数据.问题:
上面的存储过程编译能过,但在call的时候没效果,大哥们,给点意见............
(
starttime datetime,
endtime datetime,
str varchar(255)
)
beginDECLARE h INT DEFAULT 0;declare tname varchar(20);while h<TIMESTAMPDIFF(hour,endtime,starttime) doset tname=concat('vq'',''starttime'',''0000');SET @x = concat('SELECT * FROM `',tname,str);prepare s1 from @x; execute s1; deallocate prepare s1; set h=h+1;
set starttime:=DATE_ADD(starttime,Interval 1 hour);
select tname;
end while;end
需求:
1.传入3个参数,starttime为开始时间,endtime为结束时间,str为sql条件
2.根据开始时间和结束时间生成表名,表名为1小时生成1张;
3.在循环中执行sql,查询数据.问题:
上面的存储过程编译能过,但在call的时候没效果,大哥们,给点意见............
rtp_start_time bigint(20) NOT NULL DEFAULT 0,
rtp_end_time bigint(20) NOT NULL DEFAULT 0,
src_ip bigint(20) NOT NULL DEFAULT 0,
dst_ip bigint(20) NOT NULL DEFAULT 0,
ynsrc_id bigint(20) NOT NULL DEFAULT 0,
src_port int(11) NOT NULL DEFAULT 0,
dst_port int(11) NOT NULL DEFAULT 0,
disorder int(11) NOT NULL DEFAULT 0,
hit float NOT NULL DEFAULT 0,
packetlossrate float NOT NULL DEFAULT 0,
avr_jitter float NOT NULL DEFAULT 0,
max_jitter_time bigint(20) NOT NULL DEFAULT 0,
max_jitter float NOT NULL DEFAULT 0,
mos double NOT NULL DEFAULT 0,
r double NOT NULL DEFAULT 0,
maxlossnum int(11) NOT NULL DEFAULT 0,
node_id int(11) NOT NULL DEFAULT 0,
direct int(11) NOT NULL DEFAULT 0,
flux bigint(20) NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
(
starttime datetime,
endtime datetime,
str varchar(255)
)
beginDECLARE h INT DEFAULT 0;
declare cnt int;
declare tbname varchar(20);
declare dt varchar(20);
declare ho varchar(2);set cnt=timestampdiff(hour,starttime,endtime);
while h <=cnt do
set dt = date_format(starttime,'%Y%m%d%H');
set tbname=concat('vq',dt,'0000'); select tbname;-- 先在这里显示一下表名
/* 当表名没问题.就去掉这里的注释.
set @x = concat('SELECT * FROM `',tname,'` ',str); prepare s1 from @x;
execute s1;
deallocate prepare s1;
*/ set h=h+1;
set starttime=timestampadd(hour,1,starttime);
end while;end$$